USER MANUALS

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): if 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_name.

  • globaladmin: true if input_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 are Data source, View, Procedure, Web service or Widget.

  • dbadmin, dbconnect, dbcreate, dbmetadata, dberead, dbwrite and dbfile: represent the privileges that can be granted over a database.

  • elementmetadata, elementread, elementwrite, elementinsert, elementupdate and elementdelete: 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 not null 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:

  1. If the input parameter input_user_name is a username that does not exist.

  2. If the input parameter input_database_name is a database that does not exist.

  3. If a non-administrator user requests the privileges granted to another user.

  4. 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.

Add feedback