oracle user and privilege backup before refresh.

While you  refresh dev database from prod the existing schema user password and privileges will be over write with prod data.So you will lose existing user password and privileges. In this post i will show how to take backup of those user password and privileges.

Before doing refresh run the below commands.it will give the ddl statements of user password and their privileges.

1.Take the user password ddl commands using below command.

select 'alter user'||' '|| name ||' '|| 'identified by values' ||' '|| ''''|| password ||''''|| ';' from user$
where name in ('SAMPLE','TEST'); 

2.Take the privileges ddl commands using below commands.


set pages 999;
select 'grant'||' '||PRIVILEGE||' to ' ||grantee||';' from dba_sys_privs where grantee in ('SAMPLE','TEST'); 

select 'grant'||' '||PRIVILEGE||' on ' ||owner||'.'||TABLE_NAME||' to '||grantee||';' from dba_tab_privs where grantee in ('SAMPLE','TEST');

select 'grant'||' '||GRANTED_ROLE||' to ' ||grantee||';' from dba_role_privs where grantee in ('SAMPLE','TEST');

3.Post refresh run those DDL statements to retrieve the old user passwords and privileges.