Re: Recreating roles with passwords

On 8/15/07, Don Seiler <don@xxxxxxxxx> 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

Other related posts: