Shulou(Shulou.com)06/01 Report--

Related links: differences and connections of SYS,SYSTEM,DBA,SYSDBA,SYSOPER

First of all, let's talk about Oracle installation and OS user groups. Oracle often deals with operational user groups (OS User Group) in the process of installation and maintenance, from oracle users and dba groups to grid users and asm groups in 11gR2 today.

The three user groups commonly used in a single instance environment are:

1. Oinstall user group

The oinstall group is one of the OS user groups recommended by Oracle. It is recommended to create an oinstall group before the system installs Oracle products for the first time. In theory, the oinstall group should have an Oracle software product catalog (such as $CRS_HOME and $ORACLE_HOME) and an oracle Inventory information catalog repository, which records the Oracle products installed on the system.

If Oracle product software is already installed on the system, all groups in the existing oracle Inventory directory must be the primary group of users who will be used to install new oracle software products in the future.

The existing oracle Inventory owner group can be learned from the / etc/oraInst.loc location file:

Inventory_loc=/u01/app/oracle/oraInventory

Inst_group=oinstall

If the / etc/oraInst.loc location file does not exist, it is recommended to create an oinstall user group. Note that the GID of the user group on each node should be kept consistent in the RAC environment:

# / usr/sbin/groupadd-g GID oinstall

2. OSDBA user Group (dba)

OSDBA is a system DBA user group (dba) that we have to create. Without this user group, we will not be able to install database software and perform the task of managing the database.

3. OSOPER user Group (oper)

OSOPER is an additional user group (oper) that we can choose whether or not to create for the purpose of allowing os users to exercise certain database administration privileges, including SYSOPER role permissions.

How to create an OSOPER user group:

# / usr/sbin/groupadd oper

To sum up, in a single-instance environment, the Oracle owner user (usually oracle) is also a member of the oinstall, dba, and oper user groups. At the same time, the primary user group of the user must be oinstall.

In the 11.2 GI/CRS environment, the database software owner user (oracle) must also be a member of the asmdba user group.

Usermod-g oinstall-G dba,oper,asmdba oracle

Id oracle

Uid=54321 (oracle) gid=54321 (oinstall)

Groups=54321 (oinstall), 54322 (dba), 701 (asmdba), 54324 (oper)

Notice that both the OSDBA and OSOPER user groups are affected by the $ORACLE_HOME/rdbms/lib/config.c source file, which defines the default SS_DBA_GRP "dba" and SS_OPER_GRP "oper" as follows:

/ * SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. , /

/ * Refer to the Installation and User's Guide for further information. , /

/ * IMPORTANT: this file needs to be in sync with

Rdbms/src/server/osds/config.c, specifically regarding the

Number of elements in the ss_dba_grp array.

, /

# define SS_DBA_GRP "dba"

# define SS_OPER_GRP "oper"

# define SS_ASM_GRP ""

Char * ss_dba_grp [] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP}

Char * ss_dba_grp [] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP}; oracle in Oracle11g Release2 recommends managing Grid Infrastructure and ASM instances independently, so it is necessary to create more os user groups to meet different permission assignments.

The following three ASM user groups are commonly used in GI 11.2:

1. OSASM (asmadmin) user group

If we use ASM, then we must create the osasm (asmadmin) user group, whose members will be given SYSASM permissions to meet the group members' permission needs to manage Oracle Clusterware and Oracle ASM.

2. OSDBA for ASM group (asmdba) user group

Members of the OSDBA (asmdba) user group will be given read and write access to the ASM file. GI/CRS owner users and owners of all oracle database software must be members of the group. All members of the OSDBA (dba) user group must also be members of the asmdba group.

3. OSOPER for ASM (asmoper) user group

