The stored procedure
GET_CATALOG_EFFECTIVE_PERMISSIONS returns the effective privileges granted to a user. It returns a row for each database and a row for each element over which the user has any privilege.
The procedure CATALOG_PERMISSIONS also returns information about privileges but the result is different:
“CATALOG_PERMISSIONS” returns the privileges and roles granted to each user/role.
“GET_CATALOG_EFFECTIVE_PERMISSIONS” returns the effective privileges that the user has over each element. This makes it easier to know the privileges the user has over a view.
For example, let us say you grant the role read to a user over the database “customer360”. “CATALOG_PERMISSIONS” will return one row indicating this privilege. “GET_CATALOG_EFFECTIVE_PERMISSIONS” will return one row per each element of “customer360”.
GET_CATALOG_EFFECTIVE_PERMISSIONS ( input_user_name : text , input_database_name : text )
input_user_name(mandatory): name of the user.
null, the procedure returns all the elements over which the user has privileges. If not
null, it returns the privileges of the user over the elements of this database.
The procedure returns one row per each element over which the user has a privilege. Each row has these fields:
username: name of the user. The value will always be the same as
input_user_nameis an administrator. Otherwise,
globaladmin: name of the view.
dbname: name of the database over which this privilege is granted.
elementname: name of the element of a database over which this privilege was granted.
nullif this privilege is granted over a database, not a single element.
elementype: type of element of a database over which this privilege is granted. The possible values are
dbfile: represent the privileges that can be granted over a database.
elementdelete: represent the privileges that can be granted over an element of a database, not a database.
nullif this row represents a privilege granted over a database (e.g. not over an individual element).
columnpermissions: it is not
nullwhen the row represents a privilege granted over a view that has column privileges. In this case, this field contains a comma-separated list of the fields that can be projected.
trueif the user has some row restriction that applies to the specific view.
trueif the user has some custom policy that applies to the specific view.
The result of this procedure changes depending on the privileges of the user that runs it:
Administrators: the procedure returns information about the privileges of any user.
Other users, including administrators of a database: the procedure only returns information about the privileges granted to the user that executes the procedure.
The procedure returns an error in any of these cases:
If the input parameter
input_user_nameis a username that does not exist.
If the input parameter
input_database_nameis a database that does not exist.
If a non-administrator user requests the privileges granted to another user.
If the input user is a Kerberos or LDAP user (i.e. a user not created on Virtual DataPort) and is not the user running the procedure. In this case, the procedure returns an error saying that the user does not exist.