Solved

oracle 10g privileges

Posted on 2014-03-27
3
486 Views
Last Modified: 2014-03-28
I have a database with 3 users

ACME_PRCS
ACME_MSC
ACME_OUT

DEF_PRCS
DEF_MSC
DEF_OUT

I need to grant create object prividges to ACME_PRCS that will enable it to create objects only in ACME_MSC , ACME_OUT and ACME_PRCS but not in and of the DEF schemas ...

can you help me in trying to work out what grants I need to provide ACME_PRCS ...
0
Comment
Question by:jhacharya
3 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 39960844
Hi acharya,

Thats pretty simple in oracle, there are basically two types of grants in eg: create

grant ( create ) , grant (create any)

grant CREATE TABLE to <user>

r which would allow the  user to create tables in his particular schema only.

If you wish that user should create table in any schema then you have to user

grant create any table to <user>

more about grants and roles can be found in oracle documentation.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm
0
 
LVL 2

Author Closing Comment

by:jhacharya
ID: 39960870
Yep works
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39962213
Even though you've already selected an answer I feel the need to make a point.

You should NEVER grant 'create any'.  The ANY privilege allows a user perform this granted task to, well, ANY schema.

This user will not be able to create tables in the SYS and SYSTEM schemas or any other.

You really shouldn't have general users creating objects in other schemas.  If you MUST do this, create a DDL trigger that will allow or disallow the correct statements.
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

Suggested Solutions

Title # Comments Views Activity
Array of Key/Value Pairs as input to Oracle function 10 53
sql query 9 39
Oracle Next Available Number 2 32
Repeat query 13 25
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 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

17 Experts available now in Live!

Get 1:1 Help Now