8.2 How do I…Create a role?Problem
In my application, I have groups of users who perform related functions. The number of people using my application is very large and I don’t want to grant privileges on all the database objects to so many people. How do I create a role to represent a group of users?
Technique
Database roles should be created to represent related groups of privileges within the database. The CREATE ROLE statement is used to create a new role, the syntax of which is shown in Figure 8.2. The IDENTIFIED BY clause defines a password for the role. When a role contains a password, the password must be supplied when enabling the role. When a new role is created, it has no privileges, which must be granted to the role in order for the role to have any effect on a user account. The CONNECT, RESOURCE, and DBA roles are provided by Oracle to supply different levels of access. User accounts with the CONNECT role have access to the database but cannot create their own objects. They can access other user account’s objects to which they have been granted permission. User accounts with the RESOURCE role can create their own database objects. The DBA role gives the user account powerful privileges: complete access to the database and the capability to grant privileges to other user accounts.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Load CHP8_5.SQL into the SQL buffer and run it, as shown in Figure 8.5.
The file contains a CREATE ROLE statement to create a new role and the CREATE ROLE keywords are required. A name must be specified for the role when it is created. After the role has been created, it must be granted privileges to be of any use. How-To 8.1 covers the processes of granting privileges to roles.
2. Load CHP8_6.SQL into the SQL buffer and run it, as shown in Figure 8.6. The file contains a CREATE ROLE statement, which creates a new role protected with a password.
Line 1 contains the CREATE ROLE keywords to create a new role and specify the name of the role. The IDENTIFIED BY clause in line 2 specifies an optional password that must be provided when the role is enabled.
3. Load CHP8_7.SQL into the SQL buffer and run it, as shown in Figure 8.7. The file contains a DROP ROLE statement to remove the role created in Steps 1 and 2.
The DROP ROLE keywords presented in line 1 are required to remove a role from the database. The role to be removed must be specified in the statement. When one is removed, all user accounts that were granted the role immediately lose their privileges.
How It Works
The CREATE ROLE statement is used to create a new role in the database and Step 1 creates a new role using the CREATE ROLE statement. Step 2 creates a role containing a password that must be specified when the role is enabled. The IDENTIFIED BY clause creates a password for a role. Step 3 presents the DROP ROLE statement, which is used to remove an existing role. When a role is removed, the privileges provided by the role are removed immediately from the user account granted the role privileges.
Comments
Roles play a major part in the development of an organizational security model. In the early releases of Oracle, the CONNECT, RESOURCE, and DBA roles were used to administer security to user accounts. Access to tables and views was granted to each user account individually or to the PUBLIC user group, which enabled all user accounts access to the data. Roles increase the security capabilities of Oracle and reduce the maintenance required by the system administrator. A security model should be developed using roles that effectively protect your database.