The stored procedure
CATALOG_PERMISSIONS returns information about
the privileges and custom policies granted to a user and/or a role.
Each privilege is returned in a row. That is, there is one row for each privilege granted to a user or role over a database, a view or a stored procedure.
Any user can execute this procedure but the results are different depending on if the user is an administrator or not. Administrators can see the privileges granted to any user or role; non-administrators can only see the privileges granted to themselves or their roles.
This procedure does not return information about Kerberos or LDAP users (i.e. users not created on Virtual DataPort), unless is about the user running the stored procedure.
CATALOG_PERMISSIONS ( username_in : text , rolename_in : text )
username_in: name of the user for which you want to obtain its privileges.
rolename_in: name of the role for which you want to obtain its privileges.
If the user that executes the procedure is an administrator, consider the following:
- If both parameters are
NULL, the procedure returns all the privileges granted to all the users and roles. This is useful if you want to obtain all the privileges set in the Virtual DataPort server that you are connected to.
username_inis not, the procedure returns the privileges granted to a user and the privileges granted to the roles of the user.
rolename_inis not, the procedure returns the privileges granted to the role and also to the roles of this role.
- If both parameters have a value, the procedure returns the privileges
directly granted to
username_inand the privileges granted to the role
rolename_in. In this case,
username_inneeds to have the role
rolename_ingranted. Otherwise, the procedure fails.
If the user that executes the procedure is not an administrator, its behavior is similar with the following exceptions:
- The procedure does not return the privileges of other users.
NULL, it has to be the name of the user that is executing the procedure. Otherwise, the procedure returns an error.
NULL, the user that runs the procedure has to have this role granted directly or transitively. Otherwise, the procedure returns an error.
Each privilege or custom policy granted to a user/role is returned in a row.
The output schema has the following fields:
username: user that this privilege belongs to.
NULLif this row refers to a privilege granted to a role.
When the input parameter
NULL, it holds the user name. In the latter case, this field is
NULLfor the privileges granted to roles.
globaladmin: the possible values are:
NULLif the row represents a privilege granted to a role, not a user.
trueif the row represents a privilege granted to a user (not a role) and the user is an administrator or has the role
userrolename: when this field is not
NULL, it means that the row represents a privilege inherited from the role with this value.
If the field
NULL, the row represents a privilege of a user inherited from a role.
If the field
NULL, the row represents a privilege of a role inherited from another role.
This field is
NULLif the privilege was directly granted to the user or the role.
rolename: a privilege can be inherited “transitively”. That is, a user can inherit a privilege from a role which itself inherits it from another role. This column contains the original role for which the permission was granted.
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.
elementtype: type of element of a database over which this privilege is granted. The possible values are:
dbwrite: represent the privileges that can be granted over a database.
NULLif this row represents a privilege not granted over a database (e.g. over an individual element).
viewdelete: represent the privileges granted over a view of a database. Their value can be
columnpermissions: it is not
NULLwhen the row represents a privilege over a view or a procedure of a database and the view or procedure has column privileges. In this case, this field contains a comma-separated list of the fields that can be projected.
rowpermissions: it is not
NULLwhen the row represents a privilege over a view or procedure of a database and the view or procedure has row restrictions. In this case, this field is an array with the following fields:
sensitivefields: the sensitive fields of the restriction.
condition: the condition of the restriction.
action: the action taken by the restriction when the condition is not met.
custompermissions: it is not
NULLwhen the array represents a list of custom policies granted to a user/role, over a view.
The information returned by this procedure changes depending on the type of user that executes the procedure:
- Administrators: the procedure returns information about the privileges of any user or role.
- Other users, including administrators of a database: the procedure only returns information about the privileges granted to the user that executes the procedure, or to the roles granted to this user.
The procedure returns an error in any of these cases:
- If a non-administrator user requests the privileges granted to another user.
- Or if a non-administrator user requests the privileges granted to a role and this user does not have this role.
- Or 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.