Privileges and access functions

• 1: ENABLED_ROLE
• 2: GET_PRIVILEGES_DESCRIPTION
• 3: HAS_ROLE
• 4: RELEASE_SYSTEM_TABLES_ACCESS
• 5: RESTRICT_SYSTEM_TABLES_ACCESS

1 - ENABLED_ROLE

Checks whether a Vertica user role is enabled, and returns true or false. This function is typically used when you create access policies on database roles.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

ENABLED_ROLE ( 'role' )

Parameters

role

The role to evaluate.

Privileges

None

Examples

See:

• Creating column access policies
• Creating row access policies

See also

2 - GET_PRIVILEGES_DESCRIPTION

Returns the effective privileges the current user has on an object, including explicit, implicit, inherited, and role-based privileges.

Because this meta-function only returns effective privileges, GET_PRIVILEGES_DESCRIPTION only returns privileges with fully-satisfied prerequisites. For a list of prerequisites for common operations, see Privileges required for common database operations.

For example, a user must have the following privileges to query a table:

• Schema: USAGE

• Table: SELECT

If user Brooke has SELECT privileges on table s1.t1 but lacks USAGE privileges on schema s1, Brooke cannot query the table, and GET_PRIVILEGES_DESCRIPTION does not return SELECT as a privilege for the table.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

GET_PRIVILEGES_DESCRIPTION( 'type', '[[database.]schema.]name' );

Parameters

type

Specifies an object type, one of the following:

• database

• table

• schema

• view

• sequence

• model

• library

• resource pool

[database.]schema

Specifies a database and schema, by default the current database and public, respectively.

name

Name of the target object

Privileges

None

Examples

In the following example, user Glenn has set the REPORTER role and wants to check his effective privileges on schema s1 and table s1.articles.

• Table s1.articles inherits privileges from its schema (s1).

• The REPORTER role has the following privileges:

  • SELECT on schema s1

  • INSERT WITH GRANT OPTION on table s1.articles

• User Glenn has the following privileges:

  • UPDATE and USAGE on schema s1.

  • DELETE on table s1.articles.

GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on schema s1:

=> SELECT GET_PRIVILEGES_DESCRIPTION('schema', 's1');
   GET_PRIVILEGES_DESCRIPTION
--------------------------------
 SELECT, UPDATE, USAGE
(1 row)

GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on table s1.articles:

=> SELECT GET_PRIVILEGES_DESCRIPTION('table', 's1.articles');
   GET_PRIVILEGES_DESCRIPTION
--------------------------------
 INSERT*, SELECT, UPDATE, DELETE
(1 row)

See also

• Database users and privileges
• Database roles
• Granting and revoking privileges

3 - HAS_ROLE

Checks whether a Vertica user role is granted to the specified user or role, and returns true or false.

You can also query system tables ROLES, GRANTS, and USERS to obtain information on users and their role assignments. For details, see Viewing user roles.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

HAS_ROLE( [ 'grantee' ,] 'verify‑role' );

Parameters

grantee

Valid only for superusers, specifies the name of a user or role to look up. If this argument is omitted, the function uses the current user name ( CURRENT_USER). If you specify a role, Vertica checks whether this role is granted to the role specified in verify‑role.

Important

If a non-superuser supplies this argument, Vertica returns an error.

verify‑role

Name of the role to verify for grantee.

Privileges

None

Examples

In the following example, a dbadmin user checks whether user MikeL is assigned the admnistrator role:

=> \c
You are now connected as user "dbadmin".
=> SELECT HAS_ROLE('MikeL', 'administrator');
 HAS_ROLE
----------
 t
(1 row)

User MikeL checks whether he has the regional_manager role:

=> \c - MikeL
You are now connected as user "MikeL".
=> SELECT HAS_ROLE('regional_manager');
 HAS_ROLE
----------
 f
(1 row)

The dbadmin grants the regional_manager role to the administrator role. On checking again, MikeL verifies that he now has the regional_manager role:

dbadmin=> \c
You are now connected as user "dbadmin".
dbadmin=> GRANT regional_manager to administrator;
GRANT ROLE
dbadmin=> \c - MikeL
You are now connected as user "MikeL".
dbadmin=> SELECT HAS_ROLE('regional_manager');
 HAS_ROLE
----------
 t
(1 row)

See also

• GRANTS
• ROLES
• USERS
• Database users and privileges

4 - RELEASE_SYSTEM_TABLES_ACCESS

Enables non-superuser access to all system tables. After you call this function, Vertica ignores the IS_ACCESSIBLE_DURING_LOCKDOWN setting in table SYSTEM_TABLES. To resume enforcement of access restrictions on non-superusers to system tables, call RESTRICT_SYSTEM_TABLES_ACCESS.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

RELEASE_SYSTEM_TABLES_ACCESS()

Privileges

Superuser

5 - RESTRICT_SYSTEM_TABLES_ACCESS

Checks system table SYSTEM_TABLES to determine which system tables non-superusers can access. Non-superuser access to each system table is specified by the Boolean column IS_ACCESSIBLE_DURING_LOCKDOWN. When you call this function, Vertica checks the IS_ACCESSIBLE_DURING_LOCKDOWN setting on each system table, and enforces access on all non-superuser accordingly.

By default, Vertica enforces IS_ACCESSIBLE_DURING_LOCKDOWN settings. To enable non-superuser access to all system tables, you must explicitly call RELEASE_SYSTEM_TABLES_ACCESS.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

RESTRICT_SYSTEM_TABLES_ACCESS()

Privileges

Superuser