ORACLE 系统用户角色故障导致ORA-01017

迷南。 2022-06-08 10:18 181阅读 0赞

最近在为Oracle单实例配置ASM时，做了一些调整，结果导致sys用户无法以操作系统身份验证登陆，即提示ORA-01017错误。本文描述了这个故障的解决过程，供大家参考。

有关Oracle身份认证机制，可以参考：[Oracle 密码文件][Oracle]

## 一、故障现象 ##

[oracle@centos7 ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 22 09:52:26 2017
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    ERROR:
    ORA-01017: invalid username/password; logon denied

## 二、故障分析 ##

1、使用oerr命令行获取详细帮助
    [oracle@centos7 ~]$ oerr ora 1017
    01017, 00000, "invalid username/password; logon denied"
    // *Cause:
    // *Action:
    
    2、查看sqlnet.ora的配置，这个文件主要定义了是否允许开启OS用户免密码登陆认证。
    [oracle@centos7 ~]$ more $ORACLE_HOME/network/admin/sqlnet.ora
    # sqlnet.ora Network Configuration File: /u01/oracle/db_1/network/admin/sqlnet.ora
    # Generated by Oracle configuration tools.
    
    #NAMES.DIRECTORY_PATH= (TNSNAMES)
    SQLNET.AUTHENTICATION_SERVICES = (ALL)    ###此处为ALL，表面可以使用OS认证身份验证
    
    ADR_BASE = /u01/oracle
    
    3、查看config.c配置文件
    [oracle@centos7 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c
    
    /* 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" ###此处操作系统用户组定义为dba组
    #define SS_OPER_GRP "oper" ###Author : Leshami
    #define SS_ASM_GRP "" ###Blog : http://blog.csdn.net/leshami
    
    char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};  
    
    4、查看当前oracle用户所在的用户组
    [oracle@centos7 ~]$ id oracle
    uid=1001(oracle) gid=54321(oinstall) groups=54321(oinstall),54327(asmdba),54329(asmadmin)
    从上面的结果中可知，oracle用户并不属于dba组
    
    5、查看操作系统层面是否存在dba用户组
    [oracle@centos7 ~]$ grep dba /etc/group
    dba:x:54322:
    
    通过上面的分析，应该是oracle用户所在的组不包含dba组导致无法通过操作系统层面实现身份认证。

## 三、故障解决 ##

[oracle@centos7 ~]$ su - root
    Password:
    
    增加oracle用户到dba组
    [root@centos7 ~]# usermod -G dba,asmdba,asmadmin oracle
    [root@centos7 ~]# id oracle
    uid=1001(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54327(asmdba),54329(asmadmin)
    
    [root@centos7 ~]# su - oracle
    Last login: Fri Sep 22 10:03:54 CST 2017 on pts/1
    
    再次登陆成功
    [oracle@centos7 ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 22 10:27:34 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
    
    SQL>

[Oracle]: http://blog.csdn.net/leshami/article/details/5611672