Creating and Modifying Roles

When you create a role, you use the CREATE ROLE statement. Further, you need to have the CREATE ROLE system privilege to be able create roles in the database. You can either create a role as NOT IDENTIFIED, IDENTIFIED EXTERNALLY, IDENTIFIED BY PASSWORD, GLOBALLY, or USING PACKAGE. The default is NOT IDENTIFIED.

The general command used to create a role is as follows:

 CREATE ROLE rolename [NOT IDENTIFIED | IDENTIFIED {BY password | EXTERNALLY |GLOBALLY | USING package_name}] 

The role name is the name that you assign to the role and must be unique among all existing role names and usernames in the database. If you are working in a database that uses a multibyte character set, Oracle strongly suggests that the role name contain at least one single-byte character to make the encrypted role name and password combination more secure.

The IDENTIFIED BY clause specifies how the user must be authenticated before the role is enabled for the specific user to whom it was granted. The different ways that identification can be specified, if NOT IDENTIFIED is not used or inferred, are as follows:

• BY PASSWORD allows the role to be authenticated by the database based on a password.

• EXTERNALLY allows the role to be authenticated by the operating system, network, or any other external source.

• GLOBALLY allows the role to be authenticated by an enterprise directory service.

• USING package_name allows you to authenticate the user role by an application using a specific package.

Examples of creating the developers role with each of these options are as follows:

 CREATE ROLE developers; CREATE ROLE developers IDENTIFIED BY programmer; CREATE ROLE developers IDENTIFIED EXTERNALLY; CREATE ROLE developers IDENTIFIED GLOBALLY; CREATE ROLE developers IDENTIFIED USING sys.role_admin; 

Roles, when created, have no privileges associated with them; they are simply a named shell through which you can assign privileges to groups of users centrally. You have to explicitly GRANT privileges to the role or GRANT one role to another.

You can alter the authorization method for a role; however, to do this, you have to have the ALTER ANY ROLE system privilege or have been granted the role WITH ADMIN OPTION.

Granting Privileges to Roles

For roles to be useful, they need to have been granted privileges. The same privileges granted to individual users can be granted to roles, as well.

System Privileges

To grant system privileges and roles to other users and to other roles, you use the GRANT statement. The following privileges are required:

• To grant a system privilege to a role, the granting user must already have the same system privilege with the WITH ADMIN OPTION. Otherwise, the granting user must have the GRANT ANY PRIVILEGE privilege.

• To grant an already existing role that contains a system privilege to a role, the user doing the grant must either have been granted the role to be granted WITH ADMIN OPTION or must have been granted the GRANT ANY ROLE system privilege.

Object Privileges

To grant object privileges to roles, at least one of the following must be true:

• You own the object.

• You possess the GRANT ANY OBJECT PRIVILEGE system privilege. This privilege permits you to grant and revoke privileges on behalf of the owner of the object.

• The privilege that you are trying to grant must have been granted to you using WITH GRANT OPTION.

Column Privileges

You can grant INSERT, UPDATE, or REFERENCES privileges on any individual columns in a table to a role the same as you can to an individual user.

Application Roles

Application roles can only be enabled by authorized PL/SQL packages, and these roles are created with the USING package clause. Application developers do not need to secure a role by hard-coding passwords inside applications; rather, application roles can be used to specify which package is authorized to enable the role.

Now that we have created and modified roles, we need to start assigning them to users.