共计 3228 个字符,预计需要花费 9 分钟才能阅读完成。
开发同学提了一个需求,比较有意思。
需求描述:要求开发库创建一个新用户 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;
指定默认表空间是 tbs_1。
授予 resource 和 connect 角色。
2. 测试建表:
SQL> create table t1(id number);
SQL> insert into t1 values(1);
SQL> commit;
未报错,t1 表会创建在用户 A 的默认表空间 tbs_1 上。
接下来,看看他能不能在 tbs_2 上创建表。
SQL> create table t2(id number) tablespace dep_tbs;
SQL> insert into t2 values(1);
SQL> commit;
也可以。原因是用户 A 有如下系统权限:
SQL> select privilege from user_sys_privs;
PRIVILEGE
———————
UNLIMITED TABLESPACE
UNLIMITED TABLESPACE 表示对表空间的使用无限制,因此可以在任意表空间中创建表,之所以用户 A 有这个系统权限,是因为授予了 resource 角色的操作。具体可以参见之前的文章:http://www.linuxidc.com/Linux/2016-06/132439.htm
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’
发现仍可以在 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’
发现此时用户 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
准确的说,应该是 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;
/
即登录时判断用户名是否是 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.UNLIMITED TABLESPACE 权限是随着 resource 角色授予。
2. 段延迟这个新特性,会造成未有权限的表空间中可以建表的“假象”,可以使用 deferred_segment_creation 参数关闭之。
3.Oracle 实在是博大精深,任何一小细节可能都蕴含着很多知识和原理,同时他有提供了启用和关闭的方法,软件设计造诣,只能说是叹为观止了。
感谢晓飞、maclean 还有牛大师几位的讨论和建议。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-06/132438.htm