Creating Roles for Database Security in Oracle 19c

In Oracle 19c, roles remain a fundamental part of database security and are used to manage privileges efficiently. The basic process of working with roles in Oracle 19c follows the same three steps:

1. Create the role using CREATE ROLE:

           CREATE ROLE my_role;
         

2. Grant privileges to the role:

           GRANT SELECT, INSERT ON employees TO my_role;
         

3. Grant the role to a user:

           GRANT my_role TO user1;
         

Additional Considerations in Oracle 19c:

1. Secure Application Roles:
   • Oracle introduced secure application roles, which are roles that require PL/SQL logic to be enabled.
   • Example:

               CREATE ROLE app_role IDENTIFIED USING my_package;
             

2. Password-Protected Roles (Deprecated):
   • In Oracle 19c, password-protected roles (e.g., CREATE ROLE my_role IDENTIFIED BY password;) are deprecated.
3. Default Roles:
   • Roles can be set as default for users:

               ALTER USER user1 DEFAULT ROLE my_role;
             

4. System and Object Privileges in Roles:
   • Roles cannot contain privileges like SYSDBA or SYSOPER.
   • Roles can contain both system and object privileges.

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 following command describes the syntax for the role.

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.

1. Once you select Security Manager from the Oracle Enterprise Manager menu, log in as user SYS with the password SYS and then click OK.
2. The familiar Security Manager interface includes a selection for Roles in the left-hand list box. Click the plus sign to the left of the Roles entry to see the existing roles for the database.
3. The existing roles for the database are listed in the left-hand list box. Right-click the Roles entry to begin the process of creating a new role.
4. Click the Create menu choice.
5. The Create Role dialog allows you to create a role and assign privileges to the role. For now, just give the role the name of TEST_ROLE and click the Create button.
6. Security Manager lets you know the role was successfully created. Click the OK button to return to Security Manager.
7. When you return to Security Manager, you can see that a role called TEST_ROLE was added to the list of existing roles. This is the end of the simulation.

• Security Manager
  Security Manager allowed you to manage users and their privileges and roles. In previous versions of Oracle (namely 8i, 9i and 10g) most security operations were made through a tool called Oracle Security Manager. However, in Oracle Database 11g, Oracle Enterprise Manager contains all the tools to perform these tasks. Here is a screenshot of the Server tab of the Database Homepage. Note the section titled Security at the lower right hand side.

Oracle Enterprise Security Manager, provides centralized privilege management to make administration easier and increase your level of security. This lets you store and retrieve roles from Oracle Internet Directory.

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.