Grant issue on Oracle

Would like to grant user U1 some privileges that
U1 can create/update/delete/insert/select tables/views and procedures only in one tablespace TS1, that is,

U1 can
create table t1 (ID number);
where t1 is in TS1, but U1 can't

create table TS2(ID number);
where TS2 is a tablespace other than TS1.

Can it be done and how?
jl66Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
>>> U1 should not create the table T in schema U2.


That's default privileges.  No need to do anything to prevent that.

You have to specifically grant "create ANY table" privilege to allow that to happen.  Just don't do that.
0
 
slightwv (䄆 Netminder) Commented:
Does this need to be 'real time'?  Such that a new table is created in TS1, the grants are automatically applied?

The easiest way to use dba_tables (or user_tables) to generate the necessary grant statements.

If you need this 'real time', you can turn that into a DDL trigger.
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Create is easy, that is done with quotas.  If the user doesn't have quota on the tablespace (and they hopefully do not have the unlimited tablespace privilege), then they cannot create objects in that tablespace.

Views, procedures, packages and functions are all dictionary items and are stored in the SYSTEM tablespace.  They cannot be stored anywhere else.

Select/insert/update/delete is hard.  This can be done with individual grants, but how do you control it?  If an object is created, how does the user get permission to it?  If an object is moved to another tablespace, how is permission revoked?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
1.  Easiest control for the tablespace is to:
ALTER USER U1 DEFAULT TABLESPACE TS1;  -- all create statements will default here

ALTER USER U1 QUOTA 1M ON TS1; -- set quota total as appropriate

ALTER USER U1 QUOTA 0M ON TS2; --  ensures no access

2.  To enable object creation (owned by U1):  
GRANT RESOURCE TO U1;

where the RESOURCE role out-of-the-box give certain permissions.  See (using a privileged account such as SYS):

select privilege
from dba_sys_privs
where grantee = 'RESOURCE':

PRIVILEGE
--------------------------------------
CREATE TYPE
CREATE TABLE
CREATE CLUSTER
CREATE TRIGGER
CREATE OPERATOR
CREATE SEQUENCE
CREATE INDEXTYPE
CREATE PROCEDURE
0
 
slightwv (䄆 Netminder) Commented:
Please ignore my post.  I completely misunderstood the question!
0
 
johnsoneSenior Oracle DBACommented:
I'm not sure I fully understand the question either.

dvz,

How does quotas control select/insert/update/delete access?
0
 
sdstuberCommented:
also note,  views and procedures aren't really created in a tablespace.  They are simply entries within the data dictionary.

I guess in that sense they are written to the SYSTEM tablespace, but you can't control that.
0
 
DavidSenior Oracle Database AdministratorCommented:
That pings a reminder to the author, the privilege CREATE VIEW would be needed if not in the RESOURCE role.

Johnsone, perhaps it's time we had an update from the author, to see what (if anything) is on track.  But, U1 must have a default tablespace, right?  My logic is to set WHERE his/her objects will be created by default; to ensure some quota is enabled on that tablespace (obviously QUOTA UNLIMITED is an option); and to prevent creation on TS2 with the zero allowance.  

For the benefit of our audience the RESOURCE role controls the DDL access; and DML (SUID) is implied because U1 owns its own objects.
0
 
jl66Author Commented:
1) First of all, I correct my error in the question:

From
"but U1 can't
create table TS2(ID number); "

to
but U1 can't
create table U2.t(ID number);
where U2 is another user.

2) Thanks a lot for all of you. I just tried

alter user U1 DEFAULT TABLESPACE TS1;
alter USER U1 QUOTA 0 on TS1;
alter USER U1 QUOTA 0 on TS2;

SQL> create table t(id number(2));

Table created.

SQL> create table U2.t(id number(2));

Table created.

so if we force the quota in TS1 to be 0, the tables can still be created in TS1.

Every guru, any idea?
0
 
DavidSenior Oracle Database AdministratorCommented:
Ah, try to insert into the table, please.
0
 
jl66Author Commented:
Where TS2 is default tablespace for U2.

U1 should not create the table T in schema U2. How to prevent?
0
 
jl66Author Commented:
Only insert can't be performed if the quota in TS1 is 0.
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'TS1'. That is good, but as mentioned before, first, how to prevent from creating a table, a view ... in another user's schema?
0
 
johnsoneSenior Oracle DBACommented:
For preventing a user from creating an object in another schema, you need to revoke all of the ANY privileges granted to the user.

To get the ones that are directly granted, try this query:

select privilege
from dba_sys_privs
where privilege like '% ANY %'
and grantee = 'U1';

If the privilege is granted through a role, that will require some more investigation.
0
 
sdstuberCommented:
>>>  but as mentioned before, first, how to prevent from creating a table, a view ... in another user's schema?

already shown above, that's default security.

simply don't allow the user to get the "create any " privileges
0
 
DavidSenior Oracle Database AdministratorCommented:
In like manner, U2 cannot do anything with U1 data UNTIL:

U1 grants the appropriate permission(s) to user(s) and/or role(s).
0
 
jl66Author Commented:
Thanks a lot for everyone's input. Sorry for my original question which misled slightwv.
0
All Courses

From novice to tech pro — start learning today.