asked on

oracle 11G ORA-01031: insufficient privileges

i m getting Following Error
1 CREATE OR REPLACE procedure BASE_USER.create_audit_table (i_table_name in varchar2)
2 is
3 begin
4 execute immediate ('create table base_user.audit_'||i_table_name||' as select * from base_user
5 dbms_output.put_line(i_table_name || '_AUDIT table is successfully is created');
6 exception
7 when others then
8 dbms_output.put_line(sqlerrm);
9 dbms_output.put_line ('create table ' || i_table_name || '_AUDIT as select * from ' || i_table_
10* end;
SQL> r
1 CREATE OR REPLACE procedure BASE_USER.create_audit_table (i_table_name in varchar2)
2 is
3 begin
4 execute immediate ('create table base_user.audit_'||i_table_name||' as select * from base_user
5 dbms_output.put_line(i_table_name || '_AUDIT table is successfully is created');
6 exception
7 when others then
8 dbms_output.put_line(sqlerrm);
9 dbms_output.put_line ('create table ' || i_table_name || '_AUDIT as select * from ' || i_table_
10* end;

Procedure created.

SQL> sho errors
No errors.

SQL> execute create_audit_table('A');

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> execute create_audit_table('A');
ORA-01031: insufficient privileges
create table A_AUDIT as select * from A

PL/SQL procedure successfully completed.

SQL>

1  CREATE OR REPLACE procedure BASE_USER.create_audit_table (i_table_name  in  varchar2)
  2  is
  3  begin
  4   execute immediate ('create table base_user.audit_'||i_table_name||' as select * from base_user
  5    dbms_output.put_line(i_table_name || '_AUDIT table is successfully is created');
  6  exception
  7    when others then
  8     dbms_output.put_line(sqlerrm);
  9      dbms_output.put_line ('create table ' || i_table_name || '_AUDIT as select * from ' || i_table_
 10* end;
SQL> r
  1  CREATE OR REPLACE procedure BASE_USER.create_audit_table (i_table_name  in  varchar2)
  2  is
  3  begin
  4   execute immediate ('create table base_user.audit_'||i_table_name||' as select * from base_user
  5    dbms_output.put_line(i_table_name || '_AUDIT table is successfully is created');
  6  exception
  7    when others then
  8     dbms_output.put_line(sqlerrm);
  9      dbms_output.put_line ('create table ' || i_table_name || '_AUDIT as select * from ' || i_table_
 10* end;
 
Procedure created.
 
SQL> sho errors
No errors.
 
 
SQL> execute create_audit_table('A');
 
PL/SQL procedure successfully completed.
 
SQL> set serveroutput on
 
 
SQL> execute create_audit_table('A');
ORA-01031: insufficient privileges
create table A_AUDIT as select * from A
 
PL/SQL procedure successfully completed.
 
SQL>

Open in new window

sonicefu

following is the corrected code

just add AUTHID CURRENT_USER before is

CREATE OR REPLACE procedure BASE_USER.create_audit_table (i_table_name	in	varchar2)
AUTHID CURRENT_USER
is
begin
	execute immediate ('create table base_user.audit_'||i_table_name||' as select * from base_user.'||i_table_name) ;
  dbms_output.put_line(i_table_name || '_AUDIT table is successfully is created');
exception
  when others then
  	dbms_output.put_line(sqlerrm);
    dbms_output.put_line ('create table ' || i_table_name || '_AUDIT as select * from ' || i_table_name) ;
end;
/

Open in new window

sonicefu

just compile the procedure again and execute it, it'll work properly without any error.

Sujith

Your log shows that the procedure was run successfully once.
Did it create the table?

If not try to explicitly grant "create any table" privilege to the user and try again.

ASKER CERTIFIED SOLUTION

sonicefu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial