我使用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？
uj5u.com熱心網友回復：
當您授予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陳述句中的元素需要其他權限，所以讓我們分析一下
- 標識列包含一個序列
- 主鍵包含一個索引。
讓我們給用戶任何權限
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
那么，發生了什么？
When you create a table in another schema with a column as identity, you need not only the create any table and the create any sequence privileges, you also need the select any sequence privilege
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.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/350097.html
上一篇：data=pandas.read_excel('filename.xlsx'不起作用。得到一個錯誤：AttributeError:部分初始化的模塊'pandas'