Link to home
Start Free TrialLog in
Avatar of PhonyName
PhonyName

asked on

How can I not grant DBA

I'm rather new to developing an application with Oracle.  

How can I set up an administrative user that can create other users and grant roles without giving that admin user the permission to grant DBA role?

It seems like you need the "Grant Any Role" permission in order to grant ANY role, but that permits granting DBA.

Hopefully I am just overlooking something.
Avatar of leolkk
leolkk

since DBA has a number of role. you should create a new role first. (e.g. user_admin) Then grant "part" of system privileges to new roles.

Since there are many system priviles in role "DBA". The following is I suggested ( I remove "grant any role" in the list"). Certainly, you are suggested to remove much "damage power" from the list.

create role user_admin;
grant ALTER ANY CLUSTER to user_admin;
grant ALTER ANY INDEX to user_admin;
grant ALTER ANY PROCEDURE to user_admin;
grant ALTER ANY ROLE to user_admin;
grant ALTER ANY SEQUENCE to user_admin;
grant ALTER ANY SNAPSHOT to user_admin;
grant ALTER ANY TABLE to user_admin;
grant ALTER ANY TRIGGER to user_admin;
grant ALTER DATABASE to user_admin;
grant ALTER PROFILE to user_admin;
grant ALTER RESOURCE COST to user_admin;
grant ALTER ROLLBACK SEGMENT to user_admin;
grant ALTER SESSION to user_admin;
grant ALTER SYSTEM to user_admin;
grant ALTER TABLESPACE to user_admin;
grant ALTER USER to user_admin;
grant ANALYZE ANY to user_admin;
grant AUDIT ANY to user_admin;
grant AUDIT SYSTEM to user_admin;
grant BACKUP ANY TABLE to user_admin;
grant BECOME USER to user_admin;
grant COMMENT ANY TABLE to user_admin;
grant CREATE ANY CLUSTER to user_admin;
grant CREATE ANY INDEX to user_admin;
grant CREATE ANY PROCEDURE to user_admin;
grant CREATE ANY SEQUENCE to user_admin;
grant CREATE ANY SNAPSHOT to user_admin;
grant CREATE ANY SYNONYM to user_admin;
grant CREATE ANY TABLE to user_admin;
grant CREATE ANY TRIGGER to user_admin;
grant CREATE ANY VIEW to user_admin;
grant CREATE CLUSTER to user_admin;
grant CREATE DATABASE LINK to user_admin;
grant CREATE PROCEDURE to user_admin;
grant CREATE PROFILE to user_admin;
grant CREATE PUBLIC DATABASE LINK to user_admin;
grant CREATE PUBLIC SYNONYM to user_admin;
grant CREATE ROLE to user_admin;
grant CREATE ROLLBACK SEGMENT to user_admin;
grant CREATE SEQUENCE to user_admin;
grant CREATE SESSION to user_admin;
grant CREATE SNAPSHOT to user_admin;
grant CREATE SYNONYM to user_admin;
grant CREATE TABLE to user_admin;
grant CREATE TABLESPACE to user_admin;
grant CREATE TRIGGER to user_admin;
grant CREATE USER to user_admin;
grant CREATE VIEW to user_admin;
grant DELETE ANY TABLE to user_admin;
grant DROP ANY CLUSTER to user_admin;
grant DROP ANY INDEX to user_admin;
grant DROP ANY PROCEDURE to user_admin;
grant DROP ANY ROLE to user_admin;
grant DROP ANY SEQUENCE to user_admin;
grant DROP ANY SNAPSHOT to user_admin;
grant DROP ANY SYNONYM to user_admin;
grant DROP ANY TABLE to user_admin;
grant DROP ANY TRIGGER to user_admin;
grant DROP ANY VIEW to user_admin;
grant DROP PROFILE to user_admin;
grant DROP PUBLIC DATABASE LINK to user_admin;
grant DROP PUBLIC SYNONYM to user_admin;
grant DROP ROLLBACK SEGMENT to user_admin;
grant DROP TABLESPACE to user_admin;
grant DROP USER to user_admin;
grant EXECUTE ANY PROCEDURE to user_admin;
grant FORCE ANY TRANSACTION to user_admin;
grant FORCE TRANSACTION to user_admin;
grant GRANT ANY PRIVILEGE to user_admin;
grant INSERT ANY TABLE to user_admin;
grant LOCK ANY TABLE to user_admin;
grant MANAGE TABLESPACE to user_admin;
grant RESTRICTED SESSION to user_admin;
grant SELECT ANY SEQUENCE to user_admin;
grant SELECT ANY TABLE to user_admin;
grant UPDATE ANY TABLE to user_admin;


Suppose there are user role "user_roleA". Then you should:
grant user_roleA to user_admin with admin option;


then user with role "user_admin" could grant role "user_roleA" to any users.
I hope my stupid (long statement) way could help you.
The ROLES suggested NOT granted to new DBA roles "user_admin" are:

ALTER TABLESPACE
DROP TABLESPACE
CREATE TABLESPACE
MANAGE TABLESPACE
DROP ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT
ALTER PROFILE
CREATE PROFILE
DROP PROFILE

ASKER CERTIFIED SOLUTION
Avatar of tvspriya
tvspriya

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PhonyName

ASKER

Thank you both for your assistance. The Admin Option was what I needed. If I grant a role with Admin Option to a user, that user can in turn grant it to another user and does not need the Grant Any Role privelege.

Thanks again...