oracle privileges-pl
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,
2create sequence,create view
3to 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 TYPEVALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation booleanTRUE
5.object privileges
HR@prod> grant select
2on employees
3to 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)
2on 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
2to demo
3with 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
2on employees
3to demo
4with grant option;
Grant succeeded.
--demo将权限授予alice
HR@prod> conn demo/demo
Connected.
DEMO@prod> grant select
2on hr.employees
3to alice;
Grant succeeded.
--hr将权限从demo收回
ALICE@prod> conn hr/hr
Connected.
HR@prod> revoke select
2on employees
3from 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
2where 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
2where role='RESOURCE';
ROLE OWNERTABLE_NAME COLUMN_NAME PRIVILEGE GRA
---------- -------------------- -------------------- -------------------- -------------------- ---
RESOURCE SCOTTDEPT (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
2from 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 NONO
OE HR COUNTRIESHR SELECT NONO
OE HR COUNTRIESHR REFERENCES NONO
OE HR DEPARTMENTS HR SELECT NONO
OE HR EMPLOYEESHR REFERENCES NONO
OE HR EMPLOYEESHR SELECT NONO
OE HR JOBSHR SELECT NONO
OE HR JOB_HISTORY HR SELECT NONO
OE HR LOCATIONSHR REFERENCES NONO
OE HR LOCATIONSHR SELECT NONO
DEMO HR V_EMPHR SELECT NONO
11 rows selected.
6.USER_TAB_PRIVS_MADE
当前用户，授予其他用户什么样的对象权限；
HR@prod> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTORPRIVILEGE GRA HIE
---------- ---------- ---------- ------------------------------ --- ---
DEMO V_EMP HRSELECT NONO
OE COUNTRIESHRREFERENCES NONO
OE JOB_HISTORY HRSELECT NONO
OE EMPLOYEESHRSELECT NONO
OE EMPLOYEESHRREFERENCES NONO
OE LOCATIONSHRSELECT NONO
OE LOCATIONSHRREFERENCES NONO
OE COUNTRIESHRSELECT NONO
OE DEPARTMENTS HRSELECT NONO
OE JOBS HRSELECT NONO
10 rows selected.
7.USER_TAB_PRIVS_RECD
HR@prod> select * from user_tab_privs_recd;
OWNER TABLE_NAME GRANTORPRIVILEGE GRA HIE
---------- ---------- ---------- ------------------------------ --- ---
SYS DBMS_STATS SYSEXECUTE NONO
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.
页:
[1]