2

I have a schema 'TEST' that schema has own tablespace 'TEST'. But when I create index on USERS tablespace, it created. How can I restrict to use USERS tablespace in ORACLE database?

CREATE UNIQUE INDEX "TEST"."INDEX1" ON "TEST"."TESTTABLE" ("TESTNO") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "USERS"

It success, I want TEST user can't create any object on USERS tablespace. It has own TEST tablespace. Is there any configuration? Should user use TABLESPACE "TEST" instead of TABLESPACE "USERS" ?

  • I presume by "schema 'TEST' that schema has own tablespace 'TEST'" that you mean the default tablespace of 'TEST' is 'TEST'. – Colin 't Hart Sep 22 '16 at 8:33
  • Yes. but my collagues said there are some indexes on USERS tablespaces. They don't want this – AhmetMelihBaşbuğ Sep 22 '16 at 12:32
6

The user TEST may have the UNLIMITED TABLESPACE privilege, or a quota defined on the USERS tablespace. To revoke them:

revoke unlimited tablespace from test;
alter user test quota 0 on users;

If the user had the UNLIMITED TABLESPACE privilege without any quota defined, you need to define a quota on the TEST tablespace, so the user can use it again after the above commands:

alter user test quota unlimited on test;
  • ok. thanks. Because of quota 0, data couldn't be inserted on tablespace USERS. Can't we revoke "create index ... tablespace 'USERS'" ? – AhmetMelihBaşbuğ Sep 22 '16 at 12:38
1

+1 for Balazs's answer and want to share a scenario here.

If you grant RESOURCE role to any user, the user gets UNLIMITED TABLESPACE privilege(Prior to 12c) which allows the user to create his/her objects on any tablespace. You still can create index(You have CREATE TABLE priviliege) on the USERS tablespace after revoking UNLIMITED TABLESPACE privilege from TEST user BUT you can not insert data on it becuase you should have quota to do that.

Here is the demo:

SQL> create tablespace test_tbs datafile '/u01/app/oracle/oradata/orcl/test_tbs.dbf' size 50M;

Tablespace created.

SQL> create user test identified by test default tablespace test_tbs quota unlimited on test_tbs;

User created.



SQL> grant create session, resource to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create table testtable(testno number);

Table created.

SQL> CREATE UNIQUE INDEX "TEST"."INDEX1" ON "TEST"."TESTTABLE" ("TESTNO") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "USERS";  2    3    4    5    6  

Index created.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

SQL> conn / as sysdba
Connected.
SQL> revoke unlimited tablespace from test;

Revoke succeeded.

SQL> alter user test quota 0 on users;     

User altered.

SQL> conn test/test  
Connected.
SQL> drop index index1;

Index dropped.

SQL> CREATE UNIQUE INDEX "TEST"."INDEX1" ON "TEST"."TESTTABLE" ("TESTNO") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
TABLESPACE "USERS";  2    3    4    5    6  

Index created.

SQL> insert into testtable values(1);
insert into testtable values(1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST_TBS'


SQL> conn / as sysdba
Connected.
SQL> alter user test quota unlimited on test_tbs;

User altered.

SQL> conn test/test
Connected.


SQL> insert into testtable values(1);
insert into testtable values(1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
  • thanks for demo.. I see Because of quota 0, it couldn't be inserted on index. Can't we revoke "create index ... tablespace 'USERS'" ? – AhmetMelihBaşbuğ Sep 22 '16 at 12:37
  • 1
    @AhmetMelihBaşbuğ No, you can create index once you have create table privilege and appropriate quota on tablespace. – JSapkota Sep 22 '16 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.