Category Archives: 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"'

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&gt; show parameter os_authent_prefix

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

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

User created.

SQL&gt; grant create session to ops$lsc;

Grant succeeded.

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

SQL&gt; connect /@LSC07
Connected.

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=<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&gt;

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

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&gt; 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&gt; 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.

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

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…

encrypted listener password

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.

oracle voyage worm

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

FAILED_LOGIN_ATTEMPTS part 2

Ref: part 1
I reported this lack of documentation on http://forums.oracle.com/forums/thread.jspa?threadID=330359

Here is my test case (take care, it will create a new db!) :

SQL&gt; startup force quiet nomount;
ORACLE instance started.
SQL&gt; create database controlfile reuse extent management
local default tablespace users default temporary tablespace temp
undo tablespace undotbs1;

Database created.
SQL&gt; @?/rdbms/admin/catalog
SQL&gt; @?/rdbms/admin/catproc
SQL&gt; col username for a10
SQL&gt; col PROFILE for a7
SQL&gt; col LIMIT for a12
SQL&gt; select username, profile, limit from dba_users join 
dba_profiles using (profile) 
where resource_name='FAILED_LOGIN_ATTEMPTS';
USERNAME   PROFILE LIMIT 
---------- ------- ------------ 
SYSTEM     DEFAULT 10
SYS        DEFAULT 10
TSMSYS     DEFAULT 10
DIP        DEFAULT 10
DBSNMP     DEFAULT 10
OUTLN      DEFAULT 10