1.system privileges
--查看系统所有的系统权限的列表
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
。。。。
-322 UPDATE ANY CUBE BUILD PROCESS 0
-326 UPDATE ANY CUBE DIMENSION 0
-327 ADMINISTER SQL MANAGEMENT OBJECT 0
-328 ALTER PUBLIC DATABASE LINK 0
-329 ALTER DATABASE LINK 0
-350 FLASHBACK ARCHIVE ADMINISTER 0
208 rows selected.
2.创建用户
SYS@prod> create user demo identified by demo;
SYS@prod> conn demo/demo;
ERROR:
ORA-01045: user DEMO lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SYS@prod> conn / as sysdba
Connected.
SYS@prod>
--给用户授予相应的权限
SYS@prod> grant create session,create table,
2 create sequence,create view
3 to demo;
Grant succeeded.
SYS@prod> conn demo/demo
Connected.
DEMO@prod>
3.创建角色
SYS@prod> create role manager;
SYS@prod> grant create table,create view to manager;
SYS@prod> create user alice identified by alice;
SYS@prod> grant create session to alice;
SYS@prod> grant manager to alice;
4.oracle11g创建表的特点
SYS@prod> conn alice/alice
Connected.
ALICE@prod> create table test (id number,name varchar2(20));
ALICE@prod> insert into test values(1,'mary');
insert into test values(1,'mary')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
--延迟段创建
SYS@prod> show parameter deferr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
5.object privileges
HR@prod> grant select
2 on employees
3 to demo;
Grant succeeded.
HR@prod> conn demo/demo
Connected.
DEMO@prod> select count(*) from hr.employees;
COUNT(*)
----------
107
--同时授权update给一个用户和角色
HR@prod> grant update (department_id,manager_id)
2 on departments to demo,manager;
Grant succeeded.
1.权限的收回 revoke
SYS@prod> revoke create table from demo;
HR@prod> revoke update on departments from demo;
2.系统权限的级联授予与收回
create table create table
sys ------------------> demo ---------------> alice
with admin option
ALICE@prod> create table test2(id number);
create table test2(id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
DEMO@prod> create table test1(id number);
create table test1(id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
--sys授权给demo，并且with admin option
SYS@prod> grant create table
2 to demo
3 with admin option; --表示不但得到该权限，还能将这个权限授予他人；
Grant succeeded.
--demo授权给alice
SYS@prod> conn demo/demo
Connected.
DEMO@prod> grant create table to alice;
Grant succeeded.
--将权限从demo收回，alice的权限会不会受到影响？
SYS@prod> revoke create table from demo;
--demo的权限丧失
SYS@prod> conn demo/demo
Connected.
DEMO@prod> create table test3(id number);
create table test3(id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
ALICE@prod> create table test2(id number);
Table created.
结论：系统权限，并不是级联收回的；
3.对象权限的级联授予与收回
select on employees select on employees
HR -------------------------> DEMO ----------------------> ALICE
with grant option
--当前情况下，demo，alice都无权查看hr.employees
DEMO@prod> select count(*) From hr.employees;
select count(*) From hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
ALICE@prod> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
--hr给demo授权，并且with grant option
HR@prod> grant select
2 on employees
3 to demo
4 with grant option;
Grant succeeded.
--demo将权限授予alice
HR@prod> conn demo/demo
Connected.
DEMO@prod> grant select
2 on hr.employees
3 to alice;
Grant succeeded.
--hr将权限从demo收回
ALICE@prod> conn hr/hr
Connected.
HR@prod> revoke select
2 on employees
3 from demo;
Revoke succeeded.
HR@prod> conn demo/demo
Connected.
DEMO@prod> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
DEMO@prod> conn alice/alice
Connected.
ALICE@prod> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
结论：对象权限是级联收回的；
用户权限的查询，也就是用户拥有多少权限：
1.用户拥有的角色。角色拥有的对象权限和系统权限；
2.用户被授予的系统权限；
3.用户被授予的对象权限；
1.用户拥有哪些角色?
HR@prod> col username for a20
HR@prod> col granted_role for a30
HR@prod> col admin_option for a20
HR@prod> select username,granted_role,admin_option from user_role_privs;
USERNAME GRANTED_ROLE ADMIN_OPTION
-------------------- ------------------------------ --------------------
HR RESOURCE NO
2.查看角色拥有哪些系统权限？
HR@prod> col role for a20
HR@prod> col privileges for a20
HR@prod> select role,privilege,admin_option from role_sys_privs
2 where role='RESOURCE';
ROLE PRIVILEGE ADMIN_OPTION
-------------------- ---------------------------------------- --------------------
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
8 rows selected.
3.查看角色拥有哪些对象权限？
--人为授予resource一个对象权限；
SCOTT@prod> grant select on dept to resource;
Grant succeeded.
HR@prod> col role for a10
HR@prod> col owner for a20
HR@prod> col table_name for a20
HR@prod> col column_name for a20
HR@prod> col privilege for a20
SCOTT@prod> select * from role_tab_privs
2 where role='RESOURCE';
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
---------- -------------------- -------------------- -------------------- -------------------- ---
RESOURCE SCOTT DEPT (null) SELECT NO
4.查看用户被单独授予了哪些系统权限？
--使用sys用户授予hr一个权限
SYS@prod> grant create materialized view to hr;
HR@prod> col username for a20
HR@prod> col privilege for a30
HR@prod> select username,privilege,admin_option
2 from user_sys_privs;
USERNAME PRIVILEGE ADMIN_OPTION
-------------------- ------------------------------ --------------------
HR CREATE VIEW NO
HR UNLIMITED TABLESPACE NO
HR CREATE MATERIALIZED VIEW NO
HR CREATE DATABASE LINK NO
HR CREATE SEQUENCE NO
HR CREATE SESSION NO
HR CREATE PUBLIC SYNONYM NO
HR ALTER SESSION NO
HR CREATE SYNONYM NO
9 rows selected.
5.用户单独授予的对象权限？
HR@prod> col grantee for a10
HR@prod> col owner for a10
HR@prod> col table_name for a10
HR@prod> col grantor for a10
HR@prod> col privilege for a30
HR@prod> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- ---------- ---------- ------------------------------ --- ---
HR SYS DBMS_STATS SYS EXECUTE NO NO
OE HR COUNTRIES HR SELECT NO NO
OE HR COUNTRIES HR REFERENCES NO NO
OE HR DEPARTMENTS HR SELECT NO NO
OE HR EMPLOYEES HR REFERENCES NO NO
OE HR EMPLOYEES HR SELECT NO NO
OE HR JOBS HR SELECT NO NO
OE HR JOB_HISTORY HR SELECT NO NO
OE HR LOCATIONS HR REFERENCES NO NO
OE HR LOCATIONS HR SELECT NO NO
DEMO HR V_EMP HR SELECT NO NO
11 rows selected.
6.USER_TAB_PRIVS_MADE
当前用户，授予其他用户什么样的对象权限；
HR@prod> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- ---------- ------------------------------ --- ---
DEMO V_EMP HR SELECT NO NO
OE COUNTRIES HR REFERENCES NO NO
OE JOB_HISTORY HR SELECT NO NO
OE EMPLOYEES HR SELECT NO NO
OE EMPLOYEES HR REFERENCES NO NO
OE LOCATIONS HR SELECT NO NO
OE LOCATIONS HR REFERENCES NO NO
OE COUNTRIES HR SELECT NO NO
OE DEPARTMENTS HR SELECT NO NO
OE JOBS HR SELECT NO NO
10 rows selected.
7.USER_TAB_PRIVS_RECD
HR@prod> select * from user_tab_privs_recd;
OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- ---------- ------------------------------ --- ---
SYS DBMS_STATS SYS EXECUTE NO NO
8.USER_COL_PRIVS_MADE
HR@prod> select * from user_col_privs_made;
GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
---------- ---------- -------------------- ---------- ------------------------------ ---
MANAGER DEPARTMENTS MANAGER_ID HR UPDATE NO
MANAGER DEPARTMENTS DEPARTMENT_ID HR UPDATE NO
9.USER_COL_PRIVS_RECD
SCOTT@prod> grant update (deptno,dname) on dept to hr;
HR@prod> select * from user_col_privs_recd;
OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
---------- ---------- -------------------- ---------- ------------------------------ ---
SCOTT DEPT DEPTNO SCOTT UPDATE NO
SCOTT DEPT DNAME SCOTT UPDATE NO
10.SESSION_PRIVS
--查看当前用户拥有的系统权限
HR@prod> select * from session_privs;
PRIVILEGE
------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE PUBLIC SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
16 rows selected.
