Getting error like ORA-00942 or ORA-01933 or ORA-01031 and ORA-06512 or PLS-00201 and ORA-06550 or ORA-28111 or ORA-01031: insufficient privileges or PLS-00201: identifier 'x' must be declared
If there is a user which has got some permission to do the following through the role
select a table, select a view, create a table, create a view, create a trigger
and the above operations are done using calling a plsql procedure then the operation will only work if the direct grants are done for the user running plsql block.
The reason is that when a plsql block is executed then only direct grants on that user are in use and grant done through the role will not work as the role will not kick in.
Example is as show below:
If there is a user which has got some permission to do the following through the role
select a table, select a view, create a table, create a view, create a trigger
and the above operations are done using calling a plsql procedure then the operation will only work if the direct grants are done for the user running plsql block.
The reason is that when a plsql block is executed then only direct grants on that user are in use and grant done through the role will not work as the role will not kick in.
Example is as show below:
SQL> create user harv identified by abc123 default tablespace USERS temporary tablespace temp;
User created.
SQL> grant create session to harv;
Grant succeeded.
SQL> grant resource to harv;
Grant succeeded.
SQL> conn harv/abc123
Connected.
SQL> create or replace procedure harv.temp is
2 var1 varchar2(4000);
3 BEGIN
4 var1:='create table harv.tab(
5 id number)';
6 EXECUTE IMMEDIATE var1;
7 end temp;
8 /
Procedure created.
SQL> exec harv.temp
BEGIN harv.temp; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "harv.TEMP", line 6
ORA-06512: at line 1
SQL> conn / as sysdba
Connected.
SQL>
SQL> grant create table to harv;
Grant succeeded.
SQL>
SQL> conn harv/abc123
Connected.
SQL> exec harv.temp
PL/SQL procedure successfully completed.
SQL>
