Re: Creating a 'helpdesk' user

fornewsgroups_at_vikas.mailshell.com (Vikas Agnihotri) wrote in message news:<902027f8.0108151213.7b50b877_at_posting.google.com>...
> 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

DBA is *not* a system privilege, it is a *role* since 7.0

Please read your docs before you start shouting.

Regards,

Sybrand Bakker,

Senior Oracle DBA Received on Thu Aug 16 2001 - 03:51:04 CDT