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
Other design decisions that are
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
In this chapter, you’ll explore identification and authentication (I&A) examples as
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
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.
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
Back in the late 1970’s, when Oracle first
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
This configuration was
In many cases, the database accounts could be directly associated with the OS accounts. This allowed the database to
A DBA can create a user to be authenticated externally. For this discussion,
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
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
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.
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
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.
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
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
[ 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
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
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
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
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
With client-server computing, securing the network communications from the client to the server is essential to ensuring data is not maliciously
The client-server architecture also puts an emphasis on network security.
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.