0

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?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
watchme
  • 720
  • 1
  • 9
  • 25
  • 5
    `alexData` in `SELECT * FROM alexData.table` is **not** the tablespace, it's the schema. – Mat Mar 13 '18 at 13:34
  • But how can I make alex to save only in the alexData-tablespace? (Because when he creates tables in the SYSTEM-Tablespace, he cannot assign a trigger to those) – watchme Mar 13 '18 at 13:34
  • Is he creating them as `CREATE TABLE SYSTEM.MY_TABLE ...`? Because if so, he's not creating them in the SYSTEM **tablespace**, he's creating them in the SYSTEM **schema**, and you'll need to revoke the `CREATE ANY TABLE` privilege from him. – kfinity Mar 13 '18 at 13:38
  • Ok what do I have to do to resolve a "Cannot create triggers on object owned by SYS"-Exception? Create an own tablespace or an own schema for alex? – watchme Mar 13 '18 at 13:45
  • Why do you need to allow user to create triggers on SYS-owned objects? You created production tables under SYS schema? SYS is not intended fo such a usage. – wolfrevokcats Mar 13 '18 at 14:15

2 Answers2

1

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.

Jon H
  • 11
  • 3
  • I have read here: https://stackoverflow.com/questions/21935011/can-one-schema-have-multiple-users?lq=1 That as soon as you create a user, it has its own schema. But in my case, "Alex" is still saving his tables in the "SYSTEM"-Schema – watchme Mar 13 '18 at 14:07
  • There is a slightly better [answer](https://stackoverflow.com/questions/880230/difference-between-a-user-and-a-schema-in-oracle) to that question - a user and a schema are essentially the same thing. So you should be able to log in as the user `ALEX`, for which you can specify the default tablespace upon creation. Then when you create tables, they will be created in that schema, and using the tablespace you have defined. – Jon H Mar 13 '18 at 14:16
  • Thanks! I just had to set AlexData as the Default Tablespace! – watchme Mar 13 '18 at 14:22
1

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;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55