Solved

setup oracle db user like sudo in linux

Posted on 2011-03-10
3
733 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
[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 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 77

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

710 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