Windows XP source code leak could lead to problems

The first line of the link below is a little dramatic “Source code for Windows XP and Windows Server 2003 has leaked publicly for the first time. The gate for cybercriminals is wide open”, however once again we, as IT Professionals, should ensure our environments are fully upgraded and patched.

No need to add more as the article is well-written

 

Windows XP Source Code Leak Could Lead to Disaster

SQLplus command line password no longer shown on screen

I was showing some basic sqlplus connection tips to a new DBA starter today and my normal warning about putting the password on the line when calling sqlplus didn’t produce what I expected.

For ever and a day if you have entered a line within a server session such as when using putty or similar to this :-

sqlplus john/N0tverysafe 

and then ‘host’ to the unix shell and run a ps -ef|grep sqlplus you will see the password and so will anybody else who looks

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management and OLAP options

 


SQL> host

$ps -ef|grep sqlplus
oracle   21519 21165  0  Jun 27  ?        00:00:00 sqlplus -s /nolog
oracle   19165 18798  0  Jun 25  ?        00:00:00 sqlplus -s /nolog
oracle  8516  8514  0 18:33:42 pts/0    00:00:00 grep sqlplus
oracle   24027 23642  0  Jul  2  ?        00:00:00 sqlplus -s /nolog
oracle  8511  7910  0 18:33:28 pts/0    00:00:00 sqlplus john/N0tVERYsafe

I performed more experimentation and realised it was still unsecure at 11.2.0.3 however at 11.2.0.4 it had become secure

create user john identified by "N0tVERYsafe"  profile m_std_profile;
grant create session to john;

[sysopr65@teora01x][TST11204]/home/sysopr65 $sqlplus john/N0tVERYsafe

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 6 19:03:53 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management and OLAP options

SQL> host
[TST11204]/home/sysopr65 $ps -ef|grep john
sysopr65 28309 28306  0 19:04 pts/0    00:00:00 grep john
[TST11204]/home/sysopr65 $ps -ef|grep sqlplus
sysopr65 28256 22815  0 19:03 pts/0    00:00:00 sqlplus
[TST11204]/home/sysopr65 $exit

I will still try to instill good practise by ensuring people do not put username/password as a command string but the loophole does appear to  be fixed now.

I must check whether SQL Loader has had the security modification as well,  as the issue used to be seen when calling a controlfile embedded with a username and password. Although I must admit I tend to run controlfiles interactively from the unix account without a userid in and just use / as sysdba  when asked for a password.  That probably isn’t best practise but do as I say not as I do.

I have just finished typing this up and I do feel that I have written something like this up before. It might be worth me checking my own blog history.

 

Identifying when a password was last changed

Security asked to determine when certain passwords were last changed and I used the ptime column in user$ table to find out.

set lines 120 set pages 60
col name form a30
col Last_Changed form a12

   SELECT name,ptime "Last_Changed"
   FROM sys.user$ a, dba_users b
   where a.name=b.username
   order by 1;

NAME                           Last_Changed
 ------------------------------ ------------
 ANONYMOUS                      24-AUG-13
 APEX_030200                    24-AUG-13
 APEX_PUBLIC_USER               24-AUG-13
 APPQOSSYS                      24-AUG-13
 CTXSYS                         24-AUG-13
 DBSNMP                         13-MAY-16**
 DIP                            24-AUG-13
 EXFSYS                         24-AUG-13
 FLOWS_FILES                    24-AUG-13
 JOHN                           13-MAY-16****
 MDDATA                         24-AUG-13
 MDSYS                          24-AUG-13
 MGMT_VIEW                      24-AUG-13
 OLAPSYS                        24-AUG-13
 ORACLE_OCM                     24-AUG-13
 ORDDATA                        24-AUG-13
 ORDPLUGINS                     24-AUG-13
 ORDSYS                         24-AUG-13
 OUTLN                          24-AUG-13
 OWBSYS                         24-AUG-13
 OWBSYS_AUDIT                   24-AUG-13
 SCOTT                          24-AUG-13
 SI_INFORMTN_SCHEMA             24-AUG-13
 SPATIAL_CSW_ADMIN_USR          24-AUG-13
 SPATIAL_WFS_ADMIN_USR          24-AUG-13
 SYS                            13-MAY-16**
 SYSMAN                         24-AUG-13
 SYSTEM                         13-MAY-16**
 WMSYS                          24-AUG-13
 XDB                            24-AUG-13
 XS$NULL                        24-AUG-13

Note that you need to join with DBA_USERS otherwise the USERS$ view shows all the roles as well, such as RECOVERY_CATALOG_ROLE and DBA.

You will see that the date shown for most  users is 24-AUG-13 which is the date the password was created by the seed database provided by Oracle which is used as part of an install. The accounts with two asterisks have their passwords created as part of user interaction when running DBCA

