Laurent Schneider

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
LogLevel=Quiet
StrictHostKeyChecking=no
NumberOfPasswordPrompts=1

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

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
sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=1

ldap.ora
DEFAULT_ADMIN_CONTEXT = “DC=example,DC=com”
DIRECTORY_SERVER_TYPE = AD

- test it!
tnsping first

C:\> tnsping db01

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

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

Used parameter files:
C:\oracle\product\11.2.0\client_3\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)(Port=1521)))(CONNECT
_DATA=(SID=DB01)))
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();
    ods.setDriverType("oci");
    ods.setTNSEntryName("DB01");
    ods.setUser("scott");
    ods.setPassword("tiger");
    ResultSet res = ods.
      getConnection().
        prepareCall("select 'Hello World' txt from dual").
          executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

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 HelloWorld.java
C:\> java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar -Doracle.net.tns_admin=C:\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://example.com:389/db01,cn=OracleContext", "scott", "tiger").
      prepareCall("select 'Hello World' txt from dual").
      executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

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(T4CConnection.java:419)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at HelloWorld.main(HelloWorld.java:5)

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();
    ods.setDriverType("thin");
    Properties prop = new Properties();
    prop.put("java.naming.security.authentication", "simple");
    prop.put("java.naming.security.principal","CN=Laurent Schneider,CN=Users,DC=example,DC=com");
    prop.put("java.naming.security.credentials", "my_ad_pw");
 
    ods.setConnectionProperties(prop);

    ods.setURL("jdbc:oracle:thin:@ldap://w2k8.local:389/db01,cn=OracleContext,DC=example,DC=com");
    ods.setUser("scott");
    ods.setPassword("tiger");
    ResultSet res = ods.
      getConnection().
        prepareCall("select 'Hello World' txt from dual").
          executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

This works!

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 !

ex:
10g

SQL> conn scott/tiger
Connected.
SQL> select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual;
UTL_INADDR.GET_HOST_ADDRESS('LOCALHOST')
--------------------------------------------------
127.0.0.1

after upgrade
11g

SQL> conn scott/tiger
Connected.
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
Connected.
SQL> select sys.my_utl_inaddr_GET_HOST_ADDRESS('localhost') from dual;
SYS.MY_UTL_INADDR_GET_HOST_ADDRESS('LOCALHOST')
--------------------------------------------------
127.0.0.1

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

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

  SELECT *
    FROM (    SELECT CONNECT_BY_ROOT grantee grantee,
                     privilege,
                     REPLACE (
                        REGEXP_REPLACE (SYS_CONNECT_BY_PATH (granteE, '/'),
                                        '^/[^/]*'),
                        '/',
                        ' --> ')
                        ROLES,
                     owner,
                     table_name,
                     column_name
                FROM (SELECT PRIVILEGE,
                             GRANTEE,
                             OWNER,
                             TABLE_NAME,
                             NULL column_name
                        FROM DBA_TAB_PRIVS
                       WHERE owner NOT IN
                                ('SYS',
                                 'SYSTEM',
                                 'WMSYS',
                                 'SYSMAN',
                                 'MDSYS',
                                 'ORDSYS',
                                 'XDB',
                                 'WKSYS',
                                 'EXFSYS',
                                 'OLAPSYS',
                                 'DBSNMP',
                                 'DMSYS',
                                 'CTXSYS',
                                 'WK_TEST',
                                 'ORDPLUGINS',
                                 'OUTLN',
                                 'ORACLE_OCM',
                                 'APPQOSSYS')
                      UNION 
                      SELECT PRIVILEGE,
                             GRANTEE,
                             OWNER,
                             TABLE_NAME,
                             column_name
                        FROM DBA_COL_PRIVS
                       WHERE owner NOT IN
                                ('SYS',
                                 'SYSTEM',
                                 'WMSYS',
                                 'SYSMAN',
                                 'MDSYS',
                                 'ORDSYS',
                                 'XDB',
                                 'WKSYS',
                                 'EXFSYS',
                                 'OLAPSYS',
                                 'DBSNMP',
                                 'DMSYS',
                                 'CTXSYS',
                                 'WK_TEST',
                                 'ORDPLUGINS',
                                 'OUTLN',
                                 'ORACLE_OCM',
                                 'APPQOSSYS')
                      UNION 
                      SELECT GRANTED_ROLE,
                             GRANTEE,
                             NULL,
                             NULL,
                             NULL
                        FROM DBA_ROLE_PRIVS
                       WHERE GRANTEE NOT IN
                                ('SYS',
                                 'SYSTEM',
                                 'WMSYS',
                                 'SYSMAN',
                                 'MDSYS',
                                 'ORDSYS',
                                 'XDB',
                                 'WKSYS',
                                 'EXFSYS',
                                 'OLAPSYS',
                                 'DBSNMP',
                                 'DMSYS',
                                 'CTXSYS',
                                 'WK_TEST',
                                 'ORDPLUGINS',
                                 'OUTLN',
                                 'ORACLE_OCM',
                                 'APPQOSSYS')) T
          START WITH grantee IN (SELECT username FROM dba_users)
          CONNECT BY PRIOR PRIVILEGE = GRANTEE)
   WHERE table_name IS NOT NULL AND grantee != OWNER
