即使使用 CREATE 表授权，立即执行也会失败

2021-12-30 00:00:00 oracle11g oracle plsql grant execute-immediate

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

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;

然后作为该用户:

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.

相关文章