How to grant execute on dbms_lock in Oracle?
I need to use dbms_lock.sleep procedure from user usr1. I can't login as sys, but I have a password for user usr2 which have "grant any object privilege" privilege. However, when I'm logged in as usr2 and try to issue
grant execute on sys.dbms_lock to usr1
I get the ORA-01031 "insufficient privileges" exception. The same works with a test package on another user. Are the system packages treated specially, or have I missed something?
Answer
The system packages are treated specially, depending on the value of the initialisation parameter O7_DICTIONARY_ACCESSIBILITY. If that is FALSE, which is the default since Oracle 9i, then ANY privileges don't apply to the data dictionary. The documentation refers to this as 'dictionary protection'.
The closest I can find in the security guide - here and here - only refer to tables as examples.
Oracle Support note 174753.1, however, explicitly states that dictionary protection supersedes grant any object privilege. I'm not allowed to quote that but it explains what you're seeing; it might be worth looking up if you have access to it.
So, the only way for usr2 to be able to grant execute on sys.dbms_lock to usr1 is for the DBA to have done grant execute on sys.dbms_lock to usr2 with grant option.
As Ben says, you'll have to either get the DBA to grant the permission to usr1 directly, or add the with grant option to the privileges granted to usr2; or have usr2 create a wrapper procedure around the dbms_lock call and grant permissions on that to usr1.