Problems with SYSDBA/SYSOPER/INTERNAL connect

SAP note 说明了ORA-01017产生的原因
Reason and Prerequisites

Unlike a database connect with a normal database user (such as SAPR3), the SYSDBA/SYSOPER/INTERNAL connects are not based on information in the Oracle data dictionary (such as passwords), but rather on authentication using operating system groups. The advantage of this is that a connect is possible even if the database is stopped.
The following three related options are available for this type of connect:

1. CONNECT/AS SYSDBA

          The system checks whether the calling operating system user belongs to the dba (UNIX) or ORA_<sid>_DBA (WINDOWS) operating system group. If so, the user logs on with comprehensive SYSDBA privileges. If not, the system prompts the user to enter a password and then issues ORA-01017 or ORA-01031. To simplify matters, ORA_<sid>_DBA is also meant where dba is referred to in the following.

2. CONNECT/AS SYSOPER

          The system checks whether the calling operating system user belongs to the oper (UNIX) or ORA_<sid>_OPER (WINDOWS) operating system group. If so, the user logs on with slightly restricted SYSOPER privileges. If not, the system prompts the user to enter a password and then issues ORA-01017 or ORA-01031. The following also applies here: Subsequently, "oper" also means ORA_<sid>_OPER.

3. CONNECT INTERNAL

          Based on the group membership, this call automatically executes a SYSDBA or SYSOPER connect. Proceed as follows:

    a) The OS user belongs to the dba group -> CONNECT/AS SYSDBA

    b) The OS user belongs to the oper group (but not to the dba group) -> CONNECT/AS SYSOPER

    c) The OS user does not belong to either of the groups -> ORA-01017 or ORA-01031.

          The "CONNECT INTERNAL" syntax is no longer supported as of Oracle 9i and is therefore completely replaced by the direct SYSDBA/SYSOPER connect. When we refer to the SYSDBA/SYSOPER connect in the text below, we also mean the INTERNAL connect.

In the event of an error, the password prompt that appears leads to the assumption that there is a password and you forgot it, but this is generally not the case. However, this is usually incorrect. A password exists only if a password file is used (for more information, see SAP Note 168243).

Up to Release 3.1I , all relevant users belonged to the dba group and oper was not used at all. With Release 4.0B, adm (UNIX) and sapservice (WINDOWS) belonged to both groups while ora (UNIX) and adm (WINDOWS) were only assigned to the oper group. As of Release 4.5B, both adm/ora (UNIX) and adm/sapservice (WINDOWS) should belong to the dba and oper groups (especially because of Note 148535).

To solve the problems, check the following points. Unless otherwise specified, the proposed solutions refer to errors ORA-01017 and ORA-01031. Otherwise, the relevant error code is specified:

1. Membership of the operating system user to the corresponding operating system group.

2. WINDOWS: Domain user vs. local user

3. UNIX: Readability of /etc/group

4. WINDOWS: No sqlnet.ora in <oracle_home>\network\admin

5. CRYPTO parameter in sqlnet.ora

6. SQLNET.AUTHENTICATION_SERVICES entry in sqlnet.ora

7. W2K: Access Control Entries

8. UNIX: Oracle's definition of the dba and oper groups

9. AIX: mkpasswd problem

10. NLS parameter

11. WINDOWS: DBA_AUTHORIZATION parameter

12. WINDOWS: Use of special users

13. ORA-09275, Oracle >= 9i: Desupport of "CONNECT INTERNAL"

14. ORACLE_SID not set

15. ORA-09925: Audit file system full

16. ORA-09925: Missing authorizations

17. WINDOWS: Problems during authentication using domain controllers.

18. WINDOWS: Database server >= Windows 2000 in Windows 2000 domain

    19. UNIX: Duplicate or excessively long group definition in /etc/group

If no error occurs with the connect on WINDOWS 2000 but the queue times are too long, refer to Note 625222.
Solution

1. The answer to this question largely depends on the operating system:

    a) UNIX

                   Group memberships are canceled using the /etc/group configuration file. This file should include two lines (similar to below) for the dba and oper groups:
oper: ... :<sid>adm,ora<sid>
dba: ... :<sid>adm,ora<sid>

                   In particular, make sure that no additional characters appear in the corresponding lines. Blank characters before or after the comma or at the end of the line cause authentication problems similar to those that result from typing errors or hidden special characters. If you are unsure about the integrity of the entries, we recommend that you delete both lines and reenter the lines again.

In the case of authentication with LDAP, local entries for Oracle groups in /etc/group should be avoided.

                   In principle, you can also restart an authentication using NIS. However, we do not recommend this because Oracle still has some problems with this mechanism. If you are using NIS, you can use

ypcat group | grep oper
ypcat group | grep dba

                   to determine whether the groups are defined correctly and whether the <sid>adm and ora<sid> belong to these groups. When using NIS, note the following pitfalls:

    During the software installation, it may sometimes be necessary to deactivate NIS.

    The oper group IDs must be identical on NIS and locally.

    If a local group is defined in /etc/group without ora<sid> and <sid>adm but the groups are correctly assigned using NIS, the authentication may not work because Oracle gets confused by the missing entries in /etc/group.

    b) Windows NT

                   On NT, the user and group adminstration is located in the NT User Manager. Note that for safety reasons, only local groups are called for authentication by Oracle. As a result, assigning user membership to domain groups of the same name is completely ineffective. To get an overview of the local groups, choose "Select domain" and enter the name of the database server preceded by two backslashes (that is, "\\host"). In the ORA_<sid>_DBA or ORA_<sid>_OPER groups that now appear, the corresonding user must be a member for the authentication to work. If these groups do not yet exist, they must be created. The user in question can then be included in these groups.

                   If there is a problem with the SYSDBA or SYSOPER Connect when you start database jobs with transaction DB13, check to see whether the R/3 service was started correctly with the sapservice<sid> user. The user that was used to start the service is also used for calls from transaction DB13.

    c) W2K

                   You can start the maintenance of local groups on W2K by right-clicking "My Computer" and selecting "Manager" and "Local Users and Groups". The following also applies here: The operating system user must belong to the relevant local groups ORA_<sid>_DBA and/or ORA_<sid>_OPER.

