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
---------------------------------------- ---------- ------------------------------
查看本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/