oracle - 即使有 CREATE table grant，Execute Immediate 也会失败

Question

我在使用存储过程中的立即执行命令创建表时遇到问题。但是我收到“权限不足”的错误。我检查了其他线程并确保用户拥有授予它的“CREATE TABLE”权限。但是我仍然看到同样的错误。

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.

我创建的 Dummy 过程是:

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

我做错了什么吗？

Answer 1

您只有 create view 直接授予您的用户。您可以看到的其他系统权限来自角色，并且 roles are disabled in definer's-rights stored procedures .在 user_role_privs 中查看您被授予的角色，您可以在 role_sys_privs 中查看每个角色授予您的特权(角色名称为被授予者)。也可能有几层角色。

如果在尝试静态创建表之前执行set role none，您会看到同样的错误。最少设置的演示:

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;

然后作为该用户:

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

以及您的存储过程版本:

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:

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 已被直接授予，之前或在问题中没有授予。

但是，您不太可能真的想要动态创建对象，因为模式应该定义明确且稳定——这种类型的更改应该受到控制，并且应该成为发布过程的一部分。但作为练习，您需要直接拨款。