2013年4月27日 星期六
10:50
1、权限（privilege）：
【system privilege（系统权限）：针对于database 的相关权限
object privilege （对象权限）：针对于schema 的object】
2、查看系统权限
SYS @ prod > desc system_privilege_map;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
PRIVILEGE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40)
PROPERTY NOT NULL NUMBER
SYS @ prod > 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
-20 CREATE USER 0
-21 BECOME USER 0
-22 ALTER USER 0
-23 DROP USER 0
-30 CREATE ROLLBACK SEGMENT 0
-31 ALTER ROLLBACK SEGMENT 0
-32 DROP ROLLBACK SEGMENT 0
-40 CREATE TABLE 0
-41 CREATE ANY TABLE 0
-42 ALTER ANY TABLE 0
-43 BACKUP ANY TABLE 0
-44 DROP ANY TABLE 0
-45 LOCK ANY TABLE 0
-46 COMMENT ANY TABLE 0
-47 SELECT ANY TABLE 0
-48 INSERT ANY TABLE 0
-49 UPDATE ANY TABLE 0
-50 DELETE ANY TABLE 0
-60 CREATE CLUSTER 0
-61 CREATE ANY CLUSTER 0
-62 ALTER ANY CLUSTER 0
-63 DROP ANY CLUSTER 0
-71 CREATE ANY INDEX 0
-72 ALTER ANY INDEX 0
-73 DROP ANY INDEX 0
-80 CREATE SYNONYM 0
-81 CREATE ANY SYNONYM 0
-82 DROP ANY SYNONYM 0
-83 SYSDBA 0
-84 SYSOPER 0
-85 CREATE PUBLIC SYNONYM 0
-86 DROP PUBLIC SYNONYM 0
-90 CREATE VIEW 0
-91 CREATE ANY VIEW 0
-92 DROP ANY VIEW 0
-105 CREATE SEQUENCE 0
-106 CREATE ANY SEQUENCE 0
-107 ALTER ANY SEQUENCE 0
-108 DROP ANY SEQUENCE 0
-109 SELECT ANY SEQUENCE 0
-115 CREATE DATABASE LINK 0
-120 CREATE PUBLIC DATABASE LINK 0
-121 DROP PUBLIC DATABASE LINK 0
-125 CREATE ROLE 0
-126 DROP ANY ROLE 0
-127 GRANT ANY ROLE 0
-128 ALTER ANY ROLE 0
-130 AUDIT ANY 0
-135 ALTER DATABASE 0
-138 FORCE TRANSACTION 0
-139 FORCE ANY TRANSACTION 0
-140 CREATE PROCEDURE 0
-141 CREATE ANY PROCEDURE 0
-142 ALTER ANY PROCEDURE 0
-143 DROP ANY PROCEDURE 0
-144 EXECUTE ANY PROCEDURE 0
-151 CREATE TRIGGER 0
-152 CREATE ANY TRIGGER 0
-153 ALTER ANY TRIGGER 0
-154 DROP ANY TRIGGER 0
-160 CREATE PROFILE 0
-161 ALTER PROFILE 0
-162 DROP PROFILE 0
-163 ALTER RESOURCE COST 0
-165 ANALYZE ANY 0
-167 GRANT ANY PRIVILEGE 0
-172 CREATE MATERIALIZED VIEW 0
-173 CREATE ANY MATERIALIZED VIEW 0
-174 ALTER ANY MATERIALIZED VIEW 0
-175 DROP ANY MATERIALIZED VIEW 0
-177 CREATE ANY DIRECTORY 0
-178 DROP ANY DIRECTORY 0
-180 CREATE TYPE 0
-181 CREATE ANY TYPE 0
-182 ALTER ANY TYPE 0
-183 DROP ANY TYPE 0
-184 EXECUTE ANY TYPE 0
-186 UNDER ANY TYPE 0
-188 CREATE LIBRARY 0
-189 CREATE ANY LIBRARY 0
-190 ALTER ANY LIBRARY 0
-191 DROP ANY LIBRARY 0
-192 EXECUTE ANY LIBRARY 0
-200 CREATE OPERATOR 0
-201 CREATE ANY OPERATOR 0
-202 ALTER ANY OPERATOR 0
-203 DROP ANY OPERATOR 0
-204 EXECUTE ANY OPERATOR 0
-205 CREATE INDEXTYPE 0
-206 CREATE ANY INDEXTYPE 0
-207 ALTER ANY INDEXTYPE 0
-208 DROP ANY INDEXTYPE 0
-209 UNDER ANY VIEW 0
-210 QUERY REWRITE 0
-211 GLOBAL QUERY REWRITE 0
-212 EXECUTE ANY INDEXTYPE 0
-213 UNDER ANY TABLE 0
-214 CREATE DIMENSION 0
-215 CREATE ANY DIMENSION 0
-216 ALTER ANY DIMENSION 0
-217 DROP ANY DIMENSION 0
-218 MANAGE ANY QUEUE 1
-219 ENQUEUE ANY QUEUE 1
-220 DEQUEUE ANY QUEUE 1
-222 CREATE ANY CONTEXT 0
-223 DROP ANY CONTEXT 0
-224 CREATE ANY OUTLINE 0
-225 ALTER ANY OUTLINE 0
-226 DROP ANY OUTLINE 0
-227 ADMINISTER RESOURCE MANAGER 1
-228 ADMINISTER DATABASE TRIGGER 0
-233 MERGE ANY VIEW 0
-234 ON COMMIT REFRESH 0
-235 EXEMPT ACCESS POLICY 0
-236 RESUMABLE 0
-237 SELECT ANY DICTIONARY 0
-238 DEBUG CONNECT SESSION 0
-241 DEBUG ANY PROCEDURE 0
-243 FLASHBACK ANY TABLE 0
-244 GRANT ANY OBJECT PRIVILEGE 0
-245 CREATE EVALUATION CONTEXT 1
-246 CREATE ANY EVALUATION CONTEXT 1
-247 ALTER ANY EVALUATION CONTEXT 1
-248 DROP ANY EVALUATION CONTEXT 1
-249 EXECUTE ANY EVALUATION CONTEXT 1
-250 CREATE RULE SET 1
-251 CREATE ANY RULE SET 1
-252 ALTER ANY RULE SET 1
-253 DROP ANY RULE SET 1
-254 EXECUTE ANY RULE SET 1
-255 EXPORT FULL DATABASE 0
-256 IMPORT FULL DATABASE 0
-257 CREATE RULE 1
-258 CREATE ANY RULE 1
-259 ALTER ANY RULE 1
-260 DROP ANY RULE 1
-261 EXECUTE ANY RULE 1
-262 ANALYZE ANY DICTIONARY 0
-263 ADVISOR 0
-264 CREATE JOB 0
-265 CREATE ANY JOB 0
-266 EXECUTE ANY PROGRAM 0
-267 EXECUTE ANY CLASS 0
-268 MANAGE SCHEDULER 0
-269 SELECT ANY TRANSACTION 0
-270 DROP ANY SQL PROFILE 0
-271 ALTER ANY SQL PROFILE 0
-272 ADMINISTER SQL TUNING SET 0
-273 ADMINISTER ANY SQL TUNING SET 0
-274 CREATE ANY SQL PROFILE 0
-275 EXEMPT IDENTITY POLICY 0
-276 MANAGE FILE GROUP 1
-277 MANAGE ANY FILE GROUP 1
-278 READ ANY FILE GROUP 1
-279 CHANGE NOTIFICATION 0
-280 CREATE EXTERNAL JOB 0
select any table 访问dba_xxx数据字典视图
SYS @ prod > grant select any table to scott; ——授权
Grant succeeded.
SYS @ prod > conn scott/tiger
Connected.
SCOTT @ prod > desc user_sys_privs;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
USERNAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SCOTT @ prod > select * from user_sys_privs；——【查看用户拥有的系统权限】
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SCOTT SELECT ANY TABLE NO
SCOTT @ prod > select * from tom.text2;
ID
----------
1
2
3
4
5
6
7
8
SCOTT @ prod > select * from sys.dba_users;
select * from sys.dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
——默认普通用户不能去访问dba_xxx 视图，需要修改以下参数
SYS @ prod > show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SYS @ prod > alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SYS @ prod > startup force
SYS @ prod > conn scott/tiger
SCOTT @ prod > select table_name from dba_tables where owner='SCOTT';
TABLE_NAME
-------------------------
DEPT
EMP
BONUS
SALGRADE
EMPLOYEES
ADMIN_EXT_EMPLOYEES
EMP1
3、分配、回收系统权限
grant——with admin option 【如果用户获得权限时，设置此参数，用户可以将权限再授予别的用户】
SYS @ prod > grant select any table to scott with admin option;
Grant succeeded.
SYS @ prod > conn scott/tiger
Connected.
SCOTT @ prod > col usrname for a10
SCOTT @ prod > col privilege for a30
SCOTT @ prod > select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPT
--------------- ------------------------------ ---------
SCOTT UNLIMITED TABLESPACE NO
SCOTT SELECT ANY TABLE YES
SCOTT @ prod > grant select any table to tom;
Grant succeeded.
SCOTT @ prod > conn tom/tom
Connected.
TOM @ prod > select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPT
--------------- ------------------------------ ---------
TOM SELECT ANY TABLE NO
TOM CREATE SESSION NO
TOM UNLIMITED TABLESPACE NO
TOM @ prod > 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 ，在回收权限时，不能级联】。
TOM @ prod > conn /as sysdba
Connected.
SYS @ prod > revoke select any table from scott;
Revoke succeeded.
SCOTT @ prod > conn scott/tiger
Connected.
SCOTT @ prod > select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPT
--------------- ------------------------------ ---------
SCOTT UNLIMITED TABLESPACE NO
SCOTT @ prod > conn tom/tom
Connected.
TOM @ prod > select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPT
--------------- ------------------------------ ---------
TOM SELECT ANY TABLE NO
TOM CREATE SESSION NO
TOM UNLIMITED TABLESPACE NO
——【必须一一收回】
TOM @ prod > conn /as sysdba
Connected.
SYS @ prod > revoke select any table from tom;
Revoke succeeded.
SYS @ prod > conn tom/tom
Connected.
TOM @ prod > select * from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPT
--------------- ------------------------------ ---------
TOM CREATE SESSION NO
TOM UNLIMITED TABLESPACE NO
4、对象权限
grant ---------with grant option ——【如果用户获得权限时，设置此参数，用户可以将权限再授予别的用户】
SYS @ prod > grant all on scott.emp to public; ——【all 代表所有的对象权限，public 代表所有的用户 】
SYS @ prod > conn tom/tom
Connected.
TOM @ prod > select * from user_tab_privs;
no rows selected
【在视图user_tab_privs没有记载，但是权限是授予的了，一样可以执行权限【如果是系统权限就会在user_sys_privs上显示信息】】
TOM @ prod > select ename from scott.emp;
ENAME
------------------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
TOM @ prod > delete from scott.emp;
14 rows deleted.
TOM @ prod > rollback;
Rollback complete.
TOM @ prod > conn /as sysdba
Connected.
SYS @ prod > revoke all on scott.emp from public; ——【回收权限】
Revoke succeeded.
SYS @ prod > grant update on scott.emp to tom with grant option;
Grant succeeded.
SYS @ prod > create user rose identified by rose ;
User created.
SYS @ prod > grant create session to rose;
Grant succeeded.
SYS @ prod > conn tom/tom
Connected.
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
---------- --------------- --------------- --------------- --------------- --------------- ---------------
TOM SCOTT EMP1 SCOTT UPDATE YES NO
TOM @ prod > grant update on scott.emp to rose;
Grant succeeded.
TOM @ prod > conn rose/rose
Connected.
ROSE @ prod > select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
---------- --------------- --------------- --------------- --------------- --------------- ---------------
ROSE SCOTT EMP1 TOM UPDATE NO NO
——【revoke with grant option ，在回收权限时，级联。】
ROSE @ prod > conn /as sysdba
Connected.
SYS @ prod > 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
----只能从直接授予者回收权限
SYS @ prod > revoke update on scott.emp from tom;
Revoke succeeded.
SYS @ prod > conn tom/tom
Connected.
TOM @ prod > select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- ---------- ---------- ---------------------------------------- --- ---
TOM SCOTT EMP SCOTT SELECT NO NO
——针对列授予对象权限
SYS @ prod >grant update(sal) on scott.emp to tom;
Grant succeeded.
SYS @ prod > conn tom/tom
Connected.
TOM @ prod > 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
TOM @ prod > update scott.emp set sal=10000 where empno=7788;
1 row updated.
TOM @ prod > rollback;
Rollback complete.
TOM @ prod > select GRANTEE,OWNER,TABLE_NAME,COLUMN_NAME,PRIVILEGE from user_col_privs;
GRANTEE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE
---------- --------------- --------------- --------------- ---------------
TOM SCOTT EMP SAL UPDATE
与权限相关的视图
SESSION_PRIVS 【用户当前会话拥有的系统权限】
USER_ROLE_PRIVS 【用户被授予的角色】
ROLE_SYS_PRIVS 【用户当前拥有的角色的系统权限】
USER_SYS_PRIVS 【直接授予用户的系统权限】
USER_TAB_PRIVS 【授予用户的对象权限 包含了当前用户给其他用户的对象权限和其他用户给当前用户的对象权限】
ROLE_TAB_PRIVS 【授予角色的表的权限】
USER_TAB_PRIVS_RECD 【其他用户给当前用户的对象权限】
USER_TAB_PRIVS_MADE 【当前用户给其他用户的对象权限】
USER_COL_PRIVS_MADE 【在用户对象列一级上被分配的对象权限】
USER_COL_PRIVS_RECD 【在指定列上分配给用户的对象权限】
【显示用户授出的列权限【user_col_privs_made】
SYS @ prod >select GRANTEE,PRIVILEGE,TABLE_NAME||'.'||COLUMN_NAME tab_column from user_col_privs;
GRANTEE PRIVILEGE TAB_COLUMN
---------- --------------- --------------------
TOM UPDATE EMP.SAL
【显示用户所具有的列权限】
SYS @ prod > select PRIVILEGE,TABLE_NAME||'.'||COLUMN_NAME tab_column,GRANTOR from user_col_privs;
PRIVILEGE TAB_COLUMN GRANTOR
--------------- -------------------- ---------------
UPDATE EMP.SAL SCOTT
【显示用户所授出的对象权限】
SYS @ prod > select grantee ,privilege ,table_name from user_tab_privs_made;
GRANTEE PRIVILEGE TABLE_NAME
------------------------------ ---------------------------------------- ----------
HR DELETE DEPT
HR SELECT DEPT
HR UPDATE DEPT
OE SELECT EMP
【显示用户所具有的对象权限（收到）】
SYS @ prod >select privilege,table_name,grantor from all_tab_privs_recd where grantee='HR';
PRIVILEGE TABLE_NAME GRANTOR
---------------------------------------- ---------- ------------------------------
EXECUTE DBMS_STATS SYS
DELETE DEPT SCOTT
SELECT DEPT SCOTT
UPDATE DEPT SCOTT
转载于:https://www.cnblogs.com/iyoume2008/p/7526565.html
相关资源：OCA认证考试指南（1Z0-051）：Oracle Database 11g SQL Fundamentals I--详细书签版