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.
This procedure does not return the privileges of users that only exist in the Active Directory or the Identity Provider of the organization (i.e. the users is not created in Virtual DataPort), unless the input username is the same username running the query.
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.
For example, let us say you grant the role read to a user over a database. “CATALOG_PERMISSIONS” will return one row indicating this privilege. “GET_CATALOG_EFFECTIVE_PERMISSIONS” will return one row per each element of this database.
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,
dbname: name of the database over which this privilege is granted.
elementname: name of the element over which this privilege is granted.
nullif this privilege is granted over a database, not a single element.
elementtype: type of element of a database over which this privilege is granted. The possible values are
nullif the element type is not equals to
Data source. Otherwise, type of the data source. 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 this row represents a privilege over a view and the user has a row restriction over this view (Global Security Policies are also checked).
trueif this row represents a privilege over a view and the user has a custom policy applied over this view.
custompermissions are not affected by the configuration property:
This means that even the value of this columns are
false some restrictions could be applied to the elements if the property is set to
The section Column Privileges explains the meaning of this property.
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.