8.3 How do I…Grant roles to users?Problem
I have created user accounts and roles within my database. Each of my roles have differing system and object privileges. I need to assign users to my various roles. How do I grant roles to a user account?
Technique
User accounts are assigned to roles with the GRANT command. Both system and object privileges can be granted to user accounts and roles with the GRANT statement. This is described in How-To 8.1.
One or more privileges or roles can be granted to one or more user accounts with a single statement. The WITH ADMIN OPTION clause specifies that the grantee can grant this role to another user account or role.
By assigning unique sets of privileges to different roles, you can easily start to make a secure and easily managed environment for your applications. Instead of issuing GRANT commands to dozens or even thousands of users, you can assign them all to a role and issue a single GRANT command to that role. All users assigned to the role will then obtain that privilege.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Run CHP8_8.SQL, shown in Figure 8.8, to create the sample user account and the role that will be used throughout this How-To.
The CREATE USER statement creates the SMITH user account, which is granted privileges in the following steps. The CREATE ROLE statement also creates the GENERAL role, which is granted privileges and granted to the user account. The DEPT8 table is created to have an object that can be granted privileges in the sample statements. Run the SQL script to create the objects.
2. Load CHP8_9.SQL into the SQL buffer and run it, as shown in Figure 8.9. The GRANT statement contained in the file grants CREATE SESSION and ALTER SESSION privileges to the sample role.
Line 1 contains the GRANT keyword, used to grant privileges to a user account or role. The CREATE SESSION and ALTER SESSION system privileges are granted to the GENERAL role, which was created in Step 1. Any user account granted this role can connect to the database after this statement is executed.
3. Load CHP8_10.SQL into the SQL buffer and run it, as shown in Figure 8.10. The GRANT statement contained in the file grants SELECT, INSERT, UPDATE, and DELETE privileges on the DEPT8 table created in step 1 to the TECHIE role created in step 1.
Line 1 contains the GRANT keyword and specifies that SELECT, INSERT, UPDATE, and DELETE privileges are granted on the object. Line 2 specifies that the privileges are granted on the DEPT8 table created in step 1. Line 3 then specifies that the GENERAL role is the recipient of the privileges.
4. Load CHP8_11.SQL into the SQL buffer and run it, as shown in Figure 8.11. The GRANT statement contained in the file grants the GENERAL role to the SMITH user account.
This statement grants the GENERAL role created in Step 1 to the SMITH user account created in Step 1. When the statement is executed, any privileges granted to the GENERAL role are available to the SMITH user account.
How It Works
The GRANT statement is used to give privileges to user accounts or roles. Object or system privileges can be granted to user accounts or roles, and role privileges can be granted to them as well. Step 1 creates a user account, role, and table, which are used throughout this How-To. Step 2 grants the GENERAL role CREATE SESSION and ALTER SESSION privileges. User accounts granted this role can connect to the database. Step 3 grants SELECT, INSERT, UPDATE, and DELETE privileges to the GENERAL role for the DEPT8 table. Step 4 grants the GENERAL role to the SMITH user account. The SMITH user account inherits all system and object privileges that are granted to the GENERAL role.
Comments
As stated previously, privileges can be granted to a user account or role. It is much easier to create roles and grant each privilege once to the role than to each user account individually. If a database object is removed and then re-created, all grants to the object must be re-created. If roles were not in use, then it would be an arduous task to determine and re-create all privileges to each user account.