Management of superuser privileges in Oracle

Oracle users are divided into system permissions and object privileges based on the permissions granted. The highest permission is SYSDBA. SYSDBA has privileges that control all behaviors of Oracle, such as creating, start, shutdown, recovering databases, making database archives / non-archives, backup tablespaces, etc. can only be performed by users with SYSDBA privileges. These tasks don't work even if it is a normal DBA role. Sysoper is a similar permissions similar to sysdba, just less sysoper privileges with admin option, Create Database, Recover Database Until. The authentication method of these is the same approach, so only the following is only introduced by SYSDBA certification management.

There are two ways to manage SYSDBA: *** make system authentication and password file authentication. The specific choice of authentication depends on: Do you want to maintain a database on the machine running in Oracle or manage all Oracle databases distributed on different machines on a machine. If you choose to maintain the database in this machine, select *** to make system authentication may be a simple and easy way; if there is a lot of databases, you can choose the Password file authentication method.

The following picture is more intuitive description of this selection weighing process:

Use the *** configuration process for system authentication mode:

1. Establish a legal account in the *** system.

Specifically, on the NT, a local user group is first established, named ORA_ _DBA, where SID is the SID of the database instance, or established an ORA_DBA group, which does not correspond to any individual Oracle Example. This is not used by several Oracle instances on one NT. Then establish a user on the NT and set it into the group. But in fact these two steps have been automatically completed during the Oracle8i installation, generally do not manually do.

Step 3: In SQLNET.ORA (in the $ ORACLE_HOME / NETWORK / Admin Directory), set SQLNET.AUTHENTICATION_SERVICES = (NTS), which means using NT authentication.

In the fourth step, in Init . OA, set the remote_login_password to none, which means that it is not necessary to use Password authentication.

After completing the above steps, you can log in to Oracle as a super user in the SQL * Plus and Server Manager after logging in to NT, and performs some only super users to do. *** Work.

In UNIX, the situation is somewhat different. After all, this is two completely different *** as a system.

First, before installing Oracle, build a DBA group, this step is not to say, otherwise it is not installed in Oracle. A user named Oracle is generally established and adds it to the DBA group.

Step 2, set remote_login_password to none. After Oracle8.1, this parameter defaults to Exclusive. Be sure to remember.

In the third step, log in to UNIX with this username, run SQL * Plus or Server Manager, enter the following command: Connect INTERNAL (Connect / as Sysdba) to log in to Oracle.

Specific steps from PASSWORD file authentication:

ORACLE provides an ORAPWD utility to create a Password file, using ORAPWD to establish the specific steps of the authentication method as follows: 1. Use an ORAPWD utility to create a password file. grammar:

ORAPWD File = File Name Password = Internal User Password ENTRIED = Entries.

Detailed explanation:

The file name wants to include a complete full path name. If not specified, Oracle places it by default $ ORACLE_HOME / DBS (UNIX) or $ Oracle_Home / Database (NT).

The user password is the password of the user internal. Of course, you can also join other superusers in the inside.

Entries indicates the maximum number of superusers that are allowed. This is an optional. The first two must be specified. Generally, it will be set to be larger than actual, so as not enough.

2. Set the Remote_login_password in INIT . Otte_login_password to Exclusive or Shared. Use Exclusive to indicate that only current instance uses this Password file. And other users are allowed to log in into the system as the SYSDBA login system, and if you have selected Shared, it indicates that more than one instance uses this password file, accompanied by a strong constraint: SYSDBA permission can only grant two usernames and INTERNAL. . (In fact, INTERNAL is not an actual user, but only SYS as an alias when Sysdba login.)

Also remember to set SQLNET.AUThentication_services in the SQLNET.ORA file to None. Generally, it is default settings under UNIX. In NT, if you select a typical installation, an OS authentication is used, and the password file authentication method is used when customizes. Enter the Internal Password during the installation process. In this case, you don't have to create a password file and set the password of the Internal.

3. Run the following command to log in to the system with SQL * Plus or Server Manager: Connect Internal / Password.

be careful:

1. In Oracle8.1.6 Installing Under Win2000 Create a database, an error that often has a failure of the credential retrieval. This is because Oracle cannot apply an OS certified result. Generally, you can solve it by modifying SQLNET.AUTHENTICATION_SERVICES to None. At this time, Oracle will use a password file authentication method.

2. Because Oracle has several system pre-built users, it is best to change the password of these users immediately after the installation is complete. The default password of the system is: Internal / Oracle, SYS / CHANGE_ON_INSTALL, SYSTEM / Manager.

3. When selecting a password file authentication method, additional superusers can be added to the system. For example, use the following statement to add users Scott into the superuser: (executed by people with sysdba privileges)

SQL> Grant sysdba to scott; this Scott user has SYSDBA privileges. Note that SCOTT users can log in: Scott, Sys. When Scott does not enter AS SYSDBA when SCOT is logged in, SCOTT is logged in as a normal user. When AS SYSDBA is entered when logging in, the user who is logged in in SCOTT is actually SYS. This can be observed by the following image:

4. Username with Sysdba permissions in the current system can be queried from the data dictionary view V $ PWFILE_USER:

Select * from V $ pWFILE_USERS; as shown above.

5. The maximum number of users with SYSDBA privileges is determined by the Entries parameter when creating a password file. When you need to create more users with SYSDBA privileges, you need to delete the original password file and recreate one. This requires closing the database, deleting a password file, recreate a new password file, and enter a sufficient number in entries. Start Oracle before. At this time, all the super users who are authorized to authorize no longer exist, need to be re-authorized. So before recreate the password file, first query this view, write down the username, and then re-authorize after the password file is created. 6. InterNal user password forgotten processing method:

There are two ways:

1. ALTER user sys identified by new password; // This also changed the INTERNAL password, passed in Oracle8i

2. Recreate a new password file to specify a new password.