Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)

Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)

By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user. Such definer's rights subprograms are bound to the schema in which they reside, allowing you to refer to objects in the same schema without qualifying their names. For example, if schemas HR and OE both have a table called departments, a procedure owned by HR can refer to departments rather than HR.departments. If user OE calls HR's procedure, the procedure still accesses the departments table owned by HR.

If you compile the same procedure in both schemas, you can define the schema name as a variable in SQL*Plus and refer to the table like &schema..departments. The code is portable, but if you change it, you must recompile it in each schema.

A more maintainable way is to use the AUTHID clause, which makes stored procedures and SQL methods execute with the privileges and schema context of the calling user. You can create one instance of the procedure, and many users can call it to access their own data.

Such invoker's rights subprograms are not bound to a particular schema. The following version of procedure create_dept executes with the privileges of the calling user and inserts rows into that user's departments table:

Example 8-13 Specifying Invoker's Rights With a Procedure

CREATE OR REPLACE PROCEDURE create_dept (
   v_deptno NUMBER,
   v_dname  VARCHAR2,
   v_mgr    NUMBER,
   v_loc    NUMBER) 
AUTHID CURRENT_USER AS
BEGIN
   INSERT INTO departments VALUES (v_deptno, v_dname, v_mgr, v_loc);
END;
/
CALL create_dept(44, 'Information Technology', 200, 1700);

Advantages of Invoker's Rights

Invoker's rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas. All the schemas in one instance can call procedures owned by a central schema. You can even have schemas in different instances call centralized procedures using a database link.

Consider a company that uses a stored procedure to analyze sales. If the company has several schemas, each with a similar SALES table, normally it would also need several copies of the stored procedure, one in each schema.

To solve the problem, the company installs an invoker's rights version of the stored procedure in a central schema. Now, all the other schemas can call the same procedure, which queries the appropriate to SALES table in each case.

You can restrict access to sensitive data by calling from an invoker's rights subprogram to a definer's rights subprogram that queries or updates the table containing the sensitive data. Although multiple users can call the invoker's rights subprogram, they do not have direct access to the sensitive data.

Specifying the Privileges for a Subprogram with the AUTHID Clause

To implement invoker's rights, use the AUTHID clause, which specifies whether a subprogram executes with the privileges of its owner or its current user. It also specifies whether external references (that is, references to objects outside the subprogram) are resolved in the schema of the owner or the current user.

The AUTHID clause is allowed only in the header of a standalone subprogram, a package spec, or an object type spec. In the CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, or CREATE TYPE statement, you can include either AUTHID CURRENT_USER or AUTHID DEFINER immediately before the IS or AS keyword that begins the declaration section.

DEFINER is the default option. In a package or object type, the AUTHID clause applies to all subprograms.

Most supplied PL/SQL packages (such as DBMS_LOB, DBMS_PIPE, DBMS_ROWID, DBMS_SQL, and UTL_REF) are invoker's rights packages.

Who Is the Current User During Subprogram Execution?

In a sequence of calls, whenever control is inside an invoker's rights subprogram, the current user is the session user. When a definer's rights subprogram is called, the owner of that subprogram becomes the current user. The current user might change as new subprograms are called or as subprograms exit.

To verify who the current user is at any time, you can check the USER_USERS data dictionary view. Inside an invoker's rights subprogram, the value from this view might be different from the value of the USER built-in function, which always returns the name of the session user.

How External References Are Resolved in Invoker's Rights Subprograms

If you specify AUTHID CURRENT_USER, the privileges of the current user are checked at run time, and external references are resolved in the schema of the current user. However, this applies only to external references in:

• SELECT, INSERT, UPDATE, and DELETE data manipulation statements
• The LOCK TABLE transaction control statement
• OPEN and OPEN-FOR cursor control statements
• EXECUTE IMMEDIATE and OPEN-FOR-USING dynamic SQL statements
• SQL statements parsed using DBMS_SQL.PARSE()

