Oracle
Oracle 模式特權其他使用者
我有一個只有 CONNECT 權限的使用者,我想知道 RESOURCE 是否足以查詢其他模式中的對象並基於此在我自己的模式中創建對象。
即:通過從其他模式中選擇表來在我自己的模式中創建一個視圖。
SELECT ANY TABLE
特權是否足夠,或者我的使用者是否必須被授予特定對象特權才能對其他使用者執行任何操作?
RESOURCE
角色具有以下系統權限。SQL> select privilege from role_sys_privs where role='RESOURCE'; PRIVILEGE ---------------------------------------- CREATE SEQUENCE CREATE TRIGGER CREATE CLUSTER CREATE PROCEDURE CREATE TYPE CREATE OPERATOR CREATE TABLE CREATE INDEXTYPE 8 rows selected.
並且沒有餐桌特權。
SQL> select privilege from role_tab_privs where role='RESOURCE'; no rows selected
觀察後我們可以知道,具有
RESOURCE
角色的使用者可以創建某些對象,例如表和過程。但是為了使
SELECT
表屬於另一個模式,另一個使用者需要SELECT
顯式地授予對錶的對象權限。SQL> conn user1/password SQL> grant select on table to user2
現在
user2
可以通過從模式表中選擇來創建視圖(使用者應具有CREATE VIEW
系統權限)user1
。展示(基於 Oracle 11.2.0.4):
SQL> create user user2 identified by user2; User created. SQL> grant resource, connect to user2; Grant succeeded. SQL> conn user2/user2 Connected. SQL> create view v1 as select * from user1.mytest; create view v1 as select * from user1.mytest * ERROR at line 1: ORA-00942: table or view does not exist SQL> conn user1/user1 Connected. SQL> grant select on mytest to user2; Grant succeeded. SQL> conn user2/user2 Connected. SQL> create view v1 as select * from user1.mytest; create view v1 as select * from user1.mytest * ERROR at line 1: ORA-01031: insufficient privileges --Now the user has no `CREATE VIEW` system privilege SQL> conn / as sysdba Connected. SQL> grant create view to user2; Grant succeeded. SQL> conn user2/user2 Connected. SQL> create view v1 as select * from user1.mytest; View created.