Permission management for Oracle Super Users

Oracle Super Users have the highest permissions for Oracle databases. The following describes how to manage the permissions of Oracle Super Users. If you are interested, take a look.

Oracle users are divided into system and object permissions based on the granted permissions. The highest permission is sysdba. Sysdba has the privilege to control all behaviors of Oracle, such as creating, starting, closing, and restoring databases, so that databases can be archived or not archived, backup tablespace and other key actions can only be performed by users with sysdba permissions. These tasks do not work even for common DBA roles. Sysoper is a permission similar to sysdba, except that SYSOPER privileges with admin option, create database, and recover database until are less than sysdba. The two authentication methods are the same, so the following describes only the authentication management of sysdba.

Generally, there are two methods to manage sysdba: operating system authentication and password file authentication. The specific authentication method depends on whether you want to maintain the database on the machine running Oracle or manage all the Oracle databases distributed on different machines on one machine. If you choose to maintain the database on the local machine, selecting the operating system authentication may be a simple and easy way. If you have many databases and want to perform centralized management, you can select the password file authentication method.

Operating system authentication configuration process:
1. Create a valid account in the operating system.
Specifically, on NT, first create a local user group named ORA _ & lt; SID> _ DBA, where SID is the SID of the database instance, or create an ORA_DBA group, this group does not apply to any separate Oracle instance. In this way, when an NT has several Oracle instances, you do not need to manage them separately. Create a user on NT and add it to the group. However, these two steps have been completed automatically during the Oracle8I installation process. Generally, you do not need to perform them manually.
Step 3: In sqlnet. ora is located in the $ ORACLE_HOME/NETWORK/ADMIN directory. AUTHENTICATION _ SERVICES is set to SQLNET. AUTHENTICATION_SERVICES = (ETS), which means to use the NT authentication method.
Step 4: In INIT & lt; SID>. ORA, set REMOTE_LOGIN_PASSWORD to NONE, meaning password authentication is not required.
After completing the preceding steps, you can directly connect internal (CONNECT/as sysdba) in SQL * Plus and SERVER MANAGER to log on to Oracle AS an Oracle Super User after logging on to NT, perform operations that can only be performed by Oracle Super Users.
In Unix, the situation is somewhat different. After all, this is a completely different operating system.
First, create a DBA group before installing Oracle. This step is not required. Otherwise, Oracle cannot be installed. Generally, a user named Oracle is created and added to the DBA group.
Step 2: Set REMOTE_LOGIN_PASSWORD to NONE. After Oracle8.1, this parameter is EXCLUSIVE by default. Remember to change it.
Step 3: log on to Unix with the user name, run SQL * Plus or SERVER MANAGER, and enter the following command: CONNECT INTERNAL (CONNECT/AS SYSDBA) to log on to Oracle.

To use the password file for authentication, follow these steps:
Oracle provides the orapwd utility to create a password file. The specific steps for using orapwd to establish this authentication method are as follows:
1. Use the Orapwd utility to create a PASSWORD file. Syntax:
Orapwd file = file name password = internal user password entried = entries.
Explanation:
The file name must contain the complete full path name. If not specified, Oracle places it under $ ORACLE_HOME/dbsUnix by default) or $ ORACLE_HOME/DATABASENT.
The user password is the user's internal password. Of course, you can also add other Oracle Super Users to it later.
Entries indicates the maximum number of Oracle superusers allowed. This is optional. The first two must be specified. It is usually set to be larger than the actual needs, so as not to be insufficient.

2. Set REMOTE_LOGIN_PASSWORD in INIT & lt; SID>. ORA to EXCLUSIVE or SHARED. Use EXCLUSIVE to use this password file only for the current INSTANCE. In addition, other users are allowed to log on to the system as sysdba. If SHARED is selected, it indicates that more than one instance uses this password file, with a strong constraint: sysdba permissions can only be granted to sys and internal. In fact, internal is not a real user, but sys is an alias for sysdba logon .)
Remember to set sqlnet. AUTHENTICATION _ SERVICES to NONE in the SQLNET. ora file. It is usually set by default in Unix. In NT, if you select a typical installation, OS authentication is used, and password file authentication is used for custom installation. During installation, an INTERNAL password is prompted. In this way, you do not need to manually create a password file or set an INTERNAL password.

3. Run the following command in SQL * Plus or SERVER MANAGER to log on to the system: CONNECT INTERNAL/password.

Note:
1. When Oracle8.1.6 is installed in WIN2000 to create a database, a credential retrieval failure error often occurs. This is because Oracle cannot apply OS authentication results. You can modify sqlnet. AUTHENTICATION _ SERVICES in SQLNET. ora to NONE. In this case, Oracle uses Password File authentication.
2. Since Oracle has several pre-built users, it is best to change the passwords of these users immediately after the installation is complete. The default passwords are internal/oracle, sys/change_on_install, and system/manager.

3. When selecting the password file authentication method, you can add another super user to the system. For example, use the following statement to add SCOTT to a Super User: (executed by a person with sysdba permissions)
SQL> GRANT SYSDBA TO SCOTT; then the SCOTT user has the sysdba permission. Note: At this time, SCOTT users can log on as scott and SYS. When SCOTT does not enter as sysdba during logon, SCOTT is logged on AS a common user. When the as sysdba is entered during logon, the user that SCOTT logs on to is actually sys. This can be observed from:

4. the username with sysdba permission in the current system can be queried from the data dictionary view v $ pwfile_user:
SELECT * from v $ PWFILE_USERS; as shown in.
5. the maximum number of users with sysdba permissions in the system is determined by the ENTRIES parameter when the password file is created. To create more users with sysdba permissions, You need to delete the original password file and recreate one. In this case, you need to close the database, delete the password file, re-create a new password file, and enter a large enough number in entries. Start Oracle again. At this time, all original North authorized Super Users no longer exist, and need to be re-authorized. Therefore, before re-creating the password file, you must first query the view, write down the user name, and then re-authorize after creating the password file.
6. How to Deal with the password forgotten by Internal:

There are two methods:
1. alter user sys identified by new password; // This also changes the Internal password, which is passed in Oracle8I
2. Create a new password file and specify a new password.

Provides you with an in-depth understanding of Oracle system services.

How to change the password of an Oracle user

Implementation of oracle multi-column subquery

Oracle multi-Table query instance

ORACLE Database Encoding