如何在 Oracle 中授予对 dbms_lock 的执行权限？

How to grant execute on dbms_lock in Oracle?

提问by Tomasz ?uk

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

我需要使用来自用户 usr1 的 dbms_lock.sleep 过程。我无法以 sys 身份登录，但我有用户 usr2 的密码，该密码具有“授予任何对象特权”特权。但是，当我以 usr2 登录并尝试发出

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?

我收到 ORA-01031“权限不足”异常。这同样适用于另一个用户的测试包。系统包是否经过特殊处理，还是我遗漏了什么？

采纳答案by Alex Poole

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 ANYprivileges don't apply to the data dictionary. The documentation refers to this as 'dictionary protection'.

系统包被特殊对待，具体取决于初始化参数的值O7_DICTIONARY_ACCESSIBILITY。如果是FALSE，这是自 Oracle 9i 以来的默认设置，则ANY权限不适用于数据字典。文档将此称为“字典保护”。

The closest I can find in the security guide - hereand 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.

但是，Oracle 支持说明 174753.1 明确指出字典保护取代grant any object privilege. 我不允许引用它，但它解释了你所看到的；如果您可以访问它，可能值得一看。

So, the only way for usr2to be able to grant execute on sys.dbms_lock to usr1is for the DBA to have done grant execute on sys.dbms_lock to usr2 with grant option.

因此，usr2能够做到的唯一方法grant execute on sys.dbms_lock to usr1是让 DBA 完成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 usr1directly, or add the with grant optionto the privileges granted to usr2; or have usr2create a wrapper procedure around the dbms_lockcall and grant permissions on that to usr1.

正如 Ben 所说，您必须让 DBAusr1直接授予权限，或者将 加入with grant option授予的权限usr2；或者usr2在dbms_lock调用周围创建一个包装程序，并授予usr1.

回答by Ben

It soundsas 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

这听起来好像SYS还没有被授予DBA角色或SYS没有授予任何对象权限。从文档中引用

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.

要授予对象权限，您必须拥有该对象，或者对象的所有者必须使用 GRANT OPTION 授予您对象权限，或者您必须已被授予 GRANT ANY OBJECT PRIVILEGE 系统权限。如果您拥有 GRANT ANY OBJECT PRIVILEGE，则仅当对象所有者可以授予相同的对象权限时，您才能授予对象权限。

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

这意味着您不能授予执行权限，dbms_lock因为 SYS 无法这样做。

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

在安装SYS 时，系统会自动授予 DBA 角色，因此可能有人更改了此角色或创建了另一个具有 DBA 角色的用户。

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.

无论哪种方式，如果您只能访问这两个用户，就必须让 DBA 参与其中。要求他们将您需要的包的执行权限授予需要它的用户。由他们来给你一个很好的理由，为什么他们不会授予你执行你需要的包来完成你的工作。

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

如果您无法获得完全访问权限，dbms_lock您始终可以在另一个用户中创建一个包含dbms_lock.sleep您需要的过程，然后单独授予执行该过程。