Permission control problems caused by a User Creation

Permission control problems caused by a User Creation

Developers raised a requirement, which is interesting.

Requirement Description: The development database is required to create A new user A (default tablespace TBS_1). Because the database is A shared database, there are other Schemas (example: tablespace TBS_2) to be used by developers in other groups, developers of user A should avoid using create table t (col name) tablespace tbs_2 to create A table on tbs_2 by specifying A tablespace, that is, user A is prohibited from performing operations on the tbs_2 tablespace.

Procedure:
1. create user:

Create user a identified by a default tablespace tbs_1;

Grant resouce, connect to;

The default tablespace is tbs_1.
Grant the resource and connect roles.

2. Test Table creation:

SQL> create table t1 (id number );

SQL> insert into t1 values (1 );

SQL> commit;

No error is reported. Table t1 is created on user A's default tablespace tbs_1.
Next, let's see if he can create a table on tbs_2.

SQL> create table t2 (id number) tablespace dep_tbs;

SQL> insert into t2 values (1 );

SQL> commit;

Or. The reason is that user A has the following system permissions:

SQL> select privilege from user_sys_privs;

PRIVILEGE

---------------------

UNLIMITED TABLESPACE

Unlimited tablespace indicates that there is no limit to the use of table space. Therefore, you can create A table in any TABLESPACE. The reason why user A has this system permission is that the operation of the resource role is granted. For more information, see the previous article:

3. revoke the unlimited tablespace permission and test again:

Revoke unlimited tablespace from;

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'

It is found that tables can still be created on tbs_1 and tbs_2, but data cannot be inserted. The reason is that user A has no permission to use any tablespace because the tablespace permission is revoked just now.

4. Grant user A the permission to use the table space in tbs_1 before testing:

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'

In this case, user A can create A table on tbs_1 and insert data. However, you can still create tables and insert data on tbs_2.

A careful friend may be able to see some problems from (3). In step (3), user A does not have any permission to use tablespace, but can still create A table, only data cannot be inserted. After verification, this issue is related to a new feature of the 11g, the latency section (see the http://blog.itpub.net/7192724/viewspace-1247807/) where the version of this library is:

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

To be accurate, it should be a new feature of 11gR2, called the delay segment, that is, the space of the delay allocation segment. Simply put, by default, the physical space allocation of the table (as well as the index and LOB) is postponed until the first record is inserted into the table. That is, when the actual data is inserted into the table, the space is allocated for each object initialization. 11.2.0.1 does not support partition tables, bitmap join indexes, and domain indexes. In version 11.2.0.2, partitioned tables are supported.

In other words, when creating a table, it is not like the previous version. At this time, it is allocated space. Instead, when the first record is inserted in the table, the space size is defined, the storage information of the table can be seen in the relevant view only when the table is allocated space. The advantage is that the space allocation is performed only when the table is actually used, and it appears to be accurate, however, the disadvantage (or cannot be called a defect, but can only be called an illusion) is that you can create a table in a tablespace without permission, even though you cannot insert data to it.

To avoid this illusion, Oracle provides a parameter switch:

You can set this parameter at the system or session level. If this parameter is set to false, the delay segment function is disabled. In this case, you cannot create tables in tablespaces without permission.

5. Solutions to the above problems (from the database perspective ):
Solution 1: global settings
Directly set alter session set deferred_segment_creation = false to disable the latency segment feature at the system level. That is, all users in this database will not use the latency segment feature.
Solution 2: user-level settings
If you think solution 1 is too coarse-grained, you can perform fine-grained control, requiring that only user A disallow the use of delay segments, you can use triggers to control (previously useless, first write, if there are omissions, also ask the masters to make corrections ):

Create or replace trigger log_deferred

After logon 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;

/

This is to determine whether the user name is A during logon. If yes, this parameter is set to false at the session level.
No matter solution 1 or solution 2, if user A creates A table in tbs_2, an error is returned:

Create table t2 (id number) tablespace tbs_2

* ERROR at line 1: ORA-01950: no privileges on tablespace 'tbs _ 2'

Summary:
1. the unlimited tablespace permission is granted with the resource role.
2. The new section delay feature will create an "illusion" for tables in tablespaces without permission. You can disable it using the deferred_segment_creation parameter.
3. Oracle is really profound and profound. Any small detail may contain a lot of knowledge and principles. At the same time, he provides methods to enable and disable it, and the software design accomplishments can only be said to be amazing.

I would like to thank Xiao Fei, maclean, and Master NIU for their discussions and suggestions.