Solved

How can I not grant DBA

Posted on 2001-07-25
4
2,285 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now