After vanquishing the map error yesterday, we got to the real problem:

sqlplus user@mydb
SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 30 12:53:53 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
Error while trying to retrieve text for error ORA-12154

When I searched for this error in Metalink and Google, I found references to Pro-C, PHP and VB, but nothing related to SQLPLUS. In a moment of weakness, I decided to run changePerm.sh.

Before running it I did an ls -lR $ORACLE_HOME > before

After running it I did an ls -lR $ORACLE_HOME > after

Then I ran diff before after and discovered 750 changes had been made, including opening permissions to OPatch and other directories that really didn’t seem like they should be open to the world. Oracle did give me fair warning when I started the script.

$> ./changePerm.sh $ORACLE_HOME

——————————————————————————-
Disclaimer: The purpose of this script is to relax permissions on some of the
files in the database Oracle Home so that all clients can access them.
Please note that Oracle Corporation recommends using the most restrictive file
permissions as possible for your given implementation. Running this script
should be done only after considering all security ramifications.
——————————————————————————-

But I was feeling the pressure to “fix it”. I then spent hours changing permissions back to their original setting on most of the directories under $ORACLE_HOME, and along the way I discovered that removing access to $ORACLE_HOME/ldap brought the error back. Aha! Here is my revised list of the minimum permission needed for an Oracle client.

chmod 755 /opt/oracle
chmod 755 $ORACLE_HOME
chmod 755 $ORACLE_HOME/jdbc
chmod 755 $ORACLE_HOME/jdbc/lib
chmod 755 $ORACLE_HOME/ldap
chmod 755 $ORACLE_HOME/ldap/mesg
chmod 644 $ORACLE_HOME/ldap/mesg/*
chmod 755 $ORACLE_HOME/network
chmod 755 $ORACLE_HOME/network/admin
chmod 755 $ORACLE_HOME/sqlplus
chmod 755 $ORACLE_HOME/sqlplus/mesg
chmod 644 $ORACLE_HOME/sqlplus/mesg/sp1us.msb
chmod 755 $ORACLE_HOME/nls
chmod 755 $ORACLE_HOME/nls/data
chmod 644 $ORACLE_HOME/nls/data/lx1boot.nlb
chmod 755 $ORACLE_HOME/oracore
chmod 755 $ORACLE_HOME/oracore/zoneinfo
chmod 644 $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

The really odd thing is that I still have many clients that have the ldap directory locked down, and they are running without problems, even though they are using ldap for name resolution. I don’t get it, but it’s late Friday evening, and I don’t need to get it. I just need to go home!

The default permissions on the Oracle 10gR2 client after installation make the binaries unavailable to anyone outside the DBA group. What a great idea! I’ve been looking for ways to eliminate end users for years. I’m constantly advocating their termination whenever cost-cutting is required, but so far these suggestions have gone unappreciated. If I can’t eliminate them, I suppose I can’t completely lock them out of the database, either. Too bad.

Since our security policy requires that we always grant the minimum privilege needed to perform the job, I have discovered that the following permissions achieve that minimum:

chmod 755 /opt/oracle
chmod 755 $ORACLE_HOME
chmod 755 $ORACLE_HOME/jdbc
chmod 755 $ORACLE_HOME/jdbc/lib
chmod 755 $ORACLE_HOME/sqlplus
chmod 755 $ORACLE_HOME/sqlplus/mesg
chmod 755 $ORACLE_HOME/sqlplus/mesg/sp1us.msb
chmod 755 $ORACLE_HOME/nls
chmod 755 $ORACLE_HOME/nls/data
chmod 755 $ORACLE_HOME/nls/data/lx1boot.nlb
chmod 755 $ORACLE_HOME/oracore
chmod 755 $ORACLE_HOME/oracore/zoneinfo
chmod 755 $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

This has been working rather well for months, but recently we’ve had complaints of the following error when running SQLPLUS from the client machine (if users weren’t such complainers, I might not be so anxious to have them removed from the equation)

$ sqlplus
1220822 : map : Permission Denied
1220822 : map : Permission Denied
1220822 : map : Permission Denied

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 29 11:50:20 2008

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

Enter user-name: diva@mydb

The errors don’t prevent access, but they are annoying. When I searched Google for the message, I found no hits, which is what inspired me to write this post. If you are reading it, perhaps you also searched Google and found exactly one link.

The reason for the message is that the default permission on the SQLPLUS binary is 751 (rwxr-x–x). Changing the permission to 755 (rwxr-xr-x) makes the messages go away. It doesn’t make the users go away though. In my case eliminating the message just eliminated a red herring, but at least it led to the user telling me what the real problem was, instead of just complaining about the message.

As you probably know, Oracle provides a changePerm.sh script in the install directory, which relaxes permissions on Oracle directories and files. The ORAFAQ site (http://www.orafaq.com/wiki/ChangePerm_sh) suggests that the permissions will be too relaxed and that the script should only be run as a last resort. For now, I’m adding the SQLPLUS binary to my list of relaxed permissions.

chmod 755 $ORACLE_HOME/bin/sqlplus

If you support an Oracle database that is used as a resource manager for BEA WebLogic or IBM’s WebSphere products, chances are someone has come to you with a document that says you need to grant them execute on SYS.DBMS_SYSTEM. When this happens, be a Diva and tell them no. This usually puts customers into shock. It’s fun to see the looks on their faces.

The reason they are asking is because of a feature of the xarecover function. If there is a system crash during a distributed transaction, the xarecover function will attempt to clean up. Part of the clean up is a call to DIST_TXN_SYNC, a procedure that is included in the SYS.DBMS_SYSTEM package. The problem is that there are many procedures in this package, and granting the execute privilege gives access to all of them. Oddly enough, the xarecover function comes from the Oracle JDBC, so if you are looking for someone to blame for this, the culprit is Oracle, not IBM or BEA.

The good news is that Oracle has redeemed themselves by moving the DIST_TXN_SYNC procedure to a new package called DBMS_XA that is available to the public. The even better news is that they have made a patch available so you can install it in 10.2.0.2 or 10.2.0.3. It will be included in 10.2.0.4. Because the call comes from the JDBC libraries on the client machine, you have to patch both the DBMS and the client to make it work. The patch numbers are 5892995 (client) and 5945463 (DBMS).

For more information about XA, read the following white papers. You will find the information quite valuable.

http://www.techworld.com/whitepapers/index.cfm?whitepaperid=3139
http://www.oracle.com/technology/products/database/clustering/pdf/bestpracticesforxaandrac.pdf

Here is a link to the DBMS_XA documentation in Oracle 11.1 from the PSOUG web site

http://www.psoug.org/reference/dbms_xa.html

Most large organizations have a security policy, which is a good thing. Blind adherence to the policy is not. Typically, security policies are written with the end user in mind, and more often than not they are written for servers, and then applied everywhere. Policies that work well for end users logging into a server could be problematic for accounts used for automated authentication to a database.

The first problem that comes to mind is password expiration. Having a 90 day password expiration policy for end user accounts is a very good idea. It is even a good idea for end user accounts within your database. It may not be a good idea for accounts that are used to authenticate your web applications. These values are often cached, and an outage is often required to change the password and clear the old one from cache. Can your application sustain an outage every 90 days? More importantly, if you have set PASSWORD_LIFE_TIME to 90 days for the account profile, what will happen to your application when day 90 arrives? If you aren’t proactive about changing the password before the expiration data, chances are the application will fail, and you will be limited to logging in with SQLPLUS. If you are going to use PASSWORD_LIFE_TIME for these types of accounts (which I don’t necessarily recommend), set it for a high value like 366, and make changing the database password part of every software release.

The next problem that comes to mind is account locking after failed attempts. The default value of this setting used to be UNLIMITED, but as of 10gR2 the default value is 10. As I said in the profile tutorial, this is a brilliant strategy for end user accounts, but a terrible idea for application accounts, as it sets you up for a denial of service attack. The idea that you need to lock an account when it is being attacked is hard for some people to let go of. When I explained the concept to my security team, they kept suggesting that we raise the number of attempts. How about 50 or 100? The problem is that it is very easy to set up an automated attack to launch that many failed logins. It can happen in a matter of seconds. Even if you only lock the account for a minute or two, the attacker can lock up the account faster than you can type alter user account unlock;

So what do you do? To start with, be sure to use long, strong passwords. I mean really ugly passwords with 12-16 characters, and a good variety of alpha, numbers with a special character or two. If you are passing it from an application, does it really matter if the password is ys6Pklm-3uq0vm?

Next, (and this is just as important) be sure you enable auditing of failed login attempts. If you don’t know how, check out the Audit tutorial. Once you have enabled auditing, actively monitor the DBA_AUDIT_TRAIL. You can do this with an Enterprise Manager or Grid Control alert, but if you do, be sure that you have a strong password (and no account locking) for DBSNMP, or your attacker may be able to disable your effort. If you use Enterprise Manager for most of your monitoring/management activities, by all means set up the alert, but don’t rely on it as your only defense. Schedule a job to run every few minutes and alert you if any account has experienced an excessive number of failed login attempts.

declare
vSender VARCHAR2(30) := ‘oracle@dbserver’;
vRecip varchar(40) := ‘thediva@database-diva.com’;
vSub varchar(40) := ‘Excessive failed login attempts’;
vMType VARCHAR2(30) := ‘text/plain; charset=us-ascii’;
vMsg varchar2(80);
cursor fl_cursor is
select username, count(username) fail_count from dba_audit_trail
where returncode=1017 and timestamp > sysdate - 6/1440
group by username order by count(username);
userinfo fl_cursor%ROWTYPE;
begin
open fl_cursor;
loop
fetch fl_cursor into userinfo;
exit when fl_cursor%NOTFOUND;
if userinfo.fail_count > 19 then
vMsg := userinfo.fail_count||’ failed login attempts by user ‘||userinfo.username;
sys.utl_mail.send(vSender,vRecip,null,null,vSub,vMsg,vMtype,null);
end if;
end loop;
close fl_cursor;
end;

To create the user-defined metric in Enterprise Manager, choose a query with metric type number with SQL output 2 columns. Use the following query:

select username, count(username) from dba_audit_trail
where returncode=1017 and timestamp > sysdate - 1/240
group by username order by count(username) ;

Choose a value for your warning and critical thresholds, and set a schedule.