Describe 蟠螻殊 here
SQL> SET PAGESIZE 200 LINESIZE 200
SQL> SHOW USER
USER "SYSTEM"
SQL> DROP USER TEST;
螳 給.
SQL> CREATE USER TEST IDENTIFIED BY TEST
2 DEFAULT TABLESPACE USERS
3 TEMPORARY TABLESPACE TEMP;
螳 焔給.
SQL> GRANT CREATE SESSTION, CREATE TABLE
2 TO TEST;
GRANT CREATE SESSTION, CREATE TABLE
*
1 る:
ORA-00990: 蟠 覿
SQL> GRANT CREATE SESSION, CREATE TABLE
2 TO TEST;
蟠 覿給.
SQL> CONNECT TEST/TEST
郁屋給.
SQL> CREATE TABLE TEST_TABLE
2 (ID NUMBER);
CREATE TABLE TEST_TABLE
*
1 る:
ORA-01950: 企 'USERS' 蟠
SQL>
覿覈 CREATE TABLE 蟠 譯殊伎 覿蟲螻 企 燕讌 覈詩 蟆
覲 給. 願 焔轟 企 吴 企ろ伎れ QUOTA襯
れ 譯殊 蠏碁 蟆..
SQL> CONN SYSTEM/MANAGER
郁屋給.
SQL> ALTER USER TEST
2 QUOTA 1M ON USERS;
螳 覲蟆暑給.
SQL> CREATE TABLE TEST_TABLE
2 (ID NUMBER);
企 焔給.
SQL>
豢 企 螻殊 蟇一 蠍磯蓋 煙 給..蠍一 蟠 伎
譬 危ｴ覲企襦 蟆給. 蟠 ろ 蟠螻 螳豌 蟠 給.
螳豌 蟠 企 螳豌伎 螳 蟆願, ろ 蟠 一危覯伎
ろ 蟯 蟆.
磯Μ螳 蟆れ 覈 ろ 蟠 蟆伎給..る狩 螳豌企朱 蟆
企, 碁煙, 企ろ, 碁 螳 蟆 襷..ろ 蟠 朱朱 DBA 蟠
螳讌 るジ 蟆 蟠 譴..豈 磯殊 るゴ讌襷..
襾殊 蟠 譯朱 GRANT 覓語 覓碁 危ｴ覲企襦 蟆給.
grant::=
GRANT
{ grant_system_privileges
| grant_object_privileges
}
;
grant_system_privileges::=
{ system_privilege | role | ALL PRIVILEGES }
[, { system_privilege | role | ALL PRIVILEGES }]...
TO grantee_clause [IDENTIFIED BY password] [WITH ADMIN OPTION]
grant_object_privileges::=
{ object_privilege | ALL [PRIVILEGES] } [( column [, column]... )]
[, { object_privilege | ALL [PRIVILEGES] } [( column [, column]... )]]...
on_object_clause
TO grantee_clause
[WITH GRANT OPTION] [WITH HIERARCHY OPTION]
grantee_clause::=
{ user | role | PUBLIC } [, { user | role | PUBLIC }]...
ろ 蟠 覓碁 WITH ADMIN OPTION 給. 蠏碁Μ螻..襷 襷讌襷
PUBLIC企 れ 覲伎企蟲一..ROLE 蟆 譟郁 り 蟲..
朱 PUBLIC 企 れ 覈 蟆 蟠 譯朱 蟆 覩誤 蟆.
讀, USER 覈 ｌ伎朱 PUBLIC れ襯 覈 襯 企
蟆.
WITH ADMIN OPTION 願 蟠 蟠 . 企慨襦
讌..
SQL> CONN SYSTEM/MANAGER
郁屋給.
SQL> GRANT CREATE SESSION TO PUBLIC; -- 覈 蟆 CREATE SESSION 蟠 譴.
蟠 覿給.
SQL> GRANT CREATE TABLE TO TEST2;
蟠 覿給.
SQL> GRANT CREATE VIEW TO TEST
2 WITH ADMIN OPTION; -- WITH ADMIN OPTION 伎 蟠 れ
蟠 覿給.
SQL> CREATE USER TEST2 IDENTIFIED BY TEST2 --TEST2
2 DEFAULT TABLESPACE USERS
3 QUOTA 1M ON USERS;
CREATE USER TEST2 IDENTIFIED BY TEST2
*
1 る:
ORA-01920: 覈 'TEST2'()螳 るジ 襦 企螻 豢
SQL> DROP USER TEST2 CASCADE;
螳 給.
SQL> CREATE USER TEST2 IDENTIFIED BY TEST2 --TEST2
2 DEFAULT TABLESPACE USERS
3 QUOTA 1M ON USERS;
螳 焔給.
SQL> CONNECT TEST2/TEST2 -- 覦襦 語 伎螳 ..
郁屋給.
SQL> CONNECT TEST/TEST
郁屋給.
SQL> CREATE VIEW V_TEST_TABLE
2 AS
3 SELECT * FROM TEST_TABLE;
觀郁 焔給.
SQL> CONN TEST/TEST
郁屋給.
SQL> GRANT CREATE VIEW TO TEST2; -- WITH ADMIN OPTION朱 蟠 れ --> 蟠 螳
蟠 覿給.
SQL> CONNECT TEST2/TEST2
郁屋給.
SQL> CREATE TABLE TEST(ID NUMBER);
企 焔給.
SQL> CREATE VIEW V_TEST -- TEST2 襦 觀 螳ロ..
2 AS
3 SELECT * FROM TEST;
觀郁 焔給.
SQL> CONN SYSTEM/MANAGER
郁屋給.
SQL> CREATE USER TEST3 IDENTIFIED BY TEST3 --TEST3
2 DEFAULT TABLESPACE USERS
3 QUOTA 1M ON USERS;
螳 焔給.
SQL> GRANT CREATE TABLE TO TEST3;
蟠 覿給.
SQL> CONN TEST2/TEST2
郁屋給.
SQL> GRANT CREATE VIEW TO TEST3; -- TEST2 蟠 譴螳 .
GRANT CREATE VIEW TO TEST3
*
1 る:
ORA-01031: 蟠 覿豢覿
SQL> CONN TEST/TEST
郁屋給.
SQL> GRANT CREATE VIEW TO TEST2
2 WITH ADMIN OPTION;
蟠 覿給.
SQL> CONNECT TEST2/TEST2
郁屋給.
SQL> GRANT CREATE VIEW TO TEST3;
蟠 覿給.
SQL> CONNECT TEST3/TEST3
郁屋給.
SQL> CREATE TABLE TEST(ID NUMBER);
企 焔給.
SQL> CREATE VIEW V_TEST
2 AS
3 SELECT * FROM TEST;
觀郁 焔給.
SQL>
蟠 REVOKE 覓語 伎 給. れ 豌 覓碁..
覓碁 豌 覓碁企殊 蠏碁讌...れ襦 蠏語 襷豢 蠍磯
覃企..螳訖 ..
revoke::=
REVOKE
{ revoke_system_privileges
| revoke_object_privileges
}
[, { revoke_system_privileges
| revoke_object_privileges
}
]...
;
revoke_system_privileges::=
{ system_privilege | role | ALL PRIVILEGES }
[, { system_privilege | role | ALL PRIVILEGES }]...
FROM grantee_clause
revoke_object_privileges::=
{ object_privilege | ALL [PRIVILEGES] } [( column [, column]... )]
[, { object_privilege | ALL [PRIVILEGES] } [( column [, column]... )]]...
on_object_clause FROM grantee_clause [CASCADE CONSTRAINTS] [FORCE]
grantee_clause::=
{ user | role | PUBLIC } [, { user | role | PUBLIC }]...
on_object_clause::=
{ schema . object
| { DIRECTORY directory_name
| JAVA { SOURCE | RESOURCE } [schema .] object
}
}
TEST, TEST2, TEST3 襯 燕...譴螳 TEST3
CREAT VIEW 蟠 讌覲願給...蠏碁覃 TEST3 CREATE VIEW覓語
讌 危ｴ覲伎蠍 覦..
SQL> CONN TEST/TEST
郁屋給.
SQL> REVOKE CREATE VIEW FROM TEST2;
蟠 豬給.
SQL> CONN TEST2/TEST2
郁屋給.
SQL> CREATE VIEW V2
2 AS
3 SELECT ID FROM TEST_TABLE;
SELECT ID FROM TEST_TABLE
*
3 る:
ORA-00942: 企 觀郁 譟伎讌 給
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
TEST TABLE
V_TEST VIEW
SQL> CREATE VIEW V_TEST2
2 AS
3 SELECT ID FROM TEST;
CREATE VIEW V_TEST2
*
1 る:
ORA-01031: 蟠 覿豢覿
SQL> CONNECT TEST3/TEST3
郁屋給.
SQL> CREATE VIEW V_TEST2
2 AS
3 SELECT ID FROM TEST;
觀郁 焔給.
螳豌 蟠 蟆 危ｴ覲企襦 蟆給..朱 螳豌願 譯朱 蟆 れ螻
螳給. 覓伎 企慨 蟆 譬蟆譯...~
SQL> CONN TEST/TEST
郁屋給.
SQL> CREATE TABLE OBJ_TEST
2 (OBJ VARCHAR2(10));
企 焔給.
SQL> GRANT SELECT ON OBJ_TEST TO TEST2
2 WITH GRANT OPTION; -- TEST2 蟆 WITH GRANT OPTION ..
蟠 覿給.
SQL> -- TEST2 襦 TEST3 蟆 TEST OBJ_TEST 企
SQL> -- SELECT 蟠 譴.
SQL> CONNECT TEST2/TEST2
郁屋給.
SQL> SELECT * FROM TEST.OBJ_TEST;
貊螳 給.
SQL> GRANT SELECT ON TEST.OBJ_TEST TO TEST3
2 ;
蟠 覿給.
SQL> CONN TEST3/TEST3
郁屋給.
SQL> SELECT * FROM OBJ_TEST;
SELECT * FROM OBJ_TEST
*
1 る:
ORA-00942: 企 觀郁 譟伎讌 給
SQL> SELECT * FROM TEST.OBJ_TEST;
貊螳 給.
SQL> CONN TEST/TEST
郁屋給.
SQL> -- TEST2 TEST OBJ_TEST SELECT 蟠 豬.
SQL> REVOKE SELECT ON OBJ_TEST FROM TEST2;
蟠 豬給.
SQL> CONN TEST3/TEST3
郁屋給.
SQL> -- 蟠 豬蟇 TEST2 碁 TEST3 蟾讌 蠏殊 覿螳ロ 譟..
SQL> SELECT * FROM TEST.OBJ_TEST;
SELECT * FROM TEST.OBJ_TEST
*
1 る:
ORA-00942: 企 觀郁 譟伎讌 給
SQL>
INSERT, UPDATE, DELETE 蟠 襷谿螳讌 .. 譬 覲旧″ れ螻 螳
給.
GRANT REFERENCES (employee_id),
UPDATE (employee_id, salary, commission_pct)
ON hr.employees
TO oe;
WITH ADMIN OPTION 螻 WITH GRANT OPTION 谿企ゼ 蟆. 讀, WITH ADMIN OPTION
蟠 一朱 豬讌 朱 WITH GRANT OPTION 一朱 豬螳
蟆 覲 給. 企 伎 覓伎手? WITH ADMIN OPTION 朱 蟠 譯朱 蟆
譟焔慨 る狩 一危 蠍磯讌 朱 WITH GRANT OPTION 朱 蟠 譴
譟焔慨 る狩 一危 蠍磯蠍 覓語..
SQL> COL GRANTOR FORMAT A15
SQL> COL GRANTEE FORMAT A15
SQL> COL TABLE_NAME FORMAT A15
SQL> CONNECT TEST/TEST
郁屋給.
SQL> GRANT SELECT ON OBJ_TEST TO TEST2;
蟠 覿給.
SQL> SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE
2 FROM ALL_TAB_PRIVS
3 WHERE TABLE_NAME = 'OBJ_TEST';
GRANTOR GRANTEE TABLE_NAME PRIVILEGE
--------------- --------------- --------------- ------------------------------
TEST TEST2 OBJ_TEST SELECT
SQL> SELECT * FROM DBA_SYS_PRIVS
2 WHERE GRANTEE = 'TEST';
GRANTEE PRIVILEGE ADMIN_
--------------- ------------------------------ ------
TEST CREATE VIEW YES
TEST CREATE TABLE NO
TEST CREATE SESSION NO
SQL>
豐れ瓦~ 蟾覓旧觧 覺れ企..襦れ 蟆 蟲一...襦..企 危ｴ覺れ..
CONNECT, RESOURCE 襦れ 蟇..
襦れ れ 蟠 覓苦 ...蠏谿蟆 CREATE SESSION, CREATE TABLE, 企
SELECT 蟠 炎骸 螳 殊殊 譴朱 れ..
螳螳 覲襦 觜訣 覓企ゼ 蠏碁９朱 螳螳 蠏碁９ 襦れ
伎伎 蟠 覿 蟆 螻殊 蟯襴 覦覯伎..
覓朱...襦朱 螳螳 蠏碁９襷 襴蟆 襷れ伎手規..
れ 襦れ 襷 覓碁..
CREATE ROLE role
[ NOT IDENTIFIED
| IDENTIFIED { BY password | USING [schema .] package | EXTERNALLY | GLOBALLY }
]
;
螳... 煙 螳 蠏 襦れ る れ れ 譴
蟲..
SQL> CONNECT SYSTEM/MANAGER
郁屋給.
SQL> CREATE ROLE TEST_ROLE;
襦れ 焔給.
SQL> GRANT CREATE SESSION, CREATE TABLE TO TEST_ROLE;
蟠 覿給.
SQL> GRANT SELECT, UPDATE, INSERT ON TEST.OBJ_TEST TO TEST_ROLE;
蟠 覿給.
SQL> GRANT TEST_ROLE TO TEST;
蟠 覿給.
SQL> SELECT * FROM DBA_SYS_PRIVS
2 WHERE GRANTEE = 'TEST';
GRANTEE PRIVILEGE ADMIN_
--------------- ------------------------------ ------
TEST CREATE VIEW YES
TEST CREATE TABLE NO
TEST CREATE SESSION NO
SQL> CONNECT TEST/TEST
郁屋給.
SQL> SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE
2 FROM ALL_TAB_PRIVS
3 WHERE TABLE_NAME = 'OBJ_TEST';
GRANTOR GRANTEE TABLE_NAME PRIVILEGE
--------------- --------------- --------------- ------------------------------
TEST TEST2 OBJ_TEST DELETE
TEST TEST2 OBJ_TEST SELECT
TEST TEST_ROLE OBJ_TEST INSERT
TEST TEST_ROLE OBJ_TEST SELECT
TEST TEST_ROLE OBJ_TEST UPDATE
SQL>
..企郁碓 豈覲願 企慨覃 蠍覦 蟆..譴 蟆 企至 螳螳
襯 覿襯 企 蟠 譴 蟆瑚螳 譴 蟆. 企 蟆 覦覯
蟆願...覈覯 る慨 り襖 覲企 蟆...
譴 蟆 覓伎語襯 螳螻 覲伎蠍...