Asmoper and osoper are similar to additional optional user groups that can be created to satisfy the need to give users a limited set of ASM instance management privileges (ASM's SYSOPER role), which include starting and stopping ASM instances, and by default members of the OSASM (asmadmin) group will have ASM administration rights for all SYSOPER.

In the 11.2 GI/CRS environment, grid or griduser users are typically created to manage GI software and ASM instances, and grid users are created as follows:

Useradd-g oinstall-G asmadmin,asmdba,asmoper grid

Id grid

Uid=54322 (grid) gid=54321 (oinstall)

Groups=54321 (oinstall), 700 (asmadmin), 701 (asmdba), 55000 (asmoper)

There are two kinds of special permissions in Oracle, SYSDBA and SYSOPER. When DBA needs to maintain and manage the database, it must have one of these two special permissions. When the database is not open, it is impossible to log in to the database using the built-in account, but users with SYSDBA or SYSOPER permissions can log in. There are two ways to authenticate whether a user has two types of special permissions: OS authentication and password file authentication.

Whether the Oracle database is managed using OS authentication or password file authentication depends on the following three factors:

1. Parameter SQLNET.AUTHENTICATION_SERVICES setting in SQLNET.ORA parameter file

2. Parameter REMOTE_LOGIN_PASSWORDFILE setting in PFILE (SPFILE) parameter file

3. Password file orapw$SID (Linux) | PWD$SID.ora (Windows)

The basic order of Oracle authority authentication is like this. First, the setting of SQLNET.AUTHENTICATION_SERVICES determines whether to use OS authentication or password file authentication. If you use password file authentication, it depends on the latter two conditions: if the REMOTE_LOGIN_PASSWORDFILE parameter is set to non-NONE and the password file exists, you can use password file authentication normally, otherwise it will fail.

SQLNET.AUTHENTICATION_SERVICES parameter

In the SQLNET.ORA (located in the $ORACLE_HOME/NETWORK/ADMIN directory) file, you can simply open the modification with a text editor when you need it. The values of SQLNET.AUTHENTICATION_SERVICES will be different for different operating systems. We usually use some of the following settings:

SQLNET.AUTHENTICATION_SERVICES = (ALL) for Linux systems, OS authentication and password file authentication are supported.

For the Windows system, the actual experiment does not support this parameter, and the verification fails.

SQLNET.AUTHENTICATION_SERVICES = (NTS) this setting is for Windows systems only, this setting supports both OS authentication and password file authentication, and only Oracle running on Windows systems after the (NTS) value is set supports OS authentication.

SQLNET.AUTHENTICATION_SERVICES = (NONE) this setting has the same effect on Windows and Linux, specifying that Oracle uses only password file authentication.

Do not set this parameter or SQLNET.AUTHENTICATION_SERVICES = for Linux systems, OS authentication and password file authentication are supported by default.

For Windows systems, only password file authentication is supported by default, not OS authentication.

Implementation of OS authentication

Oracle uses two user groups in the operating system to control OS authentication. The names of these two user groups are different in different operating systems. Generally speaking, they are OSDBA and OSOPER, both of which are created during Oracle installation. The names of these two user groups on different systems are listed below:

Operating System Group

UNIX User Group

UNIX User Group

OSDBA

Dba

ORA_DBA

OSOPER

Oper

ORA_OPER

Users of the OSDBA user group can log in to the database with SYSDBA permissions, and users of the OSOPER user group can use SYSOPER permissions to log in to the database. Using sqlplus, you can log in in the following ways

CONNECT / AS SYSDBA

CONNECT / AS SYSOPER

Users with OS permission no longer need to enter a user name and password to log in to the database, so you can log in normally by using the following command:

CONNECT ANY_USER_NAME/ANY_PASSWORD AS SYSDBA

CONNECT ANY_USER_NAME/ANY_PASSWORD AS SYSOPER

So the steps to create a new OS authentication account are:

Set up an OS user to join the OSDBA or OSOPER user group with the newly added user login system, and then type sqlplus / AS SYSDBA to log in

REMOTE_LOGIN_PASSWORDFILE parameter

The setting of the REMOTE_LOGIN_PASSWORDFILE system parameter determines how the database uses the password file, and there are three values that can be set for this parameter:

REMOTE_LOGIN_PASSWORDFILE = NONE does not use password files

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE uses a password file, but only one database instance is available

REMOTE_LOGIN_PASSWORDFILE = SHARED multiple database instances share a password file. Under this setting, it is not possible to add other database users to the password file as special privileges users.

The REMOTE_LOGIN_PASSWORDFILE parameter is an initialization parameter that can only be specified in init.ora/pfile or modified with the following statement while the database is open, and then restart the database.

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = SPFILE

To check the current REMOTE_LOGIN_PASSWORDFILE settings, enter the following command after logging in to Oracle

SYS@seiang11g > show parameter remote

NAME TYPE VALUE

-

Remote_dependencies_mode string TIMESTAMP

Remote_listener string

Remote_login_passwordfile string EXCLUSIVE

Remote_os_authent boolean FALSE

Remote_os_roles boolean FALSE

Result_cache_remote_expiration integer 0

The following is an explanation of the 11g official document:

*

Selecting an Authentication Method for Database Administrators

Database Administrators can authenticate database administrators through the data dictionary, (using an account password) like other users. Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case-sensitive. (You can disable case sensitivity and return to pre-Release 11gbehavior by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)

Database administrators can authenticate with other users through data dictionaries (using account passwords). Keep in mind that, starting with Oracle Database 11g version 1, database passwords are case sensitive. (you can disable sizing by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE)

In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA or SYSOPERprivilege:

In addition to normal data dictionary authentication, the following methods can be used to authenticate database administrators with SYSDBA or SYSOPER permissions

Operating system (OS) authenticationPassword filesStrong authentication with a network-based authentication service, such as Oracle Internet Directory

These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. (They can also be used when the database is available.)

These methods are required to authenticate the database administrator when the database is not started or unavailable. (you can also use databases when they are available. )

Notes:

These methods replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle Database. CONNECT INTERNAL is no longer supported.

Operating system authentication takes precedence over password file authentication. If you meet the requirements for operating system authentication, then even if you use a password file, you will be authenticated by operating system authentication.

Your choice will be influenced by whether you intend to administer your database locally on the same system where the database resides, or whether you intend to administer many different databases from a single remote client. Figure 1-2 illustrates the choices you have for database administrator authentication schemes.

Figure 1-2 Database Administrator Authentication Methods

If you are performing remote database administration, consult your Oracle Net documentation to determine whether you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure.

Nonsecure Remote Connections

To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database user names that have been granted the SYSDBA or SYSOPER system privilege. This form of authentication is discussed in "Using Password File Authentication".

To connect to the Oracle database as a privileged user through an unsecured connection, you must authenticate through a password file. When using password file authentication, the database uses a password file to track database usernames that have been granted SYSDBA or SYSOPER system permissions.

Local Connections and Secure Remote Connections

You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:

If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file. If the database has a password file and you have been granted SYSDBA or SYSOPER system permissions, you can connect and authenticate through the password file.

If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users. Operating system authentication can be used if the server does not use a password file, or if SYSDBA or SYSOPER permissions have not been granted and therefore are not in the password file. On most operating systems, database administrator authentication involves placing the database administrator's operating system user name in a special group, commonly referred to as OSDBA. Users in this group are granted SYSDBA permissions. A similar group, OSOPER, is used to grant SYSOPER permissions to users.

Using Operating System Authentication

This section describes how to authenticate an administrator using the operating system.

OSDBA and OSOPER

Membership in one of two special operating system groups enables a DBA to authenticate to the database through the operating system rather than with a database user name and password. This is known as operating system authentication. These operating system groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process. The default names vary depending upon your operating system, and are listed in the following table:

Operating System Group

UNIX User Group

Windows User Group

OSDBA

Dba

ORA_DBA

OSOPER

Oper

ORA_OPER

Members of one of two special operating system groups enable DBA to authenticate the database through the operating system instead of using the database username and password, which is called operating system authentication.

Oracle Universal Installer uses these default names, but you can override them. One reason to override them is if you have multiple instances running on the same host computer. If each instance is to have a different person as the principal DBA, you can improve the security of each instance by creating a different OSDBA group for each instance. For example, for two instances on the same host, the OSDBA group for the first instance could be named dba1, and OSDBA for the second instance could be named dba2. The first DBA would be a member of dba1 only, and the second DBA would be a member of dba2 only. Thus, when using operating system authentication, each DBA would be able to connect only to his assigned instance.

Oracle Universal Installer uses default names, but you can override them. One reason to overwrite them is if you have multiple instances running on the same host. If each instance needs a different person as the DBA, you can improve the security of each instance by creating a different OSDBA group for each instance. For example, for two instances on the same host, the OSDBA group for the first instance can be named dba1, and the OSDBA group for the second instance can be named dba2. The first DBA is just a member of dba1, and the second DBA is just a member of dba2. Therefore, when using operating system authentication, each DBA will only be able to connect to its assigned instance.

Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:

If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with theSYSDBA system privilege.If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with theSYSOPER system privilege.If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails.

Preparing to Use Operating System Authentication

To enable operating system authentication of an administrative user:

Create an operating system account for the user.Add the account to the OSDBA or OSOPER operating system defined groups.Connecting Using Operating System Authentication

A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:

CONNECT / AS SYSDBA

CONNECT / AS SYSOPER

For the Windows platform only, remote operating system authentication over a secure connection is supported. You must specify the net service name for the remote database:

CONNECT / @ net_service_name AS SYSDBA

CONNECT / @ net_service_name AS SYSOPER

Both the client computer and database host computer must be on a Windows domain.

The password file stores the user name and password of the user who has been granted SYSDBA or SYSOPER privileges. It is an encrypted file, users can not modify this file, in the Linux system password file is generally saved in the $ORACLE_HOME/dbs directory, the file name is orapw$SID; in the Windows system password file is generally saved in the $ORACLE_HOME/database directory, the file name is PWD$SID.ora.

The basic steps for using password file authentication are:

Use the orapwd tool to generate a password file to set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE or SHARED to log in to the database using SYS, and create a new database user uses the GRANT command to grant the newly created user SYSDBA/SYSOPER permission

Using Password File Authentication

This section describes how to authenticate an administrative user using password file authentication.

Preparing to Use Password File Authentication

To enable authentication of an administrative user using password file authentication you must do the following:

If not already created, create the password file using the ORAPWD utility:2. ORAPWD FILE=filename ENTRIES=max_users

See "Creating and Maintaining a Password File" for details

Notes:

O When you invoke Database Configuration Assistant (DBCA) as part of the Oracle Database installation process, DBCA creates a password file.

O Beginning with Oracle Database 11g Release 1, passwords in the password file are case-sensitive unless you include the IGNORECASE = Y command-line argument.

Starting with Oracle Database 11g version 1, passwords in password files are case-sensitive unless you include the IGNORECASE = Y command line argument.

Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default) .Note:

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.

