Solved

Grant issue on Oracle

Posted on 2013-01-09
16
414 Views
Last Modified: 2013-01-09
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?
0
Comment
Question by:jl66
  • 4
  • 4
  • 3
  • +2
16 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 150 total points
Comment Utility
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
 
LVL 23

Assisted Solution

by:David
David earned 150 total points
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Please ignore my post.  I completely misunderstood the question!
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
I'm not sure I fully understand the question either.

dvz,

How does quotas control select/insert/update/delete access?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 23

Expert Comment

by:David
Comment Utility
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
 

Author Comment

by:jl66
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 23

Expert Comment

by:David
Comment Utility
Ah, try to insert into the table, please.
0
 

Author Comment

by:jl66
Comment Utility
Where TS2 is default tablespace for U2.

U1 should not create the table T in schema U2. How to prevent?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 200 total points
Comment Utility
>>> 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
 

Author Comment

by:jl66
Comment Utility
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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>  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
 
LVL 23

Expert Comment

by:David
Comment Utility
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
 

Author Closing Comment

by:jl66
Comment Utility
Thanks a lot for everyone's input. Sorry for my original question which misled slightwv.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now