Database IA

Database I&A

The database’s primary I&A mechanism is username and password. The authentication is critical because usernames can be predicted and in some cases are well known. For users identified in the database you have the following authentication choices:

• Passwords Passwords can be either authenticated by the database or by the Oracle LDAP directory as shown in Chapter 5.

• External/Strong Oracle supports operating system authentication and strong authentication that includes PKI certificates, Kerberos, DCE, and RADIUS. The RADIUS standard extends the authentication capabilities to include token cards, biometrics, and smart cards. Details on how to setup and configure Oracle for strong authentication are given in the Oracle Database Advanced Security Administrator’s Guide 10g.

• Proxy This is covered in detail in Chapter 4.

Oracle supports single sign-on both directly to the database and for Web applications deployed to the Oracle Application Server. The database’s support for strong authentication also includes support for single sign-on technologies such as Kerberos, DCE, and PKI certificates.

The Oracle Application Server single sign-on capability is deployed on an Oracle database, which provides the scalability, security, and high-availability requirements needed for an effective implementation. The server supports passwords, PKI certificates, Microsoft Windows (Kerberos on Windows 2000) authentication, and an extensible framework for plugging in third-party authentication such as token cards and biometrics. Refer to the Oracle Application Server 10g Security Guide for further details.

Associating Users with Database Schemas

Now that you have a good understanding of the various I&A techniques, you have to decide how to represent your end users (applications users) to the database. The overall security of your application and the data it touches is largely affected by how this process is done.

When it comes to building applications, there are typically three models that can be used for mapping actual end users to database accounts. There are different benefits and risks within each model, so it’s important to understand each. This understanding is crucial, because the model often restricts what you do and how you do it.

• 1:1 This one-to-one mapping occurs primarily in client-server programs. This means that every end user has a distinct database account, or schema.

• N:M This all-to-several mapping occasionally happens in Web applications. It means that all the end users are mapped to several different schemas. Each account is created based on the shared end-user privileges. All users with the same privileges are connected to the same schema.

• N:1 This all-to-one mapping is a typical Web application. The application connects all end users to the same database schema. The schema has the union of all privileges for all users connected to it.

The mappings are listed in order of easiest to hardest to build database security. There are many factors influencing the model choice. Scalability, performance, and administration ease-of-use often get most of the attention. Security, however, must also be part of the consideration. In some cases, the security requirements may supersede other requirements, such as the administration.

User Privileges for Unique Database Accounts

Depending on how your database will be accessed and the role of your application(s), you may or may not have a lot of actual end-user accounts, that is, the 1:1 mapping model. For example, it’s typical for client-server programs to run in this manner and less common for Web applications to do so.

The 1:1 mapping can occur several ways, but the bottom line is that there is a direct mapping of each user to an identity in the database (that is, the database account). In its simplest mode, the user supplies a username and password that are the actual database user account names and account passwords. Alternatively, the application could provide its own mapping. The point is not necessarily how it’s done, but that it’s done.

When creating the 1:1 database accounts, it’s important to apply the same least-privilege lessons and process used in managing accounts on operating systems. That is, you should create users with no privileges and then selectively grant them the privileges they need to get their job done.

This mapping is especially critical for database administrators and other privileged users. Sharing a privileged account among a group of users is a bad practice. In many organizations, sharing user accounts is prohibited by policy, but is nonetheless still often practiced. If something goes wrong in that case, you’ll not be able to tell which user was connected as the database user when that something happened. Therefore, there is no user accountability.

The 1:1 mapping makes the database security easy because the user’s identity is always available to the database; therefore, the user is accountable. In addition, many of the database security capabilities operate at the individual schema level. If every user has a unique schema, then the database can easily apply appropriate security to the appropriate users based on the schemas. The database can, with high assurance, distinguish between each end user, and in doing so it can employ all of its security capabilities.

Shared Database Accounts

Least privileges are important even if the users are not connecting directly to the database. It’s most common for users to have a unique application account and share a database schema. There are two mappings for this.

The first mapping, N:M, partitions users into various schemas. This is generally done by organizing the users by role—all users with the same role connect to the same schema. The important part to this is ensuring that the least-privilege principle is still upheld. You can effectively do this if all users that attach to the shared database schema are supposed to have the exact same database privileges. That is, the users are homogeneous from a privilege perspective. Conveying the user’s identity is important in this design, too, since the database may be unable to distinguish between users connected to the same schema. Chapter 6 explores how to do this using Client Identifiers.

