Oracle 12c is an enterprise-class relational database management system that provides high security and better performance. When it comes to protecting sensitive data, encryption is a primary measure. An encrypted password is a more secure way to protect logins and other sensitive information stored in Oracle databases. In this blog post, we will take a closer look at how to get encrypted passwords in Oracle 12c.
Video Tutorial:
What’s Needed
Before we start getting encrypted passwords in Oracle 12c, we need to ensure we have the following:
1. Access to an Oracle 12c database
2. A user login and password
3. Appropriate privileges to execute SQL scripts and views
What Requires Your Focus?
When getting encrypted passwords in Oracle 12c, it’s critical to focus on the following aspects:
1. Security: We must ensure that the login passwords are encrypted for maximum security, as passwords are one of the most exploited security vulnerabilities.
2. User roles: We must verify that the user has the required privileges to execute the SQL scripts and views to obtain the encrypted password
3. Encryption algorithm: We must select an appropriate encryption algorithm to ensure maximum security when getting encrypted passwords.
Different Methods to Get Encrypted Passwords in Oracle 12c
There are several methods to get encrypted passwords in Oracle 12c. Let us discuss some of them:
Method 1: Using the DBMS\_UTILITY package
The DBMS\_UTILITY package is used to execute the GET\_DDL function, which is used to retrieve DDL (Data definition language) for various database objects. Here’s how to get an encrypted password using the DBMS\_UTILITY package:
1. Connect to the Oracle 12c database using SQL*Plus or SQL developer.
2. Execute the following SQL script to obtain the created database link.
"`
SELECT db_name, db_link, username
FROM dba_db_links ;
"`
3. Execute the following SQL script to get encrypted password for a specific user:
"`
SELECT DBMS\_UTILITY.get\_hash\_value(‘password’, 101, user)
FROM dual;
"`
Pros:
1. Easy to implement
2. No need for additional setup or installation
Cons:
1. Not suitable for bulk password retrieval
2. Relatively slower compared to other methods
Method 2: Using the DBA\_USERS view
The DBA\_USERS view is a system view that contains information about all users of a particular database. Here’s how to use the DBA\_USERS view to get an encrypted password:
1. Connect to the Oracle 12c database as a sysdba account using SQL*Plus or SQL developer
2. Execute the following SQL script to get an encrypted password for a specific user:
"`
SELECT password
FROM dba_users
WHERE username = ‘username’;
"`
Pros:
1. Easy to implement
2. Suitable for individual password retrievals
Cons:
1. Not suitable for bulk password retrievals
2. Relatively slower compared to other methods
Method 3: Using the DBMS\_OBIEE\_DEFAULT package
The DBMS\_OBIEE\_DEFAULT package is used to generate URL links for Oracle Business Intelligence Enterprise Edition. However, it can also be used to get encrypted passwords in Oracle 12c. Here’s how to use the DBMS\_OBIEE\_DEFAULT package:
1. Connect to the Oracle 12c database using SQL*Plus or SQL developer.
2. Execute the following SQL script to get an encrypted password for a user:
"`
SELECT DBMS\_OBIEE\_DEFAULT.GETOBIEEPWD(‘username’, ‘password’)
FROM dual;
"`
Pros:
1. Faster compared to the other methods
2. Suitable for bulk password retrievals
Cons:
1. Requires additional setup and installation
2. May not be applicable in all cases
Why Can’t I Get Encrypted Passwords?
Some of the reasons why you might encounter issues getting encrypted passwords in Oracle 12c include:
1. Lack of privileges: Ensure that the user you are using to connect to the database has the correct roles to execute SQL scripts and views.
2. Invalid input: Ensure that you are specifying valid input parameters such as the correct username and password.
3. Method limitations: Each method has its limitations, and some may not be applicable in your use case.
Implications and Recommendations
Getting encrypted passwords in Oracle 12c involves sensitive information, and it’s critical to observe the following implications and recommendations:
1. Maintain security: Passwords are sensitive, and it’s essential to ensure that the retrieval process is secure.
2. Regularly change passwords: To ensure maximum security, it’s vital to change passwords regularly.
3. Review privileges: Ensure that the user has the correct roles and privileges to execute SQL scripts and views to retrieve passwords.
5 FAQs about Getting Encrypted Passwords in Oracle 12c
Q: Can I retrieve encrypted passwords for all users at once?
A: Yes, it’s possible using some of the methods such as using the DBMS\_UTILITY package or the DBMS\_OBIEE\_DEFAULT package.
Q: What’s the ideal encryption algorithm for retrieving passwords?
A: The ideal encryption algorithm should provide maximum security and can be decrypted when required.
Q: What’s the recommended frequency for changing Oracle database passwords?
A: It’s recommended to change database passwords every ninety days or when an administrator leaves or changes roles.
Q: Can I use the DBA\_USERS method to retrieve encrypted passwords for a user in another schema?
A: No, the DBA\_USERS method is only applicable for retrieving encrypted passwords for users in the current schema.
Q: What should I do if I don’t have the necessary privileges to retrieve encrypted passwords?
A: You should contact the database administrator to obtain the required privileges.
In Conclusion
Getting encrypted passwords in Oracle 12c is critical to ensuring maximum security for logins and other sensitive information. It’s essential to use an appropriate method to retrieve passwords and observe the necessary implications and recommendations. The different methods discussed in this blog post are suitable for various use cases, and you can choose the one that suits your needs.