Re: Creating a 'helpdesk' user

jeff_at_work.com (Jeff) wrote in message news:<9ljbik$o9t$1_at_cronkite.cc.uga.edu>...

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

Yes, thats an idea. Or I could set helpdek's default tablespace to a TEMPORARY tablespace so that they cant create any permanent objects there.

>
> 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.

Yes, I thought about this. I would need to grant helpdesk each and every application-specific role in my database WITH ADMIN OPTION so that helpdesk can then grant this to users it creates. I just thought there might be an easier option.

> 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.

Agree completely. But I read in the docs about the new init parameter o7_dictionary_accessibility which, when set to FALSE, will disallow access to the dictionary tables even when someone has the select/insert/delete ANY table privilege. So, I thought that similar to this, maybe the GRANT ANY ROLE really allows you to grant only user-defined roles, not Oracle-predefined roles like DBA, etc. Guess I was wrong.

> 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.

Yes, this is possible. A lot of work, but possible.

Thanks Received on Fri Aug 17 2001 - 14:07:49 CDT