ORDER BY grantee,
         owner,
         table_name,
         column_name,
         privilege;

sample output

GRANTEE PRIVILEGE ROLES           OWNER  TABLE_NAME COLUMN_NAME
------- --------- --------------- ------ ---------- -----------
U       UPDATE     --> R          SCOTT  DEPT       DNAME      
U       SELECT                    SCOTT  EMP                   
U2      UPDATE     --> R2 --> R   SCOTT  DEPT       DNAME      

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

CREATE TRIGGER tr
   BEFORE INSERT OR UPDATE
   ON T
   FOR EACH ROW
   WHEN (NEW.Y > 0)
DECLARE
   s   NUMBER;
BEGIN
   SELECT SUM (y) INTO s FROM t;

   IF (s + :new.y >= 1000)
   THEN
      raise_application_error (-20001, 'SUM(Y) would exceed 1000');
   END IF;
END;
/

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;
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;

    SUM(Y)
----------
     19000

SQL> select staleness from user_mviews;

STALENESS
-------------------
UNUSABLE

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;

       SUM
----------

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv;

       SUM
----------
         1

SQL> truncate table t;

Table truncated.

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

1 row created.

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

I got this question once again today in a previous post.

What’s wrong by using Toad against a database?

The worst case scenario:
- some non-technical staff is clicking around in your production database with read-write access

The best-case scenario :
- nobody has access to your database

Here is a short list on how you could protect your data :
- Give the right privilege to the right person. DBA role to the DBA, CREATE TABLE/CREATE INDEX to the developer, INSERT/UPDATE/DELETE to the application
- Restrict access to your database server. Use some firewall. Allow only the dba workstation and the application server to the Production environment

What if the end-user PC needs access to the Production database with a powerfull user? This often happend in real world. A fat client is installed on the PC, the password is somehow hardcoded, the privileges granted to the hardcoded user are uterly generous…

It is not a bad practice in this case to block access to the database server to Toad/SQLPLUS and thelike. This will very ineffeciently prevent some garage-hacker from corrupting your database, but it will prevent your sales / marketing colleagues from deleting data, locking tables and degrading performance. This could be done by some login triggers or, my preference, some administrative measures like information, auditting and sanctions.

I wrote about tracking ddl changes with a trigger there : track ddl changes

Another option is to use auditing.

A new and cool alternative is to use enable_ddl_logging (11gR2). This will track all ddl’s in the alert log

ALTER SYSTEM SET enable_ddl_logging=TRUE

Then later you issue

create table t(x number)

and you see in the alertLSC01.log

Tue Apr 05 14:43:32 2011
create table t(x number)

Wait, that’s not really verbose !?

Remember the alert log is just there for backward compatibility, it is time you start looking in the xml file

<msg time='2011-04-05T14:43:42.210+02:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:3937:4222333111' client_id='' type='NOTIFICATION'
 group='schema_ddl' level='16' host_id='srv01'
 host_addr='192.168.0.141' module='TOAD Beta 11.0.0.52' pid='2777799'>
 <txt>create table t(x number)
 </txt>
</msg>

There is not really much more there but the module, which indeed reveals someone is using TOAD to access my database !

Fuadar recently wrote : Grid Control 10.2.0.5 AIX Alert …

Basically, if you have an 10g oms Server (any OS / any release) and aix agents (any release), and according to Note 1171558.1, communication between [10g] Oracle Management Service and [AIX] Management Agents will break due to a default self-signed certificate expiring in 31 Dec 2010.

There is more than one way to solve this

1) you upgrade your oms to 11g. Good luck to do this before end of year…

2) You upgrade your oms to 10.2.0.5, apply patch 10034237 on your oms, create a new certificate, resecure all your agents. Pretty heavy stuff I promise.

3) You use a Third Party Certificate. This may work. I have not tested this for you.

4) You switch from https to http… this is of course not an acceptable workaround as the connection between the agent and the oms will be unsecure, but it may save your Silvester Party.

• allow both secure and unsecure connections to the oms

on all your OMS instances


opmnctl stopall
emctl secure unlock
opmnctl startall

• switch all your agents to http

On all your AIX hosts with an agent installed


emctl unsecure agent -omsurl http://omsserver:4890/em/*


You can find the port for unsecure in your oms server in OMSHOME/sysman/config/emoms.properties under oracle.sysman.emSDK.svlt.ConsoleServerPort.

---

Happy holidays !

---

I wrote about the new defaults in 10gR2 more than 5 years ago
FAILED_LOGIN_ATTEMPTS default to 10 in 10gR2

This new DEFAULT increases the security by preventing the users from trying millions of different passwords. However the locking of application users is noticeably decreasing the database availability

My recommendation :
- create a separate profile for the application users with high availabilty requirement with a default of 1000
- add 2 characters to the password of those accounts

SQL> create user u identified by a4sec2pw;
User created.
SQL> grant create session to u;
Grant succeeded.

“U” is a critical user in your application where account locking would mean downtime !

Let’s try to make the schema 10 times more secure and 100 times more available :

