即使使用CREATE表授予，“立即执行"也会失败 [英] Execute Immediate fails even with CREATE table grant

问题描述

我在使用存储过程中的execute Instant命令创建表时遇到问题.但是，我得到权限不足"的错误.我检查了其他线程，并确保用户具有授予它的创建表"特权.但是我仍然看到相同的错误.

I have a problem where I am creating a table using the execute immediate command in the stored procedure. However I get the error of "insufficient privileges". I checked other threads and made sure that the user has "CREATE TABLE" privilege granted to it. However I still keep seeing the same error.

SQL> select * from USER_SYS_PRIVS;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MYUSER            CREATE VIEW                              NO
MYUSER            UNLIMITED TABLESPACE                     NO

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

11 rows selected.

我创建的虚拟过程是:

create or replace procedure sp_dummy
   as
   begin
      execute immediate 'Create table Dummy99_99 (Dummy_Field number)';
   end sp_dummy;
   /

详细错误:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MYUSER.SP_DUMMY", line 4
ORA-06512: at line 1

我在做错什么吗?

推荐答案

您只将create view直接授予了用户.您可以看到的其他系统特权来自角色，并且角色在定义者权利存储过程中被禁用.在user_role_privs中查看授予您角色的时间，然后可以看到每个角色在role_sys_privs中(角色名称为被授予者)赋予您哪些特权.角色也可能有多个层次.

You only have create view granted directly to your user. The other system privileges you can see are coming from a role, and roles are disabled in definer's-rights stored procedures. Look in user_role_privs to see while roles you've been granted, and you can see which privileges each role gives you in role_sys_privs (with the role name as the grantee). There could be several layers of roles too.

如果您尝试静态创建表之前执行了set role none操作，则会看到相同的错误.最小设置的演示:

You would see the same error if you did set role none before trying to create a table statically. Demo with minimal set-up:

create role myrole;
grant create session, create table, create procedure to myrole;
create user myuser identified by mypasswd;
grant myrole to myuser;
grant create view, unlimited tablespace to myuser;

然后以该用户身份登录:

Then as that user:

SQL> connect myuser/mypasswd
Connected.
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MYUSER                         UNLIMITED TABLESPACE                     NO
MYUSER                         CREATE VIEW                              NO

2 rows selected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE

5 rows selected.

SQL> Create table Dummy99_99 (Dummy_Field number);

Table created.

SQL> drop table Dummy99_99 purge;

Table dropped.

SQL> set role none;

Role set.

SQL> Create table Dummy99_99 (Dummy_Field number);
Create table Dummy99_99 (Dummy_Field number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

以及您的存储过程版本:

And with your stored procedure version:

SQL> connect myuser/mypasswd
Connected.
SQL> create or replace procedure sp_dummy
  2  as
  3  begin
  4    execute immediate 'Create table Dummy99_99 (Dummy_Field number)';
  5  end sp_dummy;
  6  /

Procedure created.

SQL> exec sp_dummy;
BEGIN sp_dummy; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MYUSER.SP_DUMMY", line 4
ORA-06512: at line 1

要能够从存储过程动态创建表，您的DBA需要直接将create table授予用户:

To be able to create the table dynamically from a stored procedure, your DBA will need to grant create table directly to your user:

grant create table to myuser;

然后再次尝试该过程:

SQL> connect myuser/mypasswd
Connected.
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MYUSER                         UNLIMITED TABLESPACE                     NO
MYUSER                         CREATE TABLE                             NO
MYUSER                         CREATE VIEW                              NO

SQL> exec sp_dummy;

PL/SQL procedure successfully completed.

SQL> desc Dummy99_99
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY_FIELD                                        NUMBER

请注意，user_sys_privs现在显示create table已直接授予，但之前或问题中未授予.

Notice that user_sys_privs now shows that create table has been granted directly, which it didn't before, or in the question.

但是，您几乎不可能真正想要动态创建对象，因为应该对架构进行良好的定义和稳定-应该控制这种类型的更改并将其作为发布过程的一部分.但是，作为一项练习，您需要直接拨款.

However, it is very unlikely you would ever really want to create objects dynamically, as the schema should be well defined and stable - changes of this type should be controlled and be part of a release process. But as an exercise, you need the direct grant.

这篇关于即使使用CREATE表授予，“立即执行"也会失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！