Background documentationRoles Locate this document in the navigation structure

 

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 can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

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 for the CITY table are granted to ROLE_1.

GRANT DELETE ON hotel.city TO role_2

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

GRANT DELETE, ALTER ON hotel.city TO role_3

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

See also: SQL Reference Manual, GRANT Statement (grant_statement)

Assigning 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 assign 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 assigned 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

The DELETE privilege is revoked from ROLE_3.

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: SQL Reference Manual, DROP ROLE Statement (drop_role_statement)

More Information

More examples for Authorization