Security Presumptive

We have the auditors currently visiting the office.  They don't mix with anyone at all, from what I can see, apart from the one man that is the 'go between'.  They work out of a meeting room not very far away from my desk.

I feel it necessary to say before I tell you why I'm telling you about this that I work in a very secure building.  It is surrounded by high wire fences,  cameras and sound detectors.  A secure site within an already secure site.  It's safe to say it is secure!  There are very few people that can get in and generally we all know each other fairly well,  if not intimately then at least by sight or on "you left your card in the coffee machine" terms.

To get to my internal office door you have to go through 3 sets of secure doors requiring a personal level of access and also past the glass window of the 24 hours manned security office.  It's safe to say by the time you get to my office door you have passed security.  There is nothing in my office of merit or value to be secured!  There used to be something behind the door to secure.  That thing is now many, many miles away.  Any security left on my office door is there because a jobsworth didn't take it away with 'the thing'.

So back to my auditor non-interactive-'friend'.  Everyday, many times a day she opens my office door with her secure access.  My office doors are double doors.  The right hand door is next to the security point.  The left hand door is not (this is relevant!).

Some office incumbents noticed many moons ago that no one uses the left hand door.  People push the right hand door, it is locked, they then try their secure access method and if it says 'come on in' they then open the door.  An office incumbent took the bolt off the left hand door.  So now the office incumbents walk through the left hand door.  It can be done backwards whilst balancing a laptop and a hot cup of tea without the need to swing the body part attached to a pass at a reader or punch anything into a keypad with your nose.  Others use their pass and come through the locked right hand door.

 So back to my auditor non-interactive-'friend':  everyday, many times a day, she opens the right hand door with her secure access. Today I watched her use her secure access and then open the left hand door.  She has been there at least 2 weeks so this is progress but she still hasn't thought to just push the door first.

