CODEWITHSUNDEEP
databaseoracle-databaseoracle11gdatabase-administration
watchme
watchme

Reputation: 740

How to assign a user just to one tablespace? - Oracle 11g

When installing a new db, it is a best practice to create a new user and a new tablespace, because creating tables shouldn't be done with SYS/SYSTEM or/and on the tablespace "SYSTEM".

So I created the user "alex" and the tablespace "alexData" with the following commands:

CREATE TABLESPACE alexData datafile 'C:/oraclexe/alexData.dbf'
size 100M;
ALTER USER alex QUOTA UNLIMITED ON alexData;
ALTER USER alex QUOTA 0 ON SYSTEM;

I want to accomplish that the user "alex" is only able to save his data in the "alexData" tablespace.

Why? Because when he wants to access a table he shouldn't always have to mention the tablespace "alexData".

otherwise he would always need to mention it like:

SELECT *
FROM alexData.table

but I want that he always is JUST in that tablespace so he doesn't need to mention it:

SELECT *
FROM table;

Is this possible?

Upvotes: 0

Views: 3301

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

First of all , Consequence of what @Mat told, you can not use like

SELECT * FROM alexData.table_ but SELECT * FROM alex.table_, since a table may be prefixed with a schema name. As you may notice, you can not use table,which is a keyword for oracle, as table name, so i've used table_ instead.

When you create user, the object is automatically created inside that tablespace. As an example, when create table ... statement issued, there's no need to mention about the tablespace provided that you create or alter your user with DEFAULT TABLESPACE phrase:

CREATE USER alex IDENTIFIED BY alex321
DEFAULT TABLESPACE alexData
TEMPORARY TABLESPACE alexTempData;

OR

ALTER USER alex IDENTIFIED BY alex321
DEFAULT TABLESPACE alexData;

Upvotes: 1

Jon H
Jon H

Reputation: 11

You cannot create triggers on a table owned by SYS, or SYSTEM see:

Why cannot I create triggers on objects owned by SYS?

If the table on which you wish to create a trigger is a table which you have created, it should live on the ALEX schema. This schema should have privileges to create triggers on tables created in the same schema. As previously mentioned in the comments, you should revoke the CREATE ANY TABLE privilege from the ALEX schema, as this user should only be able to create tables on their own schema.

Upvotes: 1

Related Questions