Creating Roles

Creating Roles

Use the following command to create a role:

SQL> CREATE ROLE role_name [ NOT IDENTIFIED | IDENTIFIED
{ BY password | EXTERNALLY | GLOBALLY | USING package }]
where :
NOT IDENTIFIED = Indicates that no verification is required when enabling the role.
IDENTIFIED = Indicates that verification is required when enabling the role.
BY PASSWORD = Provides the password that the user required when enabling the role.
EXTERNALLY = Indicates that user must be authorized by an external service such as operating
system service.
USING package = Creates an application role, which is a role that can be enabled only by
application using as authorized package.
GLOBALLY = Indicates that a user must be authorized to use the role by the enterprise
directory service before the role is enabled with the SET ROLE statement.
For Example :
SQL> CREATE ROLE oe_clerk ;
SQL> CREATE ROLE hr_clerk IDENTIFIED BY bonus ;
SQL> CREATE ROLE hr_clerk IDENTIFIED EXTERNALLY ;
Modifying Roles
Use the following command to modify a role:
SQL> ALTER ROLE role_name [ NOT IDENTIFIED | IDENTIFIED
{ BY password | EXTERNALLY | GLOBALLY | USING package }]
For Example :
SQL> ALTER ROLE hr_clerk IDENTIFIED BY order ;
SQL> ALTER ROLE hr_clerk NOT IDENTIFIED ;
SQL> ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY ;
Assigning Roles
To grant a role to user, following syntax is used:
SQL> GRANT [role1,role2,role_n] TO {user | role | PUBLIC }
[ WITH ADMIN OPTION ] ;
where :

role = Is a collection of role to be granted.
PUBLIC = Grants the role to all users.
WITH ADMIN OPTION = Enables the grantee to grant the role to other users or role.
For Example :
SQL> GRANT oe_clerk TO scott ;
SQL> GRANT hr_clerk TO hr_manager ;
SQL> GRANT hr_manager TO scott WITH GRANT OPTION ;
NOTE:- The maximum number of database roles that users can enable is set by the
MAX_ENABLED_ROLES initialization parameter.
Establishing Default Roles
SQL> ALTER USER scott DEFAULT ROLE hr_clerk , oe_clerk ;
SQL> ALTER USER scott DEFAULT ROLE ALL ;
SQL> ALTER USER scott DEFAULT ROLE NONE ;
SQL> ALTER USER scott DEFAULT ROLE ALL EXCEPT hr_clerk ;
Revoking Roles
SQL> REVOKE hr_clerk FROM scott ;
SQL> REVOKE hr_clerk FROM PUBLIC ;
PUBLIC keyword revokes the privileges or roles from all users;
Dropping Roles
SQL> DROP ROLE role_name ;