2. Domain or local problems can occur, not only with the groups, but also with the users themselves. This is especially true if a domain user exists with the same name as a local user (for example, domain user <domain>\<sid>adm, local user <sid>adm). If the only the domain user is entered in the local group, the local user of the same name cannot be authenticated. The same applies in reverse.

3. The /etc/group file should have read authorization for all users. If necessary, add this authorization as a root with "chmod o+r /etc/group".

4. Make sure that sqlnet.ora is contained in the <oracle_home>\network\admin directory. If this file is in another directory, ORA-01031 occurs even if TNS_ADMIN indicates this directory.

5. Sqlnet.ora should not contain any entries concerning coding mechanisms such as SQLNET.CRYPTO_SEED. These are not required in connection with R/3 and can result in authentication problems in individual cases. You can determine which sqlnet.ora is pulled using Note 445038.

    6. For WINDOWS, check whether the sqlnet.ora files contains the entry

  SQLNET.AUTHENTICATION_SERVICES = (NTS)

          to activate the authentication using WINDOWS groups. In particular, make sure that you check the entry for spelling mistakes.

          Normally, there should not be any SQLNET.AUTHENTICATION_SERVICES entry in the pulled sqlnet.ora on UNIX. An entry with the value "(BEQ)" is also alright.

7. See Note 417561.

8. Whereas the names of the underlying groups on WINDOWS are static and cannot be changed, those on UNIX systems can be changed. The group names used are usually requested as part of the database installation. If an error occurs here, Oracle executes the SYSDBA/SYSOPER authentication with groups of other names. You can detect and solve this kind of problem as follows:

        a) There is a config.c (HP-UX, TRU64, LINUX) or config.s (other) file in the $ORACLE_HOME/rdbms/lib directory. Each file must include the following entries, among others:

        HP-UX, TRU64, LINUX: #define SS_DBA_GRP "dba"

                             #define SS_OPER_GRP "oper"

        SOLARIS:             .ascii "dba\0"

                             .ascii "oper\0"

        AIX:                 .string "dba"

                         .string "oper"

    b) Delete the config.o file from $ORACLE_HOME/rdbms/lib (but do not delete config.c or config.s).

    c) Stop Oracle (if you have not already done so).

    d) Use ora<sid> to switch to the $ORACLE_HOME/rdbms/lib directory and relink Oracle with "make -f ins_rdbms.mk ioracle". See Note 97953 for more information about relinking.

9. See Note 403269 and import the relevant Fix APAR IY22458.

10. In rare cases, incorrectly set NLS parameters such as ORA_NLS33 or NLS_LANG are responsible for the error screen. For more information, see Note 175636.

11. In WINDOWS, check whether DBA_AUTHORIZATION still exists in the registry and, if necessary, delete this variable.

12. For security reasons, Oracle does not allow you to set up an INTERNAL connection with the special users SYSTEM and ADMINISTRATOR. Therefore, always use your own operating system users. Scheduling programs such as AT can cause problems in this regard if you start under one of these accounts. In this case, you must either select another account or use a user switch to change to another user.

13. As of Oracle 9i, the "CONNECT INTERNAL" mechanism is no longer supported - only "CONNECT/AS SYSDBA" and "CONNECT/AS SYSOPER" still work. Refer to Note 554112 for more information.

          If you try to log on the the database using "CONNECT INTERNAL" in spite of this, the system prompts you to enter a password and issues ORA-09275. In this case, make sure that the connection setup occurs with SYSDBA or SYSOPER connect. If you use own developoment "CONNECT INTERNAL" scripts, adjust them.

14. Make sure that ORACLE_SID is set in the call environment.

15. If the Connect fails with ORA-09925 or ORA-09817, check whether the file system with the Oracle home has filled up and, if necessary, create additional space.

16. If ORA-09925 occurs along with operating system error 13 (Permission denied), there are problems accessing the files under $ORACLE_HOME/rdbms/audit. Check whether this directory exists and whether ora<sid> has write permission. If the error only occurs with users other than ora<sid>, check the s-bit mechanism in accordance with Note 583861.

17. Sporadic failure of the SYSDBA/SYSOPER connect with ORA-12638 or ORA-01031 may be the result of access problems on the verifying domain controller. See Notes 595874 and 614036.

18. Due to different authentication methods, the SYSDBA/SYSOPER log on to a database server with Windows 2000 or later fails if the domain controller is still running on Windows 2000. In this case, you can solve the problem as follows:

Log on as ADMINISTRATOR in the Windows 2000 domain controller.

Call "Control Panel" -> "Active Directory Users and Computers".

Select "Computers".

Double-click the Windows 2000 database server.

Select "Member of"

Add "Pre-Windows 2000 Compatible Access".

19. Make sure that the dba group and oper group are defined only once in each case in the file /etc/group and that a line is not longer than 512 characters.

https://launchpad.support.sap.com/#/notes/480266