There is another door that leads to a meeting room with a key card reader on it.  (Again, there is nothing of merit or value behind this door, even the furniture behind it wouldn't sell at a car boot!) The door lock does not work, men with hammers are aware of this and on a regular basis they come to it, bang their hammers, scratch their heads and leave again.  The key card reader beeps when you do stuff to it with your access and its light turns green if it likes you.

For 2 weeks I have watched my non-interactive-auditor-'friend' use her secure access to go through the door, never once doubting that it is locked.  Although bizarrely there is no handle on the door the other side and she must just push the door to get out!!!

So my point is:  just because something asks you for a password you shouldn't assume that it is secure.  Does it care what that password is?  What happens if you try to get in without the password?  The appearance of secure does not mean it is.  You might have userids, roles, profiles, passwords, key readers, whatever,  but what if, as I found recently, one table has clear text passwords in that could (note the past tense here - sorted) be read by a multitude of people?! Or what if people that are fed up with dealing with security on a daily basis can undo a bolt and no one is alerted?!

I am desperate to tell my non-interactive-auditor-'friend' to just walk through the door but I'm also now totally wrapped up in watching to see how many weeks it takes her to twig.

I am also torn about what this tells me about her approach to looking at security.  Life for many would be jolly easy if our non-interactive-auditor-'friend' just puts a lovely large green tick on a box to say that she tried the security and it all worked fine and moves onto her next job. But whilst I think/hope/know the baskets of eggs I am responsible for are safe from the foxes, I would really like her to find any security holes in anything because there are lots of people out there that are constantly pushing at the left hand door to see if it opens all on its own and I want to bolt it solid before they even try.

Read more...

audit_trail=os set with audit_syslog_level is losing the audit trail

I have recently discovered that the Oracle audit trail is not guaranteed if it is sent to syslog.

The Oracle® Database Security Guide 11g Release 2 (11.2) E36292-02 says:

• If the Operating System Audit Trail Becomes Full

• Be aware that an operating system audit trail or file system, including the Windows Event Log, can become full, and therefore, unable to accept new records, including audit records from Oracle Database. In this case, Oracle Database cancels and rolls back the operation being performed, including operations that normally are always audited. (See "Activities That Are Always Audited for All Platforms".) If the operating system audit trail becomes full, then set the AUDIT_TRAIL parameter to use database audit trail (such as DB or DB, EXTENDED). This prevents the audited actions from completing if their audit records cannot be stored. You should periodically archive and purge the operating system audit file to prevent these types of failures.
• If you plan to use operating system auditing, then ensure that the operating system audit trail or the file system does not fill completely. Most operating systems provide administrators with sufficient information and warning to ensure this does not occur. If you configure auditing to use the database audit trail, you can prevent this potential loss of audit information. Oracle Database prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.

Be careful how you read this  "Oracle Database prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement."

No it doesn't.

If the trail is sent to syslog and the filesystem becomes full, Oracle continues to work but does not write the audit trail.

A little auditing to OS background (if you want more detail on how to set it up, the manual is very easy to follow)

If you set audit_sys_operations=true a log of anything done by sysdba and sysoper is written.

This trail is written the form of .aud files to the audit_file_dest (adump). I have written previously about how to manage these in Oracle 11.2.

You can send all your oracle audit (including those generated as a result of audit statements) to these OS files instead of sys.aud$ by setting audit_trail=OS (instead of DB)

You can take this one step further though and have these logs sent to syslog instead. you might think of syslog as /var/log/messages but in fact it can be any file you like.  It is written to by the syslog service.

To send the trail to syslog you define it in oracle using the parameter audit_syslog_level.

What you put in audit_syslog_level must be defined in
/etc/syslog.conf (Linux 5) or /etc/rsyslog.conf (Linux 6)
e.g. if audit_syslog_level='local6.info'
then
 /etc/syslog.conf
*.info;local6.none /var/log/messages
local6.* /var/log/oracle/oracleaudit.log

or for AIX
local6.info /var/log/oracle/oracleaudit.log

the syslog services needs restarting after this file is changed


For Oracle you would set
alter system set audit_sys_operations=true scope=spfile;
alter system set audit_trail=os scope=spfile;
alter system set audit_syslog_level='local6.info' scope=spfile;
Shutdown immediate;
Startup;


Let me show you first how Oracle behaves when you are writing to .aud files and the file system fills up


SQL> show parameter audit
audit_file_dest string /var/log/oracle
audit_sys_operations  TRUE
audit_syslog_level ''
audit_trail string OS
SQL> shutdown immediate;

I filled the filesystem /var/log/oracle

SQL> startup
ORA-09817: Write to audit file failed. Linux-x86_64 Error: 28: No space left on device Additional information: 12 ORA-09945: Unable to initialize the audit trail file Linux-x86_64 Error: 28: No space left on device

It's clearly trying to write a file as it creates a zero byte file but can't put anything in it, so the startup fails
-rw-r----- 1 oracle dba   0 Dec 2 12:51 MYSID_ora_18762_20131202125132.aud

> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0
ERROR: ORA-09817: Write to audit file failed. Linux-x86_64 Error: 28: No space left on device

So I emptied the filesystem again

> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0
SQL>

and it's fine and the connection audit file wrote

-rw-r----- 1 oracle dba 1131 Dec 2 12:54 MYSID_ora_24020_20131202125416.aud

This is completely expected behaviour, you can't start up the database if it can't write the mandatory audit file.

But now look, let's do some audited stuff with syslog configured instead of .aud files:

sylog.conf
# Oracle audit logs local6.* /var/log/oracle/oracleaudit.log

> ls -l /var/log/oracle/oracleaudit.log
-rw------- 1 root root 7950336 Nov 25 14:49 /var/log/oracle/oracleaudit.log

SQL> show parameter audit;
audit_file_dest string /mydiagpath/admin/mysid/adump
audit_sys_operations TRUE
audit_syslog_level string LOCAL6.INFO
audit_trail string OS

I fill the file system and connect to the database

>sqlplus / as sysdba

it let me in, it should have refused

SQL> select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;
11/25/13 14:50:18
>exit

The oracleaudit.log timestamp changed,

-rw------- 1 root root 7950336 Nov 25 14:50 /var/log/oracle/oracleaudit.log

but it didn't write a record into it.  The size remained the same and the last entry was still from 14:11

tail -2 oracleaudit.log Nov 25 14:11:42 myservername Oracle Audit[25001]: LENGTH: "249" SESSIONID:[7] "7 165147" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "MYUSER" USERHOST:[12] "myserver" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "myuser" DBID:[10] "nnnnnnnnnnnn" PRIV$USED:[1] "5"

something like create user should fail right?


> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0
SQL> audit create user by system;
Audit succeeded.
SQL> audit drop user by system;
Audit succeeded.
SQL> alter user system identified by systempassword;
User altered.
SQL>

nothing written to log for the sysdba connection or work done

tail -2 oracleaudit.log
Nov 25 14:11:42 myservername Oracle Audit[25001]: LENGTH: "249" SESSIONID:[7] "7 165147" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "MYUSER" USERHOST:[12] "myserver" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "myuser" DBID:[10] "nnnnnnnnnnnn" PRIV$USED:[1] "5"

-rw------- 1 root root 7950336 Nov 25 14:57 oracleaudit.log


> sqlplus system/systempassword
SQL*Plus: Release 11.2.0.4.0
SQL> CREATE USER "NEWUSER" PROFILE "DEFAULT" IDENTIFIED BY "newuser" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
User created.
SQL> drop user "NEWUSER";
User dropped.
SQL> exit

the audit file still hasn't altered, nothing is being written,

-rw------- 1 root root 7950336 Nov 25 14:51 /var/log/oracle/oracleaudit.log

it's not overwriting itself at either end:

tail -2 oracleaudit.log
Nov 25 14:11:42 myservername Oracle Audit[25001]: LENGTH: "249" SESSIONID:[7] "7 165147" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "MYUSER" USERHOST:[12] "myserver" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "myuser" DBID:[10] "nnnnnnnnnnnn" PRIV$USED:[1] "5"

Just to show it's not wrapping:

head -1 oracleaudit.log
Nov 24 04:03:16 myservername Oracle Audit[13482]: LENGTH: "249" SESSIONID:[7] "7 152208" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "MYUSER" USERHOST:[12] "myhost" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "myuser" DBID:[10] "nnnnnnnnn" PRIV$USED:[1] "5"


show it's not going to old adump

ls -ltr /mydiagpath/admin/mysid/adump
-rw-r----- 1 oracle dba 727 Nov 5 12:12 mysid_ora_3870_2.aud
-rw-r----- 1 oracle dba 732 Nov 5 12:12 mysid_ora_5002_1.aud
-rw-r----- 1 oracle dba 1413 Nov 5 12:12 mysid_ora_4904_1.aud

No only old files in there.

show it's not going to /var/log/messages

tail -2 /var/log/messages
Nov 25 11:41:14 myhost ntpd[5325]: synchronized to nn.nn.n.n, stratum 3
Nov 25 12:26:05 myhost ntpd[5325]: synchronized to nnn.nnn.nnn.n stratum 3

nothing is being written to any logs but oracle is allowing audited transactions to happen

Nothing is being written to the audit log for ddl but let's go for dml, it can't let dml through surely:


> date Mon Nov 25 14:59:51 GMT 2013
> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 25 15:00:20 2013
SQL> audit create table by system;
Audit succeeded.
SQL> audit insert table by system;
Audit succeeded.
SQL> exit

> sqlplus system/systempassword
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 25 15:01:01 2013
SQL> create table test_table (cola number) tablespace tools;
Table created.
SQL> Insert into test_table values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_table;
COLA ---------- 1
SQL> exit

and the audit log, still absolutely nothing:

-rw------- 1 root root 7950336 Nov 25 15:02 /var/log/oracle/oracleaudit.log

tail -2 oracleaudit.log
Nov 25 14:11:42 myservername Oracle Audit[25001]: LENGTH: "249" SESSIONID:[7] "7 165147" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "MYUSER" USERHOST:[12] "myserver" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "myuser" DBID:[10] "nnnnnnnnnnnn" PRIV$USED:[1] "5"

maybe it's cached somewhere? So let's clear some space in the file system and see if Oracle write the logs that are missing.

I removed my files padding the filesystem out and waited a while then connected again.

Nov 25 14:11:42 myservername Oracle Audit[25001]: LENGTH: "249" SESSIONID:[7] "7 165147" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "MYUSER" USERHOST:[12] "myserver" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "myuser" DBID:[10] "nnnnnnnnnnnn" PRIV$USED:[1] "5"
Nov 25 15:03:39 myservername Oracle Audit[22921]: LENGTH: "249" SESSIONI D:[7] "7165484" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "MYUSER" USERHOST:[ 12] "myserver" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[20] "Authenticated by: OS" OS$USERID:[6] "myuser" DBID:[10] "nnnnnnnn" PRIV$USED:[1] "5"

Oracle started to write the new audit records, but all the events that should have been audited that happened in between didn't show.

I have reported this to Oracle but I am wondering if it is expected behaviour, once Oracle hands off to syslog it has no control about whether unix actually writes the record. 

I think it's something to understand.

Make sure your filesystem monitoring of where syslog is writing the file is good,  you can do it through OEM, you don't have to rely on your unix admins.

******
Oracle have stated:

Syslog is a logger facility which runs as a daemon process started by the Linux kernel. Different OS programs can send messages to this logger so that they are recorded as per the configuration mentioned in /etc/syslog.conf.

When Oracle Database runs as a program, it sends its audit records to be written to the same logging facility (SYSLOGD). The Oracle Database program is a client to the Syslogd daemon process.
The APIs used to send messages to Syslogd are:
1. openlog - to open the connection to the system logger
2. syslog - generates a log message, which will be distributed by syslogd
3. closelog - to closes the descriptor being used to write to the system logger.

None of these routines returns a status back to the caller. Typically, these messages are buffered in a ring buffer which gets flushed to the destination as mentioned in syslog.conf, by the SYSLOGD daemon. In other words, these are asynchronous calls to the syslogd. Therefore, the failure to write to the real destination mentioned in syslog.conf cannot be collected by the callers. In this case, its the Oracle Database program and this cannotdetermine the failure.

Perhaps there should be a message on the console or some other location indicating failure to write to Syslog. But callers don't seem to get that notification. Therefore, this bug cannot be fixed by Oracle.

Workaround would be to use OS audit files without syslog as it has been tested and found to be working as expected, they do abort the action.

In Oracle 12c Unified Auditing does not offer syslog configuration.

*******

Read more...

Oracle UKOUG Why It's worth joining.

Today I was lucky enough to be able to attend a UK Oracle User Group Database Server SIG meeting.  It's been a few years since I went to one of these.

My company doesn't subscribe to any user groups any more, it's their policy.  I suspect because the person holding the budget has no idea at all what happens at one of these and has a false impression of a time wasting out of office jolly and god forbid a good time could be had by any dungeon living IT technician.

The value of these SIG days should not be underplayed.

Today I found out how to use snoop a step on from strace/truss for suspect network issues,  I discovered the ins and outs of working from home in the UK whilst outsourcing for a Canadian company, how to compare AWR reports, looking into high buffer reads and about IBMs Netezza. All this was over coffee breaks and lunch.  Networking, sharing experiences and meeting like minded people is a great part of the day. 

But let's put the fun aside:

The Support update was a solid reminder of the needs for patching, what sorts of issues are out there etc.  But all this is available in metalink, I hear you cry.  Yep, Metalink is sat there whirring away in the background but whilst I'm in the office up to my neck in issues and blaming the infrastructure, it is good for me to be mindful of the types of major faults Oracle are still mopping up and these presentations do that for me.

Before stepping into the building I knew nothing at all about Exadata.  I'd read a little, I've stood on the sidelines of social media conversation but I didn't really know any useful basics at all.  45 minutes of e-DBA's Jason Arneil (@jarneil) and I have a grounding of the basics, enough to allow me to read technical documents without just hearing white noise.    I know I am unlikely to ever get formal training on this even if we were to implement them.  As far as I am concerned that session justified the cost of the day.

But wait there's more, Pete Finnigan did a session on Secure Coding PL/SQL.  I've seen him present on profiles, roles, inherited rights  invoker rights before and if you haven't, you should.  But this was a step on into how to start to protect you sql procedures from sql injection attacks, how to look for the holes in your pl/sql and how to start closing them.

There were sessions on  Edition Based Redefinition which is something I was totally unaware of and GoldenGate something I had heard of but had no understanding of.  I think these 2 things are well worth me being fully aware of especially knowing that GoldenGate can be short term licensed.  I now have another option to propose when I need to upgrade a critical 24/7.

But the grand finale was Jonathon Lewis presenting on Exadata compression, storage indexes and Exadata's use of indexes.  None of the presentation was directly relevant to anything I'm doing right now and by the time I'm fortunate enough to come into contact with Exadata I'm likely to have forgotten the detail, that isn't why it matters.  This is the reason why:

                                 MOTIVATION

Being a DBA can sometimes seem like a firefighting daily grind, a battle with developers, a battle with infrastructure design, a non IT driven management that don't understand the technical detail or really care.  In short it can be a drag and I start to wonder why I do it at all.  Jonathon's presentation got back to the core of what I really love, why I ended up here in the first place.  Talking about compression headers is interesting, working out how many rows you can squeeze into 32k is fun and I'm still wondering why Oracle picked 32,759 rows for a compression unit.  The process behind a query dances on the powerpoint and Jonathon at points appeared to be dancing in front of it! Why should the predicates matter...look at that hint... I didn't know that hint.. wow look at THAT hint. He over ran and as so many of us wanted to see the end, he happily stayed behind and finished the presentation for us.  He clearly enjoys the dancing data too.

Companies that don't pay for this membership are clearly very short sighted.  You need real technicians to be enthused about what they do.  If they are motivated, they will do it well, they'll even do it in their own time, they'll want to do it.  The cost of membership to a company to buy that type of staff motivation is as cheap as chips.

I am now seriously considering a personal membership for next year not so chip cheap but worth it.  I know my company's policy is not for turning but I need this sort of regular infusion of update, refresh and reinvigoration to make me the best DBA I can be.

I was rather pleased at the end of the day to have another delegate come up to me and say "What company do you work for? They must be good to be employing such intelligent people that can ask such good questions"   Bless him,  I'm not sure he understood I ask so many questions because I don't have the answers not because I was trying to bring up good points!

I'm sure if you've come here via a search on UKOUG membership you will know that it includes many privileges beyond SIG attendance including discount on Oracle University training. Go and have a look at their website for more details.

Read more...

Oracle Glogin.sql and Login.sql Security Risk

The glogin.sql and login.sql files are a very useful tool.  They are now read after each CONNECT command not just when sqlplus is started.  You can modify your LOGIN file just as you would any other script, this makes it really useful, for example:

SET SQLP "_USER >"

Oracle's documentation shows the options here.
Tom Kyte showed how the new to 11.2 exitcommit works, which would have it's uses too.

But I have a number of concerns about the potential of these files if they are left unsecured.

If the sqlplus restriction level is set to 3, the login.sql is not read. (sqlplus -r 3)
I think this would be a bit too restrictive though as it seems to wipe out a lot of stuff I would want.

The commands edit, get, host, save ,spool ,start (also @ and @@) and store are all disabled with level 3 restrict as well, so I have to assume that the files must be read by Oracle.

glogin.sql is global and lives in $ORACLE_HOME/sqlplus/admin. If there are no login.sql files that Oracle can find it will use this glogin.sql. I haven't found a way to ask ORACLE to use an alternative location for glogin.sql.

To find the login.sql on unix (tested on AIX) it first looks at the environment variable ORACLE_PATH, then your current location, then the environment variable SQLPATH.
On windows with Registry Key or environment variable SQLPATH and ORACLE_PATH set it will always still look first in the current directory for a login.sql, then goes to the SQLPATH variable.  It ignores ORACLE_PATH altogether.  Oracle say this is a feature of  Oracle on Windows not a complete oversight!  They've even added it to the documentation so they don't have to fix it!

It is important that the file is appropriately controlled.  My issue is that a user could leave a spurious login.sql in a folder that says something like:

"set feedback off
Grant myuser DBA
/
set feedback on"

He might leave that file in a place where he believes the DBA might start sqlplus from like a badly secured $ORACLE_HOME, /tmp or  just  / (C:).  If he fires up sqlplus whilst in that path he will have granted me DBA without realising it.  By ensuring that ORACLE defaults to read the $ORACLE_PATH/login.sql this risk is lessened.

So if I want to try to force ORACLE to only look in one place for a login.sql on unix I just export ORACLE_PATH in the .profile.  When the user calls sqlplus ORACLE automatically uses the login.sql in the $ORACLE_PATH. I know that the user could then change ORACLE_PATH but the gate is at least closed if not bolted. On Windows at the moment, I can't.

Of course any well audited and monitored system will pick this up (you would hope) but in the meantime he could log on and have a ball!

It is always important to lock down your oracle binary directories, you don't want any Tom, Dick or Harry in there, it's just not necessary but Oracle is just not helping here by still leaving this back door unlocked.

*update April 2011 : Oracle have accepted an enhancement request to do something with this but it has been a big battle.
Please leave any further information about how to mitigate this in the comments, I'd be really happy to hear about it.

Read more...