Connect to the database as user SYS (or as another user with the administrative privileges). If the user does not already exist in the database, create the user and assign a password.Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case-sensitive. (You can disable case sensitivity and return to pre-Release 11g behavior by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)

Grant the SYSDBA or SYSOPER system privilege to the user:7. GRANT SYSDBA to oe

This statement adds the user to the password file, thereby enabling connection AS SYSDBA.

Connecting Using Password File Authentication

Administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. They must connect using their username and password and the AS SYSDBA or AS SYSOPER clause. Note that beginning with Oracle Database 11g Release 1, passwords are case-sensitive unless the password file was created with the IGNORECASE = Y option.

For example, user oe has been granted the SYSDBA privilege, so oe can connect as follows:

CONNECT oe AS SYSDBA

However, user oe has not been granted the SYSOPER privilege, so the following command will fail:

CONNECT oe AS SYSOPER

Note:

Operating system authentication takes precedence over password file authentication. Specifically, if you are a member of the OSDBA or OSOPER group for the operating system, and you connect as SYSDBA or SYSOPER, you will be connected with associated administrative privileges regardless of the username/password that you specify.

Operating system authentication takes precedence over password file authentication. Specifically, if you are a member of the OSDBA or OSOPER group of the operating system and connect as SYSDBA or SYSOPER, the associated administrative privileges will be connected regardless of the username / password you specify.

