第十七章： 权限管理
1、权限（privilege）：system privilege and object privilege
1） system privilege：针对于database 的相关权限
2） object privilege：针对于schema 的object
2、 查看系统权限
10:55:12 SQL> desc system_privilege_map;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
PRIVILEGE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40)
PROPERTY NOT NULL NUMBER
10:55:41 SQL> select * from system_privilege_map;
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-3 ALTER SYSTEM 0
-4 AUDIT SYSTEM 0
-5 CREATE SESSION 0
-6 ALTER SESSION 0
-7 RESTRICTED SESSION 0
-10 CREATE TABLESPACE 0
-11 ALTER TABLESPACE 0
-12 MANAGE TABLESPACE 0
-13 DROP TABLESPACE 0
-15 UNLIMITED TABLESPACE 0
。。。。。。
-280 CREATE EXTERNAL JOB 0
166 rows selected.
-------select any table 访问dba_xxx数据字典视图
10:55:49 SQL> grant select any table to scott;
Grant succeeded.
10:57:59 SQL> conn scott/tiger
Connected.
10:58:03 SQL>
10:58:03 SQL> desc user_sys_privs;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
USERNAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
10:58:13 SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SCOTT SELECT ANY TABLE NO
10:58:20 SQL> select * from tom.t01;
ID
----------
1
2
3
10:58:29 SQL> select * from sys.dba_users;
select * from sys.dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
10:58:38 SQL>
---------默认普通用户不能去访问dba_xxx 视图，需要修改以下参数
11:00:23 SQL> show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
11:00:44 SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
11:00:48 SQL> startup force
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
11:01:03 SQL> conn scott/tiger
Connected.
11:02:12 SQL> select TABLE_NAME from dba_tables where owner='SCOTT';
TABLE_NAME
------------------------------
EMPLOYEES
TEST
DEPT
EMP
BONUS
SALGRADE
EMP1
EXCEPTIONS
DEPT1
ADMIN_EXT_EMPLOYEES
10 rows selected
3、分配、回收系统权限
grant ---------with admin option （如果用户获得权限时，设置此参数，用户可以将权限再授予别的用户）
11:06:55 SQL> grant select any table to scott with admin option;
Grant succeeded.
11:07:05 SQL> conn scott/tiger
Connected.
11:07:08 SQL>
11:07:08 SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SCOTT SELECT ANY TABLE YES
11:07:17 SQL> grant select any table to tom;
Grant succeeded.
11:07:22 SQL> conn tom/tom
Connected.
11:07:26 SQL>
11:07:26 SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TOM CREATE TABLE NO
TOM SELECT ANY TABLE NO
TOM CREATE SESSION NO
11:07:30 SQL> select * from scott.emp where rownum <3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
-------------- revoke with admin option ，在回收权限时，不能级联。
11:07:44 SQL> conn /as sysdba
Connected.
11:07:51 SQL>
11:07:51 SQL> revoke select any table from scott;
Revoke succeeded.
11:08:02 SQL> conn scott/tiger
Connected.
11:08:10 SQL>
11:08:10 SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
11:08:14 SQL> conn tom/tom
Connected.
11:08:18 SQL>
11:08:18 SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TOM CREATE TABLE NO
TOM SELECT ANY TABLE NO
TOM CREATE SESSION NO
11:08:20 SQL>
----------必须一一收回
11:09:01 SQL> revoke select any table from tom;
Revoke succeeded.
11:09:11 SQL> conn tom/tom
Connected.
11:09:14 SQL>
11:09:14 SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TOM CREATE TABLE NO
TOM CREATE SESSION NO
11:09:18 SQL>
4、对象权限
grant ---------with grant option （如果用户获得权限时，设置此参数，用户可以将权限再授予别的用户）
11:15:51 SQL> grant all on scott.emp to public;
----all 代表所有的对象权限，public 代表所有的用户
11:16:01 SQL> conn tom/tom
Connected.
11:16:51 SQL>
1* select * from user_tab_privs
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- ---------- ---------- ---------------------------------------- --- ---
TOM SCOTT EMP SCOTT SELECT NO NO
11:16:53 SQL> delete from scott.emp;
14 rows deleted.
11:17:53 SQL> rollback;
Rollback complete.
11:18:00 SQL
11:18:33 SQL> revoke all on scott.emp from public;
Revoke succeeded.
11:19:16 SQL> conn /as sysdba
Connected.
11:19:22 SQL>
11:19:22 SQL> grant update on scott.emp to tom with grant option;
Grant succeeded.
11:19:45 SQL> create user rose identified by rose ;
User created.
11:20:04 SQL> grant create session to rose;
Grant succeeded.
11:20:13 SQL> conn tom/tom
Connected.
11:20:16 SQL>
11:20:16 SQL> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- ---------- ---------- ---------------------------------------- --- ---
TOM SCOTT EMP SCOTT UPDATE YES NO
TOM SCOTT EMP SCOTT SELECT NO NO
11:20:32 SQL> grant update on scott.emp to rose;
Grant succeeded.
11:20:46 SQL> conn rose/rose
Connected.
11:20:50 SQL>
11:20:50 SQL> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- ---------- ---------- ---------------------------------------- --- ---
ROSE SCOTT EMP TOM UPDATE NO NO
-------------- revoke with grant option ，在回收权限时，级联。
11:20:54 SQL> conn /as sysdba
Connected.
11:21:00 SQL>
11:21:00 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
----只能从直接授予者回收权限
11:21:14 SQL> revoke update on scott.emp from tom;
Revoke succeeded.
11:21:22 SQL> conn tom/tom
Connected.
11:21:25 SQL>
11:21:25 SQL> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- ---------- ---------- ---------------------------------------- --- ---
TOM SCOTT EMP SCOTT SELECT NO NO
--------针对列授予对象权限
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>
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
11:29:53 SQL> update scott.emp set sal=10000 where empno=7788;
1 row updated.
11:30:01 SQL> rollback;
Rollback complete.
11:30:54 SQL>
1* select GRANTEE,table_name,COLUMN_NAME,PRIVILEGE from user_col_privs
GRANTEE TABLE_NAME COLUMN_NAME PRIVILEGE
------------------------------ ---------- ------------------------------ ----------------------------------------
TOM EMP SAL UPDATE
11:30:54 SQL>
视图：
显示用户授出的列权限
04:47:57 SQL>
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
显示用户所授出的对象权限
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
显示用户所具有的对象权限（收到）
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 SCOT
转载于:https://blog.51cto.com/tiany/791809
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
