Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

setup oracle db user like sudo in linux

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

721 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