8.4 How do I…Enable and disable roles at runtime?Problem
Within my application, I control the information the users can view. With the availability of easy-to-use ad hoc query tools, the users of my applications can query and modify data from the database, but I cannot remove their user accounts because they are valid users of the application. How do I restrict access by enabling and disabling roles at runtime?
Technique
Password-protected roles are the key to restricting the access of ad hoc query tools. Access can be restricted at two levels. Ad hoc tools can be restricted from modifying data or totally restricted from viewing data. Applications using the tables must embed a SET ROLE statement or call the DBMS_SESSION.SET_ROLE procedure after connecting to the database. The password of the role must also be kept secret from the users of the application. Without the password of the role and the method for enabling it, users will have restricted access to the data.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Run the CHP8_12.SQL script, shown in Figure 8.12, to create a sample table and users that will be restricted by the technique presented in this How-To.
2. Create the roles that control access to the data by running CHP8_13.SQL, as shown in Figure 8.13.
3. The CHP8_13.SQL script assumes that ad hoc query tools are only restricted from modifying data, and so it creates two roles. The first is a default role with SELECT privileges in which users can query information without enabling additional roles. A second role provides all the privileges required to use the approved applications. The second role is a non-default, password-protected role, which must be enabled by the application to be used. If ad hoc query tools are totally restricted from the data, only create the password-protected role.
4. Grant privileges on the database objects to the roles by running CHP8_14.SQL, as shown in Figure 8.14.
5. Now, grant the role privileges to the users of the application. In the example, MARY is the only user of the application. If two roles are created in Step 2, grant both roles to the users.
SQL> GRANT SELECT_ROLE, UPDATE_ROLE TO MARY;
Grant succeeded.
6. Remove the role with all privileges from the user account’s default roles by running CHP8_15.SQL. This forces the user to enable the role in order to use it.
SQL> GET CHP8_15.sql
1 ALTER USER MARY
DEFAULT ROLE ALL EXCEPT UPDATE_ROLE;
User altered.
SQL>
7. Now insert program code to enable the role into applications capable of modifying data. The technique used to enable password-protected roles at runtime is to call the DBMS_SESSION.SET_ROLE procedure, whose syntax is shown in Figure 8.15.
To enable the user to update the DEPT8 table from within a program, for example, insert the code from CHP8_16.SQL, as shown in Figure 8.16. The SET ROLE procedure in the DBMS_SESSION package enables PL/SQL statements to execute a SET ROLE statement. Executing the SET ROLE statement directly in PL/SQL causes an error.
How It Works
Step 1 creates a sample user account and table used throughout this How-To. Step 2 creates the roles used to restrict access to the database. Step 3 grants the privileges required on the database objects to the roles created in Step 2. Step 4 grants the users of the system the role privileges. Step 5 removes the password-protected role as the default from system users. Step 6 is where code would be inserted into applications that need to use the restricted table. The DBMS_SESSION.SET_ROLE procedure is used within applications to enable password-protected roles.
Comments
Password-protected roles can be used to restrict access from ad hoc query tools. They can be used to disable INSERT, UPDATE, and DELETE privileges or to disable all privileges. When SELECT privileges are restricted through password-protected roles, side effects can occur. You must be very careful how you develop your applications when restricting the SELECT privilege through roles. Some development tools look at the database before running their first statement.
For example, Oracle Reports interprets the code in PL/SQL formatting triggers before executing the first statement of code. The SET ROLE procedure must be run before this occurs, but unfortunately, it cannot. If you are restricting SELECT privileges with the roles, you cannot use PL/SQL format triggers in Oracle Reports. You must also specify all the roles that should be enabled within a single SET ROLE statement. Otherwise, just the roles specified are enabled, and no others.