ORACLE - 创建过程已授予但无法创建过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10461245/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE - Create Procedure granted but can't create procedure
提问by Mikayil Abdullayev
There's a user in the database to whom CREATE PROCEDURE privelege is granted. But when that user tries to create a simple procedure the following error is thrown: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges.
数据库中有一个用户被授予 CREATE PROCEDURE 特权。但是当该用户尝试创建一个简单的过程时，会抛出以下错误：ORA-01031：权限不足 01031. 00000 - “权限不足” *原因：试图在没有适当权限的情况下更改当前用户名或密码。如果在没有必要的操作系统权限的情况下尝试安装数据库，也会发生此错误。
here's the DDL for the procedure:
这是该过程的 DDL：
CREATE OR REPLACE PROCEDURE TOTALBASE.ROUNDUP
(CUR OUT SYS_REFCURSOR )
AS
BEGIN
OPEN CUR FOR
SELECT * FROM TOTALBASE.ABONENT;
END ROUNDUP;
What else should I consider to do to make this work? I'm suspecting that even if the privelege is granted anyone who's not in the administrators or ORA_DBA group can't create a procedure. but I'm not sure.
我还应该考虑做些什么来完成这项工作？我怀疑即使授予特权，任何不在管理员或 ORA_DBA 组中的人也无法创建过程。但我不确定。
回答by Mark J. Bobak
To create a procedure in a schema other than your own, you'll need 'CREATE ANY PROCEDURE' privilege.
要在您自己的模式以外的模式中创建过程，您需要“创建任何程序”权限。
As a general rule, this privilege should not be granted lightly, as it could easily be used to circumvent database security.
作为一般规则，不应轻易授予此特权，因为它很容易被用来规避数据库安全性。
Hope that helps.
希望有帮助。
回答by igr
After few comments below, I am trying again.
在下面的一些评论之后，我再试一次。
Do not create a procedure in another schema; only let each user create objects in their own schema.
不要在另一个模式中创建过程；只让每个用户在他们自己的架构中创建对象。
This way you have less of a reason to GRANTaccess to other schema objects. The TOTALBASEuser can than grant the EXECUTEprivilege on that procedure to current user.
这样您就没有理由GRANT访问其他架构对象。该TOTALBASE用户可以比授予EXECUTE该过程的权限当前用户。
The current user, which needs some data from a TOTALUSERtable, does not need to create or access other objects when he has the EXECUTEprivilege on function or procedure.
当前用户需要从TOTALUSER表中获取一些数据，当他拥有EXECUTE函数或过程的权限时，不需要创建或访问其他对象。
Looking from another angle, this way it's also easier to maintain: TOTALUSERprovides sort of an API in how it exposes data to the current user, hiding implementation details which can change in the future, being transparent to current user.
从另一个角度来看，这种方式也更容易维护：TOTALUSER提供某种 API，它如何向当前用户公开数据，隐藏将来可能更改的实现细节，对当前用户透明。
