Solved

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

Posted on 2016-09-03
4
50 Views
Last Modified: 2016-09-07
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.
0
Comment
Question by:goldieretriever
  • 2
4 Comments
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 41783584
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.
0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 250 total points
ID: 41783597
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
0
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 41783612
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
0
 

Author Closing Comment

by:goldieretriever
ID: 41788301
Thank you for your help - the Oracle Security guide will be a great asset for me in the future
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

929 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

9 Experts available now in Live!

Get 1:1 Help Now