Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle 10g privileges

Posted on 2014-03-27
3
Medium Priority
?
522 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 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 78

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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

963 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