Where to find ojdbc17_g.jar

I haven’t done much JDBC tracing yet, but recently for a Kerberos KCM + ANO case I wanted more traces. In the old doc, on google and myoraclesupport, you’ll often see about the _g suffix. Like using ojdbc8_g.jar and -Doracle.jdbc.Trace=true and -Doracle.jdbc.diagnostic.enableLogging=true.

But chance are, you use a recent jar and find no _g one.

Here the explanation: You don’t need it any more! You can find more info about Diagnosability in JDBC in the 26ai doc.

There are two main differences.

  • First one, while you can activate it with -D or a config file, it is dynamic parameter that can be activate with a mbean and invoke(“enableLogging”).

From the doc:

Object loader = oracle.jdbc.driver.OracleDriver.class.getClassLoader();
String loaderName = (loader == null ? "nullLoader" : loader.getClass().getName());
String name = loaderName + "@" + Integer.toHexString( (loader == null ? 0 : loader.hashCode()));
System.out.println("name = " + name);
javax.management.ObjectName diagnosticMBeanObjectName = new javax.management.ObjectName("com.oracle.jdbc:type=diagnosability,name=" + name);
javax.management.MBeanServer mbs = java.lang.management.ManagementFactory.getPlatformMBeanServer();
System.out.println("LoggingEnabled = " + mbs.getAttribute(diagnosticMBeanObjectName, "LoggingEnabled"));
mbs.invoke(diagnosticMBeanObjectName, "enableLogging", null, null);
  • Second one, you won’t see any sensitive info. If you need to trace security packets, there are two important properties to permit and enable sensitive diagnostics
java -Doracle.jdbc.diagnostic.enableLogging=true -Djava.util.logging.config.file=logging.config -Doracle.jdbc.diagnostic.permitSensitiveDiagnostics=true -Doracle.jdbc.diagnostic.enableSensitiveDiagnostics=true -cp ojdbc17.jar:. MyClass

# logging.config
oracle.jdbc.level=ALL
oracle.jdbc.Trace=true
oracle.net.ns.level=255
oracle.net.ano.level=255
handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=ALL
java.util.logging.ConsoleHandler.formatter=oracle.jdbc.diagnostics.OracleSimpleFormatter

Jun 02, 2026 4:00:21 PM oracle.net.ano.CryptoNIONSDataChannel decryptAndChecksum
FINEST: U:thread-1 main CONNECTION_ID=Olmo0KoJTF+3MIwBDXwIzQ==,TENANT=MYDB,SQL=select 'OK!' txt from dual,Packet size after decryption 169. Packet Dump :
04 01 01 01 03 00 02 2F |......./|
ED 00 00 00 00 00 00 00 |........|
00 00 00 00 00 00 00 00 |........|
00 00 01 00 00 00 00 00 |........|
00 02 2F ED 00 43 4F 52 |../..COR|
41 2D 31 32 32 36 39 3A |A-12269:|
20 63 6C 69 65 6E 74 20 |.client.|
75 73 65 73 20 77 65 61 |uses.wea|
6B 20 65 6E 63 72 79 70 |k.encryp|
74 69 6F 6E 2F 63 72 79 |tion/cry|
70 74 6F 2D 63 68 65 63 |pto-chec|
6B 73 75 6D 6D 69 6E 67 |ksumming|
20 76 65 72 73 69 6F 6E |.version|
0A A1 B6 98 B5 88 1A BA |␤.......|

Now you should see all network traces without an ojdbc8_g.jar or ojdbc17_g.jar

tnsping and instant client revisited

You enjoyed my old trick with https://laurentschneider.com/wordpress/2012/11/tnsping-and-instant-client.html ? you will love this one ❤️

sqlplus -p db01
Local Net Naming configuration file: /usr/lib/oracle/23/client64/lib/network/admin/tnsnames.ora
Attempting to contact: (description=(address_list=(address=(protocol=TCP)(host=srv01.example.com)(port=1521)))(connect_data=(service_name=db01.example.com)))
Ok (1.143 msec)

Remark: I first posted ping from within sqlplus, but -p is obviously nice. Thanks Beat Ramseier | LinkedIn   via Post | LinkedIn

Return code before grep part 3 and the pipefail hack

whenever you do

mycmd | grep mytext

the return code of command got lost, $? point to the return code of grep.  Back in time I posted https://laurentschneider.com/wordpress/2009/05/return-code-before-grep.html

( 
  ( 
    (
      mycmd
      echo $? >&3
    ) |grep mytext >&4 
  ) 3>&1 |(read x;exit $x) 
)4>&1

this did work like a charm for decades.

But earlier this year I found this sexy syntax

mycmd > >(grep mytext)