If you are not in the OSDBA or OSOPER groups, and you are not in the password file, then attempting to connect as SYSDBA or as SYSOPER fails.

If you are not in the OSDBA or OSOPER group and you are not in the password file, try to connect as SYSDBA or SYSOPER.

Creating and Maintaining a Password File

You can create a password file using the password file creation utility, ORAPWD. For some operating systems, you can create this file as part of your standard installation.

Creating a Password File with ORAPWD

The syntax of the ORAPWD command is as follows:

ORAPWD FILE=filename [ENTRIES=numusers] [FORCE= {Y | N}] [IGNORECASE= {Y | N}]

Use the tool orapwd provided by Oracle to create or reinitialize a password file:

[oracle@seiang11g ~] $orapwd

Usage: orapwd file= entries= force= ignorecase= nosysdba=

Where

File-name of password file (required)

Password-password for SYS will be prompted if not specified at command line

Entries-maximum number of distinct DBA (optional)

Force-whether to overwrite existing file (optional)

Ignorecase-passwords are case-insensitive (optional)

Nosysdba-whether to shut out the SYSDBA logon (optional Database Vault only).

There must be no spaces around the equal-to (=) character.

[oracle@seiang11g ~] $orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=XXX entries=5 force=y

Note: SYSDBA or SYSOPER privileges granted to other users who use orapwd to regenerate the password file to save will be lost and new GRANT will be required.

The set entries value cannot be modified. If you want to modify entries, you need to regenerate the password file. Before generating the password file, you can query the users who are currently granted SYSDBA/SYSOPER permission through the V$PWFILE_USERS view, and then re-grant SYSDBA/SYSOPER permission to these users after the password file is regenerated.

Command arguments are summarized in the following table.

Argument

Description

FILE

Name to assign to the password file. You must supply a complete path. If you supply only a file name, the file is written to the current directory.

ENTRIES

(Optional) Maximum number of entries (user accounts) to permit in the file.

FORCE

(Optional) If y, permits overwriting an existing password file.

IGNORECASE

(Optional) If y, passwords are treated as case-insensitive.

There are no spaces permitted around the equal-to (=) character.

The command prompts for the SYS password and stores the password in the created password file.

ORAPWD Command Line Argument Descriptions

The following sections describe the ORAPWD command line arguments.

FILE

This argument sets the name of the password file being created. You must specify the full path name for the file. The contents of this file are encrypted, and the file cannot be read directly. This argument is mandatory.

This parameter sets the name of the password file being created. You must specify the full pathname of the file. The contents of the file are encrypted and the file cannot be read directly. This statement is mandatory.

The file name required for the password file is operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

The file name required for password files is based on a specific operating system. Some operating systems require password files to follow a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

Table 1-1 lists the required name and location for the password file on the UNIX, Linux, and Windows platforms. For other platforms, consult your platform-specific documentation.

Table 1-1 Required Password File Name and Location on UNIX, Linux, and Windows

Platform

Required Name

Required Location)

UNIX and Linux

OrapwORACLE_SID

ORACLE_HOME/dbs

Windows

PWDORACLE_SID.ora

ORACLE_HOME\ database

For example, for a database instance with the SID orcldw, the password file must be named orapworcldw on Linux and PWDorcldw.ora on Windows.

In an Oracle Real Application Clusters environment on a platform that requires an environment variable to be set to the path of the password file, the environment variable for each instance must point to the same password file.

In RAC environments on platforms where the environment variable needs to be set to the password file path, the environment variable for each instance must point to the same password file.

Caution:

It is critically important to the security of your system that you protect your password file and the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.

Warning:

In terms of system security, it is very important to protect the password file and the environment variables that identify the location of the password file. Any user with access rights may compromise the security of the connection.

ENTRIES

This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because theORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

This parameter specifies the number of entries that need to be accepted by the password file. This number corresponds to the number of different users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of entries allowed can be higher than the number of users because the ORAPWD utility continues to assign password entries until the operating system block is populated. For example, if your operating system block size is 512 bytes, it will save four password entries. The number of password entries assigned is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this argument is required.

Entries can be reused when users are added to and removed from the password file. This parameter is required if you intend to specify REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE and allow users to be granted SYSDBA and SYSOPER permissions.

Caution:

When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate more entries than you think you will ever need.

When the number of assigned password entries is exceeded, a new password file must be created. To avoid this need, assign more entries than you needed before.

FORCE

This argument, if set to Y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this argument is omitted or set to N.

This parameter (if set to Y) enables you to overwrite an existing password file. If a password file with the same name already exists and this parameter is omitted or set to N, an error is returned.

IGNORECASE

If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.

If this parameter is set to y, the password is not case-sensitive. That is, case is ignored when comparing the password provided by the user when logging in with the password in the password file.

Sharing and Disabling the Password File

You use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether a password file is shared among multiple Oracle Database instances. You can also use this parameter to disable password file authentication. The values recognized for REMOTE_LOGIN_PASSWORDFILE are:

