转载

一个用户创建引发的权限控制问题

昨天开发同学提了一个需求,比较有意思。

需求描述:要求开发库创建一个新用户A(默认表空间TBS_1),由于这库是共享库,还有其他schema(示例:表空间TBS_2)被其他组的开发人员使用,需要避免使用A用户的开发人员,利用create table t(col name) tablespace tbs_2通过指定表空间的方式在tbs_2上创建表,即禁止用户A可以在tbs_2表空间上进行操作。

操作过程: 
1.创建用户A:

create user a identified by a default tablespace tbs_1; 
grant resouce,connect to a;  
  • 1
  • 2
  • 2

指定默认表空间是tbs_1。 
授予resource和connect角色。

2.测试建表:

SQL> create table t1(id number); 
SQL> insert into t1 values(1); 
SQL> commit; 
  • 1
  • 2
  • 3
  • 4
  • 2
  • 3
  • 4

未报错,t1表会创建在用户A的默认表空间tbs_1上。 
接下来,看看他能不能在tbs_2上创建表。

SQL> create table t2(id number) tablespace dep_tbs; 
SQL> insert into t2 values(1); 
SQL> commit; 
  • 1
  • 2
  • 3
  • 2
  • 3

也可以。原因是用户A有如下系统权限:

SQL> select privilege from user_sys_privs; 
PRIVILEGE 
--------------------- 
UNLIMITED TABLESPACE 
  • 1
  • 2
  • 3
  • 4
  • 2
  • 3
  • 4

UNLIMITED TABLESPACE表示对表空间的使用无限制,因此可以在任意表空间中创建表,之所以用户A有这个系统权限,是因为授予了resource角色的操作。具体可以参见之前的文章: 
http://blog.itpub.net/7192724/viewspace-1416548/

3.收回UNLIMITED TABLESPACE权限再测试:

revoke UNLIMITED TABLESPACE from a;
 
create table t1(id number); 
insert into t1 values(1)
 * ERROR at line 1: ORA-01950: no privileges on tablespace 'TBS_1'
 
create table t2(id number) tablespace tbs_2; 
insert into t2 values(1)
 * ERROR at line 1: ORA-01950: no privileges on tablespace 'TBS_2'  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

发现仍可以在tbs_1和tbs_2上建表,但均不能插入数据。原因就是由于刚才回收了tablespace的权限,导致用户A没有任何表空间上的使用权限。

4.授予用户A在tbs_1表空间使用权限再测试:

alter user a quota unlimited on gbc_tbs; 
 
create table t1(id number);
SQL> insert into t1 values(1);
SQL> commit;
 
create table t2(id number) tablespace tbs_2;
insert into t2 values(1)
 * ERROR at line 1: ORA-01950: no privileges on tablespace 'TBS_2' 
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

发现此时用户A可以在tbs_1上建表,插入数据。但仍可以在tbs_2上建表,以及插入数据。

可能细心的朋友从(3)就能看出一些问题来了,在步骤(3)中,用户A没有任何tablespace的使用权限,但仍可以create table建表,只是不能插入数据。经过查验,这个问题和11g的一个新特性有关,即“延迟段”(可参见http://blog.itpub.net/7192724/viewspace-1247807/),此库的版本是:

SQL> select * from v$version; 
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 
PL/SQL Release 11.2.0.4.0 - Production 
CORE    11.2.0.4.0      Production 
TNS for Linux: Version 11.2.0.4.0 - Production 
NLSRTL Version 11.2.0.4.0 - Production
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

准确的说,应该是11gR2的一个新特性,叫延迟段,即延迟分配段空间。简单讲,默认将表(以及索引、LOB)的物理空间分配推迟到第一条记录插入到表中时。即有实际的数据插入表中时,再为每个对象初始化空间分配。其中11.2.0.1不支持分区表 、bitmap join indexes和domain indexes。11.2.0.2版本开始支持分区表。

换句话说,在create table的时候,并不像以前的版本,此时就已经为其分配了空间,而是等表中插入第一条记录的时候,按照定义的空间大小,开始为其分配空间,此时才能在相关视图中看见该表的存储信息,好处就是空间分配只有当真正使用的时候才会进行,显得要会精确,但缺点(或者不能叫缺点,只能叫假象)就是看着好像是用户可以在一个没有使用权限的表空间中创建表,尽管不能向其插入数据。

为了避免这种“假象”,Oracle提供了一个参数开关: 
一个用户创建引发的权限控制问题
可以在system或session级别设置该参数,当为false,则会关闭延迟段的功能,此时就不可以在未有权限的表空间中创建表了。

5.针对上述问题的解决方案(数据库角度): 
方案1:全局设置 
直接设置alter session set deferred_segment_creation=false,系统级禁用延迟段特性,即此库所有用户都不会使用延迟段功能了。 
方案2:用户级设置 
如果觉得方案1粒度太粗,可以做细粒度控制,要求只有用户A禁止使用延迟段,可以利用触发器来控制(以前没用过,第一次写,要是有疏漏,还请大师们补充指正):

create or replace trigger log_deferred 
after logon on database 
declare logon_user VARCHAR2(10); 
begin select user into logon_user from dual;
  if logon_user = 'A' 
   then 
   execute immediate 'alter session set deferred_segment_creation=false';
  end if;
end;
/  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

即登录时判断用户名是否是A,如果是,则session级设置此参数为false,可以达到此目的。 
无论方案1还是方案2,用户A再在tbs_2创建表,会有报错:

create table t2(id number) tablespace tbs_2
 * ERROR at line 1: ORA-01950: no privileges on tablespace 'TBS_2'  
  • 1
  • 2
  • 3
  • 4
  • 2
  • 3
  • 4


总结: 
1.UNLIMITED TABLESPACE权限是随着resource角色授予。 
2.段延迟这个新特性,会造成未有权限的表空间中可以建表的“假象”,可以使用deferred_segment_creation参数关闭之。 
3.Oracle实在是博大精深,任何一小细节可能都蕴含着很多知识和原理,同时他有提供了启用和关闭的方法,软件设计造诣,只能说是叹为观止了。

感谢晓飞、maclean还有牛大师几位的讨论和建议。

正文到此结束
Loading...