I expect the stored procedure to be working when called by user2 as I have not marked it as authid current user. AFAIK, the default is authid definer which should run under the security context of the owner user1 and not the caller user2. But to my disappointment it doesn't work. please help.
C:\>sqlplus
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Feb 5 15:46:29 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: user1/password
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
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)
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>
NB: This is just a sample code that shows the core issue and my original code is different than this. So please don't reply about SQL injection. The real code is much complex and I dont execute the user input directly as shown here. But the issue is same. end user get the same insufficient privilege error.