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 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. But 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 setup 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, lets 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()

Running the code:

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

We now have a working solution 😊