I have a reporting database which is refreshed every day from Production. Users like to keep their existing passwords and roles and privileges assigned to them. How this task can be achieved in Oracle 11g rel 2.
Elaborate: Production database :- CRDPRD
Reporting database:- CRDRPT
Reporting database is refreshed every night from Production using RMAN backup and we have a cron script (shell script runs on the database) which runs everyday on the reporting database (CRDRPT) after the refresh. The password and usernames are hard-coded with username and a common password to every users. All users have same role (read only) except a few which has update privileges also. Most users are created on the reporting database (from the same shell script) as only some users are present in the Production environment.
The issue is if some users change their passwords then the next day they will again be overwritten by the script with the default passwords. They want their passwords be retained unless they change it.
Please let me know the best way to achieve this task?