• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 745
  • Last Modified:

setup oracle db user like sudo in linux


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
mw-hosting
Asked:
mw-hosting
1 Solution
 
AkenathonCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now