Solved

Grant issue on Oracle

Posted on 2013-01-09
16
445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
16 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38760103
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 35

Assisted Solution

by:johnsone
johnsone earned 150 total points
ID: 38760121
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
ID: 38760202
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38760222
Please ignore my post.  I completely misunderstood the question!
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38760366
I'm not sure I fully understand the question either.

dvz,

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

Expert Comment

by:sdstuber
ID: 38760376
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
ID: 38760424
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
ID: 38760448
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
 
LVL 23

Expert Comment

by:David
ID: 38760455
Ah, try to insert into the table, please.
0
 

Author Comment

by:jl66
ID: 38760467
Where TS2 is default tablespace for U2.

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

Accepted Solution

by:
sdstuber earned 200 total points
ID: 38760508
>>> 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
ID: 38760515
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 35

Expert Comment

by:johnsone
ID: 38760585
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 74

Expert Comment

by:sdstuber
ID: 38760599
>>>  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
ID: 38760661
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
ID: 38760911
Thanks a lot for everyone's input. Sorry for my original question which misled slightwv.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

710 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