SQL> create profile failed1000 limit failed_login_attempts 1000;
Profile created.
SQL> alter user u identified by a4sec2pwx1 profile failed1000;
User altered.

OK?

I already wrote about jdbc hello world and listener with tcps.

Let’s combine both technologies !
TCPS.java

import java.util.Properties;
import java.security.Security;
import java.sql.*;
import javax.net.ssl.*;

public class TCPS {
  public static void main(String argv[]) throws SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=dbsrv001)(Port=12345))(CONNECT_DATA=(SID=DB01)))";
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
    props.setProperty("javax.net.ssl.trustStore","cwallet.sso");
    props.setProperty("javax.net.ssl.trustStoreType","SSO");
    Security.addProvider(new oracle.security.pki.OraclePKIProvider());
    DriverManager.registerDriver(new oracle.jdbc.driver.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"));
  }
}

I have an auto-login wallet (cwallet.sso) with the trusted certificate from the server.

There are a few jar’s to use:

$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/oraclepki.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_cert.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_core.jar
$ CLASSPATH=$CLASSPATH:.
$ export CLASSPATH
$ javac TCPS.java
$ java TCPS
Hello SSL World

Greatly inspired by Jean de Lavarene’s white paper : SSL With Oracle JDBC Thin Driver

In my current project we are going to move from 9i to 10g really soon. One of the typical issue is to get the right privileges, because the schema owners typically had only CONNECT and RESOURCE, and that does no longer include CREATE VIEW in 10gR2.

I was just reading the 11gR2 readme today :
7.2 UNLIMITED TABLESPACE Privilege Changes
The UNLIMITED TABLESPACE system privilege will be removed from the RESOURCE role in a future Oracle Database release (reference Bug 7614645).

So, probably I should ask for TABLESPACE quotas before we go to 12g

Ftp is seen as an old-time unsecure protocol. Many shops nowadays have switched or are switching to sftp. I will try to point out some differences :

Compatibility: none. the protocol is completly different. Multiple graphical clients however do support both mode. But the basic “ftp” client will not work with sftp.

Ascii mode: only in ftp. In sftp, it is always binary so there will be no conversion. Also no blocksize, recordlength or primary/secondary space for your OS/390 connections.

Interactive mode: similar. you enter your username and password, do cd, put and get. But to quit, by will not work in sftp Use quit or exit instead

Batch mode: different. Most probably you will end up setting a private/public key infrastructure for your ssh connection and use scp (secure copy). If you are using a ssh client like putty, it is possible to do something like pscp -l user -pw password server:file .

Security: sftp is secure, ftp is not.

Speed: ftp is fast, sftp is slow !

Oh NOOOOOOO!!!!! What’s the point is bringing something new if it is slower !!!

Ok, let’s try to download a 100m file:

$ time (echo "open dbsrv01
user oracle secret
bin
get 100m"|ftp -n )

real    0m24.673s
user    0m0.030s
sys     0m0.016s
$ time scp -q oracle@dbsrv01:100m .

real    1m46.978s
user    0m0.108s
sys     0m0.202s

it is about 4x slower! Is there anything we could do about it?

Well, maybe :

$ time scp -q -o Compression=yes oracle@dbsrv01:100m .

real    0m18.634s
user    0m0.748s
sys     0m0.452s

ssh/scp/sftp have a compression mode. If you are transferring your large files across a slow network, this may be an interesting option to consider !

OpenSSH homepage : http://www.openssh.org

As a developer, you sometimes need to contact your dba to get an user trace. As a dba, sending trace files to developer is not much fun.

But how do you get access to your traces without any dba ?

One way could be to set _trace_files_public=true, but this is bad. It is undocumented, it is unsupported, there is no control to who can access the files, and probably it will not help if you have no access to the database server.

I prefer to provide a function that reads from the trace directory.

Ok, here we go :

CREATE DIRECTORY user_dump_dest AS
  '/app/oracle/admin/DB01/udump';

CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
   RETURN VARCHAR2
IS
   dest_loc   CLOB;
   src_loc    BFILE;
   ret        VARCHAR2 (4000);
BEGIN
   src_loc := BFILENAME ('USER_DUMP_DEST', file_name);
   DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
   DBMS_LOB.createtemporary (dest_loc, TRUE);
   DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
   ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
   DBMS_LOB.CLOSE (src_loc);
   RETURN ret;
END;
/

Just a small function that returns the first 4000 characters of the trace file. I could then grant execute on that function to the developers.

it works quite well

SELECT get_tracefile ('db01_ora_6224.trc')
  FROM DUAL;
GET_TRACEFILE('DB01_ORA_6224.TRC')                                      
----------------------------------------------------------------------
/app/oracle/admin/DB01/udump/db01_ora_6224.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /app/oracle/product/dbms/DB01
System name:  SunOS
Node name:  dbsrv01
Release:  5.8
Version:  Generic_117000-05
Machine:  sun4u
Instance name: DB01
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6224, image: oracle@dbsrv01 (TNS V1-V3)

*** SESSION ID:(273.54591) 2009-04-27 12:13:57.292
*** 2009-04-27 12:13:57.292
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17069], [0x3EED05050], 
[], [], [], [], [], []
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
Current SQL statement for this session:
alter PACKAGE "LSC_PKG" compile body
----- PL/SQL Call Stack -----
...