The last mapping model, N:1, connects all end users to the same database schema. This mapping is always the most questionable with regard to security. The problem with the design is that it’s difficult for the database to separate the security privileges for the different users since they are all connected to the same schema. Ensuring that only the right privileges are available to the user is left mostly to the application. From an auditing perspective, unless you are using Oracle Enterprise Users (discussed in Chapter 5), the user’s identity is not natively supported with this design, so their individual actions may be untraceable as well as unregulated.

Consider the scenario of users requiring different privileges sharing the same database schema. Figure 3-1 represents the N:1 mapping. Suppose you have three user groups—one with read-only access, another with read and write access, and an administrator group that can create and drop objects as well as read and write. If all three user groups are mapped to the same database schema, then the application must regulate what privileges to enable and disable based on what it knows about the user.

Figure 3-1: End users with different privilege sets shouldn’t be mapped to a single schema that has all the privileges of all the users.

From the database’s perspective, all users have the same privileges. This violates least privilege and defense in depth. If the application security fails, or the user circumvents the application, then the security can be compromised.

When you are designing and building an application, you should ensure that the least-privilege principle is upheld. Isolating schemas to shared users with the same privileges is one way to do this. Alternatively, using other features of the database such as Enterprise Users, Secure Application Roles, and identity preservations techniques (all covered in later chapters) will allow you to securely share database schemas among the end users.

Separate Users and Data

A critical part to ensuring a successful application is to ensure you have a separation of user database accounts and data/application database accounts. The data owner has all privileges on the data. If the users connect to this schema, even if by way of your application, you run a significant risk of a security incident. That is, if a user can break the application, or the application breaks itself, the user will have complete control on every part of the data. This includes not only access but also destructive capabilities such as truncating tables and dropping objects.

For this reason, a security best practice is to ensure the application users don’t connect directly to the data account. You may also consider disabling all user log on to the data schema using one of the techniques discussed in Chapter 2, such as locking the account, revoking privileges to connect, or creating an impossible password.

In the safest design, you have one schema that holds the data, one schema that holds any PL/SQL programs that work on the data, and at least one other schema to which the users attach.

Tip 

Never allow your end users to connect to the data schemas.

Identity Preservation

An important aspect of the identification process is identity preservation and propagation. You must ensure that user identities are available everywhere security requires them. The minute the identity stops, security stops. From a secure database perspective, this is a particularly acute problem. It often occurs that the user authenticates to an application, and the application connects to the database not as the actual user but anonymously or pseudo-anonymously. When designing database applications, the more information you can provide about the user’s identity, the finer levels of security you can apply. Or as some like to say, “The more the identity, the better the security.”

Although many applications (or databases) are designed around user-level access control and auditing, not all require it. This doesn’t imply the application or its data is not secure. Almost all applications are based on the users identifying themselves as themselves or as a member of a role or group.

Determining the Appropriate Level of I&A

After all the possible ways of doing I&A, you may be left feeling a little overwhelmed. You shouldn’t. There is a practical and sensible approach to determining what tactics and techniques you should be using. Your I&A should be determined by considering the sensitivity of the data and the privileges and access rights of the user as well as by balancing other competing factors.

Your first guiding directive is based on what you are protecting. Stronger I&A is usually required as the sensitivity of the data increases. If an application allows users access to their favorite stock list, then simple passwords with no password restrictions may suffice. If the access granted to the person allows them to obtain the nuclear launch codes, then obviously strong authentication, probably multifactor authentication, is a good choice.

The second factor for determining appropriate I&A is based on what it is the user can see and do. Remember that the greater the privileges and the greater the access, the stronger the authentication. For general users whose access is controlled, strong passwords are generally a good practice. For users with more privileges, such as DBAs, stronger authentication is desirable.

You’re probably wondering, “Why not just use strong authentication all the time?” Before you invest in this approach, you have to consider that there are other tradeoffs. Practical security has to be balanced with usability, Chapter 2 which highlights how a password policy that is too strong can be self-defeating. Therefore, the correct authentication method must consider all aspects, not only the security. Note that this doesn’t imply that strong I&A is bad; you have to do the cost-benefit comparison that compares the cost of implementation to the cost of a security breech.