While it may abuse your memory, especially on legacy/ancient operating systems, it is more understandable. A little bit. Maybe. YMMV.

Last week I discovered also, while ansiblelinting my code

set -o pipefail
mycmd | grep mytext

It looks better but it is a disgusting hack. I tested a bit and very quickly decided to add

skip_list:
- 'risky-shell-pipe'

in my .ansible-lint file.

pipefail has an annoying handling of SIGPIPE.

# ( set -o pipefail; head -c 95698 /tmp/xxx | grep -q .; echo $? )
141
# ( set -o pipefail; head -c 95698 /tmp/xxx | grep -q .; echo $? )
0
# ( set -o pipefail; head -c 95698 /tmp/xxx | grep -q .; echo $? )
0
# ( set -o pipefail; head -c 95698 /tmp/xxx | grep -q .; echo $? )
0

Random absolutely unusable stuff… avoid!

SIGPIPE is documented as :

On a last note, pipes must have a reader and a writer. If a process tries to write to a pipe that has no reader, it will be sent the SIGPIPE signal from the kernel.

so when head stops writing, grep may no longer read and the pipe may get a death signal. But it is not consistent.

To TLS or to NNE? Postquantum hints the former

When encrypting Network traffic to your database server, there are two different approaches.

One is to use Oracle Native Encryption by setting

SQLNET.ENCRYPTION_SERVER=requested

on the server sqlnet.ora and that’s about it. Very easy to setup and nothing to do on the client. Also it was a good choice for performance and security.

The other option is to setup SSL/TLS on the listener by setting

PROTOCOL=TCPS

On both the server listener.ora and the client tnsnames.ora. It is more complex and support authentication, server checks and multiple features.

I’ve write about this here:

listener with tcps

There has been a lot of hassle with it. Back in the time, when it was part of Oracle Networking Option or later called Advanced Security Option, it was too expensive for most customers, and pretty unused. There were some nasty security issue with REMOTE_LISTENER and RAC in 9i/10g so Oracle decided to waived that requirement around 2011 if I recall correctly.

But nothing was ready, not even Oracle Enterprise Manager. Old jdbc Drivers where just hanging, firewalls were confused, sqldeveloper was hanging when selecting clobs, Oracle Support wasn’t helpful, really, I did not like it.

In 23ai Oracle updated his documentation and now recommends TLS over NNE in Security Guide – 19. Securing data for Oracle connections.

Per default, SSL in Oracle 23ai now supports TLS1.3, which is Post Quantum Cryptography (PQC) ready. If you care about security, TLS adoption for your Oracle listener is recommended

sqlite3

I just tried sqlite. It is ultra-light and support SQL commands. Just try this

$ sqlite3 test.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> create table t(x number);
sqlite> insert into t values(1);
sqlite> select * from t;
1
sqlite>
$ du -h test.db
8.0K test.db

I just created a database, a table and a row, issued one select. Used 8K on my home directory.

[satire on]What else do you expect from a database?[satire off]

shell [ $x ]

Long long time ago, back in SunOS ages and shell Bourne, I saw this code

if [ $x ]; then ...

The author told me, I was a junior java developer, it checks if x exists.

I didn’t like the code. I just found out that “1 = 2” does not exist…

$ x='1 = 2'; if [ $x ]; then echo ok; else echo notok; fi
notok
$ x='foo'; if [ $x ]; then echo ok; else echo notok; fi
ok

This is also documented in some less-ancient manpages