For all other statements, the privileges of the owner are checked at compile time, and external references are resolved in the schema of the owner. For example, the assignment statement in Example 8-14 refers to the packaged function num_above_salary in the emp_actions package in Example 1-13. This external reference is resolved in the schema of the owner of procedure above_salary.

Example 8-14 Resolving External References in an Invoker's Rights Subprogram

CREATE PROCEDURE above_salary (emp_id IN NUMBER)
  AUTHID CURRENT_USER AS
  emps NUMBER;
BEGIN
  emps := emp_actions.num_above_salary(emp_id);
  DBMS_OUTPUT.PUT_LINE( 'Number of employees with higher salary: ' || 
                         TO_CHAR(emps));
END;
/
CALL above_salary(120);

The Need for Template Objects in Invoker's Rights Subprograms

The PL/SQL compiler must resolve all references to tables and other objects at compile time. The owner of an invoker's rights subprogram must have objects in the same schema with the right names and columns, even if they do not contain any data. At run time, the corresponding objects in the caller's schema must have matching definitions. Otherwise, you get an error or unexpected results, such as ignoring table columns that exist in the caller's schema but not in the schema that contains the subprogram.

Overriding Default Name Resolution in Invoker's Rights Subprograms

Occasionally, you might want an unqualified name to refer to some particular schema, not the schema of the caller. In the same schema as the invoker's rights subprogram, create a public synonym for the table, procedure, function, or other object using the CREATE SYNONYM statement:

CREATE PUBLIC SYNONYM emp FOR hr.employees;

When the invoker's rights subprogram refers to this name, it will match the synonym in its own schema, which resolves to the object in the specified schema. This technique does not work if the calling schema already has a schema object or private synonym with the same name. In that case, the invoker's rights subprogram must fully qualify the reference.

Granting Privileges on Invoker's Rights Subprograms

To call a subprogram directly, users must have the EXECUTE privilege on that subprogram. By granting the privilege, you allow a user to:

• Call the subprogram directly
• Compile functions and procedures that call the subprogram

For external references resolved in the current user's schema (such as those in DML statements), the current user must have the privileges needed to access schema objects referenced by the subprogram. For all other external references (such as function calls), the owner's privileges are checked at compile time, and no run-time check is done.

A definer's rights subprogram operates under the security domain of its owner, no matter who is executing it. The owner must have the privileges needed to access schema objects referenced by the subprogram.

You can write a program consisting of multiple subprograms, some with definer's rights and others with invoker's rights. Then, you can use the EXECUTE privilege to restrict program entry points. That way, users of an entry-point subprogram can execute the other subprograms indirectly but not directly.

Granting Privileges on an Invoker's Rights Subprogram: Example

Suppose user UTIL grants the EXECUTE privilege on subprogram FFT to user APP:

GRANT EXECUTE ON util.fft TO app;

Now, user APP can compile functions and procedures that call subprogram FFT. At run time, no privilege checks on the calls are done. As Figure 8-2 shows, user UTIL need not grant the EXECUTE privilege to every user who might call FFT indirectly.

Since subprogram util.fft is called directly only from invoker's rights subprogram app.entry, user util must grant the EXECUTE privilege only to user APP. When UTIL.FFT is executed, its current user could be APP, SCOTT, or BLAKE even though SCOTT and BLAKE were not granted the EXECUTE privilege.

Figure 8-2 Indirect Calls to an Invoker's Rights Subprogram

Description of the illustration lnpls026.gif

Using Roles with Invoker's Rights Subprograms

The use of roles in a subprogram depends on whether it executes with definer's rights or invoker's rights. Within a definer's rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.

Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.

Using Views and Database Triggers with Invoker's Rights Subprograms

For invoker's rights subprograms executed within a view expression, the schema that created the view, not the schema that is querying the view, is considered to be the current user. This rule also applies to database triggers.

Using Database Links with Invoker's Rights Subprograms

You can create a database link to use invoker's rights:

CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER
  USING connect_string;

