GET_CATALOG_EFFECTIVE_PERMISSIONS¶
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”.
Syntax
GET_CATALOG_EFFECTIVE_PERMISSIONS (
input_user_name : text
, input_database_name : text )
input_user_name
(mandatory): name of the user.input_database_name
(optional): ifnull
, the procedure returns all the elements over which the user has privileges. If notnull
, 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 asinput_user_name
.globaladmin
:true
ifinput_user_name
is an administrator. Otherwise,false
.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.null
if 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 areData source
,View
,Procedure
,Web service
orWidget
.dbadmin
,dbconnect
,dbcreate
,dbmetadata
,dberead
,dbwrite
anddbfile
: represent the privileges that can be granted over a database.elementmetadata
,elementread
,elementwrite
,elementinsert
,elementupdate
andelementdelete
: represent the privileges that can be granted over an element of a database, not a database.null
if this row represents a privilege granted over a database (e.g. not over an individual element).columnpermissions
: it is notnull
when 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.rowpermissions
:true
if the user has some row restriction that applies to the specific view.false
otherwise.custompermissions
:true
if the user has some custom policy that applies to the specific view.false
otherwise.
Privileges Required
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_name
is a username that does not exist.If the input parameter
input_database_name
is 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.