Security Roles   «Prev  Next»

Lesson 3 Creating roles
Objective Create DBA role.

Creating Roles for Database Security

As a database administrator, you can appreciate the power of roles in implementing database security. There are three steps involved in using roles:
  1. Creating the role
  2. Granting privileges to the role
  3. Granting the role to users
You create a role by using the SQL command CREATE ROLE. The syntax for the role is described in the following MouseOver. 

CREATE ROLE rolename
  1. The CREATE ROLE keywords are required.
  2. The rolename is an identifying name for the role.

SQL>  Create role select_data_role;
SQL> Grant select on emp to select_data_role;
SQL> Grant select on dept to select_data_role;
SQL> Grant select on bonus to select_data_role;

The name of the role must be unique within the database.

Creating a role with Security Manager

Creating Security-manager Role.

Creating Role

In addition to the default roles shown earlier, you can create your own roles within Oracle. The roles you create can comprise table or system privileges or a combination of both. In the following sections, you will see how to create and administer roles. To create a role, you need to have the CREATE ROLE system privilege. The syntax for role creation is shown in the following listing: 

create role role_name
[not identified
|identified {by password | using [schema.]package
|externally | globally }];

When a role is first created, it has no privileges associated with it. Two sample create role commands are shown in the following example: 
create role CLERK;
create role MANAGER;

The first command creates a role called CLERK, which will be used in the examples in the following sections.
The second command creates a role called MANAGER, which will also be featured in those examples.
The next lesson shows how to grant privileges to a role and grant a role to a user.