default listener port

Long time ago, Maxime Yuen registered 1521 for nCube License Manager.

By googling I found : Ellison cleans house at nCube, and since them 1521 has been used as a default port for Oracle. Still, you’ll see nCube in IANA.ORG service names port numbers and in /etc/services the nCube name. I don’t know which one came first, Oracle using 1521 or Larry investing in nCube, but I am pretty sure it’s related πŸ˜‰

$ curl
  -port-numbers.txt | grep 1521
ncube-lm 1521 tcp nCube License Manager 
  [Maxine_Yuen] [Maxine_Yuen]
ncube-lm 1521 udp nCube License Manager 
  [Maxine_Yuen] [Maxine_Yuen]
$ grep -w 1521 /etc/services
ncube-lm 1521/tcp # nCube License Manager
ncube-lm 1521/udp # nCube License Manager
$ netstat -a | grep ncube
tcp 0 0 *.ncube-lm *.* LISTEN

Later, still long time ago, Oracle officially registered 2483 and 2484 (tcps) for the listener communication, as documented on Recommended Port Numbers :
This port number may change to the officially registered port number of 2483 for TCP/IP and 2484 for TCP/IP with SSL.

Still, as of Oracle 12c Release 2, port 1521 is recommended.

Now, another question : do you really want to use port 1521?

On the one hand, it could be good for a hacker to know listener runs on 1521 and ssh on port 22. This is configurable of course.

On the other hand, you better use that is assigned to Oracle. RFC 6335 defines 1024-49151 as User Ports, and 49152-65535 as the Dynamic and/or Private
Ports (aka ephemeral). Remember, if a port is used before you start your listener, your listener won’t start.

Remember every network connection keeps a port busy. So if you start a network client from your database server to another server, ssh, sqlnet, mail, whatever, dns, then your port 1028 or 57313 may be busy for a client connection. Which will prevent your listener from starting. If you use port 9999, you could look on IANA and ask the owner if he plans anything on that port.

Very often, most ports are unused when you start the listener. If you find an unused port in the private range, 49152-65535, you may name it in /etc/services.

Very often I see database servers with more than one listener. Obviously, you cannot run more than one listener on port 1521. There are some case where you want different listener with different sqlnet.ora or different Oracle version. But this render consolidation (e.g. Multitenant) more painful.

The discussion on which port to use is obviously far beyond Oracle. There are gazillions of TCP/UDP servers running in the digital world and less than 65535 ports. For sure you cannot have all them on IANA.ORG, right?

In most cases, stick to Oracle recommendation, use port 1521.

Legacy users get ORA-01017 in 12.2

The default case insensitive string disappeared in 12cR2, let’s call it the 10G string in this post, but it was the same since Oracle 7 at least. It was introduced in V5 or V6 to replace clear-text passwords.

What’s happening then with my ultra-old-accounts?

You could well set a new password (or the same password again) to each account to be migrated in 11g/12cR1 before moving to 12cR2.

If nobody knows the password and nobody can change it because it is hardcoded in the application and neither easy to read (hidden / obfuscated /encrypted) nor to change, then, you are in TROUBLE ! This is documented in Note 2075401.1

First disclaimer : it is a good thing to achieve a better security. SHA1 and SHA2 are a lot better than the oldstyle-longly-hacked-unsalted-case-insensitive-homemade-algorythm. SHA3 has been published in 2015 and it not used in Oracle 12cR2 yet. SHA2 is a bit older (2001) but still recommended. SHA1 is oldish (1995) and no-longer-recommended, collision has been detected. Read more on wikipedia or crypto101

SHA-1 was a really huge improvement when introduced in 11gR1. The old self-made algorythm has been a torture for Oracle Security team. It has been published on Internet. Extremly powerfull password cracker can find your “not-too-long” password in notime. In 11g, Oracle removed the 10g String from the DBA_USERS view. I wrote about this here. It remained on the base table, USER$ until 12cR2. Now Oracle completly removed it by default in 12cR2. 10 years after SHA1 was introduced in 11gR1.

Still. You are the dba. You want to migrate your database not to chase passwords.

You could edit your sqlnet.ora to allow 10g strings.


This works

SQL> sho parameter sec_case_sensitive_logon
NAME                      VALUE
------------------------- -----
sec_case_sensitive_logon  FALSE

     VALUES 'DC6F2B33D359A95B';
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> conn u/abcdefg@pdb01
SQL> conn u/AbCdEfG@pdb01

If you have SQLNET.ALLOWED_LOGON_VERSION_SERVER=11, then you could keep the same setting of sec_case_sensitive_logon as in 11g. I recommmend the default (true).

But, that’s it ? Wellllllll… not sure.

In 12.1

SQL> select dbms_metadata.get_ddl('USER','U') from dual
     VALUES 'DC6F2B33D359A95B'

Let’s try in 12.2

SQL> select dbms_metadata.get_ddl('USER','U') from dual

While this is a perfectly working syntax, and IDENTIFIED BY VALUES is not supported *. So if create that user, then, obviously, the 10G string is lost.

Well, unsupported feature then? Hmm, yes. You should never have used identified by values.

Ok, so if I refresh my Test database with Production data, how can I save test passwords? You can’t. At least not in a supported way by using identified by values.

You could something like :

