Oracle PL/SQL Best Practices

Name

SQL-22: Apply the invoker rights method to all stored code that executes dynamic SQL (Oracle8i).

Synopsis

Whenever you create a stored program (standalone or within a package) that parses a dynamic SQL statement, you should define that program with the “invoker rights” model. This is done by adding the following clause to the program header:

AUTHID CURRENT_USER

This feature is available only in Oracle8i and above. This clause ensures that the dynamic SQL string is parsed under the authority of the schema currently running the program, which is almost always the desired behavior.

Example

If I were to create a reusable program to execute any DDL statement, I would make certain it used the AUTHID statement as follows:

CREATE OR REPLACE PROCEDURE runddl (
   ddl_in in VARCHAR2)
   AUTHID CURRENT_USER 
IS
BEGIN
   EXECUTE IMMEDIATE ddl_in;
EXCEPTION
   WHEN OTHERS
   THEN
      pl (SQLERRM)
      pl (ddl_in);
      RAISE;
END;
/

Benefits

You can build and share generic dynamic SQL utilities more easily. Developers don’t have to worry about which schema owns the utility and whether or not the requested operation will affect someone else’s schema.

Challenges

This feature is available only in Oracle8i.

Resources

runddl.pro and runddl81.pro : Generic DDL engine in both DBMS_SQL and NDS.