原文链接：点击打开链接
摘要： Oracle赋权的回收权限是使用grant和revoke语句，但是赋权和回收权限语句执行完成后就会立即生效么？另外Oracle的权限又分为系统权限、角色权限和对象权限，这三种权限的grant和revoke生效时间又是怎样的呢。
Oracle赋权的回收权限是使用grant和revoke语句，但是赋权和回收权限语句执行完成后就会立即生效么？另外Oracle的权限又分为系统权限、角色权限和对象权限，这三种权限的grant和revoke生效时间又是怎样的呢。我们来看官方文档是如何说的：
Depending on what is granted or revoked, a grant or revoke takes effect at different times:
All grants and revokes of system and object privileges to anything (users, roles, and
PUBLIC) take immediate effect.All grants and revokes of roles to anything (users, other roles,
PUBLIC) take effect only when a current user session issues aSET ROLEstatement to reenable the role after the grant and revoke, or when a new user session is created after the grant or revoke.
You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.
从上面的描述中我们可以知道，grant和revoke系统权限和对象权限时会立即生效，而grant或revoke角色时对当前会话不会立即生效，除非使用set role语句启用角色或重新连接会话后设置才会生效。
下面以11.2.0.4为例做一个测试，是否与官方文档描述的一致。
一、首先创建一个测试用户，赋予connect角色
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
sys@ORCL>
create
user
zhaoxu identified
by
zhaoxu;
User
created.
sys@ORCL>
grant
connect
to
zhaoxu;
Grant
succeeded.
sys@ORCL>
select
*
from
dba_role_privs
where
grantee=
'ZHAOXU'
;
GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R
------------------------------ ------------------------------ --------- ---------
ZHAOXU
CONNECT
NO
YES
sys@ORCL>
select
*
from
dba_sys_privs
where
grantee=
'ZHAOXU'
;
no
rows
selected
sys@ORCL>
select
*
from
dba_tab_privs
where
grantee=
'ZHAOXU'
;
no
rows
selected
sys@ORCL>conn zhaoxu/zhaoxu
Connected.
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------
CONNECT
zhaoxu@ORCL>
select
*
from
session_privs;
PRIVILEGE
------------------------------------------------------------
CREATE
SESSION
zhaoxu@ORCL>
create
table
t (id number) segment creation immediate;
create
table
t (id number)
*
ERROR
at
line 1:
ORA-01031: insufficient
privileges
|
现在的zhaoxu用户只有CONNECT角色，只能连接到数据库，其他基本什么都做不了。
二、测试系统权限和对象权限的grant和revoke
现在打开另一个会话赋予system privilege给zhaoxu用户
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
--session 2
sys@ORCL>
grant
create
table
,unlimited tablespace
to
zhaoxu;
Grant
succeeded.
--session 1
zhaoxu@ORCL>
select
*
from
session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE
SESSION
UNLIMITED TABLESPACE
CREATE
TABLE
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------------------------------------
CONNECT
zhaoxu@ORCL>
create
table
t (id number) segment creation immediate;
Table
created.
--使用segment creation immediate是因为要避免11g的新特性段延迟创建造成影响
|
在赋予zhaoxu用户create table和unlimited tablespace系统权限全会话1没有做任何操作，权限就会立即生效。
再测试revoke权限的情况
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
--session 2
sys@ORCL>
revoke
unlimited tablespace
from
zhaoxu;
Revoke
succeeded.
--session 1
zhaoxu@ORCL>
create
table
t1 (id number) segment creation immediate;
create
table
t1 (id number) segment creation immediate
*
ERROR
at
line 1:
ORA-01950:
no
privileges
on
tablespace
'USERS'
zhaoxu@ORCL>
select
*
from
session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE
SESSION
CREATE
TABLE
|
同样可以看到回收操作可以立即生效，现有session无需做任何操作。
测试对象权限的grant和revoke
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
--grant测试
--session 1
zhaoxu@ORCL>
select
count
(*)
from
zx.t;
select
count
(*)
from
zx.t
*
ERROR
at
line 1:
ORA-00942:
table
or
view
does
not
exist
--session 2
sys@ORCL>
grant
select
on
zx.t
to
zhaoxu;
Grant
succeeded.
sys@ORCL>
select
*
from
dba_tab_privs
where
grantee=
'ZHAOXU'
;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------------------------------ ------------------------------ ---------- ---------- ---------- --------- ---------
ZHAOXU ZX T ZX
SELECT
NO
NO
--session 1
zhaoxu@ORCL>
select
count
(*)
from
zx.t;
COUNT
(*)
----------
99999
zhaoxu@ORCL>
select
*
from
session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE
SESSION
CREATE
TABLE
--revoke测试
--session 2
sys@ORCL>
revoke
select
on
zx.t
from
zhaoxu;
Revoke
succeeded.
sys@ORCL>
select
*
from
dba_tab_privs
where
grantee=
'ZHAOXU'
;
no
rows
selected
--session 1
zhaoxu@ORCL>
select
count
(*)
from
zx.t;
select
count
(*)
from
zx.t
*
ERROR
at
line 1:
ORA-00942:
table
or
view
does
not
exist
|
对对象权限的grant和revoke操作与系统权限的一致，所有的命令都是立即生效，包括对已经连接的会话。
三、测试角色的grant和revoke
现在的zhaoxu用户仍然只有connect角色，并且已经打开一个会话
|
1
2
3
4
5
6
7
8
9
10
11
12
|
--session 2
sys@ORCL>
select
*
from
dba_role_privs
where
grantee=
'ZHAOXU'
;
GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R
------------------------------ ------------------------------ --------- ---------
ZHAOXU
CONNECT
NO
YES
--session 1
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------
CONNECT
|
测试grant DBA权限
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
--session 1查看会话中的角色
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------------------------------------
CONNECT
--session 2赋予zhaoxu用户dba角色
sys@ORCL>
grant
dba
to
zhaoxu;
Grant
succeeded.
sys@ORCL>
select
*
from
dba_role_privs
where
grantee=
'ZHAOXU'
;
GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R
------------------------------ ------------------------------ --------- ---------
ZHAOXU DBA
NO
YES
ZHAOXU
CONNECT
NO
YES
--session 1再次查看会话中的角色，没有dba角色，也没有查看v$session的权限
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------------------------------------
CONNECT
zhaoxu@ORCL>
select
count
(*)
from
v$session;
select
count
(*)
from
v$session
*
ERROR
at
line 1:
ORA-00942:
table
or
view
does
not
exist
--session 1执行set role命令，可以看到DBA及相关的角色已经加载到session1中了，也可以查询v$session
zhaoxu@ORCL>
set
role dba;
Role
set
.
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
......
19
rows
selected.
zhaoxu@ORCL>
select
count
(*)
from
v$session;
COUNT
(*)
----------
29
--使用zhaoxu用户打开session 3，可以看到新会话中默认会加载DBA及相关角色
[oracle@rhel6 ~]$ sqlplus zhaoxu/zhaoxu
SQL*Plus: Release 11.2.0.4.0 Production
on
Sat Jan 21 16:22:01 2017
Copyright (c) 1982, 2013, Oracle.
All
rights reserved.
Connected
to
:
Oracle
Database
11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining
and
Real
Application Testing options
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
......
20
rows
selected.
|
测试revoke DBA角色
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
--session 2回收DBA角色
sys@ORCL>
revoke
dba
from
zhaoxu;
Revoke
succeeded.
sys@ORCL>
select
*
from
dba_role_privs
where
grantee=
'ZHAOXU'
;
GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R
------------------------------ ------------------------------ --------- ---------
ZHAOXU
CONNECT
NO
YES
--session 3查看会话的角色，仍然有DBA及相关角色
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
......
20
rows
selected.
--使用zhaoxu用户打开session 4，查看只有CONNECT角色
[oracle@rhel6 ~]$ sqlplus zhaoxu/zhaoxu
SQL*Plus: Release 11.2.0.4.0 Production
on
Sat Jan 21 16:30:19 2017
Copyright (c) 1982, 2013, Oracle.
All
rights reserved.
Connected
to
:
Oracle
Database
11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining
and
Real
Application Testing options
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------------------------------------
CONNECT
--session 3执行set role命令
zhaoxu@ORCL>
set
role dba;
set
role dba
*
ERROR
at
line 1:
ORA-01924: role
'DBA'
not
granted
or
does
not
exist
zhaoxu@ORCL>
set
role
all
;
Role
set
.
zhaoxu@ORCL>
select
*
from
session_roles;
ROLE
------------------------------------------------------------------------------------------
CONNECT
|
从上面的测试中可以总结出，grant和revoke系统权限和对象权限时会立即生效，而grant或revoke角色时对当前会话不会立即生效，除非使用set role语句启用角色或重新连接会话后设置才会生效。与官方文档的描述一致。
但是有一个问题是如果查看已经连接的其他会话所拥有的role呢？
官方文档：http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG99974
system privilege:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#BABEFFEE
object privilege:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#BGBCIIEG
set role:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10004.htm#SQLRF01704
扫一扫
1981
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
