Learning Oracle PL/SQL

7.3. Analyzing the Library System's Requirements

Now that we've seen some of what Oracle has to offer, we can revisit the first security requirement of the electronic catalog. The first item says:

The creation, maintenance, and revocation of patron accounts in the electronic catalog will be allowed, including issuance of some sort of credentials such as a user account or library card.

If we do our job right, implementing this requirement will go a long way toward satisfying the next two requirements:

There will be security checks in place that make it difficult for a patron to view information about another patron's borrowing habits.

A privileged system administrator will be able to create accounts for librarians, who in turn will have authority to grant and revoke patron privileges.

I'd like to start by considering how to secure each patron's account.

7.3.1. Patron Accounts

We could, of course, set up a system whereby each library patron has an Oracle account, allowing us to use Oracle's built-in security features. However, there are some problems with this approach. The biggest is that there are potentially thousands and thousands of users, and it is sort of impractical to manage Oracle accounts for people who may log in only once. In addition, I've heard that at least some versions of Oracle have performance problems when the number of accounts gets into hundreds of thousands.

If we're not going to put the patrons' accounts into Oracle's native authentication mechanism, ...