select name,ctime "Created" , ptime "Last_Changed"
FROM sys.user$ a, dba_users b
where a.name=b.username
and a.name =  'SYS'
order by 1
/

 

NAME                           Created   Last_Changed
------------------------------ --------- ------------
SYS                            24-AUG-13 13-MAY-16

 

Progress update on my AWR repository

I received an email from a team member yesterday

Have you seen this? https://docs.oracle.com/database/121/TDPPT/tdppt_awr_warehouse.htm

Interesting idea – ever thought of implementing it?

Well of course I have implemented a AWR data repository and I thought I would catch-up on where I am with it and how it is being used.

The database started out on HPUX on 11.2.0.1 about 2 years ago and is now on Linux on 11.2.0.3. The repository now holds a total of 139 DBIDs and we have data going back 400 days for the majority of them. The storage is over 2Tb, of which the SYSAUX tablespace contains about 95% – we are compressing some data but that is an area I need to look into further

Why 400 days you might ask – well I work in a Retail organisation and as Easter is a movable feast (pun intended) and that period allows to provide data covering Easter whenever it might fall. It is possible that we would not have data for Easter if we only kept a 365 day period and Easter is a key trading period, second only to Xmas.

I suppose the obvious question is how we have used the data and was it all worth the effort.

The repository has been used for workload forecasting, security reviews, auditing, performance reviews and validation of our existing systems.

Workload forecasting is an interesting and unexpected benefit of the repository. When I originally proposed the idea it wasn’t something that had occurred to me but it has proved very useful. The process is to review how much work a system has done a year ago, look at current usage, calculate the difference and then use that to project forward. For the system that I am discussing which looks at how long it takes us to process sales data from stores we have used a combination of metrics including db time, disk read times, both sequential and scattered to produce a magic number which is what we are working against. That forecast is now being mapped against actuals and the forecast is proving quite accurate. That has allowed us to prepare the database server to support increased volumes by changing disk, adding CPU and memory to maintain and improve against last year’s performance with increased data capture and store numbers.

The value of audit_trail=DB,EXTENDED

I was recently reading a blog entry by Dominic Brooks regarding auditing and I was intrigued by the line referring to the audit_trail parameter being set to DB, EXTENDED

Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available.

Nothing wrong with that and straight from the manual but I was surprised that they were CLOBS. However on looking at them they are NVARCHAR2(2000) so CLOB-type. No issues there.

I have long thought that there were 3 benefits of having the extended value set in audit_trail

1)      Adds the DBID to the syslog file when using OS auditing for SYSDBA and SYSOPER activities. No real issue if you only have a single database on the server but if running several databases it is really mandatory otherwise you cannot tell the entries apart.

My research shows that this is no longer true from 11GR2 onwards and the DBID is always written to the syslog file now

2)      To capture the sql binds and text of any statement run as SYSDBA. This is fundamentally the purpose of using a syslog file, otherwise the DBA can do bad things and delete the entries from the AUD$ table. Having the syslog file set to capture that information and not be editable by other than the root user means that a record of all activity is kept.

Doing some testing, again with the jolt provided by Dom’s blog I found that it did not matter whether the audit_trail was DB or DB,EXTENDED, all the activity and values carried out by SYSDBA were written to that file.

From the documentation

Auditing User SYS and Users Who Connect as SYSDBA and SYSOPER
#############################################################
You can fully audit sessions for users who connect as SYS, including all users connecting using the SYSDBA or SYSOPER privileges. This enables you to write the actions of administrative users to an operating system file, even if the AUDIT_TRAIL parameter is set to NONE, DB, or DB, EXTENDED. Writing the actions of administrator users to an operating system audit file is safer than writing to the SYS.AUD$ table, because administrative users can remove rows from this table that indicate their bad behavior.

 

3)      To catch the activities of ‘ordinary’ users and to record the values of their sql statements, provided sufficient auditing has been enabled

A simple example

User AUDIT_TEST owns a table TEST1

 audit update table, delete table, insert table by AUDIT_TEST by access

declare

Begin

For i in 1..5loop

Insert into test1 values ('Peter',i);

End loop;

commit;

End;

/

select sql_bind,sql_text from dba_audit_trail where username='AUDIT_TEST'

 ACTION_NAME SQL_BIND   SQL_TEXT

------------ ---------- ----------------------------------------

INSERT       #1(1):1   INSERT INTO TEST1 VALUES ('Peter',:B1 )

INSERT       #1(1):2   INSERT INTO TEST1 VALUES ('Peter',:B1 )

INSERT       #1(1):3   INSERT INTO TEST1 VALUES ('Peter',:B1 )

INSERT       #1(1):4   INSERT INTO TEST1 VALUES ('Peter',:B1 )

