This module is about password files.
Password files provide a mechanism for authenticating database administrators who connect remotely over a network. When you have completed this module, you should be able to do the following:
- Create a new password file
- Connect to a database as
SYSDBA or SYSOPER
- Grant
SYSDBA and SYSOPER privileges to DBAs
- Configure a database to use a password file
- Rebuild a password file
- Select from v$pwfile_users to see who has
SYSDBA or SYSOPER privileges
Password files play an important role in database security, especially when you are using tools such as Oracle Enterprise Manager to remotely manage a database over a network.
The Oracle orapwd command line utility assists the DBA with granting
- SYSDBA and
- SYSOPER
privileges to other users. By default, the user SYS is the only user that has these privileges that are required to use orapwd.
Creating a password file by means of orapwd enables
remote users to connect with administrative privileges through SQL*Net.
Warning: Using orapwd gives other users the Oracle super user privileges.
The
SYSOPER privilege allows instance startup, shutdown, mount, and dismount. It allows the DBA to perform general database maintenance without viewing user data. The SYSDBA privilege is the same as connect internal was in prior versions. It provides the ability to do everything, unrestricted. If orapwd has not yet been executed, attempting to grant SYSDBA or SYSOPER privileges will result in the following error:
SQL> grant sysdba to scott;
ORA-01994: GRANT failed: cannot add users to public password file
Question: What does the Oracle Error ORA-01994: GRANT failed communicate to the user?
The Oracle error message `ORA-01994: GRANT failed` is generated when an attempt to grant a privilege or role to a user fails. The error implies that the operation could not be completed successfully. Common reasons for encountering this error include:
- Insufficient Privileges: The user attempting to grant the privilege may not have the necessary rights to perform the operation.
- Syntax Errors: The SQL statement used to grant the privilege may be incorrect or incomplete.
- Object Does Not Exist: The privilege, role, or object that you're trying to grant access to may not exist in the database.
- Target User Does Not Exist: The user or role you are trying to grant the privilege to may not exist.
- Resource Limitations: Sometimes, system or resource limitations can prevent the successful execution of the grant statement.
- Invalid Option Combinations: Certain privileges or roles may have constraints or conditions that are incompatible with other options in the GRANT statement.
- Role Hierarchy Issues: When working with roles, it's possible to run into issues if the role being granted depends on other roles that the grantee does not have.
Here's what you can do to troubleshoot:
- Check Privileges: Make sure that the user attempting the GRANT operation has the necessary administrative privileges.
- Syntax Check: Review the SQL statement for any syntactic mistakes.
- Object and User Verification: Make sure that all referenced roles, privileges, and users exist.
- Check Constraints: Ensure that you are not violating any constraints or conditions that apply to the privilege or role being granted.
- Review Database Logs: Oracle logs can provide more detailed information on why the operation failed.
- Consult Documentation: The Oracle documentation can provide specific guidelines on granting privileges and roles, including the required syntax and any constraints that apply.
- Resource Checks: Make sure the system has sufficient resources to complete the operation, especially in terms of memory and disk space.
- Contact DBA: If you are still encountering issues, it may be useful to consult with a Database Administrator (DBA) who has the required experience and privileges to diagnose and resolve the issue.
By diagnosing the cause of the error and taking appropriate corrective action, you can resolve the `ORA-01994: GRANT failed` issue.