_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 蟠螻殊
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 覈 l伎朱  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 蟠 炎骸 螳 殊殊  譴朱 れ..
 螳螳  覲襦 觜訣 覓企ゼ  蠏碁9朱  螳螳 蠏碁9 襦れ
伎伎 蟠 覿 蟆 螻殊 蟯襴 覦覯伎..
覓朱...襦朱 螳螳 蠏碁9襷 襴蟆 襷れ伎手規..
れ 襦れ 襷 覓碁..

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> 
 

..企郁碓 豈覲願 企慨覃 蠍覦   蟆..譴 蟆 企至 螳螳 
襯 覿襯 企 蟠 譴 蟆瑚螳 譴 蟆. 企 蟆 覦覯
 蟆願...覈覯 る慨 り襖 覲企    蟆...
譴 蟆 覓伎語襯 螳螻 覲伎蠍...



蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

蟆 覯企. 蠏碁覃 れ 殊 蟆企.