CREATE ANY TABLE不足以创建任何表

Question

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

create table other.dummy ...

我仍然得到ORA-01031: insufficient privileges

Oracle:是否允许在另一个架构中创建表？声称这应该有效。

我还尝试授予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权限，还需要select 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.