The SYS, SYSTEM, SYSMAN, and DBSNMP database accounts are included in all databases established by the Database Configuration Assistant (DBCA). Oracle also supports a number of other administration credentials. You must unlock and change the credentials for all these other profiles before you may use them.
After installation, all Oracle system administration users except SYS, SYSTEM, SYSMAN, and DBSMP have their passwords removed. You should unlock and reset a locked user’s password before you may use it.
I know one way to access a regular user account.
A current password can be retrieved as an encoded value, and encoded passwords can be copied across databases.
The DBMS metadata process allows users to copy user accounts with almost the same password, permissions, roles, and so on from one instance to the other without utilizing the exp/imp utilities.
You will not be able to view the password (sorry, hackers), but you will be able to copy it in its encoded form. This is incredibly beneficial for DBAs who are transferring a database from operational to testing or migrating a database from one instance to some other.
The DBMS METADATA function is used to retrieve the user credentials in this case. Once the encrypted password has been recorded, it may be re-entered into the user ID when testing is complete:
How to Save an Oracle user password?
STEP 1: We begin by extracting the encrypted password
A wise DBA never forgets that their clients are the end-users, and it is an Oracle best practice to be as discreet as possible.
As an Oracle DBA, you may need to login in as a specific user in order to grasp the nature of their problem. While it is simple to change the user ID to create a new password, the end-user is inconvenienced since they must re-set a new password.
Nevertheless, as a DBA, you may extract the encrypted password from the dba user view, store it, and then re-set the password once your testing has been completed.
Suppose you need to sign in as ORACLE in order to test your Oracle privileges:
STEP 2: Modify ORACLE’s password and sign in for testing
STEP 3: Once you’ve finished testing, you may restore the original encrypted password using the query results from step 1
How to Create Oracle Database User?
Use the CREATE USER statement to create and configure a database user, which is an account that allows you to log in to the database, as well as to specify how Oracle Database grants the user access.
A proxy application or application server can be used to link a user to a database. Refer to ALTER USER for syntax and explanation.
You must have the system privilege CREATE USER. When you use the CREATE USER statement to create a user, the user’s privilege domain is empty. A user must have the CREATE SESSION system access to connect to Oracle Database. As a result, after establishing a user, you should provide the user at least the system privilege CREATE SESSION.
For additional information, please see GRANT.
Bonus Information
- How to Install Oracle Client 19c on Windows 10? – Read here
- Oracle Data Guard Multiple Standby Databases 12c – Read here
- How to Check Database Response Time in Oracle 11g? – Read here
- How to Check AWR Retention Period in Oracle Database – Read here
