用户对象权限管理

这里讲述的是用户的对象权限管理。则一般是限制用户对数据库的对象，
比如说表的创建，访问，更新，插入等权限的管理。

---连接到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

 #由于访问（查询）的权限被收回，无法再访问表中的数据。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2128203/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2128203/