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
Solved

Grant issue on Oracle

Posted on 2013-01-09
16
432 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)
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 34

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 76

Expert Comment

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

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 34

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

860 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