Solved

setup oracle db user like sudo in linux

Posted on 2011-03-10
3
726 Views
Last Modified: 2013-11-11

Is it possible to have a user in the db that can access another users schema, with the ability to recreate, create, or compile any table/procedure/package.

We want 3 users (user1, user2, user3) to be able to access schema master_schema and have the same rights as user master on master_schema without giving them CREATE ANY privilege or create their own schemas.
0
Comment
Question by:mw-hosting
3 Comments
 
LVL 11

Accepted Solution

by:
Akenathon earned 500 total points
ID: 35100363
Oracle is not Linux. The security model is very different. You either give privileges on single objects, or system privileges on ANY object in the DB. If you don't want to use the ANY family, your only choice is to create a trigger ON DDL that grants whatever object rights you want to your three users.

Another option (which is NOT meant to emulate a sudo but to allow end-to-end user identification across all tiers) is to use proxy users. Google the syntax for alter user <username> grant connect through <another_username>.
0
 
LVL 4

Expert Comment

by:pinkuray
ID: 35103896
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35108774
>>setup a role on DB as per your requirement and then create 3 users

What role do you propose what will let a user drop and create objects in one schema and and not in their own or others?

>>without giving them CREATE ANY privilege or create their own schemas

You might think about creating a wrapper procedure in the MASTER schema that will perform the DDL.  Just grant create session and execute on that procedure.

The caveat here is you will need to explicitly grant the necessary privs to the MASTER schema.  This is because procedures run differently than when you are connected to the user.

In the following example you need to explicitly run:
grant create table to scott;

or it will not run.
conn scott/tiger

create or replace procedure exec_My_ddl(inDDL in varchar2)
is
begin
	execute immediate inDDL;
end;
/

show errors

-- show it doesn't exist now
desc from_fred;

drop user fred cascade;
create user fred identified by flintstone;
grant execute on exec_my_ddl to fred;
grant create session to fred;

conn fred/flintstone

-- this will fail
create table tab1(col1 char(1));

exec scott.exec_my_ddl('create table from_fred(col1 char(1))');

conn scott/tiger

-- show it doesn't exist now
desc from_fred;

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
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 shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 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

16 Experts available now in Live!

Get 1:1 Help Now