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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
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;
For more details please find the link below:
http://www.databasedesign-resource.com/users-in-oracle.html
http://www.adp-gmbh.ch/ora/admin/system_privileges.html
http://www.oracle-base.com/articles/misc/BasicSecurityMeasuresForOracle.php
http://download.oracle.com/docs/cd/E19253-01/820-3508/txconf-14/index.html
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6011.htm