You can use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether password files are shared among multiple Oracle database instances. You can also use this parameter to disable password file authentication.

NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.

EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (Oracle RAC) database. A SHARED password file cannot be modified. Therefore, you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPERsystem privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can changeREMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

This option is useful if you are administering multiple databases or an Oracle RAC database.

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, it is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

Note:

You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.

If REMOTE_LOGIN_PASSWORDFILE is set to shared, you cannot change the password for SYS. If you try to do so, an error message will be sent.

Keeping Administrator Passwords Synchronized with the Data Dictionary

If you change the REMOTE_LOGIN_PASSWORDFILE initialization parameter from NONE to EXCLUSIVE or SHARED, or if you re-create the password file with a different SYSpassword, then you must ensure that the passwords in the data dictionary and password file for the SYS user are the same.

To synchronize the SYS passwords, use the ALTER USER statement to change the SYS password. The ALTER USER statement updates and synchronizes both the dictionary and password file passwords.

To synchronize the passwords for non-SYS users who log in using the SYSDBA or SYSOPER privilege, you must revoke and then regrant the privilege to the user, as follows:

1. Find all users who have been granted the SYSDBA privilege.

2. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME! = 'SYS' AND SYSDBA='TRUE'

3. Revoke and then re-grant the SYSDBA privilege to these users.

4. REVOKE SYSDBA FROM non-SYS-user;5. GRANT SYSDBA TO non-SYS-user

6. Find all users who have been granted the SYSOPER privilege.

7. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME! = 'SYS' AND SYSOPER='TRUE'

8. Revoke and regrant the SYSOPER privilege to these users.

9. REVOKE SYSOPER FROM non-SYS-user;10.GRANT SYSOPER TO non-SYS-user;Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.

A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.

Creating a Password File and Adding New Users to It

Use the following procedure to create a password and add new users to it:

1. Follow the instructions for creating a password file as explained in "Creating a Password File with ORAPWD".

2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)

Note:

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.

3. Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:

4. CONNECT SYS AS SYSDBA

5. Start up the instance and create the database if necessary, or mount and open an existing database.

6. Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. Granting and Revoking SYSDBA and SYSOPER Privileges

If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:

GRANT SYSDBA TO oe

Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM oe

Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPERsystem privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPERdatabase privileges with operating system roles.

Because SYSDBA and SYSOPER are the most powerful database permissions, WITH ADMIN OPTION is not used in GRANT statements. That is, the assignee cannot grant SYSDBA or SYSOPER permissions to other users in turn. Only users who are currently connected as SYSDBA can grant or revoke SYSDBA or SYSOPERsystem permissions for other users. These permissions cannot be granted to roles because roles are available only after the database is started. Do not confuse SYSDBA and SYSOPER database privileges with operating system roles.

Viewing Password File Members

Use the V$PWFILE_USERS view to see the users who have been granted the SYSDBA, SYSOPER, or SYSASM system privileges. The columns displayed by this view are as follows:

Column

Description

USERNAME

This column contains the name of the user that is recognized by the password file.

SYSDBA

If the value of this column is TRUE, then the user can log on with the SYSDBA system privileges.

SYSOPER

If the value of this column is TRUE, then the user can log on with the SYSOPER system privileges.

SYSASM

If the value of this column is TRUE, then the user can log on with the SYSASM system privileges.

Note:

SYSASM is valid only for Oracle Automatic Storage Management instances.

Every time you use GRANT SYSDBA/SYSOPER to grant special permissions to new users or ALTER USER commands to change the password of users with SYSDBA/SYSOPER permissions in the Oracle system, Oracle will automatically modify the password file and add or modify the corresponding items, so as to ensure that users with special permissions can log in to the database normally if the data is not open.

Experimental environment:

Operating system: CentOS 7.1

Database: Oracle 11.2.0.4

There are so many long speeches above, let's do an experiment to verify it. This experiment is based on the Linux system. Before doing the experiment, create a password file using the following command:

[oracle@seiang11g ~] $orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=XXX entries=5 force=y

1. Verify OS authentication

Set the parameter SQLNET.AUTHENTICATION_SERVICES = (ALL) in SQLNET.ORA or not, REMOTE_LOGIN_PASSWORDFILE = NONE, and then do the following.

