Bypassing the Listener and Connecting to ASM Without a Password from Python Code

Requirement

I was asked to have smart code that can connect to a local ASM instance without the need for any prior preparations like setting up the listener or making any other changes in the database—and even do this without a password!

Problems

Listener
If there is already a listener that uses the default port (i.e., 1521), the ASM can register itself dynamically to the listener with no need to configure anything; however, on systems that use ports other than 1521, the listener is not registered automatically. We could set the parameter LOCAL_LISTENER like this:
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.40)(PORT=1521))';
But, I was asked to do no prior configuration.

Password

Using SQL*Plus, we have the option to connect to the database locally using password file authentication with no need to set up a listener and without using a password in the connection string.
$ export ORACLE_SID=+ASM $ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 5 20:19:06 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SYS@+ASM:SQL
But, I was asked to connect from code, not SQL*Plus.

The Solution

We can accomplish this by using the Bequeath protocol.
From Oracle documentation:

The Bequeath technique enables clients to connect to a database without using the network listener.

Is used for local connections where an Oracle Database client application … communicates with an Oracle Database instance running on the same computer.

The restrictions are:

  • Listener
    • The code will run from the database server.
    • The ASM is not configured as Flex ASM.
  • Password
    • The code will run from the same user ID as the DBA user ID.

We will start testing this protocol by connecting from SQL*Plus as a test case, and then moving to a Python code example.

Using the grid infrastructure owner (usually oracle or grid), we will add the following line to the grid home tnsnames.ora file:

ASM_BEQ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL=BEQ)
(PROGRAM=oracle)
(ARGV0=oracle+ASM)
(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')
)
)
(CONNECT_DATA=
(SID=+ASM)
)
)

Connect using sqlplus:

$ sqlplus /@ASM_BEQ as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 5 19:43:42 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SYS@ASM_BEQ:SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS,INSTANCE_ROLE,INSTANCE_MODE,ACTIVE_STATE from v$instance;

INSTANCE DATABASE INSTANCE INSTANCE ACTIVE
NAME VERSION STATUS ROLE MODE STATE
——– ———- —————– —————— ———– ———
+ASM 12.2.0.1.0 ACTIVE UNKNOWN REGULAR NORMAL

Now, let’s create a small connection test script in Python:

$ cat test_connection.py
#!/usr/bin/python

import os
import cx_Oracle

db = ‘+ASM’
oracle_home = ‘/oracle/product/12.2.0.1/grid’
os.environ[‘ORACLE_SID’] = db
os.environ[‘ORACLE_HOME’] = oracle_home
os.environ[‘PATH’] = oracle_home + ‘/bin’
print os.environ[‘ORACLE_SID’] dsn = ‘/@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=BEQ) (PROGRAM=oracle) (ARGV0=oracle’ + db + “) (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))’))) (CONNECT_DATA=(SID=” + db + ‘)))’
con = cx_Oracle.connect(dsn, mode=cx_Oracle.SYSDBA)
cur = con.cursor()
cur.execute(‘select INSTANCE_NAME,VERSION,DATABASE_STATUS,INSTANCE_ROLE,INSTANCE_MODE,ACTIVE_STATE from v$instance’)

for result in cur:
print result
cur.close()
con.close()

Run the code:

$ ./test_connection.py
+ASM
('+ASM', '12.2.0.1.0', 'ACTIVE', 'UNKNOWN', 'REGULAR', 'NORMAL')

We now have a working solution. 😊