13

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?

Tomasz Żuk
  • 1,288
  • 1
  • 12
  • 14
  • Oracle have [promised to fix this in a future release by adding a `sleep` procedure to `dbms_session`](https://community.oracle.com/ideas/4852). – William Robertson Feb 13 '18 at 12:16

2 Answers2

12

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.

Ben
  • 51,770
  • 36
  • 127
  • 149
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Just stumbled upon [this old answer](http://stackoverflow.com/a/2564566/266304) which shows a wrapper around `dbms_lock`. – Alex Poole Nov 22 '12 at 14:34
4

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.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • 2
    I see the same thing (11gR2); but `SYS` can definitely grant execute on `dbms_lock`, and apparently has to grant it to `usr2` or that gets an unknown table error rather than insufficient privileges. The same thing happens with other packages, not just that one. Although I haven't found it documented anywhere yet, it does seem like `SYS` objects have another layer of protection; which I guess isn't entirely unreasonable. Interesting. – Alex Poole Nov 15 '12 at 23:55
  • 1
    I'll check what you say, but I think a user can always grant execute on his own package to anyone, can't he? – Tomasz Żuk Nov 16 '12 at 00:56
  • sorry, but this answer is completely wrong. If `grant execute on sys.dbms_lock to usr1` it does not sound that SYS has a problem, but that user usr1 has a problem. So your assumptions are false and therefore the conclusions useless. In the [other answer to this question](https://stackoverflow.com/a/13414574/754550) it is explained that this is the expected behavior of the database. – miracle173 Jul 31 '20 at 11:32