Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle 10g privileges

Posted on 2014-03-27
3
Medium Priority
?
515 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
[X]
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
3 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 2000 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

Industry Leaders: 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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

721 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