test "$1"
test ! "$1"
could not be used reliably on some historical systems. Unexpected results would occur if such a string condition were used and $1 expanded to !, (, or a known unary primary. Better constructs are, respectively,

test -n "$1"
test -z "$1"

Never trust your senior administrator, just test test test …

And even if you test but the code is not robust, don’t do it, it may break one day

Changing 10g password in 19c

One user may complain about ORA-28008 when changing its old password in 19c.

Many users still use the old non case sensitive passwords. Resetting User Passwords That Use the 10G Password Version is a post-upgrade task of 19c.

Let’s try with the identified by values (as I wrote long time ago there alter-user-identified-by-values-in-11g )

SQL> create user u identified by values '18FE58AECB6217DB' /* password U */;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> select PASSWORD_VERSIONS from dba_users where username='U';
PASSWORD_VERSIONS
-----------------
10G
SQL> conn u/U
Connected.
SQL> alter user u identified by ************ replace U;
alter user u identified by ************ replace U
*
ERROR at line 1:
ORA-28008: invalid old password
SQL> password
Changing password for U
Old password: u
New password: ************
Retype new password: ************
Password changed
SQL> conn / as sysdba
Connected.
SQL> select PASSWORD_VERSIONS from dba_users where username='U';
PASSWORD_VERSIONS
-----------------
10G 11G 12C

You cannot use the REPLACE clause, but the sqlplus command PASSWORD still works.

PS: after having earnt a total less than 100 USD of advertissement for over 1000 USD of hosting in the last 20 years, I gave up with AdSense today. Enjoy AddFree

TIME_BUCKET group by time period

We all know how to sum up by year

select
  to_char(trunc(hiredate,'Y'),'YYYY') year,
  count(*)
from
  scott.emp
group by
  trunc(hiredate,'Y')
order by 
  trunc(hiredate,'Y')

YEAR COUNT(*)
---- --------
1980        1
1981       10
1982        1
1987        2
This is quite easy. Same for day (DD), century (CC), quartal (Q), hour (HH24), month (MM)
But how do you group by, let’s say, half-year? or fiscal year, half day, 8-hours period, etc… ?
One way is to convert to number…
select 
  replace(
    replace(
      to_char(
        trunc(
          months_between(
            hiredate, 
            date '1900-01-01'
          )/6
        )/2,
        '00.0'
      ),
    '.0','H1'
  ),'.5','H2') HY, 
  COUNT(*) 
from 
  scott.emp 
group by 
  trunc(months_between(hiredate, date '1900-01-01')/6) 
order by 
  trunc(months_between(hiredate, date '1900-01-01')/6);

HY         COUNT(*)
------- -----------
80H2              1
81H1              5
81H2              5
82H1              1
87H1              2

We could also add some offset to start in March and choose 180 days instead of 6 months etc… But it gets really hard to read (and write)

In Oracle 23.7, I could not test it yet, simply write …

select
  time_bucket(hiredate, interval '6' month, date '1900-01-01'),
  count(*)
from
  scott.emp
group by
  time_bucket(hiredate, interval '6' month, date '1900-01-01')

It is much nicer 🙂

sqlnet.ora and ldap.ora parameters in jdbc thin url

Using the latest instant client, the Easy Connect Plus improvement let you parametrise your jdbc url more intensively

 

jdbc:oracle:thin:@ldaps://ldap.example.com/db01?WALLET_LOCATION=/db00/mywallet&AUTHENTICATE_BIND=true&AUTHENTICATE_BIND_METHOD=none&DIRECTORY_SERVER_TYPE=OID

 

EasyConnect Plus appeared in 19c, but was further improved in 21c and 23ai.

See more

JDBC Developer’s Guide

https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/data-sources-and-URLs.html#GUID-44572C63-10D2-478A-BB2E-ACF6674C59CC

negative regexp

if you want to grep anything except foo, use grep -v or negative lookahead

echo -e 'foo\nbar\nboz'|grep -P '^(?!.*foo).*$'
bar
boz

it is just so beautiful !

e.g. get IP address except localhost in /etc/hosts

grep -Po '^(?!127.0.0.1)[0-9.]+' /etc/hosts
192.168.1.1
192.168.1.2

if you have no grep -P, use perl

Free blogging

Back in early 2005 I started this blog on Blogspot. Not being unserious about blogging, I bought this domain later in 2006 with paid hosting. I can recommend my GoDaddy provider for their support and attractive rates, but nowadays, I am no longer an active blogger. I’ll keep posting things occasionally on laurentschneider.wordpress.com. Saad posted the last comment back in February 2022 🙂

I thought I could maintain this site expenses covered by Google Ads. But this has been more annoying than profitable, it didn’t even cover the cost of the SSL certificate.

Anyway, see you on the free side of WordPress.

“So long, and thanks for all the fish”

XMLRoot desupport

XMLRoot has been deprecated since 12cR2 and desupported since 21c. Still, it is not really obvious to understand how to generate a prolog.

Also, you couldn’t specify the encoding.

Well, one could fake the version with double quote, but this is not recommended, there even was some dirty SQL injection in oldish versions.

select xmlroot(xmlelement(x),version '1.0" encoding="utf-8');
<?xml version="1.0" encoding="utf-8"?>
<X/>

But this is a hack,

XMLSERIALIZE is the recommended approach.

Once converted to CLOB, BLOB, Varchar2, the version can be specified, or default to 1.0.

select to_clob(xmlserialize(document xmlelement(x) version '1.0'));
<?xml version="1.0"?>
<X/>

The ‘1.0″ encoding “utf-8’ still works, but it is still a hack and not the way to go.
There is an encoding clause, but this is for binary content. If you want to generate a specific encoding in the prolog, convert it blob first and back to clob (do you really want to do that?).

select to_clob(xmlserialize(
  document xmlelement(foo) as blob 
  encoding 'us-ascii'));
<?xml version="1.0" encoding="US-ASCII"?>
<FOO/> 

select to_clob(xmlserialize(
  document xmlelement(foo) as blob 
  encoding 'utf-16be'),
  nls_charset_id('AL16UTF16'));
<?xml version="1.0" encoding="UTF-16BE"?>
<FOO/>

Also note, there is XML 1.0, XML 1.1, XML 2.0, but everybody uses 1.0. There is no work done on 2.0 and 1.1 is for very strange tags with special characters (things you cannot even type on your keyboard) .

Version is also not checked, at least in Oracle 23c.

select xmlserialize(document xmlelement("x") version '42');
<?xml version="42"?>
<x/>

SQL + DTD

How to select xml with DTD ?

  • Imagine this XML code
SQL> select xmltype(
'<!DOCTYPE FOO SYSTEM "/public/test/foo.dtd"><FOO/>', 
wellformed => 1) xml 
from dual;

XML
-----------------------------------------------------
<!DOCTYPE FOO SYSTEM "/public/test/foo.dtd"><FOO/>

Most validations are done with XSD and not DTD, so it is not that easy, even google probably lead you here 😉

  • Create the dtd in XDB
declare
b boolean;
begin 
b := dbms_xdb.createfolder('/public/test');
b := dbms_xdb.createresource(
'/public/test/foo.dtd',
'<!ELEMENT FOO (BAR*)>');
end;
/
  • Use DBMS_XMLDOM and CLOB
WITH
FUNCTION f(
doc xmltype, 
name varchar2 default null, 
sysid varchar2 default null, 
pubid varchar2 default null) 
RETURN xmltype IS
dom DBMS_XMLDOM.domdocument;
tmp CLOB;
BEGIN
dom := dbms_xmldom.NEWDOMDOCUMENT(doc);
DBMS_XMLDOM.SETDOCTYPE(dom,name,sysid,pubid);
DBMS_LOB.CREATETEMPORARY(tmp,true,DBMS_LOB.SESSION);
DBMS_XMLDOM.writeToClob(DBMS_XMLDOM.makeNode(dom), tmp);
RETURN XMLTYPE(tmp);
END;
SELECT f(xmlelement(foo),'FOO','/public/test/foo.dtd','') XML
/

XML
--------------------------------------------------------------------------------
<!DOCTYPE FOO SYSTEM "/public/test/foo.dtd">
<FOO/>
  • Cleanup
select * from resource_view;
exec dbms_xdb.DeleteResource('/public/test/foo.dtd');
exec dbms_xdb.DeleteResource('/public/test');

Largely inspired by arkzoyd

cp -r preserves links

Linux is not Unix, I keep finding out differences years after years.

I’ve been using cp -r for over 25 years to find out today, the good old cp -r is cp -Lr on Linux.

e.g. AIX

$ touch foo
$ ln -s foo bar
$ cp -r bar boz
$ ls -la boz
-rw-r--r-- boz

cp convert symlink bar to file boz.

Now I found out in Linux, cp acts like a tar

$ touch foo
$ ln -s foo bar
$ cp -r bar boz
$ ls -la boz
lrwxrwxrwx boz -> foo

This special effect of -r is not documented in the manpage.  The old man used to RTFM. More than once.

CP(1)   User Commands CP(1)
NAME
  cp - copy files and directories
DESCRIPTION
  -R, -r, --recursive
    copy directories recursively

Time passes, users no longer read MAN, they read INFO

11.1 ‘cp’: Copy files and directories
=====================================


 When copying from a symbolic link, ‘cp’ normally follows the link
only when not copying recursively or when ‘--link’ (‘-l’) is used. This
default can be overridden with the ‘--archive’ (‘-a’), ‘-d’,
‘--dereference’ (‘-L’), ‘--no-dereference’ (‘-P’), and ‘-H’ options. If
more than one of these options is specified, the last one silently
overrides the others.

Ok, so, for recursion, let’s dereference.

$ rm baz
$ cp -rL boz baz
$ ls -la baz
-rw-r----- baz

Thinks change. 18 years of blogging. I learnt something new today

DROP IF EXISTS / CREATE IF NOT EXISTS

DROP TABLE [IF EXISTS] table-name

Drop table if exists has been there for ages, in Oracle MySQL, Oracle NoSQL and others. Now we got this in Oracle Database 23c too.

First thing I do on my sandbox database is creating SCOTT tables. Now, for the first time, I can make my script 100% error free.

SQL> ed ?/rdbms/admin/utlsampl
conn / as sysdba
whenever sqlerror exit failure
alter session set container=freepdb1;
DROP USER IF EXISTS SCOTT CASCADE;
DROP USER IF EXISTS ADAMS CASCADE;
DROP USER IF EXISTS JONES CASCADE;
DROP USER IF EXISTS CLARK CASCADE;
DROP USER IF EXISTS BLAKE CASCADE;
create user scott;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT ;
DROP PUBLIC SYNONYM IF EXISTS PARTS;
alter session set current_schema=scott;
SQL> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
$ echo $?
0

23c is out

but only for developers. You can get it from oracle.com as a RH8 rpm : https://www.google.ch/search?q=download+oracle+23c

You need about 6Gb in /opt and there is a

/etc/init.d/oracle-free-23c configure -i

command that does create a database for you.

Some goodies :

select 1;

select true;

create table if not exists t(x number);

arg 1 def xxx

select '&1'

Some badies that’ll make you swear:

Process ora_pmon_SID is now db_pmon_SID

desupport mkstore and owm

desupport 8/9/10/11 client (SQLNET.ALLOWED_LOGON_VERSION_CLIENT)

desupport 32bit clients

Read the doc and follow this blog 🙂

grant select on sysman view

Quite often you select from the sysman schema, let’s say

select target_name from sysman.mgmt$target;

To do so, you better be sys.. because SYS can see everything !

Let’s see what’s missing with a standard user

connect  sys/***@oem01 as sysdba;
create user user1 identified by ***;
grant create session to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
SQL Error: ORA-00942: table or view does not exist
Fair enough, we don’t have select
connect  sys/***@oem01 as sysdba;
grant create session to user1;
grant select on sysman.mgmt$target to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
  COUNT(*)
----------
         0
This is more than enough to confuse the innocent ! the query returns 0 row! The witchcraft behind the scene is called virtual private database (VPD).
One lazy dba would grant you exempt access policy
connect  sys/***@oem01 as sysdba;
drop user user1 cascade;
create user user1 identified by ***;
grant create session, exempt access policy to user1;
conn user1/***@oem01;
select count(*) from sysman.mgmt$target;
  COUNT(*)
----------
        72
But this is hurting the need to know principle. I really want to give only select on that view.
Creating a view on the view doesn’t do the trick
connect  sys/***@oem01 as sysdba;
create or replace view sysman.v as 
  select * from sysman.mgmt$target;
grant create session to user1;
grant select on sysman.v to user1;
conn user1/***@oem01;
select count(*) from sysman.v ;
  COUNT(*)
----------
         0
Let’s try to add PL/SQL in the view :
connect  sys/***@oem01 as sysdba;
create or replace function sysman.f 
  return sysman.MGMT_TARGET_ARRAY 
is 
  r MGMT_TARGET_ARRAY; 
begin 
  select sysman.MGMT_TARGET_OBJ(TARGET_NAME,TARGET_TYPE) 
  bulk collect into r 
  from sysman.mgmt$target; 
  return r; 
end;
/
create or replace view sysman.v as select * from table(f);
grant select on sysman.v to user1;
conn user1/yF_5lOoxNp_g03Z#NMFM@oems1d;
select count(*) from sysman.v;
  COUNT(*)
----------
         0
Well, it doesn’t help yet, but now one more magic trick.
create or replace function sysman.f 
return sysman.MGMT_TARGET_ARRAY is 
  pragma autonomous_transaction;
  r MGMT_TARGET_ARRAY; 
begin 
  sysman.setEMUserContext(null,null,disable_vpd_policies=>1);
  select sysman.MGMT_TARGET_OBJ(TARGET_NAME,TARGET_TYPE) 
  bulk collect into r 
  from sysman.mgmt$target; 
  return r; 
end;
/
create or replace view sysman.v as select * from table(f);
grant select on sysman.v to user1;
conn user1/***@oem01;
select count(*) from sysman.v;
  COUNT(*)
----------
        72

ODP standalone

Without Administrative privilege, how would you install ODP.NET?

Note 784309.1 describes the required files if you have no Oracle client

  1. download XCOPY ODAC from Oracle .NET, Visual Studio, and VS Code ODAC Downloads for Oracle Database
  2. Copy the files Oracle.DataAccess.dll OraOps.dll orannzsbb.dll oraociei.dll oci.dll to your directory (ex: C:\ODAC)
  3. Add that directory to your PATH
  4. Create your sqlnet.ora and tnsnames.ora or use ezconnect string host:1521/service.example.com

That’s it. It even works with kerberos

sqlnet.ora

sqlnet.kerberos5_conf=C:\ODAC\krb5.conf
sqlnet.kerberos5_cc_name=OSMSFT://
sqlnet.authentication_services=kerberos5pre
sqlnet.kerberos5_conf_mit=true
NAMES.DIRECTORY_PATH = (TNSNAMES)
NAMES.DEFAULT_DOMAIN = example.com

tnsnames.ora

DB01.example.com=(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.com)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=DB01.example.com)))

krb5.conf

[libdefaults]
  default_realm = EXAMPLE.COM
[realms]
  EXAMPLE.COM = {
    kdc = dc1.example.com
}
[domain_realm]
.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM

let’s try

cd C:\ODAC
${ENV:PATH}="C:\ODAC"
${ENV:TNS_ADMIN}="C:\ODAC"
[Reflection.Assembly]::LoadFile('C:\ODAC\Oracle.DataAccess.dll')
$conn = New-Object Oracle.DataAccess.Client.OracleConnection(
  'User Id=/;Data Source=DB01')
$conn.open();
$com=$conn.CreateCommand();
$com.CommandText='select * from dual';
$com.ExecuteScalar();
$conn.close();
$conn.dispose()
X

PS: kerberos5pre is deprecated but kerberos5 doesn’t work with credential guard, there are a few unpublished bugs (33825536 and others)

R+ODBC

I just established a connection from R to Oracle / MSSQL using ODBC.

First, I installed R

sudo yum install R

Then, ODBC

sudo yum install unixODBC-devel freetds-libs

I set up my mirror in .Rprofile

options(repos=structure(c(CRAN="http://cran.example.com/")))

I install ODBC Library in R

R -s <<EOF
install.packages("odbc")
EOF

I configure my odbc driver to Oracle Instant Client 21c in ~/.odbcinst.ini

[OracleODBC]
Description = Oracle ODBC driver
Driver = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1

I configure my database in ~/.odbc.ini

[DB01]
Driver = OracleODBC
ServerName = DB01

Quick check (info: here I use Kerberos without password)

$ isql DB01
Connected!
SQL> quit()

Let’s do it R

library("odbc")
odbc_con <- dbConnect(odbc(), dsn="DB01")
dbGetQuery(odbc_con, 'select sysdate from dual');
     SYSDATE
   1 2022-11-25 16:08:03
dbDisconnect(odbc_con)

Done 🙂

Recommend lecture for SQL Server : R package: odbc (renenyffenegger.ch)

connect to mssql with python/linux

done for a friend :

  1. install rpm’s 
    sudo yum install -y freetds unixODBC unixODBC-devel freetds-libs python3-pyodbc
  2. create odbc.ini and odbcinst.ini
    1. for sybase go there Unix ODBC Sybase
    2. for oracle go there Unix ODBC Oracle
    3. for mssql
      1. ~/.odbc.ini : the Database definition 
        [DB01]
Driver = FreeTDS
Description = DB01
Server = src01
Port = 1433
Database = DB01
      2. ~/.odbcinst.ini : the driver definition 
        [FreeTDS]
Description = Free Sybase & MS SQL Driver
Driver64 = /usr/lib64/libtdsodbc.so.0
Setup64 = /usr/lib64/libtdsS.so.2
Port = 1433
  3. test with isql 
    isql -v DB01 myuser mypw
  4. test with python3 
    import pyodbc

conn = pyodbc.connect('DSN=DB01;UID=myuser;PWD=mypw')

cursor = conn.cursor()

cursor.execute('select \'hello world\'')

print (cursor.fetchone()[0])
    hello world

HTH

generate Excel from scott.emp

Ages ago I explained how to generate excel from the Unix command line. For instance with HTML or with XML+ZIP
To do this in Windows is way more elegant. You get your dataset from Oracle or any other source.
Then you create an Excel Object, you add content, saves, that’s all folk!
# 1) get a dataset from Oracle 
$oracle_home = (  
  gci -recurse HKLM:\SOFTWARE\Oracle  |     
    Where-Object -Property Property -eq ORACLE_HOME |          
      Where-Object -Property PSChildName -Match KEY |           
        get-itemproperty -name ORACLE_HOME  ).oracle_home;
'[INFO] '+$oracle_home;
Add-Type -Path ($oracle_home+'\ODP.NET\bin\4\Oracle.DataAccess.dll');
$conn = New-Object Oracle.DataAccess.Client.OracleConnection('User Id=/;Password=;Data Source=DB01');
$conn.open();
$dataset = New-Object Data.dataset
(New-Object Oracle.DataAccess.Client.OracleDataAdapter("select * from emp",$conn)).fill($dataset)
$conn.close();
$conn.dispose();
# 2) get a ComObject from Excel
$excel = New-Object -ComObject excel.application
$excel.visible = $False
$wb = $excel.Workbooks.Add()
$ws= $wb.Worksheets.Item(1)
$ws.Name = "Data Set"
$row=1
$col=1
foreach ($colname in $dataset.Tables[0].Columns.ColumnName){
  $ws.Cells.Item($row,$col).Font.Bold=$True
  $ws.Cells.Item($row,$col) = $colname 
  $col++
}
foreach ($datarow in $dataset.Tables[0].rows) {
  $row++
  $col=1
  foreach ($item in  $datarow.itemarray){
    $ws.Cells.Item($row,$col) = $item
    $col++
  }
}
$excel.DisplayAlerts = 'False' 
$wb.SaveAs("C:\TEMP\ora.xlsx")  
$wb.Close()
$excel.Quit()
EMPNO ENAME JOB DEPTNO
7788 SCOTT ANALYST 20
7839 KING PRESIDENT 10

proxy user revisited

A new 10.2 feature I mentioned in su in sqlplus – Laurent Schneider is the ability to use a proxy user in a connection string.

This was documented in What’s New in Oracle Call Interface?

Proxy access for a single client can be set using various connect strings.

Today I got one user complaining about ORA-01045: user USER lacks CREATE SESSION

Proxy was introduced in Oracle 8 (OCI only) and enhanced in numerous versions.

In JDBC, we used openProxySession as defined in  OracleConnection (Oracle Database JDBC Java API Reference)

In C, we used OCILogon2 and OCIConnectionPoolCreate with an empty password for the USER2 as documented in Connect, Authorize, and Initialize Functions (oracle.com)

In both case, the CREATE SESSION was required.

For the C code, check for cdemocpproxy.c in the demo directory or on google and compile it with

cc -lclntsh -I$ORACLE_HOME/rdbms/public -L$ORACLE_HOME/lib cdemocpproxy.c -o cdemocpproxy && ./cdemocpproxy

For Java, here is a sample code

import java.util.Properties;

import java.sql.*;

import oracle.jdbc.OracleConnection;




public class Proxy2 {

  public static void main(String argv[]) throws SQLException {

    String url = "jdbc:oracle:thin:@srv01:1521/DB01.example.com";

    Properties props = new Properties();

    props.setProperty("user", "user1");

    props.setProperty("password", "Secret_Passw0rd");

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    Connection conn = DriverManager.getConnection(url, props);

    OracleConnection oc = (OracleConnection)conn;

    props.setProperty(OracleConnection.PROXY_USER_NAME, "user2");

    oc.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);

    ResultSet res = conn.

      prepareCall("select user from dual").

    executeQuery();

    res.next();

    System.out.println(res.getString("USER"));

  }

}
And the preparation steps
create user USER1 identified by Secret_Passw0rd;

create user USER2 identified by Secret_Pr0xYYY;

grant create session to user2;

alter user user2 grant connect through user1;

In multiple examples found on the Internet including OracleBase, AskTom, MyOracleSupport, the user1 receives CREATE SESSION.

Without CREATE SESSION, both my C and Java examples fail with :

Error - ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied

However, if you don’t use openProxySession but simply use USER1[USER2] as a username, it succeed

As it does in SQLPLUS

SQL> connect USER1[USER2]/Secret_Passw0rd@DB01
Connected.

Or also in java :

import java.util.Properties;

import java.sql.*;


public class Proxy1 {

  public static void main(String argv[]) throws SQLException {

    String url = "jdbc:oracle:thin:@srv01:1521/DB01.example.com";

    Properties props = new Properties();

    props.setProperty("user", "user1[user2]");

    props.setProperty("password", "Secret_Passw0rd");

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    Connection conn = DriverManager.getConnection(url, props);

    ResultSet res = conn.

      prepareCall("select user from dual").

    executeQuery();

    res.next();

    System.out.println(res.getString("USER"));

  }

}

Or in C

OCIConnectionPoolCreate(... "USER1[USER2]", "Secret_Passw0rd" ...)

This is a major behavior change that I blogged 16 years ago and realized today. I am glad I blogged all my memories 🙂

ODBC/OLEDB/ODP and Kerberos

Precondition : sqlplus can connect to the database server using a kerberos ticket.

I wrote The long long route to Kerberos – Laurent Schneider in 2014, but most of the bugs in 11.2.0.2 / 12.1.0.2 are no longer interesting, so probably you should be fine to just read Configuring Kerberos Authentication (oracle.com)

let’s see

sqlplus /@db01

SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 20 13:33:00 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Last Successful login time: Fri May 20 2022 12:50:44 +02:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL>

Okay, to use ODBC, you just omit username and password. Either in Excel or in Powershell.

$ORACLE_HOME_NAME = (Get-ItemPropertyValue HKLM:\SOFTWARE\Oracle\KEY_* -name ORACLE_HOME_NAME)
Add-OdbcDsn -name "DB01" -DriverName ("Oracle in "+$ORACLE_HOME_NAME) -SetPropertyValue @("Server=DB01") -dsntype user
$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=DB01"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual",$conn)).ExecuteScalar()
$conn.close()
Remove-OdbcDsn -name "DB01" -DsnType user

Just don’t specify User Id and Password

To use ODP.NET, it is almost the same, but there is a dirty trick

$ORACLE_HOME = (Get-ItemPropertyValue HKLM:\SOFTWARE\Oracle\KEY_* -name ORACLE_HOME)
Add-Type -Path $ORACLE_HOME\ODP.NET\bin\4\Oracle.DataAccess.dll
$conn = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=/;Password=;Data Source=DB01")
$conn.open()
$com=$conn.CreateCommand()
$com.CommandText="select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual"
$com.ExecuteScalar()
$conn.close()

Here, userid is not empty, but slash. What a joke ! One tool use “” and the other “/”

Ancient Oracle versions use 2.x instead of 4.

What’s more, if you use the Managed Driver, you’ll need the latest 21.6.1 version to allow Kerberos (not tested) New Oracle Features for .NET by Release

The same SLASH Convention also applies to OLEDB. You can either create an empty test.udl file on your desktop to test or use the following code

$conn = New-Object System.Data.OleDb.OleDbConnection ("Provider=OraOLEDB.Oracle;Data Source=DB01;User ID=/;Password=;")
$conn.Open()
$cmd=$conn.CreateCommand()
$cmd.CommandText= "select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual"
$cmd.ExecuteScalar()
$conn.close()

This concludes my ODBC/ODP/OLEDB topic

Log4J and OEM Part II

After part 1, where Oracle wrote in an official document there is no impact for your targets, Critical Patch Advisory April 2022 reveals multiple vulnerabilities, one of them is Log4J severity 9.8/10.

9.8 means it is kindergarden easy to compromise confidentiality, integrity and availability even without a login.

In the meanwhile, per 30. April, Oracle released 13.5 ru-6.

Don’t be fool, it is unsafe to assume the EM-Ru contains the EM-Cpu.

Yes, you have to apply the RU-5 or RU-6 to your OEM.  Your repository database version must be certified too.

But also, even if you don’t use WLS, Coherence, FMW, FMW 3rd party, ADR, OWMS, OHS, OSS, OPSS, ADF, WebCenter, JDBC, OVD, JDK, SQL Developer, you have to patch each of those components in your OMS home with one or more separate patches.

Just to summarize,  omspatcher applies 15 patches automatically, and later have to manually apply a dozen of one-off patches. Oracle Support told me:

As much as possible, Oracle tries to make Critical Patch Updates cumulative … Fixes for the other products that do not receive cumulative fixes are released as one-off patches.

Okay, once you are done, you can apply the RU-6 to your agents.

Here again, there are additional one-offs. For the central agent, patch the WLS. For all agents, apply the one-off for CVE-2022-21392.

I didn’t know this before researching for log4j issues. I won’t provide you the step here, because they may change over time. Just read the latest CPU very carefully.

Critical Patch Updates, Security Alerts and Bulletins (oracle.com)

 

 

jdbc url with ldap ssl in 21c

the basic :

you have tns resolution over ldap. To change from ldap to ldaps, you modify :

jdbc:oracle:thin:@ldap://ldap.example.com:389/DB01,cn=OracleContext,dc=example,dc=com

to

jdbc:oracle:thin:@ldaps://ldap.example.com:636/DB01,cn=OracleContext,dc=example,dc=com

now the advanced :

Oracle Support mentions, there is no support for Mode 2 SSL (note 1664857.1)

So one approach was to either not use ldaps, or to use level 1, which means no Server authentication. But both are annoying for your directory server administrators.

There is a way to use Mode 2 with a fresh driver.

TestConnect.java

import java.util.Properties;
import java.sql.*;
public class TestConnect {
  public static void main(String argv[]) throws SQLException {
    String url = "jdbc:oracle:thin:@ldaps://ldap.example.com:636/DB01,cn=OracleContext,dc=example,dc=com";
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection conn = DriverManager.getConnection(url, props);
    ResultSet res = conn.
      prepareCall("select 'Hello SSL World' txt from dual").
    executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}
javac TestConnect.java
java -cp ojdbc8.jar:oraclepki.jar:. -Doracle.net.ldap.ssl.walletLocation=cwallet.sso TestConnect
Hello SSL World

OracleDriver (Oracle Database JDBC Java API Reference) documents five oracle.net.ldap parameters. It seems to be a 21c new feature.

Property Name
oracle.net.ldap.ssl.walletLocation
oracle.net.ldap.ssl.walletPassword
oracle.net.ldap.security.authentication
oracle.net.ldap.security.principal
oracle.net.ldap.security.credentials

This is quite nice ! Just get the ojdbc8.jar from the 21c instant client and start testing 🙂