RE: lost default role for all users

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Tue, 9 Jul 2013 19:39:37 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1186E9E9C_at_G6W2491.americas.hpqcorp.net>

I think this code has a potential problem. A user may have multiple default roles. If I am not mistaken this code will result in only the one specified role showing up after it runs. This code is great for a quick fix if no selected user has more than one default role, but if you keep it around I think the code needs commenting.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Monday, July 08, 2013 9:51 PM
To: oracle-l_at_freelists.org
Subject: Re: lost default role for all users

Denise,
SQL*Plus and the generic technique of "SQL-generating-SQL" makes everything easy...

    set echo off feedback off timing off pagesize 0 lineisze 130 trimout     on trimspool on
    spool run_set_/{role-name}/.sql/
    /prompt set echo on feedback on timing on     prompt spool run_set_/{role-name}
    /prompt
    select 'grant /{role-name}/to '||username||';'||chr(10)||'alter user     '||username||' default role /{role-name}/;' cmd     from dba_users order by 1;
    prompt
    prompt spool off
    prompt set echo off feedback 6 timing off     spool off
    _at_run_set_/{role-name}/

Hope this helps...

-Tim

On 7/8/2013 6:24 PM, Denise Gwinn wrote:
> One of my sites uses a default role for their baseline users. The
> third party software we use dropped and recreated that role. Now none
> of my users have a default role. I've looked and can't find a way to
> alter my users to set a default role except by one user at a time. Any ideas?
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 09 2013 - 21:39:37 CEST