Category list

Effective Oracle Database 10g Security by Design - page 30

Summary

Identification and authentication form the foundation of security processes. They must be implemented first, and they must be done right. It doesn’t matter what clever access control and auditing capabilities are in place if the user is not really who you think they are, then everything else is a waste of time, code, and resources.

Other design decisions that are related to I&A also impact security. Preserving user identity to the database enables the use of database security that is consistent with the defense in depth principle. You have to design this security into your applications ahead of time.

The user-to-database modeling is important to forecast what database capabilities you’ll be able to use. At the very least you should separate user schemas and data schemas. Administrators shouldn’t share accounts. Sharing schemas should only be done when end-user identity can be preserved and the database privileges are identical for all users connected to the same schema.

Understanding the identification and authentication landscape is important to the way you formulate your security policies. You should carefully balance the security requirements—value and sensitivity of data—with the usability, administration, and costs associated with the various authentication technologies.

Chapter 4: Connection Pools and Proxy Authentication

Overview

In this chapter, you’ll explore identification and authentication (I&A) examples as related to Oracle. Our discussion begins with a brief history of authentication strategies. To put today’s security environment in perspective, you’ll briefly explore the previous computing architectures: host-based systems, the use of operating system authenticated users, and the client-server I&A model.

Web applications have their own special set of security challenges and requirements. Among them is how to effectively connect application users to the database in a way that meets the high-throughput demands of the Web and preserves end-to-end security. The predominant connection technique used today relies on database connection pools. This chapter will introduce you to the concepts of connection pools and their wide uses today. You’ll see how two different types of Oracle connection pools can be used to achieve performance. However, if they’re not used correctly—if you don’t preserve the user ’s identity—they can short circuit the database security.

The last half of the chapter shows the best technique for providing identity preservation with the connection pools. Preserving a user’s identity is a necessary step to enabling database security.

Heritage

To more fully understand identification and authentication as it is used today, it’s helpful to understand not only what challenges exist today, but also how they have come about. Evolutions in computing architectures over the years have changed the ways users identify and authenticate themselves to Oracle Databases. Let’s roll back (pun intended) a few years to get a better understanding of how this process has developed.

Host-Based Identification and Authentication

Back in the late 1970’s, when Oracle first began producing commercial databases, mainframes were the predominant computing platform within most organizations. Many users didn’t do their work from PCs or desktops mainly because PC computers weren’t as prolific as they are today. They conducted their work by logging in, usually via a dumb-terminal or “green screen,” to mainframes.

To gain access to an Oracle Database, it was quite common for users to first log on to the servers on which the database resided, and then to connect to the database. Instead of connecting directly to the database from a PC, which is common today, they first connected to the mainframe operating system (OS) and then connected to the database. In this environment, sometimes referred to as a host-based model , a user had to have an account on the OS and the database.

This configuration was sensible and secure. Security occurred in several layers : the user had to have an OS account; the user had to have the privileges to execute the program on the OS that would connect them to the database; and the user optionally had to also authenticate to the database.

In many cases, the database accounts could be directly associated with the OS accounts. This allowed the database to defer user authentication to the OS. Today, you still see support of this capability with Oracle’s OS authenticated users.

OS Authenticated Database Users

A DBA can create a user to be authenticated externally. For this discussion, external authentication means authentication will be done by the operating system. The database uses a parameter called OS_AUTHENT_PREFIX to translate the actual OS username to a corresponding database username. The default for this parameter is OPS$. The following example, executed from a Linux OS, illustrates how convenient and easy it is to create an OS authenticated user. To ensure that you create the account name correctly, check the value of the OS_AUTHENT_PREFIX (which has not been modified from the default):

system@DAGGER> SHOW parameter os_authent_prefix NAME TYPE VALUE ------------------------------------ ----------- ------ os_authent_prefix string ops$

Next, create your database user. The username is the prefix value concatenated with the OS username. For an OS user with the name of dknox, the database user account has to be “OPS$dknox”:

system@DAGGER> CREATE USER ops$dknox IDENTIFIED EXTERNALLY; User created. system@DAGGER> GRANT CREATE SESSION TO ops$dknox; Grant succeeded.

From the OS account, you can now connect without issuing a username or password. The database trusts the OS to correctly authenticate the user. Once this authentication occurs, the user is permitted access to the corresponding database account.

[dknox@dagger

dknox

]$ sqlplus / SQL*Plus: Release 10.1.0.2.0 - Production on Sun Mar 28 15:42:47 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining options SQL> sho user USER is "OPS$DKNOX"

With OS authenticated users, each database account is directly linked to an OS account. OS authenticated users was (and still is for some) popular because it allows users to sign on to the database without requiring them to provide another username and password.

Today, the support for OS authentication is a popular method for supporting batch processes that need to be run from the OS. The jobs can be queued and run independently without requiring scripts to store usernames and passwords, which is a security risk in itself.

REMOTE_OS_AUTHENT

As time went on, computer networking proliferated, and applications became more distributed. Support for distributed computing was also embraced by Oracle. The REMOTE_OS_AUTHENT initialization parameter extended the trusted authentication model to the network. This technology allowed the users to have OS accounts on machines other than the server where the database resided. This capability gained popularity as the computing industry moved from mostly mainframes to a mix of large and midsize computing servers, many of which ran UNIX.

