SQL & PL/SQL

Hi to all.

I want to grant the execute privilege for all SYS schema functions/procedures. To achieve it I do the following:


SQL> connect sys/*****@orcl
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as SYS

SQL> create user test identified by test;

User created

SQL> grant create session to test;

Grant succeeded

SQL> grant execute any procedure to test;

Grant succeeded

According to the [http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm] the "grant execute any procedure" - grants Execute procedures or functions, either standalone or packaged.

So, the steps seem to be right. Then, I try to connect to the test user and execute any procedure from the SYS schema, for example, dbms_lock.sleep:

SQL> connect test/test@dizzy/orcl
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as test

SQL> begin
2 sys.dbms_lock.sleep(1);
3 end;
4 /

begin
sys.dbms_lock.sleep(1);
end;

ORA-06550: line 3, column 1:
PLS-00201: identifier 'SYS.DBMS_LOCK' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored

So, the execution fails due to insufficient rights. However, the direct grant on the sys.dbms_lock works!



SQL> connect sys/*****@dizzy/orcl as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as SYS

SQL> grant execute on dbms_lock; to test;

grant execute on dbms_lock; to test

ORA-00911: invalid character

SQL> grant execute on dbms_lock to test;

Grant succeeded

SQL> connect test/test@dizzy/orcl
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as test

SQL> begin
2 sys.dbms_lock.sleep(1);
3 end;
4 /

PL/SQL procedure successfully completed


So, to be sure that the grant on any procedure from the definite scheme is given, should I avoid giving the execute any procedure grant?

P.S. Is there any special tag for code?

Thanks in advance.