Mir Sayeed Hassan – Oracle Blog

How to Prevent an OS Authenticated Oracle User To Connect via SQLPLUS / AS SYSDBA

Login to the server

[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sat Nov 5 03:24:11 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> show user
USER is “SYS”

Here as you see anyone who has the access to this server can login to the sys credential without passing an password

Our aim to restrict the sys user login in Oracle Database with password.

Solution 1:

You can disables the OS authentication by changing to the SQLNET.ora file with SQLNET.AUTHENTICATION_SERVICES=(NONE).

In case situation, if the user has the SYSADMIN access such as who can login to root user & change this parameter easily by OS authentication enabled or remove this parameter.

To perform this method, Goto the default location of network file.

[oracle@testdb ~]$ cd $ORACLE_HOME/network/admin

[oracle@testdb admin]$ vi sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES=(NONE)

:wq

Set the config on bash_profile

[oracle@ractest1(rac11) ~]$ cat ~/.bashrc

# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# User specific aliases and functions
alias db1="source ~/bash_testdb"
alias db11="source ~/bash_rac11"
alias asm11="source ~/bash_asm1"
source ~/bash_rac11

If you are using as single instance database, Set this in bash file

[oracle@ractest1(rac11) ~]$ cat ~/bash_rac11

export ORACLE_SID=testdb
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib
export PS1="[\u@\h\[\033[01;32m\]($ORACLE_SID)\[\033[0m\] \W]\$ "
alias ll="ls -ltrah"
alias sqlp="sqlplus / as sysdba"

If you are using as rac & asm, Set this below config in all the rac node

Set this in RAC Node 1

[oracle@ractest1(rac11) ~]$ cat ~/bash_rac11

export ORACLE_SID=rac11
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib
export PS1="[\u@\h\[\033[01;32m\]($ORACLE_SID)\[\033[0m\] \W]\$ "
alias ll="ls -ltrah"
alias sqlp="sqlplus / as sysdba"

Set this in ASM Node1

[oracle@ractest1(rac11) ~]$ cat ~/bash_asm1

export ORACLE_SID=+ASM1
export ORACLE_HOME=/u02/app/11.2.0/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib
export PS1="[\u@\h\[\033[01;32m\]($ORACLE_SID)\[\033[0m\] \W]\$ "
alias ll="ls -ltrah"
alias sqlp="sqlplus / as sysasm"

Login with sqlplus / as sysdba, You should get the error 

[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 5 04:06:47 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Note: Putting the following line “SQLNET.AUTHENTICATION_SERVICES=(NONE)” in sqlnet.ora file will restrict “/ as sysdba” connections without password

Solution 2:

But My requirement : Wherever any one wants to connect to the database as SYSDBA privileged user he/she must have to input the password that means he/she can’t connect to the database using: CONN / AS SYSDBA but can able to connect using like CONN SYS@DB_NAME AS SYSDBA which will require password to authenticate.

However, as the sqlnet.ora file is owned by ‘oracle’, anyone who logs in with the ‘oracle’ OS user can change the sqlnet.ora, So you need to consider changing the owner of sqlnet.ora to another user (for example: root) and set sqlnet.ora to be read-only for oracle.

[oracle@testdb ~]$ cd $ORACLE_HOME/network/admin

[oracle@testdb admin]$ ll sqlnet.ora
-rw-r-----. 1 oracle oinstall 227 Nov 5 03:25 sqlnet.ora

[oracle@testdb admin]$ exit
logout

[root@testdb ~]# cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

[root@testdb admin]# ll sqlnet.ora
-rw-r-----. 1 oracle oinstall 227 Nov 5 03:25 sqlnet.ora

[root@testdb admin]# chown root:root sqlnet.ora

[root@testdb admin]# chmod 744 sqlnet.ora

[root@testdb admin]# ll sqlnet.ora
-rwxr--r--. 1 root root 227 Nov 5 03:25 sqlnet.ora

Verify

[oracle@shoptest ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 5 07:28:53 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Therefore you need to provide the “sys” password

[oracle@shoptest admin]$ sqlplus sys/testdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 5 07:29:48 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"

Hence implemented, Now the sys / as sysdba cannot login without the “sys” password provided.