Re: Default Roles And A Caution in oracle docs

From: EdStevens <quetico_man_at_yahoo.com>
Date: 9 Aug 2005 09:22:17 -0700
Message-ID: <1123604537.885779.41840@g14g2000cwa.googlegroups.com>

nirav wrote:
> Thank you Mark, Yes but say in a case where you are not in a position
> to revoke the roles...the impact analysis of what will break etc will
> take long time , talks with developers etc etc and so you DO NOT have
> the option of revoking any role, then in that situation what is the way
> out?
>
> -is it modifing the parameter max_enabled_role in init.ora and
> restarting?
>
> with thanks
> N.

If you create the role under your DBA account, then you WILL be in a position to revoke the roll from yourself. You created it, you granted it to yourself (in the creation), you can revoke it from yourself. It's still there to be granted to whoever actually needs it.

system_at_LCOT> connect system_at_lcotdb
Connected.
system_at_LCOT> select granted_role from dba_role_privs where grantee='SYSTEM';

GRANTED_ROLE

DBA
AQ_ADMINISTRATOR_ROLE system_at_LCOT> create role eds_demo;

Role created.

system_at_LCOT> select granted_role from dba_role_privs where grantee='SYSTEM';

GRANTED_ROLE

DBA
EDS_DEMO
AQ_ADMINISTRATOR_ROLE system_at_LCOT> revoke eds_demo from system;

Revoke succeeded.

system_at_LCOT> select granted_role from dba_role_privs where grantee='SYSTEM';

GRANTED_ROLE

DBA
AQ_ADMINISTRATOR_ROLE system_at_LCOT> select role from dba_roles order by role;

ROLE

16 rows selected.

system_at_LCOT> drop role eds_demo;

Role dropped. Received on Tue Aug 09 2005 - 11:22:17 CDT