Hello Memebers,
As part of a requirement, I have created a procedure, to create tables and indexes at run time, using 'Execute Immediate'.
Now I have given execute privileges on this procedure to another user (USER2).
Both the users USER1 and USER2 had got the privileges via two different roles.
USER2 doesnt have 'create table' privileges and this is in line with the project architecture. and I can’t change this.
Issue: Now if I run the procedure under USER2, I am getting "Insufficient privileges" error.
The same works fine when I run this under USER1.
Could someone suggest me how to achieve this without modifying the users and roles.
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 2 16:28:59 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Advanced Analytics and Real Application Testing options
SQL>
SQL> create table test_table(a number);
Table created.
SQL> drop table test_table;
Table dropped.
SQL> create or replace
2 procedure test_proc (strSql varchar) authid definer
3 as
4 begin
5 execute immediate(strSql);
6 end;
7 /
Procedure created.
SQL> grant execute on test_proc to user2;
Grant succeeded.
SQL> connect user2/password
Connected.
SQL>
SQL> BEGIN
2 user1.TEST_PROC('CREATE TABLE user1.TEST_TABLE(a number)');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "User1.TEST_PROC", line 4
ORA-06512: at line 2
SQL>