Shared memory connection with godror
Sven Illert -
Recently I was playing around with the go programming language by google. Although I am not a fan of this company anymore for various reasons, the programming language is fun to work with. Especialy when it comes to development of CLI utilities it has quite some advantages and combines the ease of use like perl and the feature of producing a compiled binary like from C. Also I am a big fan of strictly typed languages.
So I developed a little utility that extracts a lot of information from a system which has Oracle software installed and databases running. To get information out of these databases there are several ways to extract them. Of course you can just invoke SQL*Plus or SQLcl and parse the output. But that has no good error handling and is more complex than needed. The better way to do this is to use some native libary that provides a driver for the language specific SQL interface.
After some search I found godror which nearly provides what I needed. It is a native library that uses the Oracle Call Interface (the other OCI besides the Cloud Infrastructure) of an installed client. But there’s a problem. My little tool needs to connect to the local database via shared memory like you would do with the sqlplus / as sysdba
command and the documentation states the following:
The connectString can be ANYTHING that SQL*Plus or Oracle Call Interface (OCI) accepts: a service name, an Easy Connect string like host:port/service_name, or a connect descriptor like (DESCRIPTION=…).
This is not enirely true! You can specify that you want to connect with SYSDBA privileges, but the connection string cannot be the slash /
only. So now what?
There’s something called the bequeath protocol which is used to communicate to the Oracle database server without using a listener via the above mentioned shortcut with SQL*Plus. The commonly unknown thing is, that this shortcut is translated to a connection string which differs a little bit from what you may know. Let me show you an example:
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=beq)
(PROGRAM=/u01/app/oracle/product/19c/dbhome_1/bin/oracle)
(ARGV0=oracleMYSID)
(ENVS='ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1,ORACLE_SID=MYSID,LD_LIBRARY_PATH=,ORACLE_BASE=')
(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')
(CONNECT_DATA=(SID=MYSID)))
)
This connection string tells the client how to spawn a server process that has access to the database. In fact, it does exactly what the listener normaly does when you connect via TCP(S). But since you are already authenticated at OS level and hopefully in the right group with dba privileges, there’s no further authentication needed. So for my little tool to connect locally via shared memory I did the following:
var connectParams godror.ConnectionParams
connectParams.Username = "SYS"
connectParams.Password = godror.NewPassword("SYS")
connectParams.IsSysDBA = true
connectParams.ConnectString = "(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=" + oracleHome + "/bin/oracle)(ARGV0=oracle" + oracleSID + ")(ENVS='ORACLE_HOME=" + oracleHome + ",ORACLE_SID=" + oracleSID + ",LD_LIBRARY_PATH=,ORACLE_BASE=')(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(CONNECT_DATA=(SID=" + oracleSID + "))))"
db := sql.OpenDB(godror.NewConnector(connectParams))
And that’s it. Now you can query the Database without the need to connect with username and password via SQL*Net