INSERT       #1(1):5   INSERT INTO TEST1 VALUES ('Peter',:B1 )

Setting the audit_trail parameter to DB we do not see the values used which really makes whole exercise of auditing rather pointless.

INSERT

INSERT

INSERT

INSERT

INSERT

INSERT

 

So in summary, 2 out of my 3 reasons for enabling EXTENDED auditing have been blown out of the water. However the remaining one is justification in itself and I see no reason why every production system should not have it enabled.

At my site ENABLED and writing to a  SYSLOG  file is enabled by default as part of the build on every database we have . We have gone a little too far as the DBAs do not have access to even read the file never mind delete entries but as I sit down with external auditors a couple of times a year I do know they are always impressed that I can demonstrate that even with SYSDBA privileges all my activities are securely audited.

Permissions problem with 11.2.0.3 and tnsnames.ora

There is a Bug documented in MoS regarding the setting of permissions by the root.sh script (which calls roothas.pl).  This causes the ownership of grid home to be owned by root and permissions given to oinstall group

app/gridsoft/11.2.0.3 $ls -ld

drwxr-x---  65 root       oinstall      2048 Feb 27  2012 . 

This causes any user who is not in the oinstall group not  to be able to run any programs such as sqlplus. The bug reference and title is Bug 13789909 : SIHA 11.2.0.3 INSTALL CHANGES THE GRID HOME PERMISSION TO 750 .

The bug is dismissed as being not a problem because nobody should be running executables from the grid home, they should be running from the RDBMS home. A fair point until you consider the location of the tnsnames.ora file.  Any user owning  a dblink needs to access the tnsnames file and even if you link the entry in Grid/network/admin to RDBMS/network/admin the user still does not have access to tnsnames.ora file.

This has only happened in 11.2.0.3 and only on standalone RAC installs. It applies to HPUX and OEL5 s far as I am aware although it was only reported against OEL.  The resolution is easy enough – in our case it would be

chmod 755 /app/gridsoft/11.2.0.3

 However I do think oracle should address this as the bug it is and not ignore it.

The need to ensure that hashed password values are safe

Dennis Yurichev has produced a FGPA password checker which is available on the internet. I took one of our standard style passwords and pasted it into the link above and it took only 45 minutes to crack the password, which is rather a concern if anyone can get see the hashed passwords that are held in the data dictionary

DBSNMP

1FF13052A07F0164

Standard format randomly generated pwd

SOLVED

BDLTL5PD

time spent: 45m59s; average speed: 56M

That password, on a test system,  has now been changed, however it was 8 characters long and to see it cracked on a publicly available site does make one more focused on the need for security, not that I was not already.  The only good news is that password is shown in uppercase when it reality it was a mixture of case.

Lesson 1   – If using 11G ensure that the initialisation paremeter sec_case_sensitive_logon is set to true

So which privileges can be used to view hashed passwords.

The hashed value of the password was stored in sys.user$ AND in dba_users in V10, however security concerns caused V11 to be changed so that the hashed password is only stored in the sys.user$ table, despite there still being a column for it in dba_users.

What does the SQL92_SECURITY parameter actually do?

Whilst looking at our Grid Control I noticed that we had a few policy violations in the security configuration stating that SQL92_SECURITY=FALSE. These were against some of our older databases as we set it to true on all new builds. I thought I knew what this parameter did but had a look around and determined the following :-

It stops anyone being able to update or delete rows from a table owned by another user if you were using a where clause and did not have select on that table. Fair enough I thought, it sounds like it is some form of ANSI 92 standard and it is all a bit meaningless. I could not see what the issue was but I was intrigued as to why it was an Oracle security recommendation.

I set up the following test case on both a 10G and 11G database where SQL92_SECURITY was set to TRUE. Very simple but I had no failures other than the select statement which had insufficient privileges.

create user usera identified by usera;
create user userb identified by userb;

grant create session,create table to usera;
grant create session to userb;
alter user usera quota unlimited on users;
alter user userb quota unlimited on users;
show parameter sql92_security
connect usera/usera
create table tab1 as select * from all_objects where rownum <101;
select count(*) from tab1;
grant update,delete on tab1 to userb;

connect userb/userb
update usera.tab1 set object_type = 'WAS PACK' where object_type='PACKAGE';
commit;
select distinct status from usera.tab1;
prompt fails as no select privilege on usera.tab1
prompt SQL92 set to true and yet update is allowed ??
prompt now let's try a delete
delete from usera.tab1 where rownum <21;

prompt delete with a where clause works as well

select count(*) from
prompt end of case study

So what is all that about, where was I going wrong and why were my updates and deletes allowed when they shouldn’t have been.

This parameter is meant to prevent you from deleting a table when you specify a condition based on table’s columns. Conditions that are not checking the values stored in the table are allowed(rownum < X, etc.)