A current-user link lets you connect to a remote database as another user, with that user's privileges. To connect, Oracle uses the username of the current user (who must be a global user). Suppose an invoker's rights subprogram owned by user OE references the following database link. If global user HR calls the subprogram, it connects to the Dallas database as user HR, who is the current user.

CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...

If it were a definer's rights subprogram, the current user would be OE, and the subprogram would connect to the Dallas database as global user OE.

Using Object Types with Invoker's Rights Subprograms

To define object types for use in any schema, specify the AUTHID CURRENT_USER clause. For information on object types, see Oracle Database Application Developer's Guide - Object-Relational Features.

Suppose user HR creates the following object type:

Example 8-15 Creating an Object Type With AUTHID CURRENT USER

CREATE TYPE person_typ AUTHID CURRENT_USER AS OBJECT (
  person_id   NUMBER,
  person_name VARCHAR2(30),
  person_job  VARCHAR2(10),
  STATIC PROCEDURE new_person_typ (
    person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2, 
    schema_name VARCHAR2, table_name VARCHAR2),
  MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ, new_job VARCHAR2) 
);
/
CREATE TYPE BODY person_typ AS
  STATIC PROCEDURE new_person_typ (
    person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2, 
    schema_name VARCHAR2, table_name VARCHAR2) IS
    sql_stmt VARCHAR2(200);
  BEGIN 
    sql_stmt := 'INSERT INTO ' || schema_name || '.'
       || table_name || ' VALUES (HR.person_typ(:1, :2, :3))';
    EXECUTE IMMEDIATE sql_stmt USING person_id, person_name, person_job;
  END;
  MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ, new_job VARCHAR2) IS
  BEGIN
    person_job := new_job;
  END;
END;
/

Then, user HR grants the EXECUTE privilege on object type person_typ to user OE:

GRANT EXECUTE ON person_typ TO OE;

Finally, user OE creates an object table to store objects of type person_typ, then calls procedure new_person_typ to populate the table:

CONNECT oe/oe;
CREATE TABLE person_tab OF hr.person_typ;

BEGIN
  hr.person_typ.new_person_typ(1001, 'Jane Smith', 'CLERK', 'oe', 'person_tab');
  hr.person_typ.new_person_typ(1002, 'Joe Perkins', 'SALES','oe', 'person_tab');
  hr.person_typ.new_person_typ(1003, 'Robert Lange', 'DEV','oe', 'person_tab');
END;
/

The calls succeed because the procedure executes with the privileges of its current user (OE), not its owner (HR).

For subtypes in an object type hierarchy, the following rules apply:

• If a subtype does not explicitly specify an AUTHID clause, it inherits the AUTHID of its supertype.
• If a subtype does specify an AUTHID clause, its AUTHID must match the AUTHID of its supertype. Also, if the AUTHID is DEFINER, both the supertype and subtype must have been created in the same schema.

Calling Invoker's Rights Instance Methods

An invoker's rights instance method executes with the privileges of the invoker, not the creator of the instance. Suppose that person_typ is an invoker's rights object type as created in Example 8-15, and that user HR creates p1, an object of type person_typ. If user OE calls instance method change_job to operate on object p1, the current user of the method is OE, not HR, as shown in Example 8-16.

Example 8-16 Calling an Invoker's Rights Instance Methods

-- oe creates a procedure that calls change_job
CREATE PROCEDURE reassign (p IN OUT NOCOPY hr.person_typ, new_job VARCHAR2) AS
BEGIN
   p.change_job(new_job); -- executes with the privileges of oe
END;
/
-- OE grants EXECUTE to HR on procedure reassign
GRANT EXECUTE ON reassign to HR;
CONNECT hr/hr

-- user hr passes a person_typ object to the procedure reassign
DECLARE
   p1 person_typ;
BEGIN
   p1 := person_typ(1004,  'June Washburn', 'SALES');
   oe.reassign(p1, 'CLERK'); -- current user is oe, not hr
END;
/