Creating a Stored Procedure With the AUTHID Clause

By default, stored procedures and functions 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 the qualified HR.departments. If user OE calls the procedure owned by HR, the procedure still accesses the departments table owned by HR.

You can use the AUTHID CURRENT_USER clause to make stored procedures and functions 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 because invoker's rights subprograms are not bound to a particular schema.

In Example: Creating a Stored Procedure With the AUTHID Clause, the procedure is created with the AUTHID CURRENT_USER clause. This example is based on Example: Using Dynamic SQL to Create a Table in PL/SQL.

Creating a Stored Procedure With the AUTHID Clause

CREATE OR REPLACE PROCEDURE create_log_table 
-- use AUTHID CURRENT _USER to execute with the privileges and 
-- schema context of the calling user
  AUTHID CURRENT_USER AS 
  tabname       VARCHAR2(30); -- variable for table name
  temptabname   VARCHAR2(30); -- temporary variable for table name
  currentdate   VARCHAR2(8);  -- varible for current date
BEGIN
-- extract, format, and insert the year, month, and day from SYSDATE into 
-- the currentdate variable
  SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) || 
     TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') || 
     TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') INTO currentdate FROM DUAL;
-- construct the log table name with the current date as a suffix
  tabname := 'log_table_' || currentdate;

-- check whether a table already exists with that name
-- if it does NOT exist, then go to exception handler and create table
-- if the table does exist, then note that table already exists
  SELECT TABLE_NAME INTO temptabname FROM USER_TABLES 
    WHERE TABLE_NAME = UPPER(tabname);
  DBMS_OUTPUT.PUT_LINE('Table ' || tabname || ' already exists.');

  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
    -- this means the table does not exist because the table name 
    -- was not found in USER_TABLES
      BEGIN
-- use EXECUTE IMMEDIATE to create a table with tabname as the table name
        EXECUTE IMMEDIATE 'CREATE TABLE ' || tabname 
                         || '(op_time VARCHAR2(10), operation VARCHAR2(50))' ;
        DBMS_OUTPUT.PUT_LINE(tabname || ' has been created');
      END;

END create_log_table;
/

-- to call the create_log_table procedure, you can use the following
BEGIN 
  create_log_table; 
END;
/

For different methods to execute (call) stored subprograms, see Example: Techniques for Calling Stored Procedures or Functions.