How to grant execute on dbms_lock in Oracle?
oracle, privileges
Solution
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`.
Problem
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?