Local login can be successful by using the following two ways

[oracle@seiang11g ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 11:58:26 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@seiang11g >

[oracle@seiang11g ~] $sqlplus 111DB 222as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 11:58:41 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@seiang11g >

Login using password file remotely, failed

[oracle@seiang11g ~] $sqlplus sys/oracle@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 11:59:31 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

[oracle@seiang11g ~] $sqlplus sys/oracle@10.1.1.46/ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:04:36 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

2. Both certifications are invalid.

Set the parameters SQLNET.AUTHENTICATION_SERVICES = (NONE), REMOTE_LOGIN_PASSWORDFILE = NONE in SQLNET.ORA, and then do the following.

Local login failed in the following two ways

[oracle@seiang11g ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:06:26 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

[oracle@seiang11g ~] $sqlplus 111DB 222as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:06:55 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Login using password file remotely, failed

[oracle@seiang11g ~] $sqlplus sys/oracle@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:07:19 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

[oracle@seiang11g ~] $sqlplus system/oracle@10.1.1.46/ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:07:46 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

3. Verify password file authentication

Set the parameters SQLNET.AUTHENTICATION_SERVICES = (NONE), REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE or SHARED in SQLNET.ORA, and then do the following.

Local use verification OS authentication failed

[oracle@seiang11g ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:14:43 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Local authentication password file authentication, successful

[oracle@seiang11g ~] $sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:15:10 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@seiang11g >

Remote use of password file authentication, successful

[oracle@seiang11g ~] $sqlplus sys/oracle@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:18:21 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORADB11G >

[oracle@seiang11g ~] $sqlplus scott/tiger@10.1.1.46/ORADB11G

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 12:18:55 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@10.1.1.46/ORADB11G >

4. Both certifications are successful

Set the parameters SQLNET.AUTHENTICATION_SERVICES = (ALL), REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE in SQLNET.ORA, and then do the following.

Local use verification OS authentication, successful

[oracle@seiang11g ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 11:58:26 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@seiang11g >

Remote use of password file authentication, successful

[oracle@seiang11g admin] $sqlplus sys/oracle@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 14:39:32 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-12641: Authentication service failed to initialize

5. Authorize SYSDBA/SYSOPER permissions to other database accounts

Check the modification time of the password file first

[oracle@seiang11g dbs] $ll orapwseiang11g

-rw-r- 1 oracle oinstall 1536 Aug 7 18:51 orapwseiang11g

Log in to the database with SYS, create a new user test, and grant SYSDBA permissions

SYS@seiang11g > create user test identified by test

User created.

SYS@seiang11g > grant sysdba to test

Grant succeeded.

Look at the password file again. It has been modified.

[oracle@seiang11g dbs] $ll orapwseiang11g

-rw-r- 1 oracle oinstall 1536 Aug 9 13:53 orapwseiang11g

If you log in with a new test account, you can log in successfully

[oracle@seiang11g ~] $sqlplus test/test@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9 13:55:48 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORADB11G >

Note: if you want to revoke the SYSDBA permission, you only need to run the following statement.

SYS@ORADB11G > revoke sysdba from test

Revoke succeeded.

FAQ description

1. How to find users with SYSDBA or SYSOPER permissions

Using the view V$PWFILE_USERS, the SYSDB and SYSOP in the result set represent whether you have SYSDBA and SYSOPER permissions, respectively.

SYS@ORADB11G > select * from v$pwfile_users

USERNAME SYSDB SYSOP SYSAS

SYS TRUE TRUE FALSE

TEST FALSE TRUE FALSE

2. "ORA-01994: GRANT failed: password file missing or disabled" appears when granting permission

This occurs because no password file was created, or because the password file was placed in an incorrect directory and Oracle could not find it. Just rebuild or place the password file in the $ORACLE_HOME/dbs/ directory.

3. What if I forget the password of my SYS account?

If the database is logged in with OS authentication enabled, you can log in to the database with OS authentication and then modify it using the following command

Alter user SYS identified by pwd

If OS authentication login is not enabled, you need to rebuild the password file with orapwd

The password entry in orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=pwd entries=10 force=y; specifies the password of SYS.

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.