1. 系统权限：能否登录，创建对象，更改表，定义表等，通常是由DBA授予，查看有哪些DBA系统权限
1 SQL> select distinct privilege from dba_sys_privs; 2 3 PRIVILEGE 4 -------------------------------------------------------------------------------- 5 CREATE TABLE 6 UNLIMITED TABLESPACE 7 CREATE HIERARCHY 8 INHERIT ANY PRIVILEGES 9 UPDATE ANY CUBE BUILD PROCESS 10 ALTER ANY CUBE DIMENSION 11 DROP ANY EDITION 12 ADMINISTER ANY SQL TUNING SET 13 CREATE JOB 14 GRANT ANY OBJECT PRIVILEGE 15 EXECUTE ANY LIBRARY 16 ......... 17 18 242 rows selected.
2. 查看一个普通用户拥有哪些系统权限
1 SQL> conn testerp/testerp@erp 2 Connected. 3 SQL> select * from session_privs; 4 5 PRIVILEGE 6 -------------------------------------------------------------------------------- 7 CREATE SESSION 8 CREATE TABLE 9 10 SQL> conn sys/oracle@erp as sysdba 11 Connected. 12 SQL> grant create any table to testerp; ----授权 CREATE ANY TABLE 权限给TESTERP 13 14 Grant succeeded. 15 16 SQL> conn testerp/testerp@erp 17 Connected. 18 SQL> select * from session_privs; 19 20 PRIVILEGE 21 -------------------------------------------------------------------------------- 22 CREATE SESSION 23 CREATE TABLE ----只能在自己模式下创建表 24 CREATE ANY TABLE --- 不仅可以在自己模式下创建表，还可以在其他模式下（比如SCOTT）创建表，但是创建出来的表的owner是SCOTT 25 26 SQL>
3. 对象权限：能否查询、操作他人的对象(ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES,SELECT, UPDATE)
1 SQL> show user 2 USER is "SYS" 3 SQL> grant select on scott.emp to testerp; ---- 将SCOTT模式下对EMP表的SELECT权限授权给TESTERP 4 5 Grant succeeded. 6 7 SQL> conn testerp/testerp@erp 8 Connected. 9 SQL> select count(1) from scott.emp; --- TESTERP拥有对SCOTT.EMP的SELECT权限 10 11 COUNT(1) 12 ---------- 13 14 14 15
授权列给其他用户
1 SQL> conn scott/tiger@erp 2 Connected. 3 SQL> grant update(sal) on test to testerp; --- SCOTT用户将对TEST表SAL列更新的权限授权给TESTERP 4 5 Grant succeeded. 6 7 SQL> conn testerp/testerp@erp 8 Connected. 9 10 SQL> update scott.test set sal=8888; 11 12 14 rows updated. 13 14 SQL>
4. 角色包：Oracle预定义了三个角色包---CONNECT/RESOURCE/DBA
1 SQL> conn sys/oracle@erp as sysdba
2 Connected.
3
4 SQL> revoke create session,create table from testerp; ------ #回收TESTERP的CREATE SESSION 和 CREATE TABLE权限#
5
6 Revoke succeeded.
7
8 SQL> conn testerp/testerp@erp -----#回收后TESTERP不能正常登录#
9 ERROR:
10 ORA-01045: user TESTERP lacks CREATE SESSION privilege; logon denied
11
12
13 Warning: You are no longer connected to ORACLE.
14 SQL> conn sys/oracle@erp as sysdba
15 Connected.
16
17 SQL> grant connect to testerp; ---- #授权CONNECT角色包给TESTERP#
18
19 Grant succeeded.
20
21 SQL> conn testerp/testerp@erp;
22 Connected.
23 SQL> select * from session_privs; ---- #查看CONNECT角色包里包含的权限#
24
25 PRIVILEGE
26 --------------------------------------------------------------------------------
27 CREATE SESSION
28 CREATE ANY TABLE
29 SET CONTAINER
30
31 SQL> conn sys/oracle@erp as sysdba
32 Connected.
33 SQL> revoke connect from testerp;
34
35 Revoke succeeded.
36
37 SQL> grant resource to testerp; ----- #授权RESOURCE角色包给TESTERP#
38
39 Grant succeeded.
40
41 SQL> conn testerp/testerp@erp ----- #因为RESOURCE角色包里没有CREATE SESSION权限，所以授权后TESTERP不能登录#
42 ERROR:
43 ORA-01045: user TESTERP lacks CREATE SESSION privilege; logon denied
44
45
46 Warning: You are no longer connected to ORACLE.
47 SQL> conn sys/oracle@erp as sysdba
48 Connected.
49 SQL> grant connect to testerp;
50
51 Grant succeeded.
52
53 SQL> conn testerp/testerp@erp;
54 Connected.
55 SQL> select * from session_privs;
56
57 PRIVILEGE
58 --------------------------------------------------------------------------------
59 CREATE SESSION
60 CREATE TABLE
61 CREATE ANY TABLE
62 CREATE CLUSTER
63 CREATE SEQUENCE
64 CREATE PROCEDURE
65 CREATE TRIGGER
66 CREATE TYPE
67 CREATE OPERATOR
68 CREATE INDEXTYPE
69 SET CONTAINER
70
71 11 rows selected.
72
73 SQL> conn sys/oracle@erp as sysdba
74 Connected.
75 SQL> revoke create session from testerp; ---- #单独回收CREATE SESSION权限失败，因为授权的是CONNECT角色包，即怎样授权，就怎样回收#
76 revoke create session from testerp
77 *
78 ERROR at line 1:
79 ORA-01952: system privileges not granted to 'TESTERP'
80
1 SQL> select * from session_privs where privilege like '%TABLESPACE%';
2
3 PRIVILEGE
4 --------------------------------------------------------------------------------
5 CREATE TABLESPACE
6 ALTER TABLESPACE
7 MANAGE TABLESPACE
8 DROP TABLESPACE
9 UNLIMITED TABLESPACE ----- #这个权限比较特殊，是可以单独回收的#
10
11 SQL> conn sys/oracle@erp as sysdba
12 Connected.
13 SQL> revoke unlimited tablespace from testerp;
14
15 Revoke succeeded.
16
17 SQL>
81
82 SQL> revoke connect, resource from testerp;
83
84 Revoke succeeded.
85
86 SQL> grant dba to testerp; --- #授权DBA角色包#
87
88 Grant succeeded.
89
90 SQL> conn testerp/testerp@erp;
91 Connected.
92 SQL> select count(1) from session_privs;
93
94 COUNT(1)
95 ----------
96 239 --- #DBA角色包里包含239个权限#
97
当然我们也可以自己创建角色，将相关权限放到角色中，注意：对象权限和系统权限需分别放入到角色中
1 SQL> show user
2 USER is "SYS"
3 SQL> create role std; --- #创建一个名为STD的角色包#
4
5 Role created.
6
7 SQL> grant create table,create session to std; --- #将CREATE TABLE,CREATE SESSION 放入到STD角色包中#
8
9 Grant succeeded.
10
11 SQL> grant select on scott.emp to std; ----- #将对SCOTT.EMP的SELECT权限放入到STD角色包中#
12
13 Grant succeeded.
14
15 SQL> grant std to testerp; ----#将STD角色包授权给TESTERP#
16
17 Grant succeeded.
18
19 SQL> conn testerp/testerp@erp
20 Connected.
21 SQL> select * from session_privs;
22
23 PRIVILEGE
24 --------------------------------------------------------------------------------
25 CREATE SESSION
26 CREATE TABLE
27
28 SQL> conn sys/oracle@erp as sysdba
29 Connected.
30 SQL> grant create any tanble , update(sal) on scott.emp to testerp; ----#系统权限和对象权限在同一个GRANT语句中是不能授权成功的，需分别授权#
31 grant create any tanble , update(sal) on scott.emp to testerp
32 *
33 ERROR at line 1:
34 ORA-00990: missing or invalid privilege
35
5. 权限级联
1. 系统权限级联：with admin option----回收时其他用户不受影响
2. 对象权限级联：with grant option----回收时其他用户的权限也随之消失