SQL> select 'alter user "'||name||
       '" identified by values '''||
       password||''';' txt
     from user$, v$instance 
     where version > '12.2' 
     and spare4 is null  
     and type# = 1
     and regexp_like(PASSWORD,'[A-F0-9]{16}');

alter user "U" identified by values

This may work. In Maybe not in 13. Maybe not in July. It’s not supported. If it does not work, it is NOT-A-BUG.

The SHA1 was introduced 10 years ago in Oracle 11gR1. If you have not changed your password in ten years, and you don’t know how many employees and ex-employees know this password, and it is case-insensitive, and its “pseudo-hashing-algorythm” has been hacked for maybe two decades, yet, I can only warmly recommend to change those accounts passwords !

Again: so if I refresh my Test database with Production data, how can I save test passwords ?
If I were you I would design a better system for login. For human users, use global users and an Identity solution, like Oracle Universal directory. For technical account, build yourself a tool that generate a random password, and update the user and credentials, something like

) ,'[^!#-~]'
) PW
from dual;

And use it to reset your technical user and to configure your application credentials.

* Note 554605.1: the 'IDENTIFIED BY VALUES' clause on a CREATE/ALTER USER statement is not officially documented, and is intended purely for internal

Monitor audit_file_dest !

Until 11.2, audit_file_dest used to remain small with default settings and reasonably sized and active database. Suddenly, in 12c, you will sooned or later get ORA-09925: Unable to create audit trail file.

At that point, no more connection is possible to the database, it is a complete loss of service.

Why suddenly in 12c ? This is because the default for audit_sys_operations changed to true. In 11g, you used to get an 1K file each time you connect as sysdba. So a few tousands sysdba connections a weeks, a few mega, no worries.

Mon Mar 27 14:08:01 2017 +02:00
LENGTH : '155'
CLIENT USER:[6] 'oracle'
STATUS:[1] '0'

Suddenly in 12c, you get plenty files that are many Mb. For instance for AUTOTASK jobs, every single select is dumped to the filesystem. A single week-end of an quiet database may generate 1Gb of *.aud files of DBMS_SCHEDULER.

Those DB001_j000_12345_20170327140802123456789.aud files are highly useless and annoying.

LENGTH : '641'
ACTION :[490] 'select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad  */ substrb(dump("PERIOD_END_TIME",16,0
,64),1,240) val,
                      rowidtochar(rowid) rwid from "SYS"."WRP$_REPORTS_TIME_BANDS" t where rowid in (chartorowid('AABJ58AADAAAMsrAAA'),chartorowid('AABJ58AADAAAMsrAAB'),chartorowid('AABJ58AADAAAMsrAAC'),chartorowid('AABJ58A
STATUS:[1] '0'

Once your audit_file_dest is getting full, your database stops, so better delete those *_j00*_* and *_m00*_* quickly enough!

generate safe passwords

This is probably ambitious and I’ll start with a disclaimer, there is no such thing.

But ok, we know that system/manager isn’t

Apart from system/manager, there are hundreds of weak passwords that are commonly used and easy to guess.

On your database server, after a few tries, the account is lock. And maybe the attacker is detected by then.

So the worst passwords are the default passwords and passwords like oracle.

To enforce good passwords, we have verify functions, like ora12c_strong_verify_function in 12c, that checks for mixed case, special characters, etc. One may prefer to write his own and not disclose what it exactly checks.

In that function in rdbms admin, it states The maximum length of any DB User password is 128 bytes. but it’s 30 character in most cases.

If you have failed login attends of 10, chosing eleven as a password does not make it safe. If the attacker got’s the user metadata, you are screwed in no time. In Oracle 4, it’s clear text. In 7-10, it’s a doubled-DES unsalted with a fixed disclosed key encryption. There any dictionary attack takes milliseconds, and a 6 character password in sub-second. It’s got better in 11, where SHA1 could take weeks to years to have a 8 char password. Depending on its complexity. In 12c, generating a hash cost lot’s of cpu cycle, so it is no longer possible to test millions of password per second, even with the strongest hardware.

But to get a good password it is recommended and often required to use digit / letters / special signs / mixed case and no dictionary word.

I have made a small password generator for my reader using dbms_random.string, which generates pseudorandom string. It is best to use the cryptographically secure dbms_crypto.randombytes, but then you must still get a password that you can type. It should also be possible to use unicode if you like. And depending where you are going to use it, it is sometimes safer to not use signs like * or ‘ because, who know’s, your password may produce an error and end up in a logfile.

Okay, I wrote a small function that generates a 10-char string and verify it with the 12c strong verifier. And loop until one is good enough.

The chance that a random password is manager is pretty low, but it is probably best to check you got not only safe random, but also strong string

-- @?/rdbms/admin/catpvf
                               old_password    VARCHAR2 DEFAULT NULL)
   p   VARCHAR2 (30);
   c   BOOLEAN := FALSE;
   i   NUMBER := 0;
   WHILE NOT c AND i < 1000
      p := DBMS_RANDOM.string ('P', 10);
      i := i + 1;
         c := sys.ora12c_strong_verify_function (username, p, old_PASSWORD);
         WHEN OTHERS
   RETURN p;



This could well be a good initial expired password for your user. Later the user will find something easier to remember

Generate 11g password hash

An easy way to generate a value string from the ssl is to use openssl

Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important.

The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A-

$ echo "SafePassw0rDABCDEFGHIJ\c" | openssl dgst -sha1
(stdin)= 47cc4102144d6e479ef3d776ccd9e0d0158842bb

With this hash, I can construct my value

SQL> create user testuser identified by values 'S:47CC4102144D6E479EF3D776CCD9E0D0158842BB4142434445464748494A';

User created.

SQL> grant create session to testuser;

Grant succeeded.

SQL> conn testuser/SafePassw0rD

If you prefer PL/SQL over shell, use DBMS_CRYPTO

SQL> exec dbms_output.put_line('S:'||dbms_crypto.hash(utl_raw.cast_to_raw('SafePassw0rDABCDEFGHIJ'),dbms_crypto.HASH_SH1)||utl_raw.cast_to_raw('ABCDEFGHIJ'))

PL/SQL procedure successfully completed.

In 12c there is also a “T” String. According to the doc
The cryptographic hash function used for generating the 12C verifier is based on a de-optimized algorithm involving PBKDF2 and SHA-512.

permission issue due to one role

Most permissions issues are due to a missing role or privilege.

But in the following test case you need to revoke the right to get more privileges.

create table tt(x number);
create view v as select * from tt;
create role rw;
grant all on v to rw;

I’ve created a read-write role on a view. The owner of the role is the DBA, but the owner of the view is the application. Next release, the role may prevent an application upgrade

SQL> create or replace view v as select * from dual;
ORA-01720: grant option does not exist for 'SYS.DUAL'

Ok, if I drop the role, it works

SQL> drop role r;
Role dropped.
SQL> create or replace view v as select * from dual;
View created.

It is not always a good thing to grant privileges on a view, when you are not the owner of that view

list database monitoring users

I am quite familiar with the SYSMAN tables but this one required me some googling beyond the Oracle documentation.

The list of targets in your Oracle Enterprise Manager is in SYSMAN.MGMT_TARGETS. Each database target is monitored by a database user, typically DBSNMP.

To retrieve this information, you need some to hijack your database, read this : GΓΆkhan Atil

  1. you copy your encryption key to your repository database, on the OMS server
    $ emctl config emkey -copy_to_repos
    Enter Enterprise Manager Root (SYSMAN) Password :

    Now anyone with select any table on your repository will see all passwords. You don’t want to do this, but unfortunately you have to do this because even the username is encrpyted.

  3. you decrypt the credentials for db monitoring
    SELECT *
    FROM (
      SELECT target_name,
        sysman.em_crypto.decrypt (
          c.cred_salt) cred,
        cred_attr_name attr
      JOIN SYSMAN.mgmt_targets t USING (target_guid)
      JOIN sysman.EM_NC_CRED_COLUMNS c USING (cred_guid)
      WHERE c.target_type = 'oracle_database'
      AND c.set_name = 'DBCredsMonitoring' 
    ) PIVOT ( 
      MAX (cred)
      FOR (attr) IN (
        'DBUserName' AS USERNAME, 
        'DBRole' AS "ROLE")

    ----------- -------- ------
    DB01        dbsnmp   NORMAL
    DB02        dbsnmp   NORMAL
    DB03        sys      SYSDBA

  5. remove the security leak
    $ emctl config emkey -remove_from_repos
    Enter Enterprise Manager Root (SYSMAN) Password :

Now the em_crypto won’t work any more

from dual
Error at line 2
ORA-28239: no key provided
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 44
ORA-06512: at "SYSMAN.EM_CRYPTO", line 250
ORA-06512: at line 1

This information could be used to change the password dynamically accross all databases.

emcli login \
  -username=sysman \
emcli update_db_password \
  -target_name=DB01 \
  -user_name=dbsnmp \
  -change_at_target=yes \
  -old_password=oldpw \
  -new_password=newpw \

Untrusted X11 forwarding

I wrote a while ago about my security concerns regarding

xhost +
xterm -display mypc:0

Way back then, I suggested ssh tunnel. SSH is pretty easy to set up, by enabling the X11Forwarding option.

In OpenSSH 3.8 release note, 2004, there was a new default .

ssh(1) now uses untrusted cookies for X11-Forwarding

In the man ssh_config page, it’s still documented as being the default

ForwardX11Trusted The default is ‘no’

But it actually isn’t on most *ix derivates, e.g. RedHat /etc/ssh/ssh_config

# If this option is set to yes then
# remote X11 clients will have full access
# to the original X11 display. As virtually
# no X11 client supports the untrusted
# mode correctly we set this to yes.
ForwardX11Trusted yes

Who is we?

Okay, let’s go back.

If you use the unsafest method, xhost + and xterm -display pc:0, then you grant everybody the right to manipulate X.

If you use trusted ssh, which is the _undocumented_ default in Linux, then you grant this right only to anyone with access to your authority, most probably located in the file $HOME/.Xauthority. So root and yourself, at least.

If you trust neither yourself nor root, you could restrict access to your resource, preventing one hacker from switching your mouse buttons or doing a screenshot. But this is probably going to prevent most of your applications from working. Also, it probably won’t work at all if you use putty, reflection and (virtually any?) other client tools.

If you want to force Trusted mode, use -Y or -o ForwardX11Trusted=yes.

If you want to force Untrusted mode, use -X and -o ForwardX11Trusted=no.

If you use only -X, it may transparently defaults to the more convenient but less secure -Y. Sometimes. At least on Linux OpenSSH. But if you use different Unix / SSH flavours, the -X may ends with an error message like connection to “localhost:10.0” refused by server. In that case, simply use -Y. Actually, always use -Y if you want Trusted.

run sudo, ssh, password, su in simulated interactive mode

Some commands do not like non-interactive mode

$ passwd <<EOF
> oldpassword
> newpassword
> newpassword
Changing password for user lsc.
Current password for passwd: Authentication token manipulation error
$ echo oraclepassword | su - oracle
standard in must be a tty
$ echo sudopassword | sudo su - oracle
[sudo] password for lsc:
sudo: sorry, you must have a tty to run sudo

But ok, if you really want to run those in a script, you have plenty of clean (no hack there) ways of doing this.

For instance, let’s use a screen called BAR.

$ xterm -e "screen -S BAR" &
[1]     31732

Now we have an interactive terminal. It could be redirected to a frame buffer device if no x server is started.

Not really a human device, but an interactive terminal.

Now let’s send stuff

$ CR="$(echo '\r')"
$ screen -S BAR -X stuff "sudo su - oracle$CR"
$ screen -S BAR -X stuff "sudopassword$CR"
$ screen -S BAR -X stuff "id > /tmp/xxx$CR"
$ screen -S BAR -X stuff "exit$CR"
$ screen -S BAR -X stuff "exit$CR"
[1] +  Done                    xterm -e "screen -S BAR" &
$ cat /tmp/xxx
uid=100(oracle) gid=100(dba) groups=100(dba)

Usual disclaimer: it is a bad security practice to hardcode your passwords in scripts. Use this only if you really understand security. Read man openssl about how to use openssl to encrypt your password. Ask your security friends before trying

switch user in Oracle

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation Γ  la sudo.

By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user.

SQL> CONNECT app_user/xyz
ERROR:ORA-28058: login is allowed only through a proxy

SSL with PKCS12 truststore

Many many moons ago I vaguely remember having a similar issue with java keystore / truststore and microsoft certificates stores.

When you start using SSL for your listener, you could potentially face a large number of issues amoung your toolsets. In my opinion, the most disastrous one is that you cannot monitor your database with Enterprise Manager and a TCPS listener. I tried with 10g, 11g, 12c and I seriously doubt it will come in 13c, even a dozen of ERs have been filled. The best workaround I found is to use a separate listener to monitor your database and monitor the ssl-listener itself with IPC.

Today I had to deal with a driver from Datadirect, which finally works perfectly fine with SSL, but the challenge was to know what to put in the keystore and truststore…

In SQLNET, you use the single-sign-on wallet (cwallet.sso) created by OWM/orapki or ldap.

In Java, per default, you use a java keystore, that you generate with keytool (or even use the default cacerts). There is only a lexical difference between a keystore and a truststore, they could both be the same file. As documented in the JSSE Ref
A truststore is a keystore that is used when making decisions about what to trust

But for some other tools, the java keytool won’t do the trick, if the truststore cannot be of the type JKS.

One common type is the PKCS12. This is the your ewallet.p12 you get with the Wallet Manager.

E.g. from java :***

To use single-sign-on, use trustStoreType=SSO as I wrote there : jdbc-ssl

Other command formats are X509 base64 or DER file. The openssl command line allows you easy conversion

openssl pkcs12 -in ewallet.p12 -out file.pem
openssl x509 -outform der -in file.pem -out file.der

or in Windows Explorer, just click on your p12 file and then click on the certificate to export in the certificate store.

anonymous cypher suites for SSL (and a 12c pitfall)

If you configure your listener for encryption only, you do not really need authentication.

It works pretty fine until, I wrote multiple posts on ssl.

You add SSL_CLIENT_AUTHENTICATION=FALSE to your server sqlnet.ora and listener.ora and specify an “anon” cipher suite in your client. You do not need to validate the certificate, so a default wallet will do.

orapki wallet create -wallet . -auto_login_only





or if you use java, the default truststore -usually located in $JAVA_HOME/jre/lib/security/cacerts, will also do.

    System.setProperty("", "SSL_DH_anon_WITH_DES_CBC_SHA");

On some plateform however you may get something like : IBM’s Client TrustManager does not allow anonymous cipher suites.

So far so good, but if you upgrade your listener to or 12c, the anonymous suites won’t be accepted if not explicitely set up in sqlnet.ora. This is documented in Note 1434966.1

You will get something like “ORA-28860: Fatal SSL error”, “TNS-12560: TNS:protocol adapter error” in Oracle or “SSLHandshakeException: Received fatal alert: handshake_failure”, “SQLRecoverableException: I/O-Error: Received fatal alert: handshake_failure” in java.

There are two -obvious- ways to fix this. The preferred approach is to not use anonymous suite (they seem to have disappeared from the supported cypher suites in the doc).

For this task, you use another cipher suite. The easiest way is to not specify any or just use one like TLS_RSA_WITH_AES_128_CBC_SHA (java) / SSL_RSA_WITH_AES_128_CBC_SHA (sqlnet). Even if you do not use client authentication, you will then have to authenticate the server, and import the root ca in the wallet or the keystore.

# comment out ssl_cipher_suites=(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)


// comment out : System.setProperty("", "SSL_DH_anon_WITH_DES_CBC_SHA");

Or, as documented in metalink, define the suite in sqlnet.ora and listener.ora if you use or

poor man ActiveDirectory password checker

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation.

You probably were discouraged by the long long route to kerberos, where the bugs are fixed in, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that won’t be welcome by your sysadmins / winadmins.

Okay, to partly cover the password expiration issue, you could check in a profile function that the password is the one from AD.

Firstly, without SSL

(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
  sess := DBMS_LDAP.init(
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'', 
  rc := DBMS_LDAP.unbind_s(sess);
    rc := DBMS_LDAP.unbind_s(sess);

alter user lsc profile AD;

When the password expires, the user must change it to its AD Password.

If I try with a dummy password, the profile will reject this

SQL> conn lsc/pw1
ORA-28001: the password has expired

Changing password for lsc
New password:anypassword
Retype new password:anypassword
ORA-28003: password verification for 
  the specified password failed
ORA-31202: DBMS_LDAP: LDAP client/server 
  error: Invalid credentials. 
  80090308: LdapErr: DSID-0C0903A9, 
  comment: AcceptSecurityContext error, 
    data 52e, v1db1
Password unchanged
Warning: You are no longer connected to ORACLE.

I need to enter my Windows password

SQL> conn lsc/pw1
ORA-28001: the password has expired

Changing password for lsc
New password: mywindowspassword
Retype new password: mywindowspassword
Password changed

Secondly, with SSL.

Maybe simple bind without SSL is not possible (check And for sure it is better to not send unencrypted plain text password over the network.

Create a wallet with password with the ROOT Certification Authority that signed your AD. You probably could download this in your trusted root certification authorities in Internet Explorer.

Internet Explorer – Tools – Internet Options – Content – Certificates – Trusted root.

Then you create a ewallet.p12 with orapki. No need for user certificate and no need for single-sign-on. Only import the trusted root (and intermediaries if applicable).

Here is the modified code

(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
  sess := DBMS_LDAP.init(
  rc := DBMS_LDAP.open_ssl(
    sess, 'file:/etc/wallet/MSAD', 
    'welcome1', 2);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'', 
  rc := DBMS_LDAP.unbind_s(sess);
    rc := DBMS_LDAP.unbind_s(sess);

If you get SSL Handshake, be prepared, it could be anything! Check your wallet, your certificate, your permission, your wallet password.

One step further could be to expire users as soon as they change their password in AD or when they expire there.

For instance with powershell goodies for active directory

PS> (Get-ADuser lsc -properties PasswordLastSet).PasswordLastSet

Montag, 6. Oktober 2014 08:18:23

PS> (Get-ADuser king -properties AccountExpirationDate).AccountExpirationDate

Mittwoch, 16. Juli 2014 06:00:00

And in the database

SQL> SELECT ptime FROM sys.user$ 
  WHERE name ='LSC';


If PTIME is less than PasswordLastSet or if AccountExpirationDate is not null, expire the account.

In conclusion : if you do not want to use Kerberos, nor Oracle “OctetString” Virtual Directory ovid nor Oracle Internet directory oid, this workaround may help to increase your security by addressing the “shared” and “expired” accounts problematic

There an additional hidden benefit. You could set up a self-service password reset function and send a generated expired password per mail, that the user won’t be able to change without its AD password

PLS-00201 in stored procedures

When you grant table access thru a role, you cannot use that role in a stored procedure or view.

create role r;

create user u1 identified by ***;
grant create procedure, create session to u1;

create user u2 identified by ***;
grant create procedure, create session, r to u2;

conn u1/***
create procedure u1.p1 is begin null; end; 

grant execute on u1.p1 to r;

conn u2/***

create procedure u2.p2 is begin u1.p1; end; 

sho err procedure u2.p2

Errors for PROCEDURE U2.P2:

----- -------------------------------------------
1/26  PL/SQL: Statement ignored
1/26  PLS-201: identifier U1.P1 must be declared

However, If i run it in an anonymous block, it works

  procedure p2 is 

PL/SQL procedure successfully completed.

But this only works when my role is active. If my role is no longer active, then it obviously fails.

set role none;

  procedure p2 is 
ERROR at line 1:
ORA-06550: line 4, column 5:
PLS-00201: identifier 'U1.P1' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

It is all written in the doc,

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer’s rights

I knew the behavior but not the reason behind it. Thanks to Bryn for bringing me so much knowledge on plsql.

ssl version

I wrote about ssl version in jdbc thin yesterday

The default version also no longer works for the thick client with 12c client and 11g Server.

With 11gR2 :

TNS Ping Utility for 64-bit Windows: Version
OK (100 msec)

with 12cR1 :

TNS Ping Utility for 64-bit Windows: Version
TNS-12560: TNS:protocol adapter error

in trace file I see

ntzgsvp: no SSL version specified - using default version 0
ntzdosecneg: SSL handshake failed with error 29048.
ntzCreateConnection: returning NZ error 29048 in result structure
ntzCreateConnection: failed with error 542
nserror: nsres: id=0, op=65, ns=12560, ns2=0; nt[0]=29048, nt[1]=542, nt[2]=0; ora[0]=29048, ora[1]=0, ora[2]=0

I could not see this as a documented change yet, but if you force ssl_version to be 3.0, both client versions works

TNS Ping Utility for 64-bit Windows: Version
OK (80 msec)
C:> find "version" tnsping.trc sqlnet.ora

---------- TNSPING.TRC
nlstddp_dump_ptable:   ssl_version = 3.0
ntzGetStringParameter: found value for "ssl_version" configuration parameter: "3.0"

---------- SQLNET.ORA

TCPS and SSLv2Hello

Thanks to platform independence, the same java code work on different platforms.

import java.util.Properties;
import java.sql.*;

public class KeyStore {
  public static void main(String argv[]) 
      throws SQLException {
    String url="jdbc:oracle:thin:@(DESCRIPTION="+
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
      new oracle.jdbc.OracleDriver());
    Connection conn = 
      DriverManager.getConnection(url, props);
    ResultSet res = conn.prepareCall("select "+
       "') txt from dual").
    System.out.println("PROTOCOL: "+

The code above perfectly works with Linux and Windows.

Okay, in AIX you will get IllegalArgumentException SSLv2Hello at if you don’t add


The default does not work with the Oracle AIX client. Just set it to 1.0 and 3.0 and you will be a bit less plateform-dependent

check if using tcps part II

in your current session, as written there, check sys_context('USERENV', 'NETWORK_PROTOCOL')

in another session, you could grab some hints out of the network service banner. Do the maths, when it is not-not using ssl, it probably is…

select sid,program,
  case when program not like 'ora___@% (P%)' then
  (select max(case
      then 'TCP'
when NETWORK_SERVICE_BANNER like '%Bequeath%' 
      then 'BEQUEATH'
      then 'IPC'
      then 'SDP'
      then 'Named pipe'
      then 'TCPS' end)
    where i.sid=s.sid) end protocol
  from v$session s;

---------- --------------- --------
       415 sqlplus(TNS V1- BEQUEATH
       396 sqlplus(TNS V1- IPC     
         6 Toad            TCP     
         9 Toad            TCPS    
         1 oracle(DIAG)            
       403 Toad            TCP     

The long long route to Kerberos

If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost

Landscape for my setup

  • One PC with Windows (PC01.EXAMPLE.COM)
  • One DB Server with Unix (DBSRV01.EXAMPLE.COM)
  • One Microsoft Active Directory Server (MSAD01.EXAMPLE.COM)


  • user01

Tools for troubleshooting

My Software

  • PC : Oracle Client
  • Unix : Oracle Server
  • On AD : MSAD 2008

There are a lot of buggy releases (it makes me think Oracle does not test Kerberos properly)

Some hits : : Bug 12635212 : TCP/88 is not working. : Bug 17890382 : ZTK return value: 6

Also your PC must be using Kerberos (which is the case if you login to your Active Directory). The DB server needs some client libraries (krb5.client.rte on AIX).

System changes:

  • PC : edit etc\services
    C:\> find " 88" %SystemRoot%\system32\drivers\etc\services
    kerberos   88/tcp    kerberos5 krb5 kerberos-sec  #Kerberos
    kerberos   88/udp    kerberos5 krb5 kerberos-sec  #Kerberos

  • Unix : edit /etc/services
    $ grep -w 88 /etc/services
    kerberos  88/tcp  kerberos5 krb5  # Kerberos
    kerberos  88/udp  kerberos5 krb5  # Kerberos

  • On AD : disable pre-authentication
    this option has to be set for every user, under user -> user01 -> Properties -> Account -> Account options -> Select “Do not require Kerberos preauthentication”

Those are quite painful. There is a bug 2458563 fixed in (whatever it means) that should have addressed pre-authentication. still required on apparently no longer needed with a client
Editing etc/services to add the “kerberos5” string means you need admin rights on Windows and root on Unix.

Okay, now you need to create the config files. You probably should use Kerberos v5 MIT.

Kerberos5 was released in 1993, not sure why you want to use something older than this… Okay, for kerberos4, released in the 80’s, you would need on the PC and on the DB Server something like


Otherwise you need to specify : sqlnet.kerberos5_conf_mit=true
I have an open SR to support regarding : 12c upgrade guide
The SQLNET.KERBEROS5_CONF_MIT networking parameter is no longer supported in sqlnet.ora

Okay, here the configuration files

krb5.conf on the database server and on the PC

default_realm = EXAMPLE.COM


[domain_realm] = EXAMPLE.COM = EXAMPLE.COM

The config file location (kerberos4 or 5) is specified by sqlnet.kerberos5_conf.

There should be a technical account for your db server created on the MSAD that matched your db server.

On Active Directory, you create a user (e.g. : oracle_DBSRV01) who must not change password on first login. Then you extract the keytab with ktpass

ktpass.exe -princ oracle/ -mapuser oracle_DBSRV01 -crypto all -pass password -out c:\dbsrv01.keytab

As an Oracle DBA, you will probably ask this to another team who is used to Kerberos.

To verify it, you can list the content of the keytab

$ $ORACLE_HOME/bin/oklist -k dbsrv01.keytab
Kerberos Utilities for IBM/AIX RISC System/6000: Version - Production on 16-JAN-2014 12:45:08

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Service Key Table: dbsrv01.keytab

Ver      Timestamp                    Principal
 4  01-Jan-1970 01:00:00  oracle/

The principal name must match your full qualified host name. You cannot use a DNS alias.

On your PC check for the login name :

PS> $o = New-Object DirectoryServices.DirectorySearcher; 
  $o.Filter = 'servicePrincipalName=oracle/'; 

And you can verify the principal of that user

C:\> setspn -L oracle_DBSRV01
Registered ServicePrincipalNames for CN=oracle_DBSRV01,OU=MiscUsers,DC=example,DC=com

Now you’ve got your keytab, this must be on the DB Server only (and must be readable for oracle). The location is specified by SQLNET.KERBEROS5_KEYTAB.

Next step is the credential cache (CC) parameter. On your PC with the Oracle 11g client, you must set sqlnet.kerberos5_cc_name to OSMSFT://
On the server it is not neeeded. On Oracle 12c client, you must set it MSLSA:, but due to bug 17890382, it is not working yet (metalink comment : We will have to wait […] bugs are under investigation).

But before you start, you may want to test the ticket.

On Unix, you can get the ticket with kinit and check it with klist. You need to have your configuration in /etc/krb5/krb5.conf (OS Dependent). Do not forget to destroy your credential cache with kdestroy / okdstry while testing

For the DB Server

$ /usr/krb5/bin/kinit -k -t dbsrv01.keytab oracle/
$ /usr/krb5/bin/klist
Ticket cache:  FILE:/var/krb5/security/creds/krb5cc_99
Default principal:  oracle/

Valid starting     Expires            Service principal
01/16/14 17:41:26  01/17/14 03:41:26  krbtgt/EXAMPLE.COM@EXAMPLE.COM
        Renew until 01/17/14 17:41:26

$ /usr/krb5/bin/kinit user01@EXAMPLE.COM
Password for user01@EXAMPLE.COM:
Ticket cache:  FILE:/var/krb5/security/creds/krb5cc_99
Default principal:  user01@EXAMPLE.COM

Valid starting     Expires            Service principal
01/16/14 17:35:57  01/17/14 03:35:57  krbtgt/EXAMPLE.COM@EXAMPLE.COM
        Renew until 01/17/14 17:35:57

Now we can test the okinit (oracle kinit) tool to do the same. There are some specific trace options that could be set

Here the complete sqlnet.ora on the server

TRACE_DIRECTORY_OKINIT = /var/opt/oracle/krb/cc
SQLNET.KERBEROS5_CC_NAME = /var/opt/oracle/krb/cc/krb5cc_99
SQLNET.KERBEROS5_CONF = /var/opt/oracle/krb/krb5.conf
SQLNET.KERBEROS5_KEYTAB = /var/opt/oracle/krb/dbsrv01.keytab

Note the authentication service. If kerberos is not working, you may no longer be able to log / as sysdba and also some db links may no longer work.

Also note SQLNET.AUTHENTICATION_KERBEROS5_SERVICE, which is the prefix of your principal, oracle/

Then we use okinit as we did for kinit

$ $ORACLE_HOME/bin/okinit -k -t dbsrv01.keytab oracle/

Kerberos Utilities for IBM/AIX RISC System/6000: Version - Production on 16-JAN-2014 17:52:21

Copyright (c) 1996, 2013 Oracle.  All rights reserved.
$ $ORACLE_HOME/bin/oklist

Kerberos Utilities for IBM/AIX RISC System/6000: Version - Production on 16-JAN-2014 17:55:27

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: oracle/

   Valid Starting           Expires            Principal
16-Jan-2014 17:54:30  17-Jan-2014 01:54:30  krbtgt/EXAMPLE.COM@EXAMPLE.COM

$ $ORACLE_HOME/bin/okinit user01@EXAMPLE.COM

Kerberos Utilities for IBM/AIX RISC System/6000: Version - Production on 16-JAN-2014 18:15:02

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Password for user01@EXAMPLE.COM:
$ $ORACLE_HOME/bin/oklist

Kerberos Utilities for IBM/AIX RISC System/6000: Version - Production on 16-JAN-2014 18:15:12

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: user01@EXAMPLE.COM

   Valid Starting           Expires            Principal
16-Jan-2014 18:15:06  17-Jan-2014 02:15:02  krbtgt/EXAMPLE.COM@EXAMPLE.COM

In case of error, grep for “k5″ in the trace file.

Do the same on the PC01. To test okinit, temporary change the CC cache to a file




and test as in Unix.

Some errors will be easier to find out with a network sniffer on port 88

With AIX

tcpdump -v -v port 88

On Windows
Start -> Microsoft Network Monitor -> Microsoft Network Monitor -> New capture -> Display filter

Frame.Ethernet.Ipv4.TCP.Port == 88 or Frame.Ethernet.Ipv4.UDP.Port == 88

-> Apply -> Start

If you for instance only see UDP packets but no TCP packets, you probably hit bug 12635212.

I still have some KDC_ERR_S_PRINCIPAL_UNKNOWN errors with my working setup, don’t worry about those.

Ok, now that okinit works, the next step is to log in the database.

Check the adapters on the db server

$ adapters

Installed Oracle Net transport protocols are:


Installed Oracle Net naming methods are:

    Local Naming (tnsnames.ora)
    Oracle Directory Naming
    Oracle Host Naming
    Oracle Names Server Naming
    NIS Naming

Installed Oracle Advanced Security options are:

    RC4 40-bit encryption
    RC4 56-bit encryption
    RC4 128-bit encryption
    RC4 256-bit encryption
    DES40 40-bit encryption
    DES 56-bit encryption
    3DES 112-bit encryption
    3DES 168-bit encryption
    AES 128-bit encryption
    AES 192-bit encryption
    AES 256-bit encryption
    MD5 crypto-checksumming
    SHA-1 crypto-checksumming
    Kerberos v5 authentication
    RADIUS authentication

Create the user on the database db01 on the server dbsrv01. You need to have OS_AUTHENT_PREFIX=”” and do not set REMOTE_OS_AUTHENT (if you have it set, why would you need Kerberos?)

SQL> create user user01 identified externally as '';
User created.
SQL> grant create session to user01;
Grant succeeded.

Connect from the PC

$ sqlplus -L /@db01

SQL*Plus: Release Production on Thu Jan 16 18:40:43 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


This also works in Toad, SQL-Developer and other tools using the OCI thick client. Just let “User” and “Password” blank.

In SQLDeveloper, make sure you do not check Kerberos but you use OCI Thick and no username and password

There is probably a way to do it with the jdbc thin client as document in Note 1523651.1, I have not gone that far yet

Note 303436.1 : Improper format of configuration file: Remove TAB characters from KRB5.conf file. Replace with spaces.

Do you really need ASO?

If you only use the Advanced Security Option for SSL, you may not need to pay for it !

License 11.2
When used with Oracle Real Application Clusters, Oracle Advanced Security SSL/TLS is included.

But also
License 11.1
Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.

If SSL/TLS is no longer part of Advanced Security, what is then Oracle Advanced Security SSL/TLS ?

hot to bypass requiretty in sudo

You can execute it a command without password from the commande line

$ sudo -l
User lsc may run the following commands on this host:
    (root) NOPASSWD: /usr/local/bin/helloworld
$ sudo /usr/local/bin/helloworld
Hello World!

Now you try to run it via cron and you get

sudo: sorry, you must have a tty to run sudo

The message is clear, you need a terminal.

Either you edit your sudoers files to disable requiretty, or you just get yourself a terminal.

Maybe you tried to assign a pseudo terminal with ssh -t, but you may get an error if ssh has no local tty

Pseudo-terminal will not be allocated because stdin is not a terminal.

Don’t despair, read man ssh

Multiple -t options force tty allocation, even if ssh has no local tty.

Let’s try

* * * * * ssh -t -t sudo /usr/local/bin/helloworld >> /tmp/txt

This should work, providing you configured ssh keys πŸ™‚

encrypt with openssl

I want to avoid cleartext password on my filesystem

I encrypt my password with a secret key

echo tiger | openssl enc -aes-256-cbc -k MyKey -out $HOME/myconfig

Whenever I call a script, I pass the secret key

sqlplus scott/$(openssl enc -d -aes-256-cbc -k MyKey -in $HOME/myconfig)

Of course you still need the key {MyKey}, but unless you have both the file AND the key, it is safe

Enhancement Request : SSL listener and OEM

#em12c still does not support SSL ! Encrypting network connection (https, ssh, sftp) is common sense in today’s business.

In Enhancement Request 6512390, Created 19-Oct-2007, the customer requested support for SSL.

Most recent update : it is postponed to 13cR2 at least !

*** 09/14/12 04:04 am DISCUSSION ***As we kick off 13c release, cleaning up the ERs. Mass updating all 13%GC% ERs to fixby 13.2GC DEFER. If you want to implement ER in 13.1GC, please update the fixby to = 13.1GC, and update all the other fields as per guidelines published.

Considering the cost of oracle advanced security option (required to get ssl), the lack of ability to influence future product enhancement is disappointing

old-hash, SHA-1, SHA-2/512

Until pretty recently, only the dubious unsalted proprietary algorithm was available to store Oracle passwords. A bunch of tool where at the time able to decode any 6-8 characters in no time, and the rainbow approach was to precalculate all possibles passwords for a specific user.

Those time are not really for away, only starting at Oracle 11g, you could have salted/case sensitive passwords. Salted means that Scott may have many different passwords keys for tiger.

 SQL> select spare4 from user$ where name='SCOTT';

SQL> alter user scott identified by tiger;

User altered.

SQL> select spare4 from user$ where name='SCOTT';

Some users may have only the 10g version (password not changed after migrating to 11g), some may have the 11g version of both, and -who knows- some may have already have SHA-2/512 passwords. SHA2 has many advantages. The chance that 2 passwords provides exactly the same string are much lower than in SHA1 (collision) and it performs twice faster on 64 bits servers.

SQL> select username, password_versions from dba_users where username like 'U_;
USERNAME                       PASSWORD
------------------------------ --------
U1                             10G
U2                             11G
U3                             10G 11G
U4                             12C

Probably you never saw this unless you are in beta 12. But actually it is documented in the 11gR2 Documentation.

12C if a new SHA-2 based SHA-512 hash exists

grant select on sys tables

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations.

Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role.


update, 2012-07-24
For purge dba_recyclebin, you probably should purge tables individually
exec for f in(select*from dba_recyclebin where owner!='SYS' and type='TABLE')loop execute immediate 'purge table "'||f.owner||'"."'||f.object_name||'"';end loop;

For DBMS_STREAMS_AUTH, what I am actually missing, is the GRANT OPTION on some documented dba views and dbms package. So I could safely grant the grant option to my user for all sys objects that have been granted to DBA, PUBLIC and any other roles.

Kind of

create table scott.t as 
  select distinct owner,table_name,privilege 
  from dba_tab_privs t 
  where privilege not in ('USE','DEQUEUE') and owner='SYS' ;
  for f in(select * from scott.t) loop 
    execute immediate 
      'grant '||f.privilege||' on "'||f.owner||'"."'
        ||f.table_name||'" to scott with grant option'; 
  end loop;

It is better to not select from dba_tab_privs directly, as executing immediate while opening the cursor may have unexpected side effects.

This may help you to increase your security by reducing your connections as sys.

xhost+ security hole part 2

Five years ago I wrote xhost+ is a huge security hole, I turned out red this morning when my neighbour sent me a smiley via X.

Do I really want everyone to have full access to my screen? No, I don’t. And I don’t do xhost+.

So why did it happen to me ???

I am using X-Window Attachmate aka Reflection X. And in this tool, according to the doc, the default X policy is unrestricted. This is in my opinion a huge flaw in the security design. Make sure you always change this to something more secure.

In Reflection X Manager Settings, Category Security, choose for instance User-based security and Prompt. Configuring X Cookies is probably more cumbersome.

Then when you or someone else will start an XTERM on your desktop, you will get a nice dialog box :

[Reflection X]
Client could not successfully authenticate itself to Reflection X server. Would you like Reflection X to connect to this client as an UNTRUSTED client ? Client originated from (RX1303)

Ok, I have to click one more button, but at least I can deny access to my screen πŸ™‚

This system is for the use of authorized users only.

How to bypass the login banners?

There is actually more than one banner to bypass. One of the them is the message of the day banner, commonly located in /etc/motd. Typically friendly, example in AIX

*                                                                          *
*                                                                          *
*  Welcome to AIX Version 6.1!                                             *
*                                                                          *
*                                                                          *
*  Please see the README file in /usr/lpp/bos for information pertinent to *
*  this release of the AIX Operating System.                               *
*                                                                          *
*                                                                          *

This is easy to bypass, simply place .hushlogin file on your serverside homedirectory :

$ touch $HOME/.hushlogin

Yes, it is that easy.

A bit more cumbersome is the ssh banner. Which rather have an aggressive look with criminal punishment threats.

| This system is for the use of authorized users only.            |
| Individuals using this computer system without authority, or in |
| excess of their authority, are subject to having all of their   |
| activities on this system monitored and recorded by system      |
| personnel.                                                      |
|                                                                 |
| In the course of monitoring individuals improperly using this   |
| system, or in the course of system maintenance, the activities  |
| of authorized users may also be monitored.                      |
|                                                                 |
| Anyone using this system expressly consents to such monitoring  |
| and is advised that if such monitoring reveals possible         |
| evidence of criminal activity, system personnel may provide the |
| evidence of such monitoring to law enforcement officials.       |

Well, you could delete it from your ssh server but this would not please your sysadmins. The banner is actually a pre-authentication banner, so you must configure it on the client. With the unix ssh client, you lower the log level. Either with a command line option, -o LogLevel=quiet, or in a configfile

$ cat $HOME/.ssh/config

This is so quiet that you will not get any feedback if you cannot connect, but I prefer quiet than noisy.

A very commonly used Windows ssh client is putty, and there, O miracle, there is a pre-authentication-banner option to uncheck in SSH-Auth.

This appeared in putty 0.62 and it made my day today πŸ™‚

TNSNAMES and Active Directory

It is highly probable you already have MS AD in your company. Probably you use a local tnsnames.ora. Apart from setting a Oracle Internet Directory or Oracle Virtual Directory, there is one more option that you may want to consider : AD.

Ok, here is a bit of a road map :

– Schema Extension :
extending the schema is irreversible and you will have to test this properly and explain why you need this (remove the need of distributing a tnsnames, central administration) to your Microsoft Admin friends. To extend the schema, use Oracle Network Configuration Assistant. The step-by-step guide is there

– Anonymous or authenticated bind
prior to 11g, you needed to allow anonymous bind on the AD server. Your Security Admin friends will probably prefer the 11g approach of setting NAMES.LDAP_AUTHENTICATE_BIND to true. If you set NAMES.LDAP_AUTHENTICATE_BIND to true, the Oracle clients will use your windows credentials to do the tnsnames resolution.

For sql developer, use Connection Type=TNS, Connect Identifier=DB01. connection type=Ldap does not work with authenticated bind

– Import the tnsnames and / or create new entries
all done with Net Manager and pretty intuitively. Except that you will use “Directory –> Export Net Service Names” to import the tnsnames in AD

– Configure the clients


– test it!
tnsping first

C:\> tnsping db01

TNS Ping Utility for 64-bit Windows: Version - Production on 10-NOV-2011 14:42:16

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)(Port=1521)))(CONNECT
OK (20 msec)

I wrote a simple java program to check the connection :

import java.sql.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    OracleDataSource ods = new OracleDataSource();
    ResultSet res = ods.
        prepareCall("select 'Hello World' txt from dual").

C:\> set PATH=C:\oracle\product\11.2.0\client_3\bin
C:\> javac -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar
C:\> java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar\oracle\product\11.2.0\client_3\network\admin HelloWorld
Hello World

If you get and issue with ocijdbc11, you either do not have the *ocijdbc11* driver in your PATH / LD_LIBRARY_PATH / LIBPATH or the use the wrong driver. For instance if you compile with java 32bits, you cannot use the oci 64 bit.

If you use a jdbc thin ldap resolution and have no anonymous bind, it will return an error

import java.sql.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    ResultSet res = DriverManager.
      getConnection("jdbc:oracle:thin:@ldap://,cn=OracleContext", "scott", "tiger").
      prepareCall("select 'Hello World' txt from dual").

C:\>java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar HelloWorld
Exception in thread "main" java.sql.SQLException: I/O-Fehler: JNDI Package failure avax.naming.NamingException: [LDAP:error code 1 - 000004DC: LdapErr: DSID-0C0906DC, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, v1db0 ]; remaining name 'cn=db01,cn=OracleContext'
        at oracle.jdbc.driver.T4CConnection.logon(
        at oracle.jdbc.driver.PhysicalConnection.<init>(
        at oracle.jdbc.driver.T4CConnection.<init>(
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(
        at oracle.jdbc.driver.OracleDriver.connect(
        at java.sql.DriverManager.getConnection(
        at java.sql.DriverManager.getConnection(
        at HelloWorld.main(

As the error message says, the ldap server requires a bind

Let’s try to bind

import java.sql.*;
import java.util.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    OracleDataSource ods = new OracleDataSource();
    Properties prop = new Properties();
    prop.put("", "simple");
    prop.put("","CN=Laurent Schneider,CN=Users,DC=example,DC=com");
    prop.put("", "my_ad_pw");

    ResultSet res = ods.
        prepareCall("select 'Hello World' txt from dual").

This works!

how to run UTL_TCP, UTL_SMTP and the like in 11g

After we upgrade a db to 11g someone complained about an ORA-24248: XML DB extensible security not installed

I thought, it should be easy to revert to 10g mechanism. Probably wrong after reading Marco :
The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged users. This default behavior is different from, and is incompatible with, previous versions of Oracle Database.

I do not want to install XDB to send mail. Sounds like an overkill…

Ok, as an hard core dba I created a wrapper in the sys schema, something you probably should not do !


SQL> conn scott/tiger
SQL> select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual;

after upgrade

SQL> conn scott/tiger
SQL> select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual;
select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual
ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

My workaround to “disable” Fine-Grained Access to External Network Services

SQL> conn / as sysdba
SQL> create or replace function my_utl_inaddr_GET_HOST_ADDRESS(HOST VARCHAR2) return VARCHAR2 is begin return utl_inaddr.GET_HOST_ADDRESS; end;
  2  /

Function created.

SQL> grant execute on my_utl_inaddr_GET_HOST_ADDRESS to scott;

Grant succeeded.
SQL> conn scott/tiger
SQL> select sys.my_utl_inaddr_GET_HOST_ADDRESS('localhost') from dual;

If you want to use the recommended way of granting access to utl_tcp and the like, check note 453756.1

List of table and column privileges, including those via roles

I could not find this quickly enough in google so I wrote it myself.

The list of table privileges, with a connect by subquery.

 COL roles FOR a60
COL table_name FOR a30
col privilege for a9
set lin 200 trims on pages 0 emb on hea on newp none

    FROM (    SELECT CONNECT_BY_ROOT grantee grantee,
                     REPLACE (
                        REGEXP_REPLACE (SYS_CONNECT_BY_PATH (granteE, '/'),
                        ' --> ')
                FROM (SELECT PRIVILEGE,
                             NULL column_name
                        FROM DBA_TAB_PRIVS
                       WHERE owner NOT IN
                      SELECT PRIVILEGE,
                        FROM DBA_COL_PRIVS
                       WHERE owner NOT IN
                      SELECT GRANTED_ROLE,
                        FROM DBA_ROLE_PRIVS
                       WHERE GRANTEE NOT IN
                                 'APPQOSSYS')) T
          START WITH grantee IN (SELECT username FROM dba_users)
   WHERE table_name IS NOT NULL AND grantee != OWNER
ORDER BY grantee,

sample output

------- --------- --------------- ------ ---------- -----------
U       UPDATE     --> R          SCOTT  DEPT       DNAME      
U       SELECT                    SCOTT  EMP                   
U2      UPDATE     --> R2 --> R   SCOTT  DEPT       DNAME      

on materialized view constraints

Oracle is pretty strong at enforcing constraint.

Table for this blog post:
create table t(x number primary key, y number);

For instance if you alter table t add check (y<1000); then Y will not be bigger than 1000, right?

SQL> insert into t values (1,2000);
insert into t values (1,2000)
Error at line 1
ORA-02290: check constraint (SCOTT.SYS_C0029609) violated

I believe this code to be unbreakable. If you have only SELECT and INSERT privilege on the table, you cannot bypass the constraint.

Let’s imagine some complex constraint. CHECK (sum(y) < 1000)

SQL> alter table t add check (sum(y) < 1000);
alter table t add check (sum(y) < 1000)
Error at line 1
ORA-00934: group function is not allowed here
Ok, clear enough I suppose, we cannot handle this complex constraint with a CHECK condition. We could have some before trigger that fires an exception
   ON T
   WHEN (NEW.Y > 0)
   s   NUMBER;

   IF (s + :new.y >= 1000)
      raise_application_error (-20001, 'SUM(Y) would exceed 1000');
   END IF;
Now the trigger will compute the sum and return an exception whenever it fails.
SQL> insert into t values (2, 600);

1 row created.

SQL> insert into t values (3, 600);
insert into t values (3, 600)
ERROR at line 1:
ORA-20001: SUM(Y) would exceed 1000
ORA-06512: at "SCOTT.TR", line 8
ORA-04088: error during execution of trigger 'SCOTT.TR'
SQL> drop trigger tr;

Trigger dropped.

SQL> truncate table t;

Table truncated.
But I am not good with triggers, and the triggers are as bad as their developers and have dark sides like mutating triggers and thelike. As Tom Kyte mentioned in the comment, the code above is not efficient effective if more than one user update the table at the same time

Another popular approach is to create a fast-refreshable-on-commit mview with a constraint.

Let’s see how this works.

create materialized view log on t with rowid, primary key (y) including new values;

create materialized view mv
refresh fast 
on commit 
as select sum(y) sum from t;

alter table mv add check (sum < 1000);

The constraint is on the mview, so once you commit (and only at commit time), Oracle will try to refresh the mview.

SQL> insert into t values (4, 600);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t values (5, 600);

1 row created.

SQL> commit;
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.SYS_C0029631) violated

SQL> select * from t;

         X          Y
---------- ----------
         4        600

So far so good. The mechanism rollbacks the transaction in case of an ORA-12008. A bit similar to a DEFERABLE constraint.

But how safe is this after all? Oracle does not enforce anything on the table, it just fails on refresh…

Anything that does not fulfill the materialized view fast refresh requisites will also break the data integrity.

SQL> delete from t;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert /*+PARALLEL*/ into t select 100+rownum, rownum*100 from dual connect by level<20;

19 rows created.

SQL> commit;

Commit complete.

SQL> select sum(y) from t;


SQL> select staleness from user_mviews;


Your data integrity is gone. By “breaking” the mview, with only SELECT, INSERT and ALTER SESSION privilege, you can now insert any data.

This is documented as
FAST Clause

For both conventional DML changes and for direct-path INSERT operations, other conditions may restrict the eligibility of a materialized view for fast refresh.

Other operations like TRUNCATE may also prevent you from inserting fresh data

SQL> alter materialized view mv compile;

Materialized view altered.

SQL> exec dbms_mview.refresh('MV','COMPLETE');

PL/SQL procedure successfully completed.

SQL> select * from mv;


SQL> insert into t values(1,1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv;


SQL> truncate table t;

Table truncated.

SQL> insert into t values(1,1);

1 row created.

SQL> commit;
ERROR at line 1:
ORA-32321: REFRESH FAST of "SCOTT"."MV" unsupported after detail table