grant "almost" dba privies on specific schema

[Ratzie]

I do not want to grant DBA to a specific user so he can do what ever he wants within his schema.
I know... the question will be, well what do you want to grant him.
I need to make this easy, I thought of doing this in OEM, but click-i-tis started happening.

I do not want to grant CREATE ANY, etc.
the resource role looked good, but is does not allow to create views, drop views, create anything from within his schema, triggers, sequences, procedures, as well as DROP and ALTER etc...

[AINMS]

hi
you can grant these roles if you don't need it to create any
SQL> GRANT connect TO user;
SQL> GRANT dba TO user;
SQL> GRANT exp_full_database TO user;
SQL> GRANT imp_full_database TO user;
SQL> GRANT unlimited tablespace TO user;
SQL> GRANT select any table TO user;
SQL> GRANT select any dictionary TO user;
SQL> GRANT execute ON sys.dbms_lock TO user;
SQL> COMMIT;
SQL> CONNECT user/password;

HTH
AINMS

[Eric Antunes]

Hi Ratzie,

CONNECT role is a good start. See the system privileges of roles in dba_sys_privs:

select *
from ROLE_SYS_PRIVS
where role = 'CONNECT'

Regardless of roles, the user will be able to drop and alter all objects in HIS schema.

Best Regards,

Eric Antunes

[Vadim Loginov · Accepted solution]

Hi Ratzie,
I would recommend you to create a new role - ROLE_XXX.
Then you can either grant role CONNECT (if you use Oracle 9i - http://my.opera.com/onyxluo/blog/change-of-connect-role-in-oracle-10g) to ROLE_XXX or grant all appropriate system privileges to ROLE_XXX.

Grant CREATE SESSION to ROLE_XXX
Grant CREATE VIEW to ROLE_XXX
Grant CREATE TABLE to ROLE_XXX
Grant CREATE SYNONYM to ROLE_XXX
Grant CREATE SEQUENCE to ROLE_XXX
⠦⠦.
The list of all (Oracle) system privileges is here: http://www.adp-gmbh.ch/ora/admin/system_privileges.html

Finally, grant ROLE_XXX to your user.

Regards,
Vadim

[Ben Dehner]

Vadim had a good suggestion. Create a role with the various 'create ' privileges, and grant that role to the users. This is what I do, and I typically grant the following privs:

create session
alter session
create table
create view
create trigger
create procedure
create sequence
create type
create synonym
create cluster
create materialized view
create materialized view log

Two things to note:
First, if you own an object, you automagically have privileges to alter, drop, whatever on that object. However, "create session" and "alter session" are different, because these are seperate system privleges, not object privleges.

Second, any object that takes up space (tables, indexes, anything that has a "tablespace" clause ...) also seperately requires a quota on a tablespace somewhere. You still have to do this manually, can't grant a quote to a role.

[Ratzie]

Thanks for imput