9.7 Sharing Code

You create an Oracle account with a username, password, and basic roles that allow that user to create tables and procedures. You add a new user named BLAKE to the database with the following:

 CREATE USER BLAKE IDENTIFIED BY BLAKE DEFAULT TABLESPACE STUDENT_DATA TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE TO BLAKE; 

From this point forward, BLAKE can create tables and stored procedures. Another user SCOTT is also creating procedures. By default, BLAKE cannot see SCOTT'S objects and SCOTT cannot see BLAKE'S objects.

Sharing of objects is done on a per-object bases. BLAKE can grant table and package access to SCOTT on an as-needed basis. BLAKE has a package that provides selected payroll information. The PAYROLL package queries data from a sensitive SALARIES table. The SALARIES table is restricted. However, BLAKE can selectively give access to the PAYROLL package. BLAKE controls the type of salary information available through the procedures and functions he defines in the PAYROLL package. To grant SCOTT the right to use the PAYROLL package, BLAKE executes the following:

  SQL>  GRANT EXECUTE ON PAYROLL TO SCOTT; 

SCOTT can now execute any procedure or function defined in the PAYROLL package. SCOTT creates a private synonym:

  SQL>  CREATE SYNONYM PAYROLL FOR BLAKE.PAYROLL; 

From this point forward, SCOTT can write PL/SQL stored procedures that use the PAYROLL package. SCOTT cannot access the SALARIES table or any other object. Figure 9-4 illustrates this scenario.

Figure 9-4. Sharing Code.

BLAKE can revoke the GRANT EXECUTE with the following:

  SQL>  REVOKE EXECUTE ON PAYROLL FROM SCOTT; 

This immediately invalidates the procedure in the SCOTT account. If SCOTT runs the PL/SQL procedure that uses the PAYROLL package, it will fail. If BLAKE reactivates the grant, then Oracle will resolve the invalid procedure. It will recognize that SCOTT'S procedure is valid and run it.

BLAKE can revoke the privilege from SCOTT; he can grant the privilege any time. Each time it will be resolved. There should be some communication between BLAKE and SCOTT so that SCOTT can recompile the PL/SQL code when this occurs ”this alleviates Oracle from having to resolve an invalid object at run time.

Ideally, if SCOTT'S code becomes invalid, he can execute an ALTER/COMPILE command or use DBMS_UTILITY to validate the code. This topic is covered in Section 9.4, "Dependencies among Procedures."

If BLAKE wants to share his code, he must GRANT EXECUTE to a user. BLAKE cannot grant execute privileges to SCOTT through a role. When Oracle compiles or revalidates SCOTT'S procedures, it looks at the privileges in the SCOTT account. It does not look at roles. The success or failure of compiling SCOTT'S code depends on SCOTT'S privileges; roles are ignored. The following is a scenario that explains this concept.

Consider the following:

HR_ADMIN is a senior role. HR is a less privileged role. The following creates the two roles. Because HR_ADMIN is a senior role, all lesser roles are granted to HR_ADMIN.

 SQL> CREATE ROLE HR_ADMIN; SQL> CREATE ROLE HR; SQL> GRANT HR TO HR_ADMIN; 

The following SQL creates the user SCOTT and grants five roles to SCOTT. However, the default roles for SCOTT are CONNECT and RESOURCE. When SCOTT connects to the database, he only has the CONNECT and RESOURCE roles. SCOTT can enable the other roles with a SET ROLE command.

 SQL> CREATE USER SCOTT IDENTIFIED BY TIGER; SQL> GRANT CONNECT, RESOURCE, HR, HR_ADMIN TO SCOTT; SQL> ALTER USER SCOTT DEFAULT ROLE CONNECT, RESOURCE; 

The following scenario supposes that roles are used when compiling procedures ”this is not the case. This scenario is to illustrate why. Suppose BLAKE grants execute on PAYROLL to HR_ADMIN. SCOTT connects to the database with default roles CONNECT, and RESOURCE. When SCOTT connects to the database, the procedure that uses the PAYROLL package is invalid. This is because the HR_ADMIN is not a default role and has not been set. SCOTT executes the command to enable the role.

 SQL> SET ROLE HR_ADMIN; 

SCOTT has now enabled the role to which the package execute privilege was given. This scenario can be more complicated. Application programs set roles, based on the username, to enable various parts of an application. Roles can be granted to other roles. The dynamics of roles allows run-time execution of the following:

 SQL> SET ROLE HR_ADMIN, HR; SQL> SET ROLE HR; 

Given the dynamics of roles and their use within applications, it is reasonable that user accounts be the basis for procedure grants, as shown in Figure 9-4.

You cannot do the following:

• CREATE ROLE HR;

• GRANT EXECUTE ON PAYROLL TO HR;

• GRANT HR TO SCOTT;

• Expect SCOTT to write a PL/SQL procedure that uses BLAKE'S package.

BLAKE must

 GRANT EXECUTE ON PAYROLL TO SCOTT;