Remote authentication was popular because it added flexibility into the operational environment. Users could still enjoy the convenience of single sign-on, and the database server was not required to maintain OS accounts for all the database users.

However, remote authentication, while useful, created a lot of risk. The following example illustrates why.

Caution

This is for demonstration—do not do this on your production servers!

I modify my database to support remote authentication. By default, the REMOTE_OS_AUTHENT is set to false. Setting it to true allows users on other servers to log in to my database without specifying a username or password.

system@DAGGER> ALTER SYSTEM SET remote_os_authent=TRUE SCOPE=SPFILE; System

altered

.

The new value for the REMOTE_OS_AUTHENT parameter will not take effect until the database is restarted. After restarting the database, I can connect to the OPS$DKNOX database user from an OS user called dknox located on a separate server. To illustrate this, I’ll create the dknox OS user on a server called SABRE and connect to my database, which is running on a server called DAGGER. My connect string requires no username and password; it requires only a TNS alias. After connecting, I query my environment to show the name of the user, the host they are authenticated from, and the name of the database:

[

dknox@sabre

dknox]$ sqlplus /@dagger SQL*Plus: Release 10.1.0.2.0 - Production on Sun Mar 28 15:52:00 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining options SQL> COL user format a10 SQL> COL "User's Server" format a15 SQL> COL "DB Server" format a15 SQL> SELECT USER, 2 SYS_CONTEXT ('userenv', 'host') 3 "User's Server", 4 SYS_CONTEXT ('userenv', 'db_name') 5 "DB Server" 6 FROM DUAL; USER User's Server DB Server ---------- --------------- --------------- OPS$DKNOX

sabre

dagger

The risk associated with allowing remote authentication lies in the fact that any administrator or knowledgeable person can create a user with any username and be connected to the database account corresponding to that username. The administrator for the database on DAGGER may not trust the actions occurring on the SABRE (OS) server or any other server. For many, distributing trust in this manner is considered too risky for production servers. Subsequently, this implementation is seldom used today, and the default for REMOTE_OS_AUTHENT is false. Leave it set to false!

Security for Host-Based Architecture

From the database’s perspective, security for the host-based architecture was uncomplicated. Regardless of how a user authenticated, every user had a separate and distinct database account (the 1:1 user-database mapping discussed in Chapter 3). Oracle subsequently invested great resources in ensuring that the database could support proper security at the user level. These capabilities included various mechanisms for user authentication, role-based access control, object privileges, system privileges, and auditing. All of these capabilities form the core security mechanisms provided by the Oracle Database today.

Client-Server Identification and Authentication

With the proliferation of the personal computer, the computing industry evolved from a host-based model for applications to the client-server architecture. This architecture evolved from a single entity, the host, to two entities, the client (on which the users resided) and the server (on which the database resided). Software companies, including Oracle, rapidly adopted support for secure client-server capabilities.

In this architecture, users always have two accounts, one for the OS and one for the database. It’s typical for the database server and the user’s personal computer to be running different operating systems. This heterogeneity provides a more user-friendly desktop computing environment and allows the users flexibility in choosing a computer that best meets their personal needs.

Many of the client-server applications are also graphical in nature and are much easier to use than the applications in the host-based model. This helps to extend the reach of the Oracle Database to users who no longer have to understand SQL. Reports can be easily run in which simple questions such as “What are the latest YTD numbers, and how do they compare to last years numbers ?” are transparently turned into SQL queries with the results formatted and graphically displayed to give the users the latest, up-to-date information.

Security for Client-Server Architecture

With client-server architecture, like host-based architecture, database access controls and auditing are based on the uncomplicated principle that each user has a separate and distinct database account. For client-server architecture I&A, the database (as opposed to the OS) usually performs the user authentication. While passwords were initially the predominant authentication technique, the computing industry soon began to develop standard ways to allow users to conduct single sign-on. Kerberos and DCE are two popular examples of single sign-on technologies developed to support the single sign-on requirement.

In a client-server connection, the user has a direct link to the server (or database) and vice-versa, much like a phone call, where two people are directly connected to each other and each knows who is on the other end. For client-server database applications, the user authenticates once and stays connected. This is important because the user’s identity is established once and is available to the database for the entire session (just as it is in a phone conversation). The database connections aren’t shared, and database security is based on the ability to easily distinguish between users.

With client-server computing, securing the network communications from the client to the server is essential to ensuring data is not maliciously copied or altered while in transit. Soon after Oracle began supporting the client-server architecture, they realized the need to secure those communication channels. Oracle subsequently enhanced its networking software and introduced Secure Network Services (SNS) to ensure that data would be protected not only while at rest in the database, but also while in motion to and from the database. SNS, was renamed several times until it finally ended up as the Oracle Advanced Security option, provided for SQL and data encryption. To operate in a secure client-server configuration, applications utilized the SNS capabilities.

Note

The client-server architecture also puts an emphasis on network security.

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

As the value of client-server applications was realized, practically all the major application development companies, most of which ran against database-derived data, built and deployed client-server applications. The client-server architecture was popular for many years and today still plays a significant role for many Oracle customers.