Roles 

A role is a group of privileges that can be granted to database users, user groups, or to other roles.

While privileges always apply, roles must be explicitly activated for each individual database session. Every user to whom roles have been assigned can also define which of the roles should be active in each database session. This definition can be changed after a database session has been opened.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Creating a Role

Open a database session for the database administrator MONA and use the CREATE ROLE statement to create a role.

 

CREATE ROLE role_1

//

CREATE ROLE role_2

//

CREATE ROLE role_3

The roles ROLE_1, ROLE_2, and ROLE_3 are created.

 

See also:

SQL Reference Manual, CREATE ROLE Statement (create_role_statement)

 

Granting Privileges

Open a database session for the database administrator MONA and use the GRANT statement to grant privileges.

 

GRANT SELECT, UPDATE, INSERT ON hotel.city TO role_1

The database administrator MONA manages the city table and decides that the SELECT, UPDATE, and INSERT authorizations are granted to ROLE_1 for the city table.

 

GRANT DELETE ON hotel.city TO role_2

The DELETE authorization is granted to ROLE_2 for the city table.

 

GRANT DELETE, ALTER ON hotel.city TO role_3

The DELETE and ALTER authorizations are granted to ROLE_3 for the city table.

 

See also:

SQL Reference Manual, GRANT Statement (grant_statement)

 

Granting a Role

Create the database users DAVID and BORIS as described in Database Users and Their Privileges.

Open a database session for the database administrator MONA and use the GRANT statement to grant the desired roles to a different database user.

 

GRANT role_1 TO david

//

GRANT role_2 TO david

//

GRANT role_3 to boris

ROLE_1 and ROLE_2 are granted to the database user DAVID.

The role ROLE_3 is assigned to the database user BORIS.

 

Defining Which Role is to Be Used

Open a database session for the database administrator MONA. Use the ALTER USER statement to define which of the roles that are granted to a database user are to be used when a database session is opened.

 

ALTER USER david DEFAULT ROLE role_1

ALTER USER boris DEFAULT ROLE role_3

ROLE_1 is activated for database user DAVID when he opens a database session, for BORIS it is ROLE_3.

See also:

SQL Reference Manual, ALTER USER Statement (alter_user_statement)

 

During a database session, you can use the SET statement to activate additional roles granted to a database user.

Open a database session for the database user DAVID.

 

SET ROLE ALL

All roles (ROLE_1 and ROLE_2) are to be activated.

See also:

SQL Reference Manual, SET Statement (set_statement)

 

Revoking Privileges

Open a database session for the database administrator MONA and use the REVOKE statement to revoke privileges.

 

REVOKE DELETE ON hotel.city FROM ROLE_3

ROLE_3 revokes the DELETE privilege.

See also:

SQL Reference Manual, REVOKE Statement (revoke_statement)

Evaluating System Tables, ROLES, SESSION_ROLES, ROLEPRIVILEGES

 

Deleting Roles

Open a database session for the database administrator MONA and use the DROP ROLE statement to delete roles.

 

DROP ROLE ROLE_3

ROLE_3 is deleted.

See also:

Reference Manual, DROP ROLE Statement (drop_role_statement)

More examples for Authorization