Solved

oracle 10g privileges

Posted on 2014-03-27
3
497 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 77

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

Independent Software Vendors: 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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

685 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