Granting Access to Database Roles

A pseudosuperuser or dbadmin user can assign any role to a user or to another role using the GRANT command. The simplest form of this command is:

GRANT role [, ...] TO { user | role } [, ...]

Vertica returns a NOTICE if you grant a role to a user who has already been granted that role. For example:

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

See GRANT (Role) in the SQL Reference Manual for details.

Example

The following example shows how to create a role called commenter and grant that role to user Bob:

  1. Create a table called comments.

    => CREATE TABLE comments (id INT, comment VARCHAR);
  2. Create a role called commenter.

    => CREATE ROLE commenter;
  3. Grant privileges to the commenter role on the comments table.

    => GRANT INSERT, SELECT ON comments TO commenter;
  4. Grant the commenter role to user Bob.

    => GRANT commenter TO Bob;

Before being able to access the role and its associated privileges, Bob must enable the newly-granted role to himself.

  1. Connect to the database as user Bob.

    => \c - Bob
  2. Enable the role.

    => SET ROLE commenter;
  3. Insert some values into the comments table.

    => INSERT INTO comments VALUES (1, 'Hello World');

    Based on the privileges granted to Bob by the commenter role, Bob can insert and query the comments table.

  4. Query the comments table.

    => SELECT * FROM comments; 
     id |   comment
    ----+-------------
      1 | Hello World
    (1 row)
    
  5. Commit the transaction.

    => COMMIT;

Note that Bob does not have proper permissions to drop the table.

=> DROP TABLE comments;ROLLBACK 4000:  Must be owner of relation comments