GRANT (Role)

Adds a predefined role to users or other roles. Granting a role does not activate the role automatically; the user must enable it using the SET ROLE command.

Granting a privilege to a role immediately affects active user sessions. When you grant a new privilege, it becomes immediately available to every user with the role active.

Syntax

GRANT role [,...] TO { user | role | PUBLIC } [, ...]
... [ WITH ADMIN OPTION ];

Parameters

role [,...]

The name of one or more roles to be granted to users or roles

 
user | role | PUBLIC

The name of a user or other role to be granted the role. If the keyword PUBLIC is supplied, then all users have access to the role.

 
WITH ADMIN OPTION

Grants users and roles administrative privileges for the role. They are able to grant the role to and revoke the role from other users or roles.

Notes

Vertica will return a NOTICE if you grant a role with or without admin option, to a grantee who has already been granted that role. For example:

=> GRANT commentor to Bob;
NOTICE 4622:  Role "commentor" was already granted to user "Bob"

Creating Roles

These examples create three roles, appdata, applogs, and appadmin, and grant one of the roles to a user, bob: 

=> CREATE ROLE appdata;
CREATE ROLE
=> CREATE ROLE applogs;
CREATE ROLE
=> CREATE ROLE appadmin;
CREATE ROLE
=> GRANT appdata TO bob;
GRANT ROLE

Activating a Role

After granting a role to a user, the role must be activated. You can activate a role on a session basis, or as part of the user's login.

To activate a role for a user's session: 

=> CREATE ROLE appdata;
CREATE ROLE
=> GRANT appdata TO bob;
GRANT ROLE
=> SET ROLE appdata; 
SET ROLE

To activate a role as part of the user's login: 

=> CREATE ROLE appdata;
CREATE ROLE
=> GRANT appdata TO bob;
GRANT ROLE
=> ALTER USER bob DEFAULT ROLE appdata;
ALTER USER

Granting One Role To Another

Grant two roles to another role:

=> GRANT appdata, applogs TO appadmin;
 -- grant to other roles
GRANT ROLE

Now, any privileges assigned to either appdata or applogs are automatically assigned to appadmin as well.

Checking for Circular References

When you grant one role to another role, Vertica combines the newly granted role's permissions with the existing role's permissions. Vertica also checks for circular references when you grant one role to another. The GRANT ROLE function fails with an error if a circular reference is found. 

=> GRANT appadmin TO appdata;
WARNING:  Circular assignation of roles is not allowed
HINT:  Cannot grant appadmin to appdata
GRANT ROLE

Granting Administrative Privileges

A superuser can assign a user or role administrative access to a role by supplying the optional WITH ADMIN OPTION argument to the GRANT statement. Administrative access allows the user to grant and revoke access to the role for other users (including granting them administrative access). Giving users the ability to grant roles lets a superuser delegate role administration to other users.

Note: A user with a DBADMIN role must have the ADMIN OPTION enabled to be able to grant a DBADMIN role to another user.

As with all user privilege models, database superusers should be cautious when granting any user a role with administrative privileges. For example, if the database superuser grants two users a role with administrative privileges, both users can revoke the role of the other user. This example shows granting the appadmin role (with administrative privileges) to users bob and alice. After each user has been granted the appadmin role, either use can connect as the other will full privileges.

=> GRANT appadmin TO bob, alice WITH ADMIN OPTION;
GRANT ROLE
=> \connect - bob
You are now connected as user "bob".
=> REVOKE appadmin FROM alice;
REVOKE ROLE

See Also