Niall's Oracle Pages

To double post. I have removed the first version of the Oracle Express/AD posts from yesterday. Sorry about that.

A question that hasn't been asked, but probably should have been, about yesterday's posts is Why would you want to do this in custom code?. HTMLDB already has an LDAP authentication mechanism built in. In my case the simple answer is that I just want to make my application available to all members of my domain, without caring what their DN might be, an equivalent to using windows authentication in IIS.

Finally, a couple of people have pointed out that Don has confused me with David Litchfield on his new pages. I've dropped him a line so hopefully that will change shortly. In the meantime its extremely unlikely that I would ever call for anyone's resignation from anywhere - with the possible exception of Rafa Benitez if there are any more choices along the lines of Crouch over Owen, and even then you can forgive someone who brings back the European Cup a multitude of sins.

A recent post on the Oracle XE forum about replacing mod_ntlm as an authentication mechanism for applications on Windows prompted me to write up a mechanism you can use to authenticate users of your application against a Microsoft Active Directory. It works as follows. You create a custom authentication function for HTMLDB this must take two parameters of specific names, and only those two parameters, and return a boolean indicating success or failure. My function merely takes the username and password supplied by the end-user and attempts a simple LDAP bind against AD. By default this will work for all AD users in your organisation and the user will be authenticated. If it fails the user won't be authenticated. The function itself can be downloaded here. A step by step guide is below:

Create a new database user U1 – I used the HTMLDB
interface for user administration.

Login as u1 and create demo application

Navigate to the sql workshop

Load the authenticate_aduser script and edit for the
domain controller hostname and your domain (in the post windows2000
format)

Run the script

You should check that the script was successful

Return to your application in application builder and
choose shared components>authentication schemes and create a new
authentication scheme from scratch.

You only need enter a name for the scheme – I used ad_auth

Click the new scheme to edit it, ensure that you enter the
authentication function as shown below.

Now change the authentication scheme to ad_auth and test

During a discussion about a poorly designed in-house system that has been extended in an ad-hoc, undocumented and uncontrolled fashion (I know my readers have none of these things but bear with me), the designer claimed.

This behaviour is an emergent property of the system

Beats bug any day.

Which is what the title of my last post becomes if you play Chinese Whispers for too long. The reason for this post, Howard has posted a follow up, to my follow up to Pete's followup to a post on pete's forum. Yep, Chinese whispers alright.

Anyway Howard as usual makes some excellent points. In particular

But I'm afraid I take the old-fashioned view that a breach of security in a database means the DBA wasn't doing their job properly. That's the DBA's fault, not the Corporation's for failing to ship the product in an idiot-proof configuration.

and

In other words, if a vaguely competent DBA knows what he or she is doing, the sort of lock-down that Niall and Pete are after is already achievable.

I remain however somewhat unconvinced. This sort of argument is, to me, all rather reminiscent of the defence Microsoft had at the time of the Slammer worm, that a fix was already available, that competent people would have applied it and anyway no sensible dba would have a database exposed to the net. All of these turned out to be bad assumptions, and unlike Howard I don't see any quality of organisations that run Oracle that is likely to make them not subject to the same issues that plagued Microsoft houses a couple of years ago.

Howard is absolutely correct that the security of Oracle databases is the province of the dba, in shops where however the dba may have hundreds of databases of varying degrees of importance, and multiple accounts and features to look after, any help is greatly to be welcomed. Shipping roles like CONNECT and RESOURCE well illustrate that security isn't (or hasn't) been a priority for Oracle Corp.

For me it is instructive to note that, as Howard suggests, a competent dba can require - for example - strong passwords by the use of profiles and has been able to since version 8. So for 8 years we have been able to require at least 4 character passwords, but even in 2005 Oracle doesn't require this for system accounts. 'SYSTEM/XE' or even more scarily 'SYS/XE AS SYSDBA' will be viable hacks for internet exposed systems for the forseeable future.

I, and I am sure Pete, do not argue that Oracle is insecure, merely that shipping it with insecure defaults that require a degree of manual intervention (and a level of knowledge beyond the 2-day dba) is a decision that may well harm Oracle themselves as Oracle databases start to get hacked.

I guess in the end I disagree with this paragraph from Howard's post most of all

I expect SQL Server to be supervised by people whose skillset in the security division is somewhat depleted (I'm not saying SQL Server DBAs are dumb, just that the nature of what they are administering makes it more likely than not that they won't have had to think about these sorts of things before being required to administer a production database). I don't think we should expect the same of Oracle DBAs.

DBAs do much the same job, regardless of product, they are responsible for corporate data and work in the same high pressure, relatively low pay environment (compared for example to the CFO who has similar responsibility for controls over finance). It doesn't cut it for me to say that Oracle people do security and SQL Server people don't. I'd strongly expect the range of Oracle installs to exhibit the same vulnerabilities as that of SQLServer installs.

Pete Finnigan has posted an interesting article on his security related blog. In it he makes the following suggestions to Oracle Corp.

Come on Oracle, lets have a secured installation option - (how about a secure wizard option?).



* set all parameters that could cause insecurities to safe values

* force all installed users passwords to be set - and not to any dictionary word

* install profiles by default to enforce password complexity

* close all un-needed ports - if they are needed then open manually - e.g. iSQL*Plus

* force a listener password to be set - again force complexity and also non-dictionary

* get rid of 99% of the PUBLIC privileges that are granted by default

* many more....

I think that this is an excellent basis for discussion, though I disagree with some of the suggestions and think others too vague.

The suggestions I'd like to see implemented are

force all installed users passwords to be set as strong passwords

install profiles by default to enforce password complexity

close all un-needed ports

force a strong listener password to be set

In addition I'd like to see a tool that can be run against installed databases that provides the following information.

Which features are installed and enabled

Which ports are in use and by what

Any user accounts that do not have a strong password

I'd also like the default installations from DBCA not to include any extra features at all (i.e SYSAUX would contain only SYSTEM).

These two suggestions would ensure that DBAs who after all have to make the security configuration decisions (and often the policy though I think that that is wrong) would have to explicitly choose everything that they install, and would be able to report and document what was installed.

I guess what I am thinking about is something along the lines of the strangely named Surface Area Configuration Tool for Microsoft SQL Server 2005

The suggestions that I think need more thought are

set all parameters that could cause insecurities to safe values

A list of all such parameters and a discussion about what was or was not safe would be an interesting exercise; I very much doubt sensible defaults could be decided in all cases.

get rid of 99% of the PUBLIC privileges that are granted by default

This is highly desirable in theory, but I rather suspect that it would break a large number of installed applications and itself cause real harm to customers. A committment for Oracle not to grant PUBLIC access to new functionality and to reduce the existing exposure over time would be very welcome though.

As many will be aware the support for Oracle XE is peer support, by way of a hosted forum over at forums.oracle.com. This is a place I intend to frequent regularly, but boy is my patience wearing thin.

I first started getting annoyed with the login process last week, but today I have tried 4 times to login. The average length of time from hitting go on the username/password screen to seeing the forum is 98 seconds. This is really not acceptable.

I got asked a question yesterday which required me to revisit some old knowledge. The question was this.

I have a 9i database on server1 and I want to clone it to server2. I do have a cold backup of the database from server1 to restore to server2 but the directory structure is slightly different. In other words how do I duplicate a database from one location to another.

My initial answer was as follows.
I'd do this using the 9i RMAN duplicate database functionality since its much easier than the old way and you can pester Support if you mess it up. If you did want to do this Tim Hall has a how-to on his site and Howard Rogers has a windows example on his. The questioner though hadn't really used RMAN and wanted to know how to do this using the old fashioned approach.

A bit of memory searching later and this is what I came up with.

Backup the source control file to trace using
alter database backup controlfile to trace;

Hand edit the resulting trace file to create a create controlfile sql script. You'll want to make the following changes.

Change RESUSE to SET

Change file locations as appropriate

Create a new parameter file for the database. You can do this directly from the existing spfile if you have one as follows.
SYS @ train>create pfile='c:\oracle\10.1.0\db\database\initclone.ora' from spfile;

Hand edit the resulting init.ora, you'll want to change all occurrences of the old database name to the new one.

Copy the password file if you are using one to a copy with the appropriate new name.

Copy the datafile backups from your cold backup. NB you don't need the tempfiles.

Set the ORACLE_SID environment variable to the new name.

If you are running on windows create the new OracleService using
oradim -new -sid

Run your create controlfile script. The final steps are all shown below.


C:\oracle\10.1.0\admin\train\scripts>oradim -new -sid clone -pfile 'c:\oracle\10.1.0\db\database\initclone.ora'
Instance created.

C:\oracle\10.1.0\admin\train\scripts>set oracle_sid=clone

C:\oracle\10.1.0\admin\train\scripts>sqlplus /nolog

SQL*Plus: Release 10.1.0.4.0 - Production on Fri Nov 11 09:30:36 2005

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

@ >connect / as sysdba
Connected to an idle instance.
SYS @ clone>@createclone
ORACLE instance started.

Total System Global Area 293601280 bytes
Fixed Size 788908 bytes
Variable Size 95417940 bytes
Database Buffers 197132288 bytes
Redo Buffers 262144 bytes

Control file created.


Database altered.


Tablespace altered.



And your new database is up and running.

Permalink posted by Niall @ 9:37 AM

There is a thread on the new Express forum here (registration of Express required) on backing up Express Edition. If you have registered and have views on whether Express should ship with the same backup and recovery capabilities as the paid for editions of Oracle then this is the place to go.

Just to be clear Express will have backup and restore capabilities, but there is an internal debate that customers may well be able to influence on whether it should ship with archivelog capability.

Just some quick observations before I go attend Lisa Dobson's presentation on how to be a newbie.

If you are arranging a big conference, do try to make sure you hold it in a city with reliable power. Central Birmingham losing all power just as registration opens for 2500 delegates was not a good thing. The organisers did fantastically well to keep day one on track.

A note for foreign visitors. If you intend to visit Birmingham for the UKOUG conference, make sure you ask for the ICC and not the 'Conference Centre'. You will be sent to the NEC which is bigger, better and miles away. Of all people you'd have thought the HOTSOS guys wouldn't make unwarranted assumptions about the number of conference centres in a given location.

Tom Kyte was excellent yesterday, showing some cool sql stuff, most of which I'd come across before but a really neat trick that might be used for outer join elimination in some circumstances.

Connor McDonald did his usual 800 slides in 2 minutes. This actually worked really well as the subject - here are some cool features - can lead the unwary into just implementing, being told that a feature is available but not how to do it means you have to read and test.

Last but not least Jonathan Lewis did a fantastic entry level intro to the CBO detailing some strategic decisions you have to make, and the principle of cost based optimisation - that the cost of a query is the estimated runtime of that query.