RelationalDBDesignRelationalDBDesign


Security Roles   «Prev  Next»
Lesson 4Granting privileges with a role
ObjectiveGrant privileges to a role and a role to a user.

Granting Privileges with role

Creating a role is only the first step in using roles. You also must assign privileges to the role, and then assign the role to one or more users.

Granting privileges to a role

You grant a privilege to a role like you grant a privilege to a user. The syntax for granting the INSERT privilege on the BID table to the BIDDER role, is:
GRANT SELECT ON BID TO BIDDER;

Granting a role to a user

You grant a role to a user like you grant a privilege to a user. The syntax for granting the role BIDDER to user BID1 is:
GRANT BIDDER TO BID1;
You can grant a role to another role. This allows you to build hierarchical systems of roles. However, you can not have circular grants. If you grant the BROWSER role to the BIDDER role, you can not also grant the BIDDER role to the BROWSER role.

Privilege domains

A user can have several roles at the same time. The roles then work together to create a privilege domain. The privilege domain is all of the privileges granted to a user by all of his or her roles.
For instance, if the BIDDER role allowed INSERT and UPDATE privileges on the BID table, and the BROWSER role only allowed SELECT on the BID table, a user with both the BIDDER role and the BROWSER role could SELECT, INSERT, and UPDATE the BID table. The following graphic illustrates the creation of this privilege domain:

Privileges
Green privileges granted with BROWSE role, Blue privileges granted with BID role

The next lesson is about default user roles. Exercise
Click the Exercise button to practice creating roles for the COIN database.
Granting Privileges with Role -Exercise