Categories
12c dba security

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';
SPARE4
----------------------------------------------------------------
S:96A5FF65BFF84D7AAC6F8F00879881E8506FE57F555E5BA2927B606DC4F1

SQL> alter user scott identified by tiger;

User altered.

SQL> select spare4 from user$ where name='SCOTT';
SPARE4
----------------------------------------------------------------
S:AE23FB94A462C44A75040CE3BA731E3EF08C4A270F5940491045CBCEF63C

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

Categories
dba security sql Uncategorized

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.

Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE

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' ;
begin
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;
end;
/

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.

Categories
security unix windows

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 192.168.0.1 (RX1303)
[Yes][No]

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

Categories
security

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
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 ๐Ÿ™‚

Categories
11g 11gR2 java ldap security sql developer toad

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
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.(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.(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!

Categories
11g 11gR2 dba security

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 !

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

Categories
dba security sql Uncategorized

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

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

Categories
security sql

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

Categories
dba security sql developer sqlplus toad

On using Toad against a database

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.

Categories
dba security sql

track ddl change (part 2)

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 ๐Ÿ™‚



create table t(x number)


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

Categories
Enterprise Manager installation security

[alert] Oracle agents on AIX may not work in 2011 with OMS10g

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 !


Categories
dba security sql

On FAILED_LOGIN_ATTEMPTS

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?

Categories
java security sql

jdbc ssl

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

Categories
dba security

CONNECT no longer has CREATE VIEW, what’s next?

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 ๐Ÿ˜‰

Categories
linux security unix

to ftp or to sftp

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

Categories
dba security

grant access to trace files

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.

Categories
dba security sql

track DDL changes

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

Categories
11g Blogroll dba security sql

alter user identified by values in 11g

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.

Categories
Blogroll dba security

How to resolve ORA-09925 ?

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

Categories
11g Blogroll security

the password is not longer displayed in dba_users.password in 11g

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!

Categories
Blogroll ldap oid security

Configure OID with SSL

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.

Categories
installation linux security

xhost + is a huge security hole

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@chltlxlsc1:$ ssh -X oracle@localhost
Last login: Fri Max 2 10:24:09 2007 from localhost
oracle@chltlxlsc1:$ if xterm -e true; then echo success; fi
success

Another way to do this is to use X cookies.

lsc@chltlxlsc1:$ xauth extract ~/my-x-cookie $DISPLAY< lsc@chltlxlsc1:$ setfacl -m u:oracle:r ~/my-x-cookie lsc@chltlxlsc1:$ su - oracle -c "DISPLAY=$DISPLAY bash" Password: oracle@chltlxlsc1:$ if xterm -e true; then echo success; fi Xlib: connection to ":0.0" refused by server Xlib: No protocol specified xterm Xt error: Can't open display: :0.0 oracle@chltlxlsc1:$ xauth merge ~lsc/my-x-cookie xauth: creating new authority file ~oracle/.Xauthority oracle@chltlxlsc1:$ if xterm -e true; then echo success; fi 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"'

Categories
dba security

user identified externally with SSL certificate

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.
Categories
dba security

check if using tcp or tcps

yesterday I wrote howto listener ssl.

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

Categories
dba security

listener with 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
  2. you need a CA to approve a certification request

  3. Install Oracle Database 10gR2 Enterprise Edition with Advanced Security Options
  4. Start Wallet Manager
  5. from Database Oracle Home, start $ORACLE_HOME/bin/owm

  6. create a new Wallet
  7. define a password

  8. add certificate request
  9. fill the fields or chose advanced :
    CN=yourhost,DC=yourdomain,DC=com

  10. Start OCA
  11. from OCA home, start $ORACLE_HOME/oca/bin/ocactl start

  12. Open OCA homepage
  13. Open your browser on
    https://yourhost.yourdomain.com:6600/oca/user
    Install the ROOTca in your browser

  14. Request a new certificate
  15. Server/SubCA Certificates – Request a certificate – Paste String from OWM

  16. Approve the certificate
  17. 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.

  18. Import the certificate in OWM
  19. 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

  20. Save the wallet
  21. 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=TCPS)
(HOST=yourhost.yourdomain.com)(PORT=1521)))

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

Start the listener
$ lsnrctl start
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)
(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>

Categories
Blogroll dba security

Restrict network access to listener

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

Categories
Blogroll dba security

sqlnet.wallet_override=true

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.

Categories
Blogroll dba security

Oracle Password Repository

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

Categories
Blogroll dba security

dbms_sheduler jobs

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

Categories
Blogroll dba security

security bug revealed

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.

Categories
Blogroll dba OEM security

lock system, restrict dbsnmp

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…