Re: Recreating roles with passwords

On 8/15/07, Don Seiler <don_at_seiler.us> wrote:
>
> I'm writing a little perl script to migrate users and roles to another
> server. Some of the roles have passwords, but I can't find the
> passwords. The dba_users view, for example, gives me the encrypted
> value of the user's password so that I can build my CREATE USER sql
> statement with IDENTIFIED BY VALUES. Does anyone know where, if
> anywhere, I can find the password for a role so that I can recreate it?
>

Roles are users.

 SQL> create role testrole identified by "testrole"; Role created.

 SQL> create role testrole2 identified by values 'testrole2'; Role created.

SQL> l
  1 select u.name , u.password
  2 from sys.user$ u, dba_roles r
  3 where r.role = u.name
  4* and r.role like 'TEST%'

SQL> /

NAME                           PASSWORD
------------------------------ ------------------------------
TESTROLE                       DE7ECF87248747A5
TESTROLE2                      testrole2

2 rows selected.

So, the answer is no, you cannot get the password from the database.

But, you can use the encrypted password, and generate the code using the VALUES statement:

To recreate the TESTROLE role:

create role testrole identified by values 'DE7ECF87248747A5';

This is how exp/imp does it.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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