Re: Creating a 'helpdesk' user

In article <902027f8.0108170602.477f40e4_at_posting.google.com>, fornewsgroups_at_vikas.mailshell.com (Vikas Agnihotri) wrote:
>postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message
> news:<a20d28ee.0108160051.7f87a05_at_posting.google.com>...
>> fornewsgroups_at_vikas.mailshell.com (Vikas Agnihotri) wrote in message
> news:<902027f8.0108151213.7b50b877_at_posting.google.com>...
>> > 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.

I'm pretty much a newbie at this sort of stuff, so I'll just throw out a couple of ideas that the more experienced can chuckle over and say 'what a newbie!' :-)

1. How about granting the CREATE TABLE WITH ADMIN OPTION, but restrict hepdesk's user quotas to 0, disallowing them the space to actually create objects in a tablespace? (more below)

>> > 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)?

2. I'd think you'd just need to grant the roles to helpdesk that it needs to grant to other users instead of GRANT ANY ROLE.

The reason GRANT ANY ROLE grants DBA is because DBA is just another role to Oracle. DBA is only special in your mind because it's already been granted all the system privileges to do anything in the database... but I wouldn't be surprised if you could drop the role entirely without Oracle complaining.

One other possibility might be to create a stored procedure that (using your rights and the DBMS_SQL package) grants the rights and roles you need. If that would work, you could simply grant execute rights on the one procedure to helpdesk without granting them any additional rights. If this is possible, please post back because I'd like to know too. Received on Fri Aug 17 2001 - 10:00:35 CDT