Wall-to-wall SSO and EUS

After introducing Kerberos, and getting SSO to the database working, it's time for some excitement: combine Single Sign On (SSO) with Enterprise User Security (EUS).
Sure, I blogged about Enterprise Security a long time ago (See the "Enterprise Security" entries of 2005), but there I used certificates; in fact another identity.
In this entry, some of the 2005 stuff returns, but parts will differ.

The latest and the greatest.

For those of you how wonder why I bother to blog about this "ancient" version, read the statement of direction on SSO server (note 979951.1 on MOS). Basically, when you use Portal, Forms or Discoverer, you need OSSO 10GR3 (even Forms 11G).
It's not that ancient when it will be around for some time...

Goal.

What I want, is the same a the previous entry on SSO, but I do not wish to create users: I want to use EUS instead.

Preparations.

In addition to what's already in place, I need a complete SSO infrastructure.
The brave may want to do this Enterprise-sized setup, for now I'll just go with the "all-in-one" Identity Management install. Even patched it to 10.1.4.3.

Steps

In order to achieve all this, I would need to:

1. Set up synchronization between Active Directory and OID
2. Set up Enterprise Security in the database(s)
   
3. Set up a trusted relation between OSSO Application Server and AD
4. As a bonus: try to persuade Forms menu security to use EUS

I will blog about these - stay tuned...

Single Sign On to the Database (or WNA for SQL*Plus)

In this entry, I'll demonstrate how easy it is to do Kerberos authentication to the database, or -rephrasing that- how to do WNA in the database.
The goal of this exercise will be to allow users to log on to a remote database using the following syntax:

sqlplus /@db10g

Scenario

I like to think major sites have serious machines and ditto operating systems. Workstations are uncannily often based on MS Windows, so I'll use MS Windows for a client, and Linux for database server.

Prerequisites

Both client and server need to have the Advanced Security Option installed, for which Oracle wants to get paid. Just read the license conditions: if you want to do Kerberos, you need to pay.

Checks

Now, how do you check whether you have that installed? On *ixes, you can use the command adapters:

Installed Oracle Advanced Security options are:

RC4 40-bit encryption
RC4 56-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
AES 256-bit encryption
MD5 crypto-checksumming
SHA-1 crypto-checksumming
Kerberos v5 authentication
RADIUS authentication
[ora10@db1 ~]$

According to the documentation, that is proof. I have found that it is not always the case, and you may find yourself without the Oracle Kerberos utilities. Better check if these are available, they sould be:

[ora10@db1 ~]$ ls $ORACLE_HOME/bin/ok*
/oracle/db10/10GRel2/bin/okdstry /oracle/db10/10GRel2/bin/okinit
/oracle/db10/10GRel2/bin/oklist /oracle/db10/10GRel2/bin/okdstry0
/oracle/db10/10GRel2/bin/okinit0 /oracle/db10/10GRel2/bin/oklist0

That concludes checks on the software stack on the server. If you miss anything: install the Advanced Security Option!

Client checks

For MS Windows clients, there is unfortunately not much else to do than to fire up OUI, and list the installed options. Fore mentioned Kerberos utilities should also be available on the client, so you may want to check that out as well.

Hands on

Service Principal

First thing to do, is to allow the database server to get Kerberos tickets. In order to do that, I need a Kerberos service principal. In order to do that, I need to create a domain user on my MS Windows 2000 Domain Controller:

I have chosen the name of the instance, but that does not really matter. You may want to use a more generic user name if you have an environment with many instances per machine.
Make sure you set "Use DES encryption" and "Do not require preauthentication", as shown here:

You may have to patch your MicroSoft Active Directory to support this. Check out Knowledge Base article 833708 on how to do this in the registry, or apply SP 1.
I think everybody has SP1 (or better) - I did not have to change anything on my PDC image, but this may cause problems.

Keytab file

Now that I created a user, I must transfer the credentials to the database server. That involves exporting keys to a file, and it is done using this statement on the PDC (or member domain controller):

ktpass -princ service/username@AD.DOMAIN -mapuser username -pass APassWord -DesOnly -crypto des-cbc-md5 -ptype KRB5_NT_PRINCIPAL -out c:\serv.keytab

This is an actual session:


The beauty is, that you may forget the password: you will never need it again. In fact, you need not know the password, unless things don't work, and you want to troubleshoot.
You should remember the service name: you need to specify that in configuration files. I use "orcl" in this example - you may want to use a more generic name, like krb5srv.
Oracle MOS Document states you can only use -crypto des-cbc-crc for encrytion; this is incomplete; you can also use -crypto des-cbc-md5, as I did. (you need md5 for HTTP principals, but that's an other story).

Prepare server

Binary transfer the keytab file to the server. These files are called keytab files, as they contain a table of keys - the keytab for short.
Logon to the database server an cd to your TNS_ADMIN directory. Which happens to be $ORACLE_HOME/network/admin, if you do not use $TNS_ADMIN.
Alter the sqlnet.ora file to look like:

[ora10@db1 admin]$ cat sqlnet.ora
sqlnet.authentication_services=(beq, kerberos5)
sqlnet.kerberos5_conf=/oracle/db10/admin/krb5.conf
sqlnet.kerberos5_keytab=/oracle/db10/admin/db1.keytab
sqlnet.authentication_kerberos5_service=orcl
sqlnet.kerberos5_conf_mit=true

Make sure the variables point to the correct locations of the keytab file and the (general) Kerberos configuration file. This configuration file contains:

[ora10@db1 admin]$ cat /oracle/db10/admin/krb5.conf
[libdefaults]
default_realm = HOME.LOCAL
[realms]
HOME.LOCAL = {
kdc=192.168.1.250
}
[domain_realm]
.home.local = HOME.LOCAL
home.local = HOME.LOCAL

Mind you, I am completely bypassing the fact, your server should be configured to support Kerberos, i.e. without support for Kerberos on OS level, this is less likely to succeed.

Prepare Client

Client side is about the same as the server, apart from the service principal/keytab file. Change sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES= (all)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
sqlnet.kerberos5_conf=C:\oracle\clt10g\network\admin\krb5.conf
sqlnet.kerberos5_conf_mit=true
sqlnet.authentication_kerberos5_service=orcl
# sqlnet.kerberos5_cc_name=C:\oracle\clt10g\network\admin\krbcache
sqlnet.kerberos5_cc_name=OSMSFT://

Note the last line: it is MS specific, and allows the use of the MS Windows internal Kerberos cache, instead of a file based one (as specified the line above).
Note however, using the internal cache will disallow the use of Oracle Kerberos utilities; these will err off with "OSD error"

The Kerberos configuration file is similar to the one on the server, and connects realms to domains:

[libdefaults]
default_realm = HOME.LOCAL
[realms]
HOME.LOCAL = {
kdc = w2k-pdc.home.local
}
[domain_realm]
.home.local = HOME.LOCAL
home.local = HOME.LOCAL

You may now test the Kerberos setup. You should be able to get a ticket from the Kerberos Ticket Granting Server (which is MS Active Directory, in this case).
Try to get a ticket for a known MS domain account, I can log on to the domain "home.local" as frank, that would make my account "frank@HOME.LOCAL" - and be careful, Kerberos is strangely case sensitive! The oklist utility reveals:

Ticket cache: win2kcc
Default principal: frank@HOME.LOCAL

Valid Starting Expires Principal
08-Sep-2010 12:56:03 08-Sep-2010 22:56:03 krbtgt/HOME.LOCAL@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:03 08-Sep-2010 22:56:03 krbtgt/HOME.LOCAL@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 13:28:28 08-Sep-2010 22:56:03 orcl/db1.home.local@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:04 08-Sep-2010 22:56:03 W2K-PDC$@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:04 08-Sep-2010 22:56:03 ldap/w2k-pdc.home.local/home.local@HOME.LOCAL renew until 15-Sep-2010 12:56:03

Now, create a database user "FRANK@HOME.LOCAL", in uppercase, and quoted, as Oracle uses the commercial at-sign for special purposes. You should now be able to login:

C:\Documents and Settings\frank>sqlplus /@db10

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Sep 8 13:51:56 2010
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> show user
USER is "FRANK@HOME.LOCAL"
SQL>

Stay tuned

Of course, things start getting interesting when adding OID and EUS. Wall-to-wall SSO!
Later...

Over the top

As much as I like privacy (google changed it's privacy statement just now, btw), I hate any form of censorship on the internet.
RIM seems to give up on privacy, by allowing governments to decrypt Blackberry traffic.
Google is not just content with our search and surf behaviour on the internet, it now wants to know what we're talking about and introduces free phone services.
And Apple is censoring ping.

Sigh. What a world. Next entry will be on Oracle, and SSO to the database.
Update: Craigslist does censor, too.

[Edit]Seems DRM is broken... Someone published a master key...[/edit]

An old fox looses hair...

but not it's tricks. MicroSoft's "new" IE9 interface looks suspiciously like Google's Chrome (or Iron, which lacks the privacy loopholes of Chrome).
How very innovative - oh no, MS has been doing that since they dubbed QDOS "MS-DOS".
And yes, QDOS meant Quick and Dirty Operating System, and yes, it was "bought" by MicroSoft. The 50k US$ was not the initial amount (that was considerably lower).

Secure...?

What is really worrying is the [quote]provisions for “recognized,” or “protected,” sites which will allow users to go straight from the Windows taskbar to these sites without having to open IE first.[/quote]
Knowing the way MicroSoft implements security, these [quote]recognized, protected sites will be treated more like traditional Windows applications[/quote] will open a whole new range of possibilities. For hackers, that is.

Privacy

[update]

The new, right wing government signed a treaty with the US on DNA and fingerprint exchange. Quote (in Dutch): Een woordvoerder van het ministerie benadrukt dat het om een versnelling van een bestaande procedure gaat die alleen geldt voor de gegevens van zware criminelen. Alleen mensen die een straf van minimaal vier jaar hebben gekregen komen terug in de database. Which roughly translates to: A spokesman from the ministry emphasized that is is just a speed-up of existing procedures, that only delas with data on heavy criminals. Only those with a punishment of four years or more in prison are in the database.

I say: create database link goodbye_privacy connect to X identified by Z, and my fingerprints are found, too.
See below: "I am a criminal"

No deal.

The other day, I got an email from the cable company I have internet, phone and (digital) television from. If I were interested to extend the number of digital television channels for a small amount.
Fair enough; I'd get motor channel, travel channels and science channels: all stuff that interests me, for just a few bucks monthly. I was ready to be served, and clicked the link.
However, the second of three screens I had to fill in, insisted on me indicating sex and date of birth. I fail to see why they needed that, and the more I started thinking about it, the stranger the concept became. I closed the browser.

Ownership

Apart from pissing me off, asking details the company already knew (I have a subscription, damned!), I really, really wonder what a cable company wants with the fact whether I'm a boy or a girl. Or when I was born - maybe they toss in a free porn channel when I'm over 21? What do they do with all these data?
I want to be able to see what they know about me, and decide whether or not they need that data. Cable companies do not need birth dates.

Prime

Actually, there's a programme called Prime that handles just that. It helps deciding whether data is needed for a certain transaction (e.g. when ordering from Amazon, an address is handy, but birth date is superfluous), and -what's more- it tags data: "destroy after six months". Check it out.

I'm a criminal

Well, I guess I must be: the government insisted on having my fingerprints taken and stored in a central database. They really twist your arm to get these prints: no passport unless you deliver. I fail to see why they would need my fingerprints. Oh yeah - I heard about the (stupid!) European guideline for a digitally recorded fingerprint in your passport, "to make it safer". But that is one fingerprint, not four!
It has already been demonstrated that remote identity theft by reading the (ill encoded) chip in your passport, is possible, and a matter of minutes with sophisticated machinery. Costly, but that has never been stopping those parties interested in these documents. And no, contrary to popular belief, there's no need for close encounters: distances of up to 10 meters (30 ft) reading chip data, are achieved.
The danger is that most people believe that passports (or other identification means) are safe, and "unbreakable" and thus the bearer of the identity must be that identity. The opposite has been demonstrated.

Iris scan

Before introduction of the fingerprint on passports, security people were consulted, or given room to comment on the idea. I heard that one of the leading security people of Amsterdam Airport was amongst them. He (or she?) opposed to fingerprints as unsafe and too easy to forge. Just search for "forge fingerprint".
Instead, iris scans would be much safer. He/she was muffled: European guidelines say fingerprints. How stupid.
Interestingly, Schiphol Airport frequent flyers can bypass normal check-in and passport(!) procedures by enrolling into the Privium Club, and having their iris scanned upon entrance as a means of identification. I think I'll put scans of my fingers online soon.

Energy

A while ago, the government wanted people to install smart energy meters. By law, punishable by a hefty fine if rejected. It would allow energy distributors to better and more efficiently distribute electricity and it would allow for energy savings by giving insight in your electricity usage. Guess the fact it should be by law was inspired by the eco terrorists in this country.
This meter would upload data every 15 minutes to the power grid, which would then feed the data to the retailer. Consumers would check the retailers web site for their usage.
Of course, that would not only allow thiefs to find out when I was on vacation, it would also make it quite easy to find out where religious Muslims live: they would be up at 5 am in the morning for prayers. Function creep danger: instead of monitoring electricity usage, police can install energy taps in addition to telephone taps.

Now, with some reasoning, the smart meter concept can still be introduced:

Anonymize!

The first thing to do is to make data, relevant to the power distributer (the gird), anonymous. This can be archived by combining data of a couple of hundred homes, or start metering at a block (of houses) level.

Store locally

The second thing to is, is to change the design of the meter to allow local storage - if I want insight in my electricity usage, I can download my meter readings and create a database. A personal database. And by the time the retailer needs the data for billing purposes, I can push the button, and condensed data will be sent to the retailer.
You need to keep your goals clear; do not try to use the electricity meter to archive energy savings as well as smarter grid control. Do not engineer function creep.

Pay-per-ride

The Dutch pay-per-ride ("rekeningrijden") system (if it ever gets realized) uses local storage. The minister involved was warned in the early stages of the project not to allow real-time positioning of vehicles. In stead, the unit only sends how long, and against what fee the car was driven every now and then. Data about when and where remains in the car. This is good policy, from privacy perspective. As long as no back doors are possible; function creep would allow the police to write speeding tickets based on historical data ("you cannot have driven from Amsterdam to The Hague in 40 minutes during rush hour!").

Hardware

To minimize chance of function creep, hardware should be used as much as possible: no data means no privacy invasion. Licence plate recognition is commonly used by the police in The Netherlands. When matched against a license plate database (e.g. stolen car, car used with heist), it's a "hit", to be acted upon. All other data ("no-hits") should be destroyed, according to Dutch law. It has happened more than once, that this data was stored: "Very convenient for police cases".
When sensors would have been used that only transmit the hits, this function creep could not have happened.

Again: anonymize!

Very often, there's no need for personal data. The dreaded "OV-chip card" could (and should!) be anonymous. In stead, the default is a personal chip card, that allow the card company to register where and when you traveled. There's a so-called anonymous card, which isn't: you cannot pay anonymous cash: it needs a bank account, due to the nature of the crediting system: you pay the maximum amount upon check-in before the journey, and get restitution upon check-out. In order to be sure you will pay, there's a minimum deposit value that needs to be on your card. This means every card has an identity: either name (by default), or number (the "anonymous" card).
I liked those old train tickets; they worked by attributes. Blue ticket: first class. Yellowish-brown: second class. Anonymous. Simple. Transparent.

Call me old fashioned... I'd rather you'd call me prudent. Wary. Superstitious perhaps, especially when the government is involved

DNSSec, please

Or is it "just" an insecure server? Whole story is here.
Oracle TimesTen users may want to check the latest Patch set

ORA-28047 "database is not a member of any enterprise domain in OID"

Amazing... Two-and-a-half-thousand hits and no answer. Metalink: two hits and no answer. Why me?
OK; here's what is happening, and how to resolve the issue. I have not figured out what causes the problem - it seems intermittent.

Update: might have something to do with settings (stickyness?) on the loadbalancer.

Symptoms

You will be confronted with this error while trying Enterprise Security:

sqlplus s/s@<missing_alias>
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 11 10:26:49 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-28047: database is not a member of any enterprise domain in OID

Enter user-name:

You have registered the database, and all seems well. No errors were shown during the process, none to be found in the logs, and your database can be found, using LDAP name resolving.
It can also be found in your default Realm (LDAP root in Oracle slang) using:

ldapsearch -h <OID_hostname> cn=<missing_alias>

What causes ORA-28047

Exactly what is says: your database is not known in any Enterprise Domain.
You may check it, using this query:

ldapsearch -h <OID_hostname> -D cn=orcladmin -w <your_password> cn=OracleDefaultDomain uniquemember|findstr <missing_alias>

Change findstr to grep if you're on Unix or Linux; I was resolving this issue with a client that uses MS Windows workstations.
Change OracleDefaultDomain to something else, if you use multiple security domains (I doubt if any)

How to resolve

Well, that's obvious now: add a member to the OracleDefaultDomain. For some reason, Oracle's Directory Manager does not allow you to do that, but other tools do. If all else fails, you can still do it from the commandline, using ldapmodify:

ldapmodify <OID_hostname> -D cn=orcladmin -w <your_password> -f ora28047.ldif

The contents of the file ORA28047.ldif is:

dn: cn=OracleDefaultDomain,cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=<your_realm_here>
changetype: modify
add: uniquemember
uniquemember:cn=<missing_alias>,cn=oraclecontext,dc=<your_realm_here>

Mind the formatting... lines cannot be broken (as blogger formatting does)

ORA-28273

That should be the sign that all works: plus connects to the database, the database knows it needs to go to the OID, it cannot find the specified user ("s") in the OID:

sqlplus s/s@<missing_alias>
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 11 10:26:49 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-28273: No mapping for user nickname to LDAP distinguished name exists.

Taking it one step further: ORA-28274

If you use an actual OID-known account, you should get ORA-28274: No ORACLE password attribute corresponding to user nickname exists:

sqlplus bortel/s@<missing_alias>
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 11 10:26:49 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-28274: No ORACLE password attribute corresponding to user nickname exists.