oracle - CREATE ANY TABLE 不足以创建任何表？

Question

我使用 SYSTEM 用户将 CREATE ANY TABLE 授予用户 TEST，但是当我尝试执行时

create table other.dummy ...

我仍然得到 ORA-01031: insufficient privileges

Oracle : Grant Create table in another schema?声称这应该有效。

我还尝试授予CREATE ANY INDEX，因为该表具有 PK，因此包含一个索引，但这并没有改变任何东西。

GRANT ALL PRIVILEGES 做到了这一点，但我更喜欢一些更有限的东西。

实际的CREATE TABLE语句是:

CREATE TABLE OTHER.DUMMY_ENTITY ( 
    ID NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY, 
    NAME VARCHAR2(30) 
)

除了 CREATE ANY TABLE 之外，我还需要授予哪些权限？

Answer 1

当您将CREATE ANY TABLE 权限授予特定用户时，该用户将能够在数据库中创建任何表，只要此类表的创建与您的语句兼容正在运行。在您的情况下，您不仅仅是在创建一个表。

让我们模拟您的场景，通过创建具有此类权限的用户然后尝试在另一个模式中创建表。

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 5 10:54:17 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> show user
USER is "SYS"
SQL>
SQL> create user test_grant identified by "Oracle_123" ;

User created.

SQL> grant create session, create any table to test_grant ;

Grant succeeded.

SQL> exit

现在，我正在连接 test_grant 以在架构 test

中创建一个表作为您的表

sqlplus test_grant/"Oracle_123"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 5 10:55:28 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> create table test.t1_privs ( c1 number generated by default on null as identity primary key , c2 varchar2(1) ) ;
create table test.t1_privs ( c1 number generated by default on null as identity primary key , c2 varchar2(1) )
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> create table test.t2_privs ( c1 number, c2 varchar2(1) ) ;

Table created.

如您所见，我可以在其他架构中创建表，但不是您要创建的表。显然 create table 语句中的元素需要其他权限，所以让我们分析一下

1. 标识列包含一个序列
2. 主键包含一个索引。

让我们给用户那些权限

SQL> grant create any index, create any sequence to test_grant ;

Grant succeeded.

再试一次

sqlplus test_grant/"Oracle_123"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 5 11:06:47 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 05 2021 11:03:31 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> create table test.t1_privs ( c1 number generated by default on null as identity primary key ,  c2 varchar2(1) ) ;
create table test.t1_privs ( c1 number generated by default on null as identity primary key,  c2 varchar2(1) )
*
ERROR at line 1:
ORA-01031: insufficient privileges

那么，发生了什么？

当您在另一个模式中创建表并将列作为标识时，您不仅需要 create any table 和 create any sequence 权限，您还需要选择任意序列权限

SQL> grant select any sequence to test_grant ;

Grant succeeded.

sqlplus test_grant/"Oracle_123"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 5 11:31:44 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 05 2021 11:29:36 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> create table test.t1_privs ( c1 number generated by default on null as identity primary key, c2 varchar2(1) ) ;

Table created.