Solved

How can I not grant DBA

Posted on 2001-07-25
4
2,293 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:PhonyName
  • 2
4 Comments
 
LVL 1

Expert Comment

by:leolkk
ID: 6320524
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.
0
 
LVL 1

Expert Comment

by:leolkk
ID: 6320533
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

0
 
LVL 1

Accepted Solution

by:
tvspriya earned 100 total points
ID: 6320548
As a SYS/SYSTEM user give only the ncessary privileges to the Admin user to run your application plus 'CREATE USER' privilege and Grant CONNECT & RESOURCE with ADMIN option to this APPADMIN user.

I have not tried it out myself, you can give a try.

Hope this helps.

SP
0
 

Author Comment

by:PhonyName
ID: 6320883
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...
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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

808 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