The definition of the stored procedure is
CREATE OR REPLACE PROCEDURE EMP_PROC
AS
BEGIN
EXECUTE
IMMEDIATE 'CREATE TABLE EMP_DUMMY AS SELECT * FROM EMP WHERE 1=2';
END;
/
SQL> EXEC EMP_PROC
BEGIN EMP; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "EBP.EMP_PROC", line 5
ORA-06512: at line 1
Oracle security model -that it needs to know
explicitly somewhere in the procedure what kind of privileges to use.
The way to let Oracle know that is to use AUTHID
keyword in the CREATE OR REPLACE statement.
If you want the same level of privileges as the
creator of the procedure, you use AUTHID DEFINER.
If you
want Oracle to use the privileges of the user currently running the stored
procedure, you want to use AUTHID CURRENT_USER.
The procedure declaration looks as follows:
CREATE OR REPLACE PROCEDURE EMP_RPOC
AUTHID CURRENT_USER IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE EMP_DUMMY AS
SELECT * FROM EMP WHERE 1=2';
END;
/
Tags:
Oracle