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

x
?
Solved

setup oracle db user like sudo in linux

Posted on 2011-03-10
3
Medium Priority
?
741 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 2000 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 78

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

879 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