使用者物件許可權管理

這裡講述的是使用者的物件許可權管理。則一般是限制使用者對資料庫的物件，
比如說表的建立，訪問，更新，插入等許可權的管理。

---連線到SYS使用者建立測試表：

sys@PROD>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

 

sys@PROD>create table mytable(

  2  id number(5),

  3  name varchar2(10),

  4  created date);

Table created.

 

---往表中插入資料：

sys@PROD>insert into mytable values(13125,'yuuu',sysdate);

1 row created.

sys@PROD>commit;

Commit complete.

 

sys@PROD>insert into mytable values(13126,'hhhsss',sysdate);

1 row created.

sys@PROD>commit;

Commit complete.

 

---檢視錶中的資料：

sys@PROD>select * from mytable;

        ID NAME       CREATED

---------- ---------- -------------------

     13125 yuuu       2016-11-09 15:04:21

     13126 hhhsss     2016-11-09 15:04:53

 

---把表mytable的查詢插入與更新的許可權授權給使用者：

sys@PROD>grant select,insert,update on mytable to suxing;

Grant succeeded.

 

sys@PROD>grant select,insert,update on mytable to scott;

Grant succeeded.

 

--嘗試連線到suxing或者Scott使用者對錶mytable進行一些操作：

--suxing使用者查詢：

suxing@PROD>select * from mytable;

select * from mytable

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

 

suxing@PROD>select * from sys.mytable;

        ID NAME       CREATED

---------- ---------- ---------

     13125 yuuu       09-NOV-16

     13126 hhhsss     09-NOV-16

 

--suxing使用者插入資料：

suxing@PROD>insert into mytable values(13127,'ssuu',sysdate);

insert into mytable values(13127,'ssuu',sysdate)

            *

ERROR at line 1:

ORA-00942: table or view does not exist

 

suxing@PROD>insert into sys.mytable values(13127,'ssuu',sysdate);

1 row created.

suxing@PROD>commit;

Commit complete.

 

--檢視資料：

suxing@PROD>select * from sys.mytable;

        ID NAME       CREATED

---------- ---------- -------------------

     13125 yuuu       2016-11-09 15:04:21

     13126 hhhsss     2016-11-09 15:04:53

     13127 ssuu       2016-11-09 15:20:59

 

--suxing使用者更新表資料：

suxing@PROD>update sys.mytable set name='hhss'

  2  where id=13126;

1 row updated.

suxing@PROD>commit;

Commit complete.

 

--再次查詢資料：

suxing@PROD>select * from sys.mytable;

        ID NAME       CREATED

---------- ---------- -------------------

     13125 yuuu       2016-11-09 15:04:21

     13126 hhss       2016-11-09 15:04:53

     13127 ssuu       2016-11-09 15:20:59

 

--suxing使用者刪除資料：

suxing@PROD>delete sys.mytable where id=13127;

delete sys.mytable where id=13127

           *

ERROR at line 1:

ORA-01031: insufficient privileges

 

---在SYS使用者下執行刪除該條資料的語句：

sys@PROD>delete sys.mytable where id=13127;

1 row deleted.

sys@PROD>commit;

Commit complete.

 

---在Scott使用者查詢資料表的資料：

scott@PROD>select * from sys.mytable;

        ID NAME       CREATED

---------- ---------- ---------

     13125 yuuu       09-NOV-16

     13126 hhss       09-NOV-16

---收回許可權：

sys@PROD>revoke select, insert,update on mytable from suxing;

Revoke succeeded.

sys@PROD>revoke select,insert,update on mytable from scott;

Revoke succeeded.

--再次檢視錶資料：

suxing@PROD>select * from sys.mytable;

select * from sys.mytable

                  *

ERROR at line 1:

ORA-00942: table or view does not exist

 #由於訪問（查詢）的許可權被收回，無法再訪問表中的資料。