Controlling Privileges Through Roles

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 19.  Managing Database Security


graphics/newterm_icon.gif

A role is an object created in the database that contains group-like privileges. Roles can reduce security maintenance by not having to grant explicit privileges directly to a user. Group privilege management is much easier to handle with roles. A role's privileges can be changed, and such a change is transparent to the user .

If a user needs SELECT and UPDATE table privileges on a table at a specified time within an application, a role with those privileges can temporarily be assigned until the transaction is complete.

When a role is first created, it has no real value other than being a role within a database. It can be granted to users or other roles. Let's say that a schema named APP01 grants the SELECT table privilege to the RECORDS_CLERK role on the EMPLOYEE_PAY table. Any user or role granted the RECORDS_CLERK role now would have SELECT privileges on the EMPLOYEE_PAY table.

Likewise, if APP01 revoked the SELECT table privilege from the RECORDS_CLERK role on the EMPLOYEE_PAY table, any user or role granted the RECORDS_CLERK role would no longer have SELECT privileges on that table.

graphics/note_icon.gif

Roles are not supported by MySQL. The lack of role usage is a weakness in some implementations of SQL.


The CREATE ROLE Statement

A role is created with the CREATE ROLE statement.

 graphics/syntax_icon.gif CREATE ROLE  role_name;  

Granting privileges to roles is the same as granting privileges to a user. Study the following example.

 graphics/mysql_icon.gif graphics/input_icon.gif  CREATE ROLE RECORDS_CLERK;  graphics/output_icon.gif Role created. graphics/mysql_icon.gif graphics/input_icon.gif  GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE_PAY TO RECORDS_CLERK;  graphics/output_icon.gif Grant succeeded. graphics/mysql_icon.gif graphics/input_icon.gif  GRANT RECORDS_CLERK TO USER1;  graphics/output_icon.gif Grant succeeded. 

The DROP ROLE Statement

A role is dropped using the DROP_ROLE statement.

 graphics/syntax_icon.gif DROP ROLE role_name; 

The following is an example:

 graphics/mysql_icon.gif graphics/input_icon.gif  DROP ROLE RECORDS_CLERK;  graphics/output_icon.gif Role dropped. 

The SET ROLE Statement

A role can be set for a user SQL session using the SET_ROLE statement.

 graphics/syntax_icon.gif SET ROLE role_name; 

The following is an example:

 graphics/input_icon.gif  SET ROLE RECORDS_CLERK;  graphics/output_icon.gif Role set. 

You can set more than one role at once:

 graphics/mysql_icon.gif graphics/input_icon.gif  SET ROLE RECORDS_CLERK, ROLE2, ROLE3;  graphics/output_icon.gif Role set. 

In some implementations, such as Oracle, all roles granted to a user are automatically default roles, which means the roles will be set and available to the user as soon as the user logs in to the database.


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net