Creating a 'helpdesk' user

I want to create a 'helpdesk' user to allow our App HelpDesk group to handle routine DBA activities like creating a user, altering user, assigning roles depending on the application, etc.

So, I did the following:

Create User HELPDESK Identified By password
/

GRANT ALTER SESSION TO HELPDESK
/

GRANT ALTER USER TO HELPDESK
/

GRANT CREATE SESSION TO HELPDESK
/

GRANT CREATE USER TO HELPDESK
/

GRANT GRANT ANY ROLE TO HELPDESK
/

GRANT SELECT_CATALOG_ROLE TO HELPDESK
/

Alter user HELPDESK default role SELECT_CATALOG_ROLE
/

This is working fine. But few problems:

1. They cannot grant 'create table' to a new user they create. To do this, I would need to 'grant create table to helpdesk WITH ADMIN OPTION'. I dont want to do this because I dont want HELPDESK to create tables.

Is there a way to grant just the admin option part i.e. allow them to grant CREATE TABLE to others but not be able to create tables themselves.

2. The main and (surprising) problem with the above is this:

Even though HELPDESK is a non-DBA user created expressly for user creation, HELPDESK can create a new user and grant DBA to the user and thus have a DBA access to the database!

This seems silly. What am I missing? How can it be so easy to subvert Oracle's security? Oracle's GRANT ANY ROLE system privilege should have the intelligence to not grant DBA. Otherwise, whats the point of this system privilege? Granting it to anyone is akin to giving them DBA access.

Anyway, lets take it one step back. Maybe I need to re-think my whole appproach.

Does anyone have any ideas on how to accomplish what I want? i.e. create a helpdesk user to create new users, modify them, grant *application* roles to them (defined by us)?

Thanks Received on Wed Aug 15 2001 - 15:13:13 CDT