In my testcase I deleted the table rows without a condition which read one of the the tables columns. If instead of executing delete from usera.tab1 where rownum =21 I had executed delete from usera.tab1 where object_name = ‘TEST’; I would get an error.

The purpose of this parameter is to allow a user one to delete table data without giving them the possibility of guessing what values are stored in that table. Imagine that there is a table with contacts and I only have delete rights on that table. If I am able to delete the table with a condition based on table columns I can find out via multiple attempts the contents of the table. I can for example find out whether ‘John Smith’ is a contact or not :

SQL> delete from contacts where contact_first_name='John' and contact_family_name='Smith'; 

1 row deleted
SQL> rollback;
Rollback complete.

Since the row was deleted I know now that John Smith is a contact. Then I do a rollback of the transaction and the table is as it was before the delete. If the parameter is set to TRUE I cannot make use of this trick and I can only delete the rows blindly. I will be able to delete without knowing what I am going to delete.

Once I understood what could be done I understood the reason for the init.ora parameter to be enabled. However there is very little information out on the web which explains the reasons and that is why I think the parameter can be misunderstood.

Prior to enabling it on a database that is in use I would check that nobody had update or delete privileges ona table that they did not own where they did not have select privilege.

select *
from dba_tab_privs a
where privilege in ('UPDATE','DELETE')
and not exists (select null 
                from   dba_tab_privs b
                where  privilege = 'SELECT'
                and    a.grantor = b.grantor
                and    a.grantee = b.grantee
                and    a.table_name = b.table_name);

Hacking into an Oracle database

At a site I was working at they employed a security company to perform penetration testing on a new application (Oracle/Peoplesoft/Unix/Windows/NT). The test was from within the network  and they failed to breach Oracle database security directly. However …

Password guessing against a NT server resulted in the discovery of a domain admin account (db2admin), which resulted in full compromise of this host and also the remaining servers that are members of this domain.

When you build a Windows server you are asked for an initial password (which you are expected to change later on). This password is stored in a build file and as it had not been changed after the installation  it was then used to logon to the domain (any of  the servers as local administrator)This allowed searching of files on the NT server which produced a file containing the oracle account and password to the people account (peoplesoft admin user)

Once onto the database a reasonably privileged account with a weak password was discovered. From there the encrypted values of the users were captured from dba_users and were compared against a known list of passwords and permutations based on the current password i.e. a zero used instead of an ‘o’. This compromised an account with create procedure and from there they were into the OS.

 

These issues have all been resolved and we are much more secure than we were but it just shows how a lapse in security somewhere on the network can allow what appears to be innocuous access which eventually provides full ingress into what is supposed to be a secure system.

More problems with orapwd file and shipping redo logs

OEL Linux 11.1.0.6

I had a orapwd file set up on both databases and have restarted the databases since I re-created the orapw file. I could connect using ‘sqlplus sys/password@tnsnames_alias as sysdba’ between both nodes and the sys password is the same on both nodes.  However archived redo logs files were not shipping across to the standby node and I was pulling my hair out struggling to get it to work.

Error messages were :-

*** 2008-05-09 09:44:43.898
Redo shipping client performing standby login
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1031 and errmsg is ‘ORA-01031: insufficient privileges
‘
An attempt to establish secure session using password verifier based approach will be attempted..
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1031 and errmsg is ‘ORA-01031: insufficient privileges
‘
An attempt to establish secure session using password verifier based approach will be attempted..
OCISessionBegin failed -1
.. Detailed OCI error val is 1017 and errmsg is ‘ORA-01017: invalid username/password; logon denied
‘
*** 2008-05-09 09:44:44.127 1095 krsh.c
Error 1017 received logging on to the standby
————————————————————
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
————————————————————


The passwords were lowercase on both nodes. In fact I was using  a cut’n’paste to create both password files, only replacing  the node name.
Eventually I fixed the problem by re-creating the password files on both nodes (for the 3^rd or 4^th time) and restarting the databases. I added the parameter ignorecase=y and everything sprung to life.

This gets me to think that the password was being translated to uppercase when it came across to the remote node

There is a bug on Metalink (6804839) ORA-01017 (16191) LOGGING ONTO STANDBY WHEN SEC_CASE_SENSITIVE_LOGON=FALSE

The bug detail itself is not public but the title seems to confirm that it that bug is likely to be related to my problem although in my case sec_case_sensitive is set to true on both nodes already.

How annoying metalink can be, searching all sources for ORA-01017 or 16191 (both in the title of that bug) does not bring it up.

Another suggestion was to copy the orapwd file from one server to the other and ensure that permissions are correct

So the short answer is that if you are stuck with a similar problem try recreating the orapwd file with ignorecase=y set