Re: Sql to create 'ALTER USER...'

Try this. Be sure to turn on serveroutput.

Steve
Unemployeed DBA

declare

role_var varchar2(1000) := null;
grantee_var sys.dba_role_privs.grantee%TYPE; comma_var varchar2(1) := null;

Cursor c1 is
  select distinct grantee
    from dba_role_privs;

Cursor c2 is
  select granted_role
    from dba_role_privs
   where default_role = 'YES'
     and grantee = grantee_var;

begin

For c1_rec in c1 loop

  grantee_var := c1_rec.grantee;

  For c2_rec in c2 loop

    role_var := role_var||comma_var||c2_rec.granted_role;     comma_var := ',';

  End Loop;

  dbms_output.put_line ('alter user '||c1_rec.grantee||' default role '||role_var||';');

  role_var := null;
  comma_var := null;

End Loop;

End;
/

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, September 20, 2001 3:15 PM

Does anybody have a query that will generate the SQL for setting default roles for users? I am trying to set the default roles for users in a test database to match those that are in a production database. The problem is that you must denormalize the data from DBA_ROLE_PRIVS into one ALTER USER command. The type of output I am looking for is:

alter user him default role role1,role4,role7; alter user her default role role4,role5,role7; etc...

Maybe this is easy and I am just having trouble concentrating this afternoon...

Thank you,

Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA USA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
  INET: jhostetter_at_decommunications.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Smith
  INET: ssmith_at_ai.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).