Solved

oracle 10g privileges

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

624 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