I should mention than granting access to trace files is a security issue as a hacker could dump some security modules. But it will be fine for your trusted developers.

Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema.

CREATE TABLE AUDIT_DDL (
  d date,
  OSUSER varchar2(255),
  CURRENT_USER varchar2(255),
  HOST varchar2(255),
  TERMINAL varchar2(255),
  owner varchar2(30),
  type varchar2(30),
  name varchar2(30),
  sysevent varchar2(30),
  sql_txt varchar2(4000));
   
create or replace trigger audit_ddl_trg after ddl on schema
declare
  sql_text ora_name_list_t;
  stmt VARCHAR2(4000) := '';
  n number;
begin
  if (ora_sysevent='TRUNCATE')
  then
    null;
  else
    n:=ora_sql_txt(sql_text);
    for i in 1..n
    loop
      stmt:=substr(stmt||sql_text(i),1,4000);
    end loop; 
    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
      stmt
    );
  end if;
end;
/

Thanks Yas for your comment, I added the SQL text

I wrote about dba_users changes in 11g .

When spooling alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords.

When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved.

SQL> create user u identified by u;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> connect u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect u/u
Connected.

Per default only the proper case works

SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> connect u/U
Connected.
SQL> conn u/u
Connected.

When sec_case_sensitive_logon=false, both uppercase and lowercase passwords work (10g behavior).

When issuing a create user identified by values, you must chose if you want to have both passwords, only the case insensitive or only the case sensitive.

SQL> select password,spare4 from user$ where name='U';

PASSWORD
------------------------------
SPARE4
--------------------------------------------------------------
18FE58AECB6217DB
S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4

The hashes are in USER$.

SQL> alter user u identified by values '18FE58AECB6217DB';
User altered.
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When only the 10g oracle hash is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4';
User altered.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied

When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is on false, the login failed as there is no 10g string. This setting is probably the most secure setting as the 10g string is not saved in USER$.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4;18FE58AECB6217DB';
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When using both hashes, switching back and forth to 11g mechanism is possible.

This morning I had to solve an ORA-09925: Unable to create audit trail file and it was not as straightforward as usual…

There is a note 69642.1 on Metalink, [edit]which is now up to date for 10gR2[/edit].

1) AUDIT_FILE_DEST is not writable

$ env
_=/usr/bin/env
ORACLE_SID=FOO
TERM=dtterm
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_3
PWD=/u01/app/oracle/product/10.2.0/db_3
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"                
SQL> startup 
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 9925
$ grep -i audit_file_dest $ORACLE_HOME/dbs/*$ORACLE_SID.ora
audit_file_dest=/bar
$ ls -lad /bar
/bar not found
$ su -      
root's Password:
# mkdir /bar 
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"                
SQL> startup 
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
$ su -
root's Password:
# chown oracle /bar
# exit
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"
SQL> startup quiet nomount
ORACLE instance started.
SQL> shutdown abort
ORACLE instance shut down.

2) $ORACLE_BASE/admin/$ORACLE_SID/adump exists and is not writable

$ ls -lad $ORACLE_BASE/admin/$ORACLE_SID/adump  
drwxr-xr-x   2 root   dba .../admin/FOO/adump
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"     

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 09:02:29 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
$ su -
root's Password:
# chown oracle /bar/admin/FOO/adump 
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"     

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 09:02:48 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> quit

3) $ORACLE_HOME/rdbms/audit is not writable

$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
db_name=FOO
$ $ORACLE_HOME/bin/sqlplus -L "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 08:48:09 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
$ ls -lad $ORACLE_HOME/rdbms/audit 
drwxr-x---   2 root     dba ... $ORACLE_HOME/rdbms/audit
$ cd $ORACLE_HOME; su
root's Password:
# chown oracle ./rdbms/audit
# exit
$ $ORACLE_HOME/bin/sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - 
Production on Mon Dec 17 08:49:12 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> quit

By reading Pete Finnigan’s Oracle security weblog today, I discovered that the password is no longer displayed in DBA_USERS in 11g.

select username,password 
from dba_users 
where username='SCOTT';
USERNAME PASSWORD
-------- ------------------------------
SCOTT

select name,password 
from sys.user$ 
where name='SCOTT';
NAME  PASSWORD
----- ------------------------------
SCOTT F894844C34402B67

on the one hand, it is good for the security.

On the other hand, it is a huge change which is not documented (I immediately sent comments to the Security and Reference book authors) and it will make a lot of script failing (scripts that use to change the password to log in and change it back to the original value afterwards).

Protecting the hash is extremely important, check your scripts for 11g compatibility!

First you need to install OID. Check the Installation Guide, the Doc and download the Software. If you do not need the dbconsole, stop it (emctl stop dbconsole) and remove the oracle_home/hostname_sid directory

Once you have a running OID, test it with ldapsearch. For this workshop, I use two servers and two usernames. Having the client and the server sharing the same wallet is not a good idea. If you have an Oracle Database running as user oracle, prefer using a different user for OID installation. By installing OID, I specified the following staticports.ini (missing from CD, bug 5936042) :

Oracle HTTP Server port  = 44000
Oracle HTTP Server Listen port  = 44001
Oracle HTTP Server SSL port  = 44002
Oracle HTTP Server Listen (SSL) port  = 44003
Oracle HTTP Server Diagnostic port  = 44004
Java Object Cache port  = 44005
DCM Discovery port  = 44006
Oracle Notification Server Request port  = 44007
Oracle Notification Server Local port  = 44008
Oracle Notification Server Remote port  = 44009
Application Server Control port  = 44010
Application Server Control RMI port  = 44011
Oracle Management Agent port  = 44012
Log Loader port  = 44013
ASG port  = 44014
Oracle Internet Directory port  = 44015
Oracle Internet Directory (SSL) port  = 44016
Oracle Certificate Authority SSL Server 
    Authentication port  = 44017
Oracle Certificate Authority SSL Mutual
    Authentication port  = 44018

Ok, let’s do the search from another server

lsc@dbserver01 $ ldapsearch -h oidserver01 -p 44015 -z 1
orclreplicaid=oidserver01_oid1014,cn=replication configuration
ldap_search: Sizelimit exceeded

Now let’s try with SSL. First, with no authentication (-U 1).

lsc@dbserver01 $ ldapsearch -h oidserver01 -p 44016 -z 1 -U 1
orclreplicaid=oidserver01_oid1014,cn=replication configuration
ldap_search: Sizelimit exceeded

Fine. Let’s create the wallets. You need a wallet for your client (lsc@dbserver01), a wallet for your server (ldapusr@oidserver01). Create a certification request for CN=lsc,cn=users,dc=yourdomain,dc=com, export your user certificate from your client wallet (lsc@dbserver01) and import it as a trusted certificate in your oid wallet (ldapusr@oidserver01). It is the same procedure as described in the user identified externally with SSL certificate post

Then, launch oidadmin (you can launch it from a pc client), and configure the OID for ssl.
User for OID is CN=ORCLADMIN
In Oracle Internet Directory Servers –> cn=orcladmin@oidserver01 –> Server Management –> Directory Server. Right click on Configuration Set1 and chose Create Like. In the Configuration Set2, chose the SSL Settings, specify SSL Client and Server Authentication, SSL only, file://etc/ORACLE/WALLETS/lsc, SSL Port 44019. Apply. Quit

Start a second instance with oidctl where oid1014 is your SID

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
    instance=2 configset=2 start

Check the status :

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
    status
oidctl:Checking Oracle Internet Directory Processes ...

    Process oidmon is alive as PID = 16191 

    Checking OIDLDAPD instance 1  ...
    Process oidldapd (dispatcher) is alive as PID = 16197
        port=44015 sslport=44016
            oidldapd (server)     is alive as PID = 16206

    Checking OIDLDAPD instance 2  ...
    Process oidldapd (dispatcher) is alive as PID = 16422
        sslport=44019
            oidldapd (server)     is alive as PID = 16426

    Checking ODISRV instance 1  ...
    Process odisrv   is alive as PID = 16203

so far not bad!

Let’s pray !

$ ldapbind -h novgaasdv01.eu.novartis.net -p 44019  -U 3
    -P mywalletpasswd -W file://etc/ORACLE/WALLETS/lsc  
    -D cn=orcladmin -w myorcladminpasswd
bind successful

What a satisfaction I have being failing on this for days. Mostly getting meaningless message like UnKnown Error Encountered. You cannot start anything with that error. It could be a wallet path error, a wallet password error, a non-authorized certificate, a certification authority problem, and many other errors. Really poor error messaging there. One may argue that meaningless error message on unsuccessful login does increase the security, but well, it is a nightmare to debug

Now I need to stop the non-secure part of it :

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
    instance=1 stop

Added 29.3.2007
If I want to use a SSL to authentify my user, I must create a user, for example with the Security Console http://oidserver01:44000/oiddas/ui/oideushome, which matches my certificate.

Maybe you have a firewall in your company, only your PC can access the production server, only you have the root password in prod, and your company spend a lot of money in security, but you dare using xhost +.

This is a huge security hole, because it gives anyone access to your X resources, not only your display, but also your mouse and your keyboard, so anyone can read/modify/corrupt what you are typing/clicking. It is a bad habit to use xhost+. Even using xhost +localhost give access to your keyboard to anyone on localhost…

So what else could you use ?

The simplest is probably ssh tunnelling.

lsc@chltlxlsc:~> ssh -X oracle@localhost
Last login: Fri Max  2 10:24:09 2007 from localhost
oracle@chltlxlsc:~> xterm -e true && echo success
success

Another way to do this is to use X cookies.

lsc@chltlxlsc1:~&gt; xauth extract ~/my-x-cookie $DISPLAY<
lsc@chltlxlsc1:~&gt; setfacl -m u:oracle:r ~/my-x-cookie
lsc@chltlxlsc1:~&gt; su - oracle -c "DISPLAY=$DISPLAY bash"
Password:
oracle@chltlxlsc1:~&gt; xterm -e true && echo success
Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified

xterm Xt error: Can't open display: :0.0
oracle@chltlxlsc1:~&gt; xauth merge ~lsc/my-x-cookie
xauth:  creating new authority file ~oracle/.Xauthority
oracle@chltlxlsc1:~&gt; xterm -e true && echo success
success

No need to type all. Here is my alias

alias oracle='
  xauth extract $HOME/my-x-cookie $DISPLAY;
  setfacl -m u:oracle:r $HOME/my-x-cookie;
  su - oracle -c "export DISPLAY=$DISPLAY;
    xauth merge $HOME/my-x-cookie; 
    bash"'

Today I configured my database to identify users with certificates.

Check my previous post listener with tcps to find out how to configure a listener with SSL, which is a requisite.

Ok, I have a listener.ora and a tnsnames.ora with SSL. I do not need a sqlnet.ora, the default values work.
listener.ora

LISTENER=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=chltlxlsc1)(PORT=1521))
    )
    (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCPS)(HOST=chltlxlsc1)(PORT=15210))
    )
  )

tnsnames.ora

LSC07=
  (DESCRIPTION=
    (ADDRESS_LIST=(
      (ADDRESS=(PROTOCOL=tcp)(HOST=chltlxlsc1)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcps)(HOST=chltlxlsc1)(PORT=15210))
    )
    (CONNECT_DATA=
      (SERVICE_NAME=LSC07.lcsys.ch)
    )
  )

sqlnet.ora

# empty file

Now I have one user, lsc, which is going to connect with its own certificate. First, I need to create a wallet for that user. I run owm as lsc, I create a new wallet, a new certification request with the DN:CN=lsc, I copy/paste my certification request in the CA server, I import the user certificate, and I save it in the system default, /etc/ORACLE/WALLETS/lsc.

Now I need to trust that certificate, so I export my user certificate to a file, and make that file readable for oracle. As oracle, I run the wallet manager owm, I import the trusted user certificate, and I save in system default, /etc/ORACLE/WALLETS/oracle.

One more thing I need to configure now is the wallet location for lsc in the sqlnet parameter file. To avoid conflicts, I create a specific sqlnet for that user in homedir called .sqlnet.ora (with a leading dot).

~lsc/.sqlnet.ora

WALLET_LOCATION=
  (SOURCE=
    (METHOD=FILE)
    (METHOD_DATA=
      (DIRECTORY = /etc/ORACLE/WALLETS/lsc)
    )
  )

Last thing to do : create the user in sqlplus.

SQL> show parameter os_authent_prefix

NAME               VALUE
------------------ ------------------------------
os_authent_prefix  ops$

SQL> create user ops$lsc identified externally as 'CN=lsc';

User created.

SQL> grant create session to ops$lsc;

Grant succeeded.

Now I can remotely log on my database without a password.

SQL> connect /@LSC07
Connected.

yesterday I wrote howto listener ssl.

To check the protocol :
SQL> select sys_context('USERENV','NETWORK_PROTOCOL') PROTOCOL from dual;

PROTOCOL
--------
tcps

How can you use SSL to encrypt your network traffic?

Here is how I did it.

1. Install Oracle Certification Authority 10.1.4

you need a CA to approve a certification request

2. Install Oracle Database 10gR2 Enterprise Edition with Advanced Security Options
3. Start Wallet Manager

from Database Oracle Home, start $ORACLE_HOME/bin/owm

4. create a new Wallet

define a password

5. add certificate request

fill the fields or chose advanced :
CN=yourhost,DC=yourdomain,DC=com

6. Start OCA

from OCA home, start $ORACLE_HOME/oca/bin/ocactl start

7. Open OCA homepage

Open your browser on

https://yourhost.yourdomain.com:6600/oca/user

Install the ROOTca in your browser

8. Request a new certificate

Server/SubCA Certificates – Request a certificate – Paste String from OWM

9. Approve the certificate

log on the admin page,

https://yourhost.yourdomain.com:6600/oca/admin

define your identity, then in Certificate Management, select the certification request and approve it.

10. Import the certificate in OWM

Select the approved certificate, view details.
In owm, import user certificate , and copy paste the BASE64 string.
To get the CA string, download the CA certificate from

https://yourhost.yourdomain.com:6600/oca/certDownload

11. Save the wallet

The certificate should show [Ready]
Select Autologin
Save in system default (/etc/ORACLE/WALLETS/oracle)

Ok, we are now ready to use the TCPS protocol

listener.ora
LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=<b>TCPS</b>)
(HOST=yourhost.yourdomain.com)(PORT=1521)))

tnsnames.ora
LSC01=(DESCRIPTION=(ADDRESS=(PROTOCOL=<b>TCPS</b>)
(HOST=yourhost.yourdomain.com)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=LSC01)))

Start the listener
$ lsnrctl start
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=<b>TCPS</b>)
(HOST=yourhost.yourdomain.com)(PORT=1521)))
The command completed successfully

Test it!
$ sqlplus scott/tiger@lsc01
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
SQL>

If I have a limited number of db clients which are authorized to access my listener, I can restrict access to my listener by setting only two parameters in sqlnet.ora.

TCP.VALIDNODE_CHECKING = yes
TCP.INVITED_NODES = (dbclient001,chltlxlsc1)

chltlxlsc1 is my db server, I include it in the list, it is required to start the listener locally.

From dbclient001, I can connect :

$ sqlplus scott/tiger@lsc02

SQL*Plus: Release 10.1.0.4.2 - Production on Wed Nov 22 09:47:43 2006

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

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

From dbclient002, I cannot connect

$ sqlplus scott/tiger@lsc02

SQL*Plus: Release 10.1.0.4.2 - Production on Wed Nov 22 09:48:26 2006

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

ERROR:
ORA-12547: TNS:lost contact

I recently posted about Oracle Password Repository (OPR).

I did get a comment from Andreas Piesk about something similar in Oracle, the wallet.

Let’s do a quick test.

First, I create a .sqlnet.ora in my home directory (I do not want to mess up the system-wide sqlnet.ora).

$ cat /home/lsc/.sqlnet.ora
SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/lsc)))

I now create the wallet

$ mkstore -create -wrl /home/lsc
Enter password:
Enter password again:

and the credentials

$ mkstore -wrl /home/lsc -createCredential LSC01 scott tiger
Enter password:
Create credential oracle.security.client.connect_string1

now I try to login

$ sqlplus /@LSC01

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:23:35 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> sho user
USER is "SCOTT"

This sounds to work very nicely. Let’s see if this is more secure than OPR :

$ mkstore -wrl /home/lsc -list
Enter password:

Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.connect_string1
Enter password:
oracle.security.client.connect_string1 = LSC01
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.username1
Enter password:
oracle.security.client.username1 = scott
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.password1
Enter password:
oracle.security.client.password1 = tiger

Definitely! The password is not reveal, unless you know the password of the wallet. Remember in OPR, the application had direct access to the password. Here it is not the case, if you do not know the password of the wallet, you may login, but you cannot find out what the password is. I like this very much.

of course do not forget to protect your wallet

Ok, what I did not achieve until yet is logging in externally when using wallet_override :

$ mv /home/lsc/.sqlnet.ora /home/lsc/.sqlnet.ora.disable
$ sqlplus /

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:35:56 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> sho user
USER is "OPS$LSC"
$ mv /home/lsc/.sqlnet.ora.disable /home/lsc/.sqlnet.ora
$ sqlplus /

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:37:13 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

I will update this if I find out how to login externally too.

I checked this tool today :
http://sourceforge.net/projects/opr

This tool provide a simple way of not hardcoding passwords in shell scripts. Hardcoding passwords in shell scripts is a bad practice. The source code may be shared by many developers, may resides on unsecure servers (CVS), may be printed, etc… The passwords may change often too.

This tool uses a simple file to stores the passwords encrypted. Well, I urge you to secure this file to make it non-accessible for others.

I am not going to decode nor rate the encryption algorythm, the fact is, you do not have clear text passwords. It could be also possible to have clear text password in a separate text file, which would not be that less secure, but it is ugly.

To make it clear (I hope), it is a 2-ways encryption, so if you have access to the file and the source code of the algorythm, you can crack the password. Sounds weak? Well, Oracle Proxy Users with Internet Directory, Application Server, Portal and all those products are not much different. The only 100% passwordless solution I am aware of is the external OS identification.

Well, that said, let’s look how it works.

# ./configure
# make
# make install

let’s create the repository

 
$ export OPRREPOS=$ORACLE_HOME/dbs/oprrepos
$ opr -c

store the password for scott on LSC01

$ opr -a LSC01 SCOTT lsc
please enter the password :
please re-enter the password :
entry (LSC01, scott, lsc) added.

let’s test

$ sqlplus scott/$(opr -r LSC01 SCOTT)@LSC01

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jul 25 13:51:48 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

This is quite convenient. I will use this to store the RMAN password to connect to the RMAN repository in my backup scripts

I read in blog from Pete Finnigan about the potential security hole in DBMS_SCHEDULER package.

DBMS_SCHEDULER as a new alternative for DBMS_JOB by Patrick Sinke

Note that on some OS, like AIX5L / oracle 10.2.0.2, the job runs as ORACLE, not as NOBODY

Pete Finnigan just mentioned a bug, which allow any user in any oracle version to get dba privilege Imperva discovers a critical access control bypass in login bug.

This is incredible!

Well, Pete urged you to apply CPU2006January asap.

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION.

Except SYS, SYSTEM and DBSNMP

To minimize this security problem, I implemented the following strategy on my test system.

1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE
2) alter user SYSTEM account lock;
3a) in 10gR2 :
alter user dbsnmp quota 1T on sysaux;
create role secure_oem_role;
grant advisor, analyze any, analyze any dictionary, create job, create procedure, create session, create table, manage any queue, select any dictionary to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_AQ” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_AQADM” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_DRS” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_MONITOR” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_SERVER_ALERT” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_SYSTEM” to secure_oem_role;
grant EXECUTE on “SYS”.”DBMS_WORKLOAD_REPOSITORY” to secure_oem_role;
exec SYS.DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(‘DEQUEUE’, ‘ALERT_QUE’, ‘SECURE_OEM_ROLE’)
revoke EXECUTE ON “SYS”.”DBMS_SERVER_ALERT” from dbsnmp;
revoke EXECUTE ON “SYS”.”DBMS_SYSTEM” from dbsnmp;
revoke UNLIMITED TABLESPACE from dbsnmp;
revoke SELECT ANY DICTIONARY from dbsnmp;
revoke CREATE PROCEDURE from dbsnmp;
revoke CREATE TABLE from dbsnmp;
revoke OEM_MONITOR from dbsnmp;
grant secure_oem_role to dbsnmp;
3b) in other versions, you probably can remove more and grant less, I think only in 10g it is necessary to have “quota”. In my other databases, dbsnmp have 0 segments.

Check what system privileges are potentially dangerous to the system :

select path
from
(
select
grantee,
sys_connect_by_path(privilege, ‘:’)||’:'||grantee path
from (select grantee, privilege, 0 role from dba_sys_privs union all select grantee, granted_role, 1 role from dba_role_privs)
connect by privilege=prior grantee
start with role=0
)
where
grantee in (
select username from dba_users
where lock_date is null
and password != ‘EXTERNAL’
and username != ‘SYS’)
or grantee=’PUBLIC’
/
:ADVISOR:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY:SECURE_OEM_ROLE:DBSNMP
:ANALYZE ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP
:CREATE JOB:SECURE_OEM_ROLE:DBSNMP
:CREATE PROCEDURE:SECURE_OEM_ROLE:DBSNMP
:CREATE SESSION:USER1
:CREATE SESSION:USER2
:CREATE SESSION:SECURE_OEM_ROLE:DBSNMP
:CREATE TABLE:SECURE_OEM_ROLE:DBSNMP
:MANAGE ANY QUEUE:SECURE_OEM_ROLE:DBSNMP
:SELECT ANY DICTIONARY:SECURE_OEM_ROLE:DBSNMP

it sounds better…

I just tried today to limit power of rman :

REVOKE ALTER SESSION, CREATE DATABASE LINK FROM RECOVERY_CATALOG_OWNER;

It seems I can still do a backup… probably those privilege are not needed by rman, maybe just inherited from Connect in an older released !?

There a few major changes in the database administration and the database security between 9i and 10g.

In 9i, I used to grep in the listener.ora to find out the password.

LISTENER_LSC61 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200))
))
PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF

this 64bit encrypted string can be used in 9i to stop the listener

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.6.0 – Production on 05-DEC-2005 14:33:51

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener listener_lsc61
Current Listener is listener_lsc61
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)))
The command completed successfully

As a dba, it is quite handy, because you can use grep (or awk) to find out the password out of the listener.ora. As a security admin, you should make sure the listener.ora is not readable. Note that the default, when created by netmgr, is to be world-readable

However, this does no longer work in 10g

LISTENER_LSC62 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC62 = 1234567890ABCDEF

the encrypted string can no longer be used

$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 – Production on 05-DEC-2005 14:37:24

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener listener_lsc62
Current Listener is listener_lsc62
LSNRCTL> set password 1234567890ABCDEF
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10062)(QUEUESIZE=200)))
TNS-01169: The listener has not recognized the password
TNS-01189: The listener could not authenticate the user

As a security admin, you would think it is better so. But, how are you going to stop the listener in your script? Well, in 10g, we can use local authentification (default). So if the script is started as oracle, we would not need to use password

LISTENER_LSC63 =
(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200))
))

PASSWORDS_listener_LSC63 = 1234567890ABCDEF

$ whoami
oracle
$ hostname
dbsrv85a.ex.zkb.ch
$ lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.1.0 – Production on 05-DEC-2005 14:43:33

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set current_listener LISTENER_LSC63
Current Listener is LISTENER_LSC63
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10016)(QUEUESIZE=200)))
The command completed successfully

I read in an Alex Kornbrust post on Pete Finnigan forum, that a LOCAL_OS_AUTHENTICATION “undocumented” parameter could be used to “avoid” local authentication, but in that case, it is going to be a nightmare to “stop” the listener in an automated script, well, we can still use “kill”, but it is not very beautifoul.

I wrote a mini script to protect my customer from being attacked by an “oracle voyage worm” variant :

revoke CREATE DATABASE LINK from CONNECT;
revoke ALL on SYS.UTL_FILE from PUBLIC;
revoke ALL on SYS.UTL_HTTP from PUBLIC;
revoke ALL on SYS.UTL_SMTP from PUBLIC;
revoke ALL on SYS.UTL_TCP from PUBLIC;
grant EXECUTE on SYS.UTL_FILE to XDB;
grant EXECUTE on SYS.UTL_HTTP to MDSYS;
grant EXECUTE on SYS.UTL_HTTP to ORDPLUGINS;
@?/rdbms/admin/utlrp

Than, in OEM 10g, check for policy violations.

I added a few grants to special oracle internal users, to avoid invalid objects, which is also a policy violation in OEM… OEM will report a violation if those accounts are not locked and expired