Create Oracle User to only have ability to create tables, triggers, functions etc in one tablespace

goldieretriever
goldieretriever used Ask the Experts™
on
Please help me in creating an Oracle user that can only create Oracle objects in a single tablespace that can only view tables that are granted them in another tablespace within the same SID.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA
Commented:
Functions, procedures, packages, triggers, views, etc. are created in the SYSTEM tablespace.  There is no way around that.  You cannot control where Oracle stores code.

If you want a user to only create objects in one tablespace, then only give them quota in that one tablespace.  Unless they have a role that has been granted unlimited tablespace or it is granted to public, that works just fine.

You cannot grant privileges based on a tablespace.  You can generate a script or statements that would do that, but there are no tablespace privileges.  Something like this:
SELECT 'grant select on ' 
       || owner 
       || '.' 
       || table_name 
       || ' to <newuser>;' 
FROM   dba_tables 
WHERE  tablespace_name = '<ts>'; 

Open in new window

Replace <newuser> with the username and <ts> with the tablespace.  That will generate a list of SQL statements, then just run them.
Geert GOracle dba
Top Expert 2009
Commented:
after you have read and understood the security guide for assigning privileges
you will understand that, even with the latest version, that's not possible

http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_privileges.htm#TDPSG30000
Senior Oracle DBA
Commented:
How is 11.2 the latest version?  If you are going to say read the latest version of the doc, at least post a link to the latest version.  This would be the latest documentation version of the same section of documentation.

http://docs.oracle.com/database/121/TDPSG/GUID-800F33CF-26E2-4246-86D5-7DD5B3B7B2ED.htm#TDPSG30000

Author

Commented:
Thank you for your help - the Oracle Security guide will be a great asset for me in the future

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial