管理权限（三）--对象权限的管理

 

1、授予对象权限

是指访问其他用户方案对象的权限。

GRANT object_priv｜ALL [(columns)]

ON object

TO {user|role|PUBLIC}

[WITH GRANT OPTION];

ALL：所有对象权限

PUBLIC：授给所有的用户

WITH GRANT OPTION：允许用户再次给其它用户授权。

——针对列授予对象权限

11:24:05 SQL> grant update(sal) on scott.emp to tom;

Grant succeeded.

11:29:39 SQL> conn tom/tom

Connected.

11:29:51 SQL> update scott.emp set comm=100 where empno=7788;  ——对该列无权限修改

update scott.emp set comm=100 where empno=7788

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> update scott.emp set sal=10000 where empno=7788;

1 row updated.

SQL> rollback;

Rollback complete.

SQL> select GRANTEE,OWNER,TABLE_NAME,COLUMN_NAME,PRIVILEGE from user_col_privs;

GRANTEE    OWNER           TABLE_NAME      COLUMN_NAME     PRIVILEGE

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

TOM        SCOTT           EMP             SAL             UPDATE

2、显示对象权限

1）显示对象权限

04:39:24 SQL> select grantor ,owner ||'.'||table_name object,privilege

04:39:34   2   from dba_tab_privs

04:39:41   3     where grantee='HR';

GRANTOR    OBJECT          PRIVILEGE

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

SYS        SYS.DBMS_STATS  EXECUTE

SCOTT      SCOTT.DEPT      UPDATE

SCOTT      SCOTT.DEPT      SELECT

SCOTT      SCOTT.DEPT      DELETE

2)显示列权限

04:42:15 SQL> col owner for a10

04:42:58 SQL> col table_column for a15

04:43:08 SQL> col privileg for a10

04:43:14 SQL> select owner ,table_name||'.'||column_name table_column, privilege  from dba_col_privs

04:44:00   2   where grantee='HR';

OWNER      TABLE_COLUMN    PRIVILEGE

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

SCOTT      EMP.SAL         UPDATE

3)显示用户授出的列权限

04:47:57 SQL> l

1   select grantee,privilege,table_name||'.'||column_name

2    tab_column

3*     from user_col_privs_made；

4）显示用户所具有的列权限

select privilege,table_name||'.'||column_name tab_column,

04:49:38   2    grantor

04:49:43   3      from all_col_privs_recd

04:49:53   4       where grantee='HR';

no rows selected

5)显示用户所授出的对象权限

04:42:47 SQL> col table_name for a10for a10

04:51:19 SQL> select grantee ,privilege ,table_name

04:51:34   2    from user_tab_privs_made;

GRANTEE                        PRIVILEGE                                TABLE_NAME

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

HR                             DELETE                                   DEPT

HR                             SELECT                                   DEPT

HR                             UPDATE                                   DEPT

OE                             SELECT                                   EMP

6）显示用户所具有的对象权限

04:52:45 SQL> select privilege,table_name,grantor

04:52:58   2    from all_tab_privs_recd

04:53:10   3      where grantee='HR';

PRIVILEGE                                TABLE_NAME GRANTOR

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

EXECUTE                                  DBMS_STATS SYS

DELETE                                   DEPT       SCOTT

SELECT                                   DEPT       SCOTT

UPDATE                                   DEPT       SCOTT

3、收回对象权限

grant  ---------with grant option  ——（如果用户获得权限时，设置此参数，用户可以将权限再授予别的用户）

SQL> grant all on scott.emp to public; ——all 代表所有的对象权限，public 代表所有的用户

SQL> conn tom/tom

Connected.

SQL> select * from user_tab_privs;

no rows selected

——在视图user_tab_privs没有记载，但是权限是授予的了，一样可以执行权限【如果是系统权限就会在user_sys_privs上显示信息】

SQL> select ename from scott.emp;

ENAME

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

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

14 rows selected.

SQL> delete from scott.emp;

14 rows deleted.

SQL> rollback;

Rollback complete.

SQL> conn /as sysdba

Connected.

SQL> revoke all on scott.emp from public;——回收权限

Revoke succeeded.

SQL> grant update on scott.emp to tom with grant option;

Grant succeeded.

SQL> create user rose identified by rose ;

User created.

SQL> grant create session to rose;

Grant succeeded.

SQL> conn tom/tom

Connected.

GRANTEE    OWNER           TABLE_NAME      GRANTOR         PRIVILEGE       GRANTABLE       HIERARCHY

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

TOM        SCOTT           EMP1            SCOTT           UPDATE          YES             NO

SQL> grant update on scott.emp to rose;

Grant succeeded.

SQL> conn rose/rose

Connected.

SQL> select * from user_tab_privs;

GRANTEE    OWNER           TABLE_NAME      GRANTOR         PRIVILEGE       GRANTABLE       HIERARCHY

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

ROSE       SCOTT           EMP1            TOM             UPDATE          NO              NO

——revoke          with grant option ，在回收权限时，级联。

SQL> conn /as sysdba

Connected.

SQL> revoke update on scott.emp from rose;

revoke update on scott.emp from rose

*

ERROR at line 1:

ORA-01927: cannot REVOKE privileges you did not grant

----只能从直接授予者回收权限

SQL> revoke update on scott.emp from tom;

Revoke succeeded.

SQL> conn tom/tom

Connected.

11:21:25 SQL> select * from user_tab_privs;

GRANTEE              OWNER      TABLE_NAME GRANTOR    PRIVILEGE                                GRA HIE

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

TOM                  SCOTT      EMP        SCOTT      SELECT                                   NO  NO