How to grant execute on dbms_lock in Oracle?

Question

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 1

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.

Answer 2

It sounds as though SYS hasn't been granted the DBA role or that SYS doesn't have the GRANT ANY OBJECT privilege. To quote from the documentation

To grant an object privilege, you must own the object, or the owner of the object must have granted you the object privileges with the GRANT OPTION, or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you have the GRANT ANY OBJECT PRIVILEGE, then you can grant the object privilege only if the object owner could have granted the same object privilege.

This implies that you can't grant execute on dbms_lock because SYS wouldn't have been able to do so.

On installation SYS is automatically granted the DBA role so maybe someone's been changing this or created another user with the DBA role.

Either way you're going to have to get your DBA involved if you only have access to these two users. Ask them to grant execute on the packages you need to the users that need it. It's up to them to give you a good reason why they won't grant you execute on the packages you need in order to do your job.

If you can't get full access to dbms_lock you can always create a procedure in another user that wraps dbms_lock.sleep you need and then grant execute on that procedure alone.