Database security is probably not a good term; it makes you focus on restricting access. You think of passwords, encryption, and the things that keep people you don't want away from your data. If you referred to database "insecurity" or openness, you might focus on the other side of the same problem; how do you assure that people you do want to access your data have the easiest time getting it?
I suppose that the absurd extreme of a secure database is the old computer joke about "write-only" memory chips and Cold War paranoia. But today, we do have deliberately open databases -- they are on Web sites, and anyone with a connection anywhere on Earth can get to them. Great for advertising, but not for trade secrets and classified information.
This month I look at encryption, access security, standards conformance, and other things that make you feel that your data and software are doing what they should.
Users must have smart card readers, which are not yet widely available, installed on their computers. But Pdr Andler of Finnish Hewlett-Packard says that later this year, smart card readers will become standard equipment on Scandinavian computers.
But no matter the success of the secure email system, the NPSS project is a clear sign that, unlike the United States, northern Europe is moving forward with exporting encryption technology across national barriers and plans to incorporate it as a part of normal data access. Compare the following quotes from an NPSS press release to those of U.S. officials, particularly those who have been pushing the Clipper chip and weak cryptography for citizens.
"Finnish policy has not been to start with regulations and fear of Net issues," says Anu Lamberg, the head of the Information Network Unit in the Finnish Ministry of Transport and Communications. "The American discussion on this matter has been funny to watch, but I hope nobody in Europe or Finland starts to question the very basics of democracy."
For project developers, using strong encryption algorithms was never an issue. "From the very beginning we've been basing this on strong crypto," says Vesa-Pekka Moilanen, technical director for Finland Post and mastermind of the email project.
Risto Siilasmaa is the CEO and president of DataFellows, one of the only makers of encryption programs in Finland. "... but nobody is going to limit strong crypto," he says. "I have not met a single leading Nordic official who says otherwise."
What if a Nordic citizen enters the United States with the email program installed on his or her laptop? I have no idea what the legal situation would be. But go one step further: What will happen when an international company is providing Internet access to a database via such a smart card cryptography system?
Cookies are an attempt to "fake" a transaction session. The Web site server writes a file -- the cookie -- on the computer that called it and uses this file to find the last state the user was in when she left the Web site the next time she visits the site. The major problem with cookies is that they reside on the "client" side and not on the "server" of the "transaction" session. Think of the dim-witted bank clerk sticking a Post-it note on your forehead so he can read it every time instead of asking you again and again.
There is not a lot of security in this approach. I can make copies of the cookie and my password for other people, and the Web site has no easy way of telling the counterfeit from the real cookie. Some people also get nervous about having files they did not intentionally download put on their machines.
If you have not seen smart cards yet, they have a chip built into the card that has a block of storage that is physically secured and separated from the rest of the card and from the computer that operates the card reader. In financial applications, the smart card reader accesses this secure storage with a set of codes and records debit and credit transactions in currency amounts against an account balance stored in the card. It requires both the buyer and seller to agree on protocols and encryption keys.
The same secure memory could store a huge encryption key on the smart card and even have the Web site server change the key after each access (or during the session). Although you cannot stop someone from stealing the smart card itself, there is no practical way to counterfeit it (yet), and the key control is now on the server side.
The scenario would be that you log on to a secure database and give your user ID and password while the smart card provides a key for message passing. The server looks at the user, the password, the time of the transaction, and anything else it needs to tell it what key it needs to use to go with the user's key. If the user and the ID match to a valid user but are actually an intruder using another smart card, then the key will be wrong and all the intruder can get is garbage.
However, if you would like to do the test yourself at home, the SQL Test Suite, version 6.0, can be downloaded from the Web pages of the Software Diagnostics and Conformance Testing Division. To download this conformance testing software, go to www.itl.nist.gov/div897/ctg/software.htm#pubsoft and select SQL.
This release of the SQL Test Suite was completed in December 1996 and includes new tests to validate the Intermediate level of conformance to SQL-92 standards. The test suite contains additional tests or profiles to help evaluate conformance to:
Meanwhile, The Open Group is forming a new Specification Working Group (SWG) to respond to the "Open Database Access & Interoperability" white paper from Antonio Gualtieri. Briefly, the goal of the Open Database Infrastructure/Connectivity Specification Working Group is to provide specifications or to make sure that specifications exist or are being developed that will create a database infrastructure environment where there is:
"How do I truly know that Abdul sent you? This could be a trick!"
"Behold, shahib, I bear the ring of Rama Lama Ding Dong!"
"Excellent! Take me to Abdul."
This is the approach that Microsoft favors for ActiveX components that are transmitted over the Internet. The company had to do something, and this was easier than making ActiveX truly safe itself. In March, Microsoft was embarrassed on German television when two programmers demonstrated how an ActiveX component loaded over the Internet could access checking-account data kept in Quicken files.
As in those old movies, the simple authentication approach to security assumes that nobody can cut off the fingers of the real secret agent, get the ring, and impersonate him. The more realistic spy movies used another confirmation sign to check out the other guy -- remember how James Bond once spotted a Russian spy because he ordered red wine with fish?
VeriSign (www.verisign.com) was founded in April 1995 as a spin-off of RSA Data Security to bring an electronic version of this to the Internet. The company authenticates, issues, and manages Digital IDs for customers. VeriSign Digital IDs enable trusted electronic commerce by authenticating the individuals, organizations, and content in an electronic transaction. VeriSign has issued Digital IDs to more than 10,000 Web sites and 4,000 individuals, which can display a VeriSign "Authentic Site" seal. When a user moves the mouse over the seal, an interactive popup appears to show that the seal is genuine and not a bitmapped replica. Clicking on the seal links to additional interactive Web pages, giving you a validation test.
Firefly Network (www.firefly.com) received an unqualified opinion from Coopers & Lybrand L.L.P. for internal control over privacy procedures. The Coopers & Lybrand rating is a key component of Firefly's efforts to create a standard for the trusted exchange and management of profile information between businesses and between businesses and end users. The Coopers & Lybrand opinion results from the first formal evaluation of any Internet-based company's design of an internal control system as it relates to the company's privacy procedures and policies. Coopers & Lybrand is using its work with Firefly as a model for the initiatives currently underway with the eTRUST and the implementation of a universal trust mark. The eTRUST (www.eTRUST.org) is an organization formed to address consumer online data capture and usage that includes the Electronic Frontier Foundation, Interactive Services Association, and the Direct Marketing Association.
|
Roles person | role |
| ============= | |
| 'Smith' | 'O' |
| 'Smith' | 'D' |
| 'Jones' | 'O' |
| 'White' | 'D' |
| 'Brown' | 'X' |
| person | combined_role |
| ====================== | |
| 'Smith' | 'B' |
| 'Jones' | 'O' |
| 'White' | 'D' |
Nigel's first attempt involved making a temporary table, but this was taking too long. One way to do this in SQL-89 was to construct a UNION query with one table expression for the single-role employees and one for the dual-role employees.
SELECT R1.person, MAX(R1.role)
FROM Roles AS R1
WHERE R1.role IN ('D','O')
GROUP BY R1.person
HAVING COUNT(*) = 1
UNION ALL
SELECT R2.person, 'B'
FROM Roles AS R2
WHERE R2.role IN ('D','O')
GROUP BY R2.person
HAVING COUNT(*) = 2;
Can you come up with something better? (See Puzzle Answer)
SELECT DISTINCT R1.person,
IF EXISTS (SELECT *
FROM Roles AS R2
WHERE R2.person = R1.person
AND R2.role IN ('D','O')
AND R2.role <> R1.role)
THEN 'B' ELSE R1.role END AS combined_role
FROM Roles AS R1
WHERE R1.role IN ('D','O');
-- Joe Celko