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.
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.
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.
SQL Reference Manual, CREATE ROLE Statement (create_role_statement)
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.
SQL Reference Manual, GRANT Statement (grant_statement)
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.
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.
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.
SQL Reference Manual, SET Statement (set_statement)
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.
SQL Reference Manual, REVOKE Statement (revoke_statement)
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.
Reference Manual, DROP ROLE Statement (drop_role_statement)
More examples for Authorization