Carview!

Content preview·Buy reprint rights for this chapter

When Marlene Theriault's 91-year-old father learned that she was writing a book, his first question was, "What's it about?"

"Security," she said.

Astounded, her father asked, "You're writing a book about social security?"

To each one of us, the word "security" may mean something different, depending on how and where the word is used. To the elderly, security may mean a government-issued check deposited to their bank account each month. To a woman traveling alone, security may mean a hotel room door locked with both a deadbolt and a heavy chain. To a movie star or politician, it may mean a bodyguard who travels everywhere with them. To your company, it may mean maintaining a guard force to ensure that your office buildings are safe.

Just as a guard force helps ensure that people do not enter buildings or areas in which they don't belong, in the computer world "security" may translate into hardware, software, and a set of technical and personnel procedures that together help ensure that unauthorized people do not gain access to areas of information they should not see—and that authorized people do not jeopardize your system and data by exceeding their authority.

There are many facets to computer security. Most security practitioners identify the following different aspects of security:

Secrecy and confidentiality: Data should not be disclosed to anyone not authorized to access it.
Accuracy, integrity, and authenticity: Accuracy and integrity mean that data can't be maliciously or accidentally corrupted or modified. Authenticity is a variant on this concept; it provides a way to verify the origin of the data.
Availability and recoverability

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many facets to computer security. Most security practitioners identify the following different aspects of security:

Secrecy and confidentiality: Data should not be disclosed to anyone not authorized to access it.
Accuracy, integrity, and authenticity: Accuracy and integrity mean that data can't be maliciously or accidentally corrupted or modified. Authenticity is a variant on this concept; it provides a way to verify the origin of the data.
Availability and recoverability: Systems keep working, and data can be recovered efficiently and completely (with no loss of accuracy or integrity) in case of loss.

These terms may all appear to be quite similar, but in reality they are very different. And, different systems have mechanisms that achieve these goals in different ways. For example, encryption is a way of enforcing secrecy and confidentiality. Passwords and digital signatures aid in enforcing authenticity. Backups are a way of helping to guarantee availability and recoverability. Auditing helps ensure accuracy and integrity. Depending on your specific environment and user base, some of these aspects of security may be more important than others. In a classified military environment, for example, secrecy is usually the most important goal. In a banking environment, accuracy and integrity of data may be more important. For most of us, availability and recoverability of data may be more important than anything else as we go about our daily work.

What is your environment? Where are the threats to your system's security coming from? What actions can you take to protect your Oracle databases? How much action is enough to protect your company's valuable data without compromising your systems' performance and your employees' rights?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Oracle security model is a multi-layered one. It incorporates the protection of files and objects both inside and outside the database, as well as a variety of administrative policies and technical strategies. This section provides a brief overview of the range of features that comprise "Oracle security." Subsequent chapters will describe these features and explain how they work together.

Have you ever wrapped a very tiny present and decided to have some fun with the person receiving the gift? You put the very small gift-wrapped package into a larger box, put some kind of filler in the box to hold the package still, wrap that package in the same or different wrapping paper, and put it into another box. You continue using larger and larger boxes until the true size of the gift is totally hidden from view. Like packing one box inside of another, there are actually several layers of security involved in setting up and maintaining the protection of your database and system. In some respects, you are hiding layers of files and data from the general user's view. You are also making some determination about how much security is enough to protect your own particular system, database, applications, and specific data.

The layers of security which you can implement consist of the following:

Protecting the Oracle operating system files—the RDBMS and Oracle software
Protecting the application code which interacts with your Oracle database
Controlling connections to the database
Controlling access to the database tables through roles, grants, triggers, and procedures
Controlling access to a table through views, triggers, and procedures

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Whether you are an IS manager, a system administrator, or a database administrator, there are many different procedures, policies, and plans you may be called on to help develop as your organization plans its security. In this book, we use the following definitions:

Procedures: These map out, in a step-by-step fashion, the actions you need to take to perform a job successfully.
Policies: These detail your company's rules and reflect your company's standards and code of ethics.
Plans: These are documents that outline the approaches to be used to implement and enforce your company's policies.

Within the realm of database security, you may need to construct the following:

Security policies and an accompanying security plan
An auditing plan and procedures
A database backup and recovery plan and procedures

The auditing plan and the database backup and recovery plan are sometimes included in the security plan.

The following sections briefly examine these different entities and discuss why they are important to your organization.

Over lunch on several days during the International Oracle User Group conference (IOUG-A Live) in the spring of 1998, we casually discussed with several groups of DBAs the topic of Oracle security and security plans. To a person, everyone agreed that security policies (which outline the company's position on security issues) and a security plan (which describes, in detail, how the policies will be implemented and enforced) are vital to an organization. However, all of the DBAs we talked with admitted that they have been fighting an uphill battle to get their company's management to allocate the funds and lend the support needed to create such security policies and to write and enforce a workable security plan.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle provides many software tools, operating system files, and database constructs that help you protect your data. Some tools, delivered with the basic Oracle system, you are likely to use every day; others you might use only in certain environments and situations to impose a higher level of security on your system. There are also "add-on" tools you can purchase from Oracle at additional cost that provide higher levels of data protection. This section takes a quick look at the various available tools and examines what each of these tools provides for you.

There are two particular products delivered with the basic Oracle8 product set that will help you implement better database security. They are the Oracle Enterprise Manager (OEM) and the Oracle Security Server (OSS) Manager.

Oracle Corporation currently delivers the OEM with the base product set. The OEM is a set of utilities which are personal computer-based and use a graphical user interface (GUI). These utilities provide a way to manage one or more of your databases from a single computer. The components of the OEM are easy to use and let you perform many of your day-to-day DBA functions either interactively or on an automatic, scheduled basis.

The OEM is delivered with Oracle8 and includes the following features:

A set of database administration tools
An event monitor you can configure to watch for specific situations within your databases
A job scheduler to perform maintenance tasks on a scheduled basis
A graphical interface to the Recovery Manager tools (not available in Oracle7)

The OEM is supported to work with Oracle version 7.1.6 databases and higher.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When we say the word "refrigerator," what do you think of? Do you picture a large, rectangular box with one door or two? Or do you picture the objects inside—fresh vegetables, dairy products, ripe fruit? What does a refrigerator have to do with Oracle and security? Well, nothing, really, except that you can see the exterior of a refrigerator and not have any idea what the box contains. You see the structure—its shape and features—but the contents are a mystery to you until you open the door and look inside.

Just as the refrigerator has an outside structure, an Oracle database is comprised of a number of complex pieces of software that enable you to create data storage areas (databases) and to develop applications that interact with the databases. Once a database is created, you can see the "objects" inside. Unlike a refrigerator, which is a physical entity containing only physical objects, an Oracle database is comprised of both physical files and logical representations (the objects inside).

To determine what actions you need to take to protect your system, you first need to understand the composition of that system. You need to understand what part each component plays within the system and how the pieces interact. Once you have a basic grasp of the Oracle components, you'll be better able to create your security policies and implement a sound security plan for your organization.

This chapter provides an overview of the Oracle components that comprise the Oracle file system (external components) that are relevant to RDBMS security. Chapter 3, describes the logical (internal) database components or objects.

The physical components of an Oracle database system consist of the basic, delivered Oracle software and various storage files for each database. The storage files contain different forms of information. Those most relevant to security are:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The physical components of an Oracle database system consist of the basic, delivered Oracle software and various storage files for each database. The storage files contain different forms of information. Those most relevant to security are:

Tablespace datafiles: These datafiles can contain data, indexes, rollback segments, or temporary segments. Rollback segments and temporary segments are explained here. Data and indexes will be explained in Chapter 3.
Rollback segments: These segments are used to keep track of the way the data looked before it was changed. During recovery, any uncommitted (saved) transactions still in the rollback segments are used to "roll back" these changes to restore the database to the last stable state.
Temporary segments: These segments are allocated by Oracle when a user session requires an area in which a sort operation must be performed. Temporary segments are used by the RDBMS as a "scratch pad" to build temporary tables in order to do work requested by the user. These segments are created in the tablespace that has been designated the TEMPORARY tablespace.
Control file: A form of binary storage file used to keep track of the status of the physical structure of a database. This file enables the database to start up and is used for database recovery. It records several critical maximum values and tracks the archive log numbers as well as the location and status of each tablespace datafile in the database.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Many people use the terms instance and database interchangeably, but the instance and the database are actually separate entities. Let's look at what happens when the database is started from the Server Manager utility (svrmgrl, svrmgrm, or svrmgr30 for Oracle8 on Windows NT). The startup process follows:

The background processes are started.
The System Global Area (SGA) is allocated in memory.
The background processes open the various files.
The database is ready for use.

During the startup, messages are displayed that notify you of what is happening. Here is a sample startup sequence:

SVRMGR>  startup
ORACLE instance started.
Total System Global Area      11865072 bytes
Fixed Size                       33708 bytes
Variable Size                 10672196 bytes
Database Buffers               1126400 bytes
Redo Buffers                     32768 bytes
Database mounted.
Database opened.

You can see that the instance is started before the files that constitute the database are opened. The instance consists of the background processes and the SGA. The SGA totals are listed before the database is started but after the "ORACLE instance started" message. The database is the collection of logical objects and physical files necessary to support the system, and the database system is the instance, SGA, and files. Figure 2.1 shows the components of the database system after the database has been started.

Figure 2.1: Components of the database system after startup

Now, while what we've said is technically accurate, you'll find the actual usage at Oracle sites to be quite different. Many people use the terms "instance," "database," and "database system" interchangeably. In this context, they are referring to the entire system: the background processes, the System Global Area, and the data, control, and redo log files.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The database is comprised of several different types of files that serve different functions within the system. We describe each component in the following sections.

A tablespace is an internal object used to represent a physical storage area. It is the logical name for an entity used to access the operating system files called datafiles. In this book, when we speak of a tablespace we will mean the logical tablespace name as well as the files assigned to it. Oracle uses a tablespace to house the following different kinds of structures:

Database object structures—like tables, indexes, packages, procedures, triggers, etc.
Rollback segments
Temporary sort segments

A tablespace must have one or more datafiles assigned to it; otherwise, it cannot be created. These files may be on one or more disks. When you create a table or index, you will specify the tablespace in which the object is to be created. If this is not done, then your default tablespace will be used. The STORAGE clause used in the CREATE statement will determine how much space is allocated from the tablespace. Several examples of STORAGE clauses appear in the following sections. When this initial allocation is filled, more space will be assigned, this time according to the NEXT parameter in the STORAGE clause. The DBA monitors space utilization and, as the tablespace becomes full, will need to add more datafiles or rebuild the tablespace to combine the current datafiles and increase the allocation of space.

Figure 2.2 shows the layout of a tablespace's datafiles on more than one disk.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In Chapter 2, we described the Oracle operating system files — the physical files that are particularly important to Oracle security. This chapter looks at the internal Oracle components that are accessible only after the database is started. In contrast to the external components, which are individual data files, these components cannot be physically "touched" or identified from outside the database. They exist as components within the large operating system files created for Oracle. They contain the objects and the data dictionary. You can manipulate these components, but only by using the SQL language.

In May, 1998 at the Mid-Atlantic Association of Oracle Professionals' spring conference, one of the sessions played a game of trivia during which many questions about Oracle were asked. One of the "stumper" questions was this: "What was the forerunner of the SQL*Plus language called?" The answer, in case you ever end up in a trivia game yourself, is UFI — User Friendly Interface. UFI was an early SQL command interpreter. The DBA would use UFI to create users much as they would use SQL today. Users would log in to the database via UFI. The DBA could also give the user enough privileges to create tables and other objects. SQL has since become the standard language for manipulating data in modern relational databases.

Just as the SQL name has changed over time, so has other nomenclature. For example, let's look next at the changes in the terms "user" versus "schema."

Oracle7 introduced the schema concept. Like the "instance" vs. "database" terms we discussed in Chapter 2, "user" and "schema" are frequently used interchangeably. A user is equivalent to a computer account. The DBA will create a user account, assign a password, and define a default working tablespace, a temporary sort area, and quota — if the user is to be allowed to create any database objects.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In May, 1998 at the Mid-Atlantic Association of Oracle Professionals' spring conference, one of the sessions played a game of trivia during which many questions about Oracle were asked. One of the "stumper" questions was this: "What was the forerunner of the SQL*Plus language called?" The answer, in case you ever end up in a trivia game yourself, is UFI — User Friendly Interface. UFI was an early SQL command interpreter. The DBA would use UFI to create users much as they would use SQL today. Users would log in to the database via UFI. The DBA could also give the user enough privileges to create tables and other objects. SQL has since become the standard language for manipulating data in modern relational databases.

Just as the SQL name has changed over time, so has other nomenclature. For example, let's look next at the changes in the terms "user" versus "schema."

Oracle7 introduced the schema concept. Like the "instance" vs. "database" terms we discussed in Chapter 2, "user" and "schema" are frequently used interchangeably. A user is equivalent to a computer account. The DBA will create a user account, assign a password, and define a default working tablespace, a temporary sort area, and quota — if the user is to be allowed to create any database objects.

Users can log in to the database and perform work. They can create and own objects such as views, tables, and stored programs. In general, any object a user creates is considered to be owned by that user. However, there are exceptions to this general rule , such as a user who is granted system privileges that allow him or her to create objects on behalf of another user.

From an Oracle perspective, the schema refers to all of the objects owned by a user. There is a CREATE SCHEMA statement, but on closer examination, you will find that the schema name used in this statement must be the same as the Oracle username.

The CREATE SCHEMA statement is used to create all user objects at one time in a single statement. But it allows only three operations:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Prior to Oracle8, the term "object" was used loosely to mean any entry in the data dictionary other than a user. Thus, an object could be a table, synonym, view, index, stored procedure, trigger, and so on. Oracle8 introduced the concepts of both an object-relational database and a user-created object type. Discussion about Oracle8 new features becomes very confusing as soon as relational objects, database objects, and object types are mentioned.

In this book, the term "objects" will be used in the generic sense to mean any item created by a user and will include (but not be limited to) tables, synonyms, views, indexes, stored procedures, and triggers, and so on. "Object" references may include Oracle8 "object types" as well. We will try to make the distinction as clear as possible in all cases.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A table is the basic building block for storing data in the database. Conceptually, a table is a file that is created and maintained within the data files assigned to the database.

When a user creates a table, he or she specifies parameters such as the table name, the column names, their data types, and their lengths. The tablespace is also usually specified in the CREATE statement to ensure that the table is created in the proper tablespace. If a tablespace name is not specified, the table will be created in the user's default tablespace. An initial amount of space is specified for the table's data using the INITIAL parameter of the STORAGE clause in the CREATE TABLE statement. When that amount of space is filled, more space will be allocated based on the value set by the NEXT parameter in the STORAGE clause. If no STORAGE clause is present in the CREATE TABLE statement, the default storage values of the tablespace in which the table has been created will be used. The kernel handles this allocation, and the space comes from the datafiles assigned to the tablespace in which the table was created.

Suppose the database has a data01 tablespace and there are three datafiles named persdata01.dbf, persdata02.dbf, and persdata03.dbf associated with the data01 tablespace. User mary creates a table named "employee" and specifies the data01 tablespace. A command such as the following might be used while maryis logged into SQL*Plus:

CREATE TABLE employee
  (employee_num          NUMBER(6)   NOT NULL,
   employee_name         VARCHAR2(20),
   employee_location     VARCHAR2(10),
   manager_name          VARCHAR2(20),
   init_employment_date  DATE,
   title                 VARCHAR2(20))
TABLESPACE data01
STORAGE (INITIAL    275K
            NEXT    50K
      MAXEXTENTS    UNLIMITED
     PCTINCREASE    0);

The kernel will allocate 275 Kbytes in one of the three datafiles of the data01 tablespace. Generally, the space allocated will be the first contiguous space found that is greater than or equal to the size specified for the initial allocation. If many tables have already been created, this table may be in file

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A trigger is a special stored program attached to a table. A trigger is executed when the event on which it is based occurs. There is a distinct difference between triggers and other types of stored programs. A trigger is directly associated with a table and always includes the event for which an action will occur. Since execution, or "firing of the trigger," is controlled only by this event, triggers cannot be executed directly by a user. In contrast, stored programs can be executed directly by an authorized user.

A trigger cannot be created unless the text of the command includes a table name. Consequently, if the table is dropped, the trigger will also be dropped automatically.

The statement that creates a trigger includes both the triggering event and the table name. You can specify that a trigger is to be fired either before or after the event, followed by one or more actions such as INSERT, UPDATE, or DELETE. Within Oracle8, you can also specify the INSTEAD OF condition. That is used to solve the problem of updating through complex views. All triggers are based on modification events. Triggers can be quite useful from a security standpoint to track or prevent activities that change the data.

For security and/or monitoring purposes, tables are frequently created with extra fields to capture the username, time, and modification action performed. These additional columns are intended to be used for auditing. However, such a scheme only retains the last event unless you take other precautions to preserve the history. The information is also available to any user with the SELECT privilege on the table. A trigger can be used instead of maintaining this auditing data within the table. The trigger would be set to execute before an INSERT, UPDATE, or DELETE for each row. The trigger body would write audit information to another table. This data could include the name of the table being modified, the date and time, the username, and any other pertinent information, including the actual data before and after the modification was made. The user performing the action does not have to have any privileges on the underlying "audit" table where this information is written.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle allows a definition to be stored in the data dictionary that describes how data is to be retrieved from one or more tables. This logical definition is called a view. A view may be thought of as a layer on top of the tables that actually contain the data. Views do not store any data themselves; they only define what data is to be retrieved and, in many cases, the restrictions for retrieving the data. Views are treated exactly like tables when data is being selected. In fact, in the sample statement below, there is no way to tell if the table emp_sal specified in the query is really a table or a view:

SELECT employee_name, current_salary 
  FROM emp_sal;

Figure 3.1 shows how a user would access the view as though it were a table and how execution of the view causes required data to be retrieved from several tables.

Figure 3.1: View implementation

Views can be used for several purposes relevant to security; for example, they can simplify user access by pre-joining tables and they can limit the data retrieved.

Consider the two tables referenced in Figure 3.1: employee and salary. The employee table contains the constant employee information such as name, social security number, and other basic data which is not expected to change. The salary table contains the employee number along with the employee's job, when the job was started and ended, as well as the salary. This type of structure would be used to maintain a history of employment. To simplify access, a view could be written to join the two tables and provide salary data along with the employee's name so that an authorized user could write a simple query as shown in the SELECT statement. Using the employee and salary tables, the view could be created as follows.

CREATE OR REPLACE VIEW emp_sal AS
SELECT a.employee_num, a.employee_name, a.init_employment_date, b.salary
  FROM employee a, salary b
 WHERE a.employee_num = b.employee_num;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Programs written in PL/SQL can be stored in compiled form in the database. These programs are referred to as either procedures or functions. The only difference is that, by definition, a function must return a value, while a procedure does not have to return a value. However, there is no problem in coding a procedure to return a value. Stored programs can be executed by table triggers, applications, or users. In this book, we use the phrase "stored program" or "program" to refer to both procedures and functions.

Stored programs can be created using either of two methods. You can create the program simply as a program, or you can create it in two parts: a package that specifies the programs to be implemented, and a package body containing the actual code.

Users or applications can execute stored programs provided that EXECUTE permission on the program has been granted. Unlike table triggers, procedures and functions are executed by an explicit call. For example, suppose a procedure called give_raise has been written and requires an employee number and percentage of raise as arguments. The call might then look like this:

SQL>  EXECUTE give_raise(8138, 23);
PL/SQL procedure successfully completed

Executing a function interactively is a bit more complex, as a function always returns a value and the calling statement structure must be able to receive the value. Stored functions can be included in SQL statements. The position in the SELECT statement satisfies the requirement for a place to receive the return value from the function. However, since only a single value is returned and not a set of values, this is generally useful only in SQL statements that are expected to return only one row.

A package is a method of creating a program in two parts, the specification and the body. There are several advantages to using the package approach. One of these is

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A synonym is another name for something. We use synonyms every day. Probably the most common synonym usage is a nickname or familiar name used in conversations. Few people named "William" are called by that name, except perhaps William the Conqueror. Most Williams prefer to be called "Bill." Margaret becomes Peg or Peggy, John becomes Jack (which doesn't make much sense. But then, most of the "synonyms" we describe in the following sections don't make much sense either!).

Synonyms are used in the Oracle database to provide location transparency by concealing the owner and location of the object. (The next section explains why you might want to do this.) The object can be a table, view, stored procedure, package, function, snapshot, sequence, or even another synonym. Synonyms in the Oracle database are either public or private. If a synonym is public, then all users can reference the synonym even though they may not have privileges to access the underlying object itself. In that case, an error is returned. If a user creates a synonym without the PUBLIC keyword (a special privilege is required to do this), then the synonym is private and can be used only by the user who created the private synonym.

The primary reason for using synonyms is to avoid the requirement to know the owner of the object. If mary owns the employee table and ralph wants to select some data from it, then the command would look like this:

SQL> SELECT * 
       FROM mary.employee;

If ralph does not know that mary owns the table, ralph will have a problem attempting to access that table. On the other hand, mary (a privileged user) creates a public synonym with the following command:

SQL> CREATE PUBLIC SYNONYM employee 
        FOR mary.employee;

mary then grants access privileges by way of the synonym either to PUBLIC or to specific users. All of those users granted the privilege can refer to the synonym without adding

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The DBA can create a user account but, until the CREATE SESSION system privilege is granted, that user cannot even log on or connect to the database. Once a minimum privilege of CREATE SESSION is granted, the user can connect, but cannot access any objects other than those granted explicitly to him or to the public user.

The public user is a special entry in the data dictionary. Privileges granted to public are automatically available to every user in the system who has been granted the CREATE SESSION privilege. Granting public access is commonly done with tables containing data that must be made generally available, or for objects where access by any user will not compromise anything within the database. A typical example of an area of information which can be made available to the public to view is a States lookup or reference table which contains state names and their respective abbreviations. There is no reason to prevent anyone from seeing the values in the States lookup table, although there would be good business reasons to prevent the general public from having INSERT, UPDATE, or DELETE privileges on this table.

Privileges fall into two general categories: system privileges and object privileges. System privileges allow the user to log on to the system and create or manipulate objects. Object privileges allow the user some sort of access to the data within an object, or allow the user to execute a stored program.

Section 3.8.1.1: System privileges

Some examples of system privileges are:

CREATE SESSION
CREATE TABLE
ALTER SESSION
CREATE ANY VIEW

There are more than 80 system privileges available to Oracle users. The number of privileges will vary depending on the Oracle version being used. A complete list can be found in the data dictionary table called system_privilege_map. In the above list, the CREATE SESSION privilege is the only privilege required for a user to connect to a database. In a secure system, this might be the only privilege granted to a user. In general, when considered at the lowest level, the system privileges do not convey any rights for data access. The lowest-level system privileges are the ones like the first three items in the preceding list. However, note the word ANY in the fourth item. The CREATE ANY VIEW privilege allows exactly that. Any user given this privilege may create a view within any other user's area. For example, if user

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A role is a named collection of privileges. A role may be assigned to a user, but a user cannot be assigned to a role. For example, users can log in to the database; roles cannot. A user can own objects while a role cannot. The function of a role is to group logically associated privileges and allow those privileges to be passed to a user by referencing the role. Consequently, when a user is assigned (granted) a role, that user inherits all the privileges assigned to the role. If the role's privileges are later changed, then the new privileges will be in effect the next time the user logs in to the database.

In an Oracle system, grants are issued on individual tables to individual users. This sounds very simple, but when hundreds of tables and users are involved, the implementation and management can be very complex. The usual situation is for a manager to ask the DBA to give ralph the same privileges that mary has. If grants have been made to individual users, the first task will be to find out what privileges mary has, and then create a script to duplicate those privileges, and finally to run that script to give ralph the privileges.

The action of giving an employee the same privileges as another employee is much easier if roles are used. The DBA creates a role and grants some privileges to it. These privileges can be a mix of system and object privileges. In a financial system, there might be a FINCLERK role. This role could include the CREATE SESSION privilege to allow the users to log on, and also some grants to enable table access. In turn, this role is granted to all the users who need the financial clerk privileges. In many systems, only one or two roles are needed by most users. The task of identifying the privileges held by a specific user now becomes a simple matter. The DBA can query the data dictionary view, DBA_ROLE_PRIVS, and specify the user of interest, as shown in the next section. (Chapter 4, describes this view and other data dictionary views.)

Figure 3.2 shows the difference between assigning individual privileges to each user and assigning the privileges to a role and then assigning the role to each person.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle provides a way for you to control the allocation and use of resources in the database on a user-by-user basis. This mechanism is known as a profile . There are actually two different (and unrelated) types of profiles available in an Oracle database:

Product profiles
System resource profiles

Product profiles let you block access to specific commands or Oracle products. For several releases prior to Oracle7, you could control user access to almost any product by creating a record in the PRODUCT_USER_PROFILE table. For example, suppose that you did not want the user mary to be able to log on to SQL*Plus interactively. You would put an entry into the PRODUCT_USER_PROFILE table which indicated that mary was not to be granted access to SQL*Plus. In Oracle7, the PRODUCT_USER_PROFILE table was replaced with two tables: PRODUCT_PROFILE and USER_PROFILE.

Oracle 7 also introduced the concept of the system resource profile, which is used to limit the amount of database system resources available to each user; you limit resources by establishing a profile for each user. Oracle supplies a default profile for users who do not have custom profiles.

Chapter 6 , describes both product profiles and system resource profiles.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When you want to find out the meaning of a word, how it is spelled, or its derivation, what do you do? Generally, you go to the nearest dictionary and look up the word. In the same way, when you want to find out the contents of your database, you go to the data dictionary to look up the information of interest. From a security perspective, you will use the data dictionary to gain information vital to your security implementation. If you are a DBA, you will spend a great deal of your time interacting with the data dictionary to gain information about the various objects with which you must interact. You will use the data dictionary to examine user accounts and user quota assignments, to look up the location of datafiles on your system, and to obtain the information you need to perform your job effectively. As a developer, you will use a different view of the data dictionary to keep track of the schema objects within your application. If you are a casual application user, you might never even see a data dictionary entry directly.

Oracle's data dictionary consists of two layers: the tables that make up the real data dictionary and a series of views that allow you to access the information in the data dictionary. Most of the data dictionary views are written to restrict your access to only the data appropriate for your specific level of privilege. The views have meaningful names along with equally meaningful attribute names. In this chapter, we will examine the following information about the Oracle data dictionary:

What the data dictionary is
How the data dictionary is created
How the data dictionary is structured
What type of information is available
How that information may be used in a security system

For more detailed information, see the Oracle8 Concepts Manual, Release 8.0, Part A58227-01, Chapter 4, "The Data Dictionary."

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Oracle data dictionary is primarily an internal record of the state of all objects in the database. These objects include:

Tables
Users
View definitions
Indexes
Triggers
Sequences
Constraints on tables and columns
Database links
Synonyms
Stored programs

The data dictionary is created by the RDBMS when the database is initially created. The data dictionary is maintained by the RDBMS based on actions performed by the users, application developers, or database administrator. The data dictionary we use is actually a dictionary of the "metadata," the data that describes the objects in the database. These values are dynamic and are changed by the RDBMS as the objects change. For instance, when you create a table, the table name, along with the names of all the columns in the table and the column characteristics, are recorded in the data dictionary. If you drop the table or rename it, or if a column definition is modified, then the appropriate entry in the data dictionary is updated by the RDBMS on your behalf. In fact, you cannot directly make changes to the data dictionary—regardless of your privilege level.

Modification of a column definition does not mean there is a change in the data stored in the column—only that a change has occurred to some characteristic of the table's column such as the column name, data type, length, or other characteristic.

There is an audit function supported by the data dictionary. If you set it up properly, the data dictionary will also track, within a collection of audit tables, actions performed by users. From a security perspective, being able to keep track of users who have made modifications to a specific table can be very important. For example, you might want to keep track of who has modified salary information and when the updates were made. (Auditing is discussed more fully in Chapter 5, Chapter 10, and Chapter 11.) Finally, by learning how the views were constructed in the data dictionary, you will see how access to table data can be controlled down to the row level.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Data dictionary views may be grouped into four general categories:

Those showing data about a user's own objects, labeled "USER_"
Those showing data available to any user in the database, labeled "ALL_"
Those showing data available to any DBA, labeled "DBA_"
Everything else

Oracle provides, within the data dictionary, a view that you can access to see the composition of all of the data dictionary views. If you query the DICTIONARY view, you will find the specific names of data dictionary objects you have the privilege to access. You will not see any other objects. The DICTIONARY view is very simple, containing only two columns: table_name and comments. The column "table_name" is somewhat misleading because all of the "table" names are really views, synonyms, or other objects. None of the entities listed are actually tables. Here is a very small sample section of the DICTIONARY view, with minor formatting, so you can see what we are talking about:

SQL> COLUMN table_name FORMAT A20
SQL> COLUMN comments FORMAT A50 WORD
SQL> SELECT * FROM DICTIONARY;
TABLE_NAME           COMMENTS
-------------------- --------------------------------------------------
ALL_ALL_TABLES       Description of all object and relational tables 
                     accessible to the user
ALL_ARGUMENTS        Arguments in object accessible to the user
ALL_CATALOG          All tables, views, synonyms, sequences accessible
                     to the user

If you create a user in a version 8.0.4 database and give that user just the ability to connect to the database (the CREATE SESSION privilege), the list of table names available to that user, as obtained from the DICTIONARY view, would total 242, distributed as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Why would you want to know about the internal structure of the data dictionary of the database? Not only because some of the best examples of basic security implementation may be found within the Oracle data dictionary, but also because the more thoroughly you understand how the database is put together, the better you can protect it.

The data dictionary uses base tables for which nobody has implicit privileges, and many views, most of which are qualified so the user can see only those rows that are appropriate. Source code for the data dictionary is found in the SQL.BSQ file, which is read when the database instance is created. The views (with which many users are familiar) are created by CATALOG.SQL. There are other views created by other programs that are executed at database creation time, but we'll focus on these two scripts in our examples here.

In general, unless a user has been granted the DBA role, or comparable privileges, that user cannot access these base tables. These base tables are created when the SQL.BSQfile is executed at the time of database initialization and owned by a user known as sys. All of the views, including those that will be addressed later, are based on the base tables created from the statements in this file.

You must not modify the SQL.BSQ file or the CATALOG.SQL file, but you'll find the scripts within the files very educational. Therefore, we suggest that you copy the files to other file names and then walk through the copies. If either of these files is modified and something goes wrong with your database, Oracle Support may not be willing to assist in the repair efforts based on the modified files.

The Oracle-delivered scripts for these files can be found in the.../rdbms/admin directory on most systems. For a Windows NT database, the files will be found in:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Although the data dictionary views provide a wide range of information about the state of the database, the views shown in Table 4.1 are particularly important for providing security information. We've also listed the tables on which these views are built. We'll examine each of the views listed in this table later in this chapter.

Table 4.1: Data Dictionary Views for Security
View Name	Type of Information Available	Tables on Which View Is Built
DBA_PROFILES	Profiles and their associated resource and time limits	profile$, profname$, resouce_map, obj$
DBA_ROLES1	All roles that exist in the database	user$
DBA_ROLE_PRIVS1	Roles granted to users and other roles	user$, sysauth$, defrole$
DBA_SYS_PRIVS1	System privileges granted to users and roles	user$, sysauth$, system_privilege_map

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This section examines each of the views important for security. We will look at the information the view provides and whether the view has an ALL_ and/or USER_ counterpart. You will also find details on the composition (the columns) for each view and an explanation of why the view is important from a security perspective.

The DBA_PROFILES view lists all profiles and their limits. This view determines what profiles exist in your database, what resources have been limited, and what the limit is for each resource. The new password parameters are set by creating a profile. This view enables you to see the values to which the password limits have been set. The resources not marked as "PASSWORD" in the output below are parameters that are also available in a version 7 database. Columns include:

PROFILE: Profile name. Limited to 30 characters
RESOURCE_NAME: Name of resource controlled by profile
LIMIT: Limit placed on this resource for this profile
RESOURCE_TYPE: Added in Oracle8; indicates whether the profile is KERNEL or PASSWORD

If you never create a profile in an Oracle8 database, this view will contain the sixteen rows shown below. We have added formatting statements to be able to fit the information on one line for each row.

SQL> COLUMN profile FORMAT a10
SQL> COLUMN resource_name FORMAT a25
SQL> COLUMN limit FORMAT a10
SQL> SELECT * 
  2    FROM dba_profiles 
  3   ORDER by 1, 2;
PROFILE    RESOURCE_NAME             RESOURCE LIMIT
---------- ------------------------- -------- ----------
DEFAULT    COMPOSITE_LIMIT           KERNEL   UNLIMITED
DEFAULT    CONNECT_TIME              KERNEL   UNLIMITED
DEFAULT    CPU_PER_CALL              KERNEL   UNLIMITED
DEFAULT    CPU_PER_SESSION           KERNEL   UNLIMITED
DEFAULT    FAILED_LOGIN_ATTEMPTS     PASSWORD UNLIMITED
DEFAULT    IDLE_TIME                 KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_CALL    KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_SESSION KERNEL   UNLIMITED
DEFAULT    PASSWORD_GRACE_TIME       PASSWORD UNLIMITED
DEFAULT    PASSWORD_LIFE_TIME        PASSWORD UNLIMITED
DEFAULT    PASSWORD_LOCK_TIME        PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION  PASSWORD UNLIMITED
DEFAULT    PRIVATE_SGA               KERNEL   UNLIMITED
DEFAULT    SESSIONS_PER_USER         KERNEL   UNLIMITED
16 rows selected.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Okay, get your pencil and paper out. It's time for a pop quiz!

Question 1. You've just created an Oracle database. How many user accounts exist on your system?

1
2
8
It depends on the version

Question 2. With the same database as above, how many roles exist in the database?

1
3
6
It depends on the version

If you answered number 4 — "It depends on the version" — for each of the questions, you are correct. Give yourself a gold star.

As we mentioned in Chapter 4, when you create an Oracle database, Oracle performs many tasks in conjunction with the creation. There are several scripts that are run. Some of these scripts create default roles and default users in your database.

Chapter 3, explains that using roles can help you lighten your workload by letting you assign many privileges to many users quickly by performing the following tasks:

Create a role
Grant to the new role the privileges you want the set of users to have
Grant the role to each of the users

Oracle supplies several default ("canned") roles that you might use to quickly assign privileges to users. However, there are problems you need to be aware of when you use these Oracle-supplied default roles. In order to completely protect your database, you need to know what the default roles are and the advantages and disadvantages of using these roles.

Oracle also creates several default users within a database. Unfortunately, the usernames and passwords for these accounts are hardcoded into the scripts that reside in your operating system's Oracle directory structure. Many of the passwords are commonly known by most DBAs, and many are easy to guess.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Those of you who are familiar with earlier versions of Oracle (before version 6) will remember that three privilege levels were defined before the concept of roles was introduced in version 6. Oracle used the same names for the new roles that were used for the privilege levels. These are CONNECT, RESOURCE, and DBA. Four more roles were added in version 7.1.6: SYSDBA, SYSOPER, EXP_FULL_DATABASE, and IMP_FULL_DATABASE; these roles did not exist as privilege levels in earlier releases. Several more default roles have been added in Oracle8: DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and SELECT_CATALOG_ROLE. The following list briefly describes how the default roles are used.

There is one more default user group that exists in every database — public. public is a user group that is really a quasi-role; every user who is created belongs automatically to the public group. public does not have a password and cannot connect to the database, and, like a role, cannot own objects. See the section "Grants to public" later in this chapter.

CONNECT: Allows users to log into the database, to create objects, and to perform exports.
RESOURCE: Grants users the privileges necessary to create procedures, triggers, and (for Oracle8) types within the user's own schema area.
DBA: Allows users virtually unlimited privileges.
SYSDBA: Allows users to connect to the database and remotely perform privileged actions like starting up and shutting down the database (basically equivalent to the DBA role).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The CONNECT role was originally intended to allow users to log in to the database. In versions of Oracle before version 6, the CONNECT privilege enabled a user to create a session in a database and allowed little else. In version 6, the CONNECT role was given the privileges shown in Table 5.1 and allowed the user to connect to the database, to create views, synonyms, and database links, and to perform table or user exports. The CONNECT role now conveys far more privileges than the original CONNECT privilege did. The most critical system privilege allowed by this role is CREATE SESSION; without this system privilege, the user cannot log on to the database.

The system privileges for the CONNECT role, shown in Table 5.1, are the same for versions 7.X and 8.0 of the database.

Table 5.1: CONNECT Role System Privileges
Privilege
ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW

Only the CREATE SESSION privilege is required for a user to log in to the database. The remaining privileges, except for CREATE SYNONYM, are generally not required by a user whose primary interface is through applications.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The RESOURCE role grants a user the privileges necessary to create procedures, triggers and, in Oracle8, types within the user's own schema area. Granting a user RESOURCE without CONNECT, while possible, does not allow the user to log in to the database. Therefore, if you really must grant a user RESOURCE, you have to grant CONNECT also — or, at least, CREATE SESSION — so the user can log in.

The system privileges for the RESOURCE role are shown in Table 5.2.

Table 5.2: RESOURCE Role System Privileges
Privilege
CREATE CLUSTER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE (new in Oracle8)

There are several potential problems with the use of the RESOURCE role.

Section 5.3.2.1: The Oracle-supplied roles can be moving targets

As we mentioned earlier in the section "About the Defaults," the system privileges of an Oracle-supplied role may change with a new version or upgrade release. For example, the privileges listed in Table 5.2 are from an Oracle8 RESOURCE role. Note that in an Oracle7 database, the CREATE TYPE privilege does not exist. There is another problem that has as much or more impact on your database security, which we examine next.

Section 5.3.2.2: UNLIMITED TABLESPACE access

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The role that literally has the "keys to the kingdom" is the DBA role. With few exceptions, this role will allow the granted user to do almost anything he wants within SQL*Plus or the Server Manager Utility. Well, almost anything. Unless the user has also been placed in the system group, which enables DBA access and the ability to CONNECT INTERNAL or CONNECT / AS SYSDBA, he will not be able to start up or shut down the database, or even completely destroy it by issuing a CREATE DATABASE statement on an existing database. However, he will be able to do tremendous damage to a database by adding or removing tablespaces or other objects — either maliciously or unintentionally.

DBA role system privileges are shown in Table 5.3. There are 77 of these privileges in Oracle7, and 89 in Oracle8. The 12 new privileges in Oracle8 encompass actions with directory, library, and type.

A user who has been granted the DBA role has ADMIN OPTION and can therefore pass any of the associated privileges to other users with or without ADMIN OPTION if he chooses.

Table 5.3: DBA Role System Privileges
Privilege
ALTER ANY CLUSTER
ALTER ANY INDEX
ALTER ANY LIBRARY (new in Oracle8)
ALTER ANY PROCEDURE
ALTER ANY ROLE
ALTER ANY SEQUENCE
ALTER ANY SNAPSHOT
ALTER ANY TABLE

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In Chapter 8, we'll examine the use of operating system privileges for database access. One of the privileges used for authentication is the OSDBA operating system privilege. Another operating system privilege is OSOPER. Working hand in hand with these privileges is a mechanism known as the SYSDBA and SYSOPER roles, created in version 7.1.6 of the Oracle RDBMS.

If a database account has been granted the SYSDBA or SYSOPER role, that account gains the ability to connect in any of the following ways:

CONNECT INTERNAL AS SYSDBA
CONNECT / AS SYSDBA
CONNECT INTERNAL AS SYSOPER
CONNECT / AS SYSOPER

The user can then perform privileged actions like starting up and shutting down the database. An account connected as SYSDBA can perform any function a user who has been granted the DBA role can perform. The SYSDBA privilege should be granted as cautiously as the normal DBA role. The SYSDBA role enables you to perform the following actions (as well as execute any of the privileges shown in Table 5.4):

Create a database
Start up a database
Alter a database mount or open
Alter a database for backup
Enable archive logging
Recover a database
Restrict a session
Shut down a database

SYSDBA role system privileges are shown in Table 5.4. There are 77 of these privileges in Oracle7, and 89 in Oracle8. The 12 new privileges in Oracle8 encompass actions with directory, library, and type. From a privilege perspective, the SYSDBA privileges are identical to the DBA privileges.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As you've surely concluded by now, neither the CONNECT nor the RESOURCE role is appropriate for general users — those who will use the applications or will use an interactive query tool such as SQL*Plus. Not only are these roles far too permissive in terms of system privileges, but they also do not convey any intelligence about the user, as we'll discuss in the following section.

Suppose that we have Accounts Payable, Accounts Receivable, Personnel, and Organization logical databases, and a user has the CONNECT role. You would not be able to tell from the role which of these logical databases would be used. However, if you had defined roles such as AP_USER, AR_USER, PERS_USER, and ORG_USER, then you could tell rather quickly to which group the user belonged. These roles would only need one system privilege — CREATE SESSION — and any minimal table or procedure privileges required to get them started.

Another advantage of using roles in a specific manner is the ability to disable particular groups of users without impacting the remainder of the user community. Should it be necessary to block the personnel group, all the DBA would have to do would be revoke CREATE SESSION from the PERS_USER role. Since that role would be assigned to the personnel users as the default, the users would not be able to log in to the database. All other users would be unaffected and could continue their work. When the database or application work on the Personnel logical database was completed, granting CREATE SESSION to the PERS_USER role would re-enable the logins for those users.

The term " logical database" is used here to distinguish a group of objects (tables, views, etc.) that are associated with an application or schema area which is housed in a database. This is what the user generally means when referring to the "database." There may be many logical databases within a single physical database. The logical databases may be associated with only one application or may share tables between applications. For example, an employee table might be used by the HR application in its logical database as well as the Payroll application in its logical database.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In the various versions of Oracle, when you create a database, some user accounts are automatically created by default. In every version, sys and system are created. In the later versions of Oracle7 (beginning in version 7.1.6 or version 7.2), the user dbsnmp is created automatically to support the Oracle Enterprise Manager intelligent agent. As of Oracle8, scott (a standard account you can use for demonstrations, testing, and training users in a known environment) is automatically created and populated, as are other users based on the cartridges you have selected for installation. Let's take a closer look at the default user accounts that are created and their potential impacts on database security.

When a Personal Oracle8 database is created using version 8.0.3, several users are created by default:

sys
system
dbsnmp
scott
demo
po8 (for Personal Oracle8)

The sys user is always assigned the password change_on_install. sys is the heart of the Oracle system. You must work very carefully when logged on as sys, since you have the ability to do severe damage to the database from this account.

The system user always receives the password manager. Because the sys and system passwords are pre-set, the DBA always knows the initial passwords necessary to log on as either

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In Chapter 7, we recommend that you use a spreadsheet approach to list the envisioned objects within an application. We tell you to identify the actions that would be permitted against each object and to group the actions together and assign them to a role. Let's look briefly here at how you might do this.

For example, let's look at a piece of an application that will be used in a car dealership. The departments follow:

New and used car sales
Customer vehicle servicing
Car leasing
Automobile parts
General bookkeeping
Automobile detailing
Paint and body shop

When a new car is received, an entry is made into the database in the automobile information area detailing the car's vehicle identification number (VIN), make and model, color, wholesale price, accessories, etc., reflecting the information needed for the car's invoice. The dealership manager or sales manager might examine the car and decide to add a sunroof or other feature to improve the sales appeal. A work order will be written up and the body shop will perform the work. The car will go to the sales floor where it will be shown. A customer will come in, test-drive the vehicle, fall in love with it, and buy it. The sale will be finalized and the car delivered to the new owner. Assuming that the customer is loyal and stays in the dealership's area, the car will be returned to the dealership periodically for maintenance and repair work. Over the life of the car, several different areas of the dealership will interact with that car's records, but for different purposes. The body shop, parts department, and service department will need to be able to read the record and update it as work is performed on the car, while the sales force will only need to read it. The dealership manager might be the only person allowed to modify the price of the car.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle provides many features that will help you secure your databases. Among these are the ability to create customized user profiles, the use of passwords and other forms of user account controls, and the use of object controls (i.e., views, roles, synonyms, and grants). This chapter describes profiles, passwords, and synonyms. You will find information on views, roles, and grants in earlier chapters.

There are two different forms of profiles available in an Oracle database: product profiles and system resource profiles. Product profiles , though the PRODUCT_PROFILE and USER_PROFILE tables, let you block access to individual Oracle products such as SQL, SQL*Plus, and PL/SQL; you can block by individual command, or you can block the entire product from access by a single user, a specific group of users, or everyone. System resource profiles , as the name implies, let you control the use of resources on your system. For example, through a system resource profile, you can limit the number of separate sessions a user can have at one time or the amount of CPU time which can be used on a per-session basis.

In Oracle8, passwords and password features have been enhanced substantially. The new password features include the ability to age and expire passwords, track password history, and lock accounts. Although these abilities have been around for years in many operating systems, they are new to Oracle databases and a very welcome addition to the stable of Oracle security measures. This chapter describes the new password features. It describes how to set password parameter values by creating or modifying a profile. It also discusses ways to avoid displaying a user's password in a command line when you need to perform work as a privileged user (for example, when testing an application).

This chapter also describes synonyms . We all use synonyms every day, but we may not even notice we're doing so. Nicknames, addresses, and telephone numbers are all synonyms that represent something else; given a telephone number or address, we may respond, "That is the number or address of XXX — a specific person, business, or place." Even an Internet address, the Uniform Resource Locator (URL), represents a physical machine's address. Synonyms are used in an Oracle database to represent other objects in the same way a telephone number, address, or URL identifies a person or physical location. The primary function of a synonym in the database is to provide location transparency — a nice buzzword to describe the fact that you don't know where the referenced object is located. For instance, by using synonyms, developers can write code that does not reference a specific schema. When the code moves into production, the objects can be placed in another schema and, as long as the synonyms point to the correct objects in the schema, the application will work as it did in development.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As we mentioned earlier, Oracle provides two very different types of profile: product profiles and system resource profiles. The two have no relationship to each other, but both establish limits or restrictions of some kind. Product profiles limit user access to certain Oracle commands or products, while system resource profiles limit a particular user's use of certain system resources (e.g., sessions or CPU time).

When people talk about "profiles," they are usually referring to system resource profiles.

With product profiles, you can block access to Oracle's SQL, SQL*Plus, and PL*SQL products. Any of the following can be blocked:

One single command within any one of the product sets
A group of commands within one or more of the product sets
All of the commands within one or more of the product sets

Section 6.1.1.1: PRODUCT_PROFILE and USER_PROFILE tables

The PRODUCT_PROFILE and USER_PROFILE tables provide the mechanism used to block commands. You create these tables by running the PUPBLD.SQL script while connected as user system. The script is used to establish tool resource limits on the Oracle tools. Product functions blocked in this table are not table-specific, they are system-wide, so you should carefully consider the impact of using this feature.

These PRODUCT_PROFILE and USER_PROFILE tables are not referenced directly by the users. The PUPBLD.SQL install script creates two views — PRODUCT_PRIVS on the PRODUCT_PROFILE table, and USER_PRIVS on the USER_PROFILE table. This latter view limits the records retrieved to those pertaining only to the user executing the query.

The PUPBLD.SQL script must be run by user

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

For years, many operating systems have supported the ability to define password composition, complexity, aging, expiration, history, and account locking. With past releases, Oracle has lagged behind. But with the advent of Oracle8, these features have now become available in an Oracle database. All the password functions mentioned here are defined using the CREATE PROFILE statement, as shown in the previous section. To enable password management in your Oracle system, the UTLPWDMG.SQL script must be run as sys from the sysmgr account. This script can be found in:

The $ORACLE_HOME/rdbms/admin directory on a UNIX system
The $ORACLE_HOME/rdbms/admin directory on a Windows NT system
The $ORACLE_HOME:[rdbms] directory on an OpenVMS system

First, let's examine what each of these features lets the DBA accomplish.

Oracle's password composition and complexity features enable the DBA to describe how a password must look (its physical composition). Oracle's new complexity verification mechanism checks each password to ensure that it is complex enough to provide reasonable protection from someone who might be trying to guess a password to break into the database. Since the complexity verification is provided by Oracle through a PL/SQL function, you can add even more complexity to the default mechanism by writing your own function. The function must be owned by sys in order to perform properly.

Section 6.2.1.1: Basic rules

The basic rules enforced by the default PL/SQL function include a minimum length for the password and the requirement that one or more alphabetic, numeric, and punctuation marks must appear within the password. The default rules are that the password must:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This section looks at how and why we use synonyms in a database for security. As we said in Chapter 3, a synonym is really a nickname for something else. In an Oracle database, a synonym can represent one of several kinds of objects — a table, view, sequence, snapshot, program, procedure, function, or even another synonym. For our discussion, we will use a table as our reference object.

Normally, if you want to access an object, you have to know the owner of the object and the object name. If you want to look at something in the employee table owned by mary, you would say:

SELECT *
  FROM mary.employee;

The "mary.employee" reference is called a fully qualified path name . Because you have supplied all the information Oracle needs to locate the object, after verifying the statement's syntax for correctness and checking to see that you have privilege to access the mary.employee table, your query will be processed and the results returned to you.

Suppose, though, that you do not know the owner's name and you issue the following query:

SELECT *
  FROM employee;

Since you did not include the owner or schema name, the RDBMS will assume that you have a table called employee and will first look in your own object area and your list of private synonyms for the table. If Oracle does not find the table within your own area, it will look at publicly available objects and public synonyms that have been defined to include the location name (i.e., a synonym). If Oracle does not find an employee table reference in any of these areas, you will get an error message such as the following:

ORA-00942:  table or view does not exist

This is probably the most frequently encountered error message.

The solution then would seem to be to always code everything with fully qualified path names, right? Not really, since there is a problem with this approach. Such statements are usually embedded into applications, and if the owner of the table changes, all the applications have to be modified and recompiled.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many steps to securing your system and its data. But one of the first—and one that too few organizations take—is the development of a security policy that outlines and maps out the enforcement of a security plan. We've included this chapter as the first one in the "Implementing Security" part of this book because we believe that the creation of security policies and the implementation of a security plan must precede the more operational steps of securing your system and database.

What's the difference between a security plan and a security policy? A security policy identifies the rules that will be followed to maintain security in a system, while a security plan details how those rules will be implemented. A security policy is generally included within a security plan. A security plan might be as simple as a verbal statement from the highest-level management that all accounts on a system must be protected by the use of a password. Or a security plan might be a thick document spelling out in great detail exactly how security will be implemented within the company's systems. Just as there are many individual needs and many different approaches to security, there are many types of database security policies. We'll present many aspects of these policies in this chapter; some may or may not apply to your specific organization. A checklist at the end of this chapter provides a resource you'll be able to use to evaluate which features of a security plan are important for your own particular environment. Also, bear in mind that, no matter how thorough a plan appears to be, changing environments can lead to holes in a security system. Therefore, you will need to re-examine your security plan on a regular basis to ensure its currency.

Why does a company need a security policy and plan? What's the point of having them? A security policy, included within a security plan, helps to ensure that everyone is in sync with the company's needs and requirements. With a firm policy in place, every employee knows what is expected — what the rules are—and how the requirements are to be implemented. The limits are clearly defined and consistent guidance is provided for everyone. Statements within a security plan can help to ensure that each employee knows the boundaries and what the penalties of overstepping those boundaries will be. For example, here are clear, concise rules employees can easily understand and follow:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Why does a company need a security policy and plan? What's the point of having them? A security policy, included within a security plan, helps to ensure that everyone is in sync with the company's needs and requirements. With a firm policy in place, every employee knows what is expected — what the rules are—and how the requirements are to be implemented. The limits are clearly defined and consistent guidance is provided for everyone. Statements within a security plan can help to ensure that each employee knows the boundaries and what the penalties of overstepping those boundaries will be. For example, here are clear, concise rules employees can easily understand and follow:

Always log off the system before going to lunch.
Never share a password with anyone else.
Never bring software from home to put on your machine at work.

In order to have a truly solid and meaningful policy defined, the highest level of management needs to be committed to ensuring that the security policy will be enforceable. The security policy might state:

Any employee leaving a computer unsecured will be formally reprimanded.
Any employee found sharing a password will be suspended for one day.
Unlicensed software found on a personal computer will be removed and the personal computer user will be shot at sunrise. Survivors will be prosecuted.

Under certain circumstances, the requirement to have and enforce a security policy may come from an agency outside the company. In the case of banks, external agencies control and define what constitutes security for the databases within the bank. The company, however, might decide that even more rigid standards are necessary or that further definitions are required to ensure that no financial transactions become compromised and that confidentiality is maintained. The bank may implement a security plan that further defines exactly how the standards will be implemented, maintained, and audited.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

From the earliest releases of the Oracle database, a mechanism has been provided to let users connect to the database in order to perform tasks via user accounts. There are several different types of Oracle user accounts—both operating system and database—that a company might implement:

Although they are created with the CREATE USER command, some accounts are used to house application schemas. These accounts own objects like tables, views, indexes, triggers, procedures, etc.
Another type of account is used by Oracle itself to enable the database engine work to be performed; these accounts are sys and system.
In later versions of the RDBMS, an account to enable the intelligent agent to connect to each database is automatically created during database creation. This account is dbsnmp and carries full DBA privileges.
Each application might need one or more accounts to enable work to be performed.
Each user in your system may require an individual Oracle account with specific privileges to enable the user to work with an application.
One or more accounts may be needed to enable one or more DBAs to perform database maintenance and duties.

Each account type must be considered and a decision reached on whether that account type will be used and how it will be set up and administered. In smaller organizations, there may be little need for some types of accounts discussed in this section. In very large organizations, there may be a need for more extensive divisions of database account types.

The most obvious account type is the one used for database administration. A small company might have one person acting as system administrator, database administrator, and network administrator, while a larger company might have several people acting as administrators for specific areas. At your site, who will have access to the code area for installation and maintenance of the Oracle software? There may be one or more accounts that will need to be established for various administrative tasks as well as privilege sets—both operating system and database privileges—to perform the required tasks.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You need to determine the mechanism for the creation of new accounts. There are many possible mechanisms. One form of account creation that is gaining popularity is for a company to enable "restricted" access to their sites on the World Wide Web. A person who wants to access a more privileged area of a web site might be required to register with that site through electronic registration. The person is presented with a form requesting his name, company name, address, email address, and other information. He might be prompted to select a username and password. He submits the completed form and, within some space of time, receives in his email account an acknowledgment that he has registered, along with notification or verification of a username and a password for his use in accessing the site. At no time has the person seen or talked to a human being, but he has now been authorized as a user on a system.

In this example of web site access, we are not judging the procedure or security. (If we were, we might question sending a password via email.) We are merely outlining a general method of a request for an account in which the requester and the administrator have never seen or spoken with each other. Other forms of more anonymous account requests would be via telephone voice mail or electronic mail. On a more personal level, a meeting might be held between the administrator and the requester.

The policy team or higher-level management must decide whether access to the database can be granted through an electronic request, or whether some level of management must physically sign a form acknowledging that the employee seeking entry into the system is a valid employee with a proven need to interact with a specific application area. The authorization required might even be as casual as a user picking up a telephone or walking into a designated person's office and saying, "I need access to xyz system" and receiving an account on that system. Thus, we see that account requests might be generated through a number of different venues (or a combination of several of the approaches listed here):

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are several different types of standards for usernames. What username standards will you enforce at your site?

Let's consider a uniform approach for usernames across systems within a company. Such an approach has several benefits:

It is easier to administer than randomly generated usernames or usernames selected by the user.
It ensures that the username will be the same for each operating system, each database, each application, and for email interaction.
It can require the inclusion of specific characters or numbers within a username, so a standard makes it easier to ensure that those requirements are always met.

A possible disadvantage to having a username standard is that anyone who has been associated with the company may have enough information to be able to determine any employee's username easily.

In the case where a username is constructed using part or all of a person's actual name, the username is easy to remember; you only need to know what the standard is to determine what the composition of the username is or will be. An example of a standard using parts of a person's name as a username is:

The first three letters of the person's first name
Plus the first letter of the middle name
Plus the first four letters of the last name
Plus a designating number at the end to fulfill the requirement of some operating systems to include both alphabetic and numeric or special characters in a username

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Many children have clubs in which a secret word is used to gain entry to the clubhouse. In my club, the password was hobgoblin. Since no one but our group knew the secret word, we could feel pretty confident that someone saying hobgoblin at our clubhouse door was a member to be allowed in. Operating systems and the Oracle database use passwords in much the same way.

When you are developing your database security plan, you'll need to make a number of decisions about password use at your site:

Whether a user will be permitted to create and change his own password
How frequently the password will expire and how long a grace period will be allowed before the account is locked
Whether a set standard for password composition is to be used, and what that composition will be
Whether account lockout will be enabled, whether the account can be automatically unlocked, or whether a security manager will have to intervene to unlock a locked account
Whether a password will be permitted to be reused, and what length of time must pass before a password can be reused
How the user or designated account manager will actually change the password—through a created form, through a SQL script, etc.
If users will not be permitted to change their own passwords, the mechanism by which users will be notified of password changes

The decision to enforce a specific pattern for passwords raises the question of just how secure the password will really be since anyone who knows the imposed template will know the form passwords for the system must take. A previous employee could pose a security threat because the username and password structures are known to him or her. If a template for passwords is to be used, we recommend you make the template complex enough to ensure greater difficulty in breaking the password security.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In the spreadsheet shown earlier in this chapter, references to "ap_clerk," "hr_manager," and "hr_developer," among others, were used in the "Roles" area of the chart. The convention displayed there was the application name coupled with a task nomenclature—for example, the Human Resources application (hr) coupled with the "clerk" tasks of entering or updating data within areas of the hr application. In this application, the ability to delete information was not a duty deemed appropriate for a clerk to perform. Only a manager can delete information.

The security plan team must decide on the naming conventions that will be used for role creations on a database-by-database and application-by-application basis. The composition of each role (who will be allowed to perform what actions) also needs to be identified, as well as the designation of who will create and assign roles for each application in each database.

By default, until Oracle version 7.1.6, Oracle supplied three default roles within a database (CONNECT, RESOURCE, and DBA). From version 7.1.6 forward, Oracle supplies two additional roles (SYSDBA and SYSOPER). These are described in some detail in Chapter 5.

Because the composition of these roles has changed from version to version of the RDBMS, we recommend that DBAs define their own roles for user access. For example, in Oracle's version 6, the RESOURCE role was granted to users who were performing development tasks within a database because the RESOURCE role included the ability to create tables. In Oracle7, the ability to create tables appears in the CONNECT role. However, no tables or indexes can actually be created without a tablespace quota being granted to the user.

As we explained in Chapter 3, Oracle provides the ability to grant privileges directly either to specific users or to roles. The security team will need to decide whether privileges will ever be directly granted to a specific user or will be granted only through roles. If direct grants are allowed, you'll have to decide under what circumstances they will be used. During application development, for example, the developers will have access to the application schema and will have, through that account, many direct privileges.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Views are wonderful mechanisms for hiding data from different classes of users. For example, I would not want my salary to be visible to the majority of employees in my company. Suppose that my salary is resident in a table called EMPLOYEES. The table might be comprised of columns for the employee's name, location, telephone number, manager's name, department name, and salary. In this case, the only information in this table that might be considered sensitive is salary. Therefore, you might create a view called "emp_view" to display all of the columns except the salary column.

As with roles, the security plan needs to define any conventions for view names, a designation for who will be permitted to create views, a designation of who can grant access to which views, and an identification of who can say that a view is, in fact, necessary or unnecessary.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In Oracle8, a new Security Server facility is supplied to either stand alone or work in conjunction with several third-party vendor products for generating certificates of authentication. These certificates enable users to connect to various applications and databases without having to provide a username and password each time they want to change connections from one place to another. The Oracle Security Server is discussed in Chapter 15.

If a company is planning to implement, or has implemented, a single sign-on utility for user authorization, the security plan should detail who will administer the Oracle Security Server and how users will receive accounts and interact with the Security Server.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Within your organization, there will be many different interactions with employees. You need to determine how to deal with these interactions and add your organization's policies to the security plan. The driving force behind any security policy should be what an employee needs to know to perform his or her job effectively.

What privileges do particular employees need? What limitations should you place on these privileges? A major problem with trying to determine how employees will be able to access a database or application is the need to balance giving enough privilege to enable the employee to get the job done against the risk of allowing too much access to sensitive information. If a security plan becomes too rigid, employees may feel they are not trusted or may not be able to perform their jobs effectively.

Section 7.9.1.1: Pre-employment tracking

Before an employee is ever hired, an employment application, resumé, or both, is usually submitted for consideration to a company. Many companies track their candidate submittals using computer programs that interact with a database. The information presented in a job application or resume is private and must be handled with care. Your security plan should include procedures for employment application and resumé handling.

Section 7.9.1.2: New hires

Once an employee is hired for a position, the security plan should clearly state the steps to be followed for giving a new employee access to platforms and databases needed to perform his or her job effectively. If you've already determined the possible functions for an employee using a specific application, the task of knowing what accesses (roles and grants) are needed by that employee will be made much more easily.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As mentioned at the beginning of this chapter, security policies differ greatly in how complex and specific they are. Some policies may be as simple as an electronic memorandum designating a particular employee as the point of contact to create accounts for anyone needing access to a database or system. For most companies, however, a more formal document may be in order. Here is a sample generic index of topics you might include in a security plan:

Topics
Corporate Philosophy on Security
Configuration of the Operating Systems by Divisions
Configuration of the Databases for each System by Division
Configuration of Applications by Database and System for Each Division
Configuration of Single Sign-on Utility
Composition of Certificates of Authority
USERID Guidelines for a Database Account
USERID Guidelines for an Operating System Account
Password Guidelines for a Database Account
Password Guidelines for an Operating System Account
Operating System Access Types
Database Access Types
Authorization for Establishment of User Accounts
Establishing a USERID for a Database
Establishing a USERID for an Operating System

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The following checklist is provided as an aid to ensure that you've identified and addressed all of the necessary areas of interest to your company. The checklist is designed to be a guide for you and your team to ensure that topics that need to be included in your security plan will not be overlooked.

Have You	Yes/No
Identified all of the key players?
Obtained management buy-in (at all levels)?
Collected all applicable system and database information?
Identified the specific types of accounts required for each system—both operating system and database?
Determined who will have authority to approve accounts?
Determined who will create/delete/manage accounts?
Determined a user tracking method and implementation?
Decided how account approval will be performed: email, web site, hard-copy form, etc.?
Identified all affected applications on each system?
Identified a username and password structure?
Determined what constitutes a security breach and the appropriate penalty for each breach?
Identified all sensitive data on the system and created methods to protect that data?
Determined what forms of monitoring will be used?
Determined what forms of backup will be used?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The best approach to doing anything is supposedly to begin at the beginning, proceed through to the end, and then stop. But how do you determine where the "beginning" is? When it comes to database security, you may feel that there are several different points of origin. However, it seems to us that you should first protect the Oracle source code. Before you begin to install the Oracle code, there are steps you should take to establish the environment for the source code installation. Since each platform Oracle has been ported to has different procedures for Oracle software installation, we won't cover detailed information on code installation in this book. However, we'll look briefly at the approaches you can use to protect your Oracle environment in the correct way "from the beginning."

To help you to understand the various ways you can set up your Oracle source code and applications code, we'll first present a discussion of alternative architectures: "dumb terminal," two-tier, and three-tier. We'll describe the system-level approaches you can use to secure your database and the methods you can use to enable connection to your database without the use of passwords — either directly from the operating system level or remotely through the use of SQL*Net. Because SQL*Net has become an integral part of the way you interact with the database from both a two-tier and three-tier architecture, we'll briefly explore the methods used to configure and interact with this product. Finally, we'll look at the initialization parameters used to configure the database at database startup.

Although we use the term SQL*Net throughout this chapter, in Oracle8 the product name has changed to Net8.

Not that long ago, the common environment for performing computer-related tasks was to log on to an operating system from a terminal directly connected to that system. From your connection, you could interact with applications and, possibly, a database or two. Everything lived on the server:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Not that long ago, the common environment for performing computer-related tasks was to log on to an operating system from a terminal directly connected to that system. From your connection, you could interact with applications and, possibly, a database or two. Everything lived on the server:

The operating system code
The database software
The applications code

As computing has evolved, additional ways of interacting with a computer and an RDBMS have also evolved. There are now three common approaches:

A direct connection to a computer on which we perform all our work
A client/server (two-tier) architecture
A thin client (three-tier) architecture

We'll describe each of these architectures briefly in the following sections.

Originally, the Oracle software, database(s), applications code, and everything else necessary to perform the required work was housed locally on one machine. You worked from a terminal directly connected to the computer to perform whatever tasks were required. Using this method of connection meant that you had to be in the same building as the computer — or at a distance limited by how far away from the computer the hardwiring would reach and still be usable. Figure 8.1 illustrates a direct connection to the database.

Figure 8.1: Direct connection to a database server

There were several common problems with this approach.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many approaches you can take to securing an operating system and database and an equal number of views on what really fulfills the definition of a "secure operating system." In a systems administration class we attended many years ago, the instructor drew a picture on the board. The picture was of a large room in which a very large computer and operator's console had been placed. There was only one door leading into the room and in front of that door, he drew a picture of a guard with a gun. He explained that he had just drawn a system that was quite secure: the only truly secure system is the system that allows no one to ever interact with the computer inside. A friend of ours describes his vision of the truly secure computer as "a computer in a lead-lined vault (no EMF emissions to be sniffed) with no network connections and a Marine guard with `shoot on sight' orders."

Okay, these are not real-world solutions. However, both descriptions do bring home the point that as long as there are people interacting with a system, there is always the potential for a security breach. One of the most formidable threats to any system's security is the user who does not log off the system at night and does not have password protection on his terminal or personal computer. In this section, we'll look at ways of protecting logins to the system.

From the operating system perspective, security measures are used to protect access to files. On most operating systems, security is accomplished by creating specific groups of users with specific access rights defined for them. Each file within the directory can be granted different access restrictions. You might assign the ability to read from and write to one file while another file might receive read, write, and execute privileges or only read access, as you deem appropriate. For example, you might want the salesmen in your automobile dealership to be able to see what cars are in stock, so you give them read access to the car information. You might want your administrative assistants to enter information on the new cars as they arrive at your lot — so you give them read and write privileges.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are three approaches commonly used to enable a user to connect to a database without explicitly typing a password. All of the approaches take advantage of the OPS$ account.

With the first approach, the account is created with the actual prefix "OPS$" used in the account name, and a password is assigned to the user. The user can connect from the operating system level to a database by using just a "/" in place of the username/password string. The user does not have to type his username or password to gain access to the database. If, however, the user wants to connect to the database from a remote client, he can use his username and password and connect successfully. The advantage of this approach is that the user can hide both his username and password when using a command-line connection and still retain the ability to use SQL*Net to connect to the database from a client machine.

With the second approach, you set theINIT.ORAparameter OS_AUTHENT_PREFIX="carview.php?tsp=", and the actual username without the "OPS$" prefix is used in creating the user account. No password is assigned to the account. Instead, the account is created with the IDENTIFIED EXTERNALLY option, as described later in this chapter. The user can connect to the database from the operating system level but has no password to enable remote connection from a client machine.

The only difference between the second and third approach is the use of the INIT.ORA parameter REMOTE_OS_AUTHENT set to "TRUE" to enable an "identified externally" account to connect to the database from a remote client. The disadvantage of this form of access is that database security could potentially be compromised by someone other than the intended user accessing the database without needing to know either a username or a password.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The advent of a product to enable the interaction with the database from a remote client has been a boon to the computing world. For the Oracle RDBMS, that product was SQL*Net (Net8 for Oracle8). SQL*Net enables the ability to connect to a database in Tokyo from a database in New York and share information between the two. While this ability has been a benefit to industry, it carries with it the potential for security breaches.

Connections via SQL*Net are controlled by three files, the LISTENER.ORA file, the TNSNAMES.ORA file, and the SQLNET.ORA file. If you do not define the database in these files (called "configuring the Listener"), connecting to your database from a remote client is virtually impossible. In an environment where the Oracle Names Server is used, the third file, SQLNET.ORA, defines the location of the Names Server and the order in which a database connection is determined. The SQLNET.ORA file indicates either that the Names Server is to be checked first for a database's connection information or that the TNSNAMES.ORA file is to be checked first.

You must configure these files if you are not going to use a Names Server, after installing the SQL*Net product on your system and in order to run a listener process on your system (see the section "About the Names Server" later in this chapter) as follows:

LISTENER.ORA: This is a required file on the database server, and it contains the configuration for the listener. If several listeners are to be used on a node, they will all share the same LISTENER.ORA file. The file contains three parts: the area that defines the listening addresses, the SID of each database it listens for, and parameters that define the listener's behavior.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As we described in Chapter 2, the initialization file is a list of parameters supplied by Oracle with your database. You can change the values of these parameters to configure the database system. The file is used to modify parameters that affect performance, set global defaults and limits, and establish file names and locations. This file is generally referred to as the INIT.ORA file even though its actual name might vary. By default, when a database is created, a file with the naming convention of INIT<DATABASE SID>.ORA is also created.

From version 7.3 on, some of the INIT.ORA parameters can be dynamically modified using the following commands:

ALTER SESSION: When specified, the parameters are only changed for that session and will not remain in effect after the session ends.
ALTER SYSTEM: When specified, the parameters will remain in effect until the database is shut down but may not affect the current session.
ALTER SYSTEM DEFFERED: When specified, will not affect the current sessions but will affect all future sessions until the database is shut down.

There are more than 100 different parameters that can be set in the INIT.ORA file. When a value is changed in the INIT.ORA file, the value will not take effect until the database is "bounced," or shut down and then restarted using the modified INIT.ORA parameter file. Since the Oracle RDBMS has a default value for every parameter available, the absence of a value for a parameter in the file will signal that the default value is to be used. The Oracle-supplied default

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many approaches you can use to implement security on your system. In this chapter, we're going to show you a sample application demonstrating one such approach. We're not going to include every detail of every step that was taken to build the original application on which this example is based. Our goal is not to teach you how to implement only one security method. Instead, we'd like to provide you with ideas you can use to build your own system.

The sample application is a credit card review and certification system. To set the stage, we'll provide a brief overview of the system's functionality and present a discussion of the environment and requirements of the system. In Chapter 3, when we talked about database objects, we said that the tables in the database could be used by more than one application. This is the case with the credit card system. There will be references to objects (mostly tables) shared with other applications. We'll refer to these objects as enterprise objects and the entire database as the enterprise database. Access to each object is controlled by the privileges that you give (or don't give) to each user.

In this chapter we'll use these steps in describing how the application was built:

Define the functionality of the application.
Describe the portions of the security plan that pertain to the application.
Explain how to build a role-object matrix (generally referred to as "the matrix").
Show how to build the views defined in the matrix.
Describe how to build the roles that were defined in the matrix.
Show how to assign privileges to roles and users through the GRANT command.
Explain how to use password-protected roles to implement security on the system.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

First, let's take a look at the credit card review and certification application to see its purpose in life. The XYZ Corporation has issued corporate credit cards to many of its employees for various reasons:

Administrative assistants can order and pay for supplies for their areas.
Consultants use their cards to cover their expenses when they travel to customer sites.
Sales personnel entertain customers and hold business meetings outside of the corporate offices.
Company purchase order personnel use their cards for purchases under a specific dollar amount.
Many other employees have cards for other reasons.

As part of the corporate system of accountability, each employee with a card must review his or her statement electronically and certify that the changes are correct. This is the same action you probably take each month when you receive your personal statement. This application allows the certification to be done electronically. Once certified as accurate, the information can be posted to the corporate accounts payable tables. A manager might also want to verify her section's spending to ensure that her area is not going over budget. The highest-level managers might want to confirm that employees are not abusing the system. A salesman, out of town on business, may want to verify that he has not exceeded his card limit. Each of these employees will require different forms of access and different privilege levels within the system.

To accommodate all the different needs of the various employees in the system, a credit card review and certification system has been developed. Of course, there is much more to the complete credit card system than the areas we'll discuss. In the rest of the chapter, we'll present the general development steps for a portion of the application.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

To control the levels of access different users will have in your system, you have to identify the specific privileges for each object. Database roles will be used as part of the security system, and you must associate these privileges with specific roles.

It does not actually matter whether you determine the roles first or the table privileges first. However, we recommend you determine the roles first since that tends to make it easier to determine the privileges required for each objects.

The security plan (introduced in Chapter 7) for your application will include the definitions of the types of access for the system. We recommend you write a security plan even if the system being designed is not complex. By creating a security plan, you document the security approach that was intended. Should all of the people involved in the project leave, there will still be a record of the thinking behind the application implementation. The security plan serves two purposes:

It documents the system security approach and concepts.
It defines the specific access requirements.

From the security plan and from interviews with the customers, you can determine what the user grouping will be, and you will be able to establish some role names to represent those groups. Once you have that, you can continue with the role-object access matrix.

Access definitions from the security plan are used to determine role names. You can develop a matrix showing which roles will be given which type of access to which tables. This is the part of implementing a security system that everybody hates. In every attempt to establish a security system, all the participants secretly know that specific access rights have to be determined, and everybody tries to avoid determining them because it is a tiresome job. Unfortunately, you cannot have a successful and secure system without clearly specifying the roles and access rights.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

To refresh your memory, a view is a description of how data is to be retrieved from the underlying tables. It does not store data but it is treated as though it were a table in SQL statements. Views can be used to perform the following actions:

Limit the rows accessible to a user (row-level security)
Limit specific columns accessible to a user (column-level security)
Pre-join several tables (removing the requirement that the user understand the complexity of joining tables)

Most of the views we discuss here are used for row-level security. One way to achieve row-level security is to use views that include qualifying conditions based on some characteristic of the user login id. If the table is never to be accessed via a query tool, then application-implemented security can be used. Even this form of security usually relies on the same login id characteristics.

In this section we discuss the logic used to determine the structure of the views, and we provide examples of how the logic is incorporated into the view definitions.

The view definitions we provide all use the command "SELECT *" for clarity. The * means retrieve all columns. In a real system, however, you would replace the * with a list of specific column names. The columns in the view take their names from the columns in the tables from which they are selected, but you can provide aliases for these columns. There are two syntax constructs you can use to declare column aliases in a view: with one you declare the aliases first, with the other you embed the aliases in the SELECT list. The following code examples both create the emp_v view on the employee table (the FROM and WHERE clauses are omitted for clarity):

In this example you declare aliases first:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Roles are actually rather easy. Assume for now that the TSEC package (discussed later in this chapter) has already been created. We will create a set of login roles with minimal privileges. For each role listed in the matrix, we create a set of functional roles with passwords. The statements for the functional roles are followed by an INSERT into the APP_ROLES table (whose purpose is described in the section "Using Password-Protected Roles"). Finally, we grant the object privileges to the functional roles.

The APP_ROLES table is not listed in the matrix for security reasons. It is not accessed directly by the users — they know nothing about it. However, this table is an integral part of the security approach and will be discussed later in this chapter.

Figure 9.1 shows two users: sue has been granted the cdholder role and thus has access to the application as a valid card holder; cathy has not been granted any of the available application roles and cannot see any application data.

Figure 9.1: Levels of access control

In Figure 9.2, we see the steps involved when the user sue attempts to connect to the database. The database validates that sue has the appropriate privileges — granted through the CDHOLDER role — to interact with specific tables.

Figure 9.2: Row access control by application

doc
  Create one or more login roles.  These are not password protected and
  will be the user's only default role.  Note that execute privilege is
  granted on the package, and not the package.procedure.
#
CREATE ROLE cduser;
 GRANT create session TO cduser;
 GRANT execute ON tsec TO cduser;
CREATE ROLE cdappruser;
 GRANT create session TO cdappruser;
 GRANT execute ON TSEC TO cdappruser;
CREATE ROLE cdsysadmuser;
 GRANT create session TO cdsysadmuser;
 GRANT execute ON tsec TO cdsysadmuser;
CREATE ROLE cdfinmonuser;
 GRANT create session TO cdfinmONuser;
 GRANT execute ON tsec TO cdsysadmuser;
doc
  Create the functional roles with passwords.  Also insert a row
  into the app_roles table for each functional, passworded role that
  is created.
#
-- create a general role for the view access.
CREATE ROLE cdgenaccess;
-- functional roles.
CREATE ROLE cdholder IDENTIFIED BY apw_4u;
INSERT INTO app_roles
VALUES ('cdholder', 'orapgm', 'apw_4u', 'splash', null);
CREATE ROLE cdappr IDENTIFIED BY apw_4u;
INSERT INTO app_roles
VALUES ('cdappr', 'orapgm', 'apw_4u', 'splash', null);
etc.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Two types of grants will be made. First, we give table access to the roles; then the roles are granted to the users. Remember from the previous section that the login roles already have the CREATE SESSION privilege and EXECUTE privilege on the TSEC package. These grants follow the matrix, so we'll provide only a few examples here.

doc
  grant table and view access to the roles.
#
--  general access to the views.
GRANT select ON approvers_v TO cdgenaccess;
GRANT select ON card_holder_v TO cdgenaccess;
etc.
-- specific object grants.
PROMPT ************* cdholder *************
PROMPT
PROMPT s,i,u,d
PROMPT
GRANT select, insert, update, delete ON comment_log TO cdholder; 
etc.
PROMPT s,i,u
PROMPT
GRANT select, insert, update ON discrep TO cdholder;
etc.
PROMPT s,i
PROMPT
PROMPT none
PROMPT s
PROMPT
GRANT select ON card_trans_b TO cdholder;
GRANT select ON dispute_code TO cdholder;
GRANT select ON dispute_sub_code TO cdholder;
GRANT select ON err_log TO cdholder;
GRANT select ON card_trans TO cdholder;
GRANT select ON def_cost_center TO cdholder;
GRANT select ON card_seq TO cdholder;
-- views
GRANT select ON dept_def_cc_v TO cdholder;
PROMPT other grants
PROMPT
GRANT select, update (reconcile, receive_date, reconcile_date,
      parent_id, parent_item, reconciled_by_emp)
   ON card_trans_item_b TO cdholder;
PROMPT ************* cdsysadm *************
PROMPT
PROMPT s,i,u,d
PROMPT
GRANT select, insert, update, delete ON def_cost_center TO cdsysadm;
etc.
PROMPT s,i,u
PROMPT
GRANT select, insert, update ON dispute_code TO cdsysadm;
GRANT select, insert, update ON dispute_sub_code TO cdsysadm;
etc.
PROMPT s,i
PROMPT
PROMPT none
PROMPT s
PROMPT
GRANT select ON dispute_code TO cdsysadm;
GRANT select ON dispute_sub_code TO cdsysadm;
GRANT select ON err_log TO cdsysadm;
GRANT select ON card_trans TO cdsysadm;
GRANT select ON def_cost_center TO cdsysadm;
GRANT select ON card_trans_item TO cdsysadm;
-- sequences
GRANT select ON card_seq TO cdsysadm;
GRANT select ON temp_card_nbr_seq TO cdsysadm;
PROMPT other grants
PROMPT none
etc.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Access control really begins when the application is started and continues through the entire session. We have divided the control approach into the following three steps.

Restricting user startup of the application: We want to prevent unauthorized users from even seeing the application display.
Row access control by the application: The application can supplement the views. For performance reasons, we may have the application directly access the base table and we may restrict access to the rows in a manner similar to the view.
Enabling access through the application: We have the application initiate a process that is completed entirely within the database. The approach will avoid hardcoding the password in the application and will not require the user to know the password.

Before you can control what the user can do, you must know something about the user. This is typically the user's login name. Through the login name, you should be able to obtain the user's organization information, the type of job the user performs, and so on. You must also know something about the application. This information should be complementary to the information you know about the user. In this application, we are able to get the user's information because the login names are part of the record in the EMPLOYEE table.

Because all access is role-based, the application can check the roles the user has and compare those to the roles assigned to the application in the APP_ROLES table. We describe this table more thoroughly later in the section "Using Password-Protected Roles."

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Database auditing is the monitoring and recording of activities occurring within a database. You typically audit to ensure that no unauthorized users are removing data from the data dictionary or accessing tables they should not have the privileges to see. You might also want to audit specific tables that help you determine the volume of accesses occurring at peak times. This type of auditing is helpful in analyzing trends and evaluating system performance.

The Oracle RDBMS provides functions that let you audit most actions that can be taken within and against the database. These actions can include (but are not limited to) the following:

Viewing, modifying, or removing information from tables
Creating or removing objects like tables, indexes, views, procedures, triggers, etc.
Executing programs

This standard Oracle functionality does not support auditing at the row level. In other words, through standard auditing, you can audit actions that have been performed against a table, but not what has changed in a specific row of that table. To gain the ability to monitor who has changed a specific row of a table or exactly what action was taken against a row of a table, custom code is required; we'll show you an example of such code in Chapter 11.

This chapter mainly discusses the standard auditing functionality of Oracle. Where appropriate, we'll mention custom approaches that you might want to take to extend this capability.

There are many schools of thought about enabling auditing on a database. If your company has implemented firewalls or other isolating security measures, you might believe that your system is secure. You might feel there is no need to enable auditing. Often, the issue of whether to perform any kind of auditing is overlooked when a security plan is being written. If auditing has not been discussed by the people creating the security policy and plan, an auditing policy and auditing plan may not be created. There are some companies that have never seen a need to implement auditing within their database and have never had a problem — that they are aware of.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The first step in developing an audit plan is to determine why you need to audit. Not every site does. As we've mentioned, there are two main reasons for auditing:

Security auditing — to determine if someone is attempting to break into your system
Performance auditing — to determine why the system is so slow

Once you've figured out why you want to implement auditing, deciding which objects to audit will be easier. Determining the purpose will also help you narrow your scope to avoid gathering too much superfluous information.

One good reason for auditing is to confirm your suspicions that something or someone is causing a problem. For example, you might suspect that data is being deleted from a table that should not be losing any records. In order to determine whether or not this is the case, you could enable auditing to track deletions from that specific table. By limiting the scope of the audit, you get a much clearer picture of the specific activity you want to track. Having said that, we'll admit there are times when the suspicious activity may be so subtle that you must first enable general auditing and then, after you evaluate the audit results, narrow the auditing to better pinpoint the source of the problem. There is no rule that says you cannot enable one form of auditing for a period of time and then disable that auditing and enable another type.

If you are performing auditing to determine the volume of traffic interacting with specific areas of your database, we recommend you narrow the scope of your auditing to those specific areas that will provide you the information you are looking for. If you are interested in monitoring I/O, then enabling object monitoring will not accomplish your purpose. You should also audit over a set period of time to limit the volume of data collected. In this way, the information you've gathered will not get cluttered or overwhelmed with extra, unneeded data. Once you have gathered enough information to fulfill your auditing purposes, you can archive the audit information and purge it from the database audit history to free space in the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Once you've decided to enable some form of auditing, you next have to decide where the audit information will be stored. If the operating system supports an audit trail that's stored outside the database, you can write the audit trail either directly to an operating system file or to the database.

Two INIT.ORA parameters control the auditing actions:

AUDIT_FILE_DEST: Tells Oracle the directory name in which the audit trail is to be written. The default value for this parameter is $ORACLE_HOME/RDBMS/AUDIT.
AUDIT_TRAIL: Enables or disables auditing.

As a value for AUDIT_TRAIL, specify one of these values: "NONE," "OS," or "DB." If you specify "NONE" (the default), no non-default auditing will occur. If you specify "OS," system-wide auditing will be turned on and the results written to a file in the AUDIT_FILE_DEST directory. The information written to the operating system file will be encoded and is not readable. If you specify "DB," system-wide auditing will be enabled and the results stored in the SYS.AUD$ table in the sys schema in an unencoded, readable format.

Oracle supplies several views against the SYS.AUD$ table to make viewing of the audit information easier. Oracle-supplied tools, such as SQL*Plus, can be used to generate reports about the auditing outcome.

Because the SYS.AUD$ table is owned by sys, the values are stored in the system tablespace. High audit activity results in fragmentation of the system tablespace — not a good thing to have happen. Therefore, if you decide to use the database to hold the audit information, be sure to move the SYS.AUD$ table to another tablespace.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

By default, from Oracle version 7.X on, the following sequence of events occurs when the database is created:

CATALOG.SQL is run and calls several other scripts.
CATAUDIT.SQL is run as one of the scripts called from CATALOG.SQL.
The auditing views are created.
A public synonym is created for each of the auditing views.
Public access is granted to enable SELECT on each of the auditing views.

Thus, from the point in time when the database is created, the ability for anyone to audit activities in the database exists. In the case of USER_ audit views, as opposed to DBA_ audit views, the user will only be permitted to view information in his own area and not be able to see information in another user's schema. The CATAUDIT.SQL script can be found in the $ORACLE_HOME/RDMBS80/ADMIN directory on most platforms. On an OpenVMS system, all of the "CAT" scripts (Oracle-delivered scripts with the first three letters of the script name "CAT") can be found in the ORA_ROOT:[RDBMS] directory.

The auditing views require few resources prior to being activated. However, since public is granted access to them, be sure to protect them from tampering. Running the CATAUDIT.SQL script will create the auditing views shown in Table 10.1. Only the access to the DBA_ views for auditing is granted to the SELECT_CATALOG_ROLE.

Table 10.1: Oracle Standard Auditing Views (from CATAUDIT.SQL)
Auditing View

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Auditing is usually not an enormous drain on performance, but there is an impact. Generally, the cost to performance of auditing is under about 5 percent. Enabling auditing on various areas of the database can affect performance in two ways:

It can fill up storage space.
It can slow down query response time.

If you enable auditing on a table with which users interact heavily, an entry will be made to the audit trail each time the audited action occurs. The overhead of frequent writes to the audit trail can impede overall database performance. Inside a PL/SQL program, statements are individually audited. Therefore, if a PL/SQL script is being run with auditing enabled on a table that is accessed in a loop, each time the table is accessed a record will be written to the audit trail. This could present a potentially high performance cost if too many objects or audit types are enabled.

If you are auditing to the database, every row of audit data is written to the system tablespace by default. The more objects you audit, the greater the amount of storage space consumed and the more potential fragmentation to the tablespace. You must ensure that the amount of auditing enabled is sufficient for the purposes you have envisioned but is not excessive. Monitoring the amount of data being stored into the SYS.AUD$ table on a daily basis during the time in which auditing is enabled will help the DBA learn how frequently the SYS.AUD$ table needs to be archived and cleaned out. You must be willing to take a proactive stance when enabling auditing to ensure that the performance costs — of space used and system slowdown — will remain reasonable.

Once a user has connected to the database with either session or privilege auditing enabled (we'll describe these types in the Section 10.6 later on), auditing will remain in effect for that user's session, even if auditing is disabled after the session has started. On the other hand, if modifications are made to schema object audit options, those options become available immediately and take effect in the current user's session.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As we mentioned earlier in this chapter, some actions will be stored to operating system files whether auditing is enabled or not. These actions are:

Database startup
Database shutdown
Connection to the database from a privileged account
Structural changes made to the database, like adding a tablespace datafile, etc.

When the database is started up, a record is written automatically to an operating system file. If the database was started with either sys or internal, the user information will not be recorded. The information recorded is the operating system username of the process starting the database, the terminal identifier, the timestamp (date and time) when the database was started, and whether or not auditing was enabled. The purpose of writing this information is to create a record of anyone attempting to start the database and disable auditing in order to hide their actions. At the time of database startup, the database audit trail is not yet available, so the startup information is always written to an operating system audit file.

In all of the auditing situations listed above, the information is recorded to an operating system log. If the operating system does not enable Oracle to access its audit facility, Oracle will record the information in a log in the same directory in which the background processes record their activities.

The first type of default auditing occurs during database startup. An example of operating system audit entries stored automatically for a Windows NT system running Oracle version 8.0.4 is shown here:

Audit trail: ACTION : 'startup' OS_AUTHENT_PREFIX : OPS$. (3:55:41 a.m.)
Audit trail: ACTION : `startup' AUDIT_TRAIL : none. (3:55:40 a.m.)
Audit trail: ACTION : `connect INTERNAL' OSPRIV : OPER CLIENT USER: SYSTEM CLIENT TERMINAL: MLT-PC. (3:55:31 a.m.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are several different forms of auditing that can be enabled. Each form covers a different area of interest within the database:

Statement-level auditing
System-level or privilege auditing
Object-level auditing

The general command syntax used to enable any form of auditing is shown in Figure 10.1.

Figure 10.1: General command syntax

Statement-level auditing falls into two categories: Data Definition Language (DDL) statements and Data Manipulation Language (DML) statements. This type of auditing can be very broad or very specific. The statement-level audits are based on the type of SQL statement presented. An example of a statement-level audit would be to audit any action performed on tables, such as CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, etc. Another example of statement-level auditing would be to audit specific actions of one user on a session-by-session basis.

Section 10.6.1.1: Enabling and viewing statement-level auditing

To view what statement-level auditing has been enabled in a database, you can issue the statement:

SQL> AUDIT SESSION BY mary;
Audit succeeded.
SQL> SELECT user_name, audit_option, success, failure 
   2   FROM sys.dba_stmt_audit_opts;
USER_NAME   AUDIT_OPTION                             SUCCESS    FAILURE
----------- ---------------------------------------- ---------- ----------
MARY        CREATE SESSION                           BY ACCESS  BY ACCESS

In this example, we see that mary is being audited "by session." Whenever

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As we've mentioned several times in this chapter, you need to closely monitor the SYS.AUD$ table as long as any form of auditing is occurring in the database. This is the only data dictionary table from which Oracle permits DBAs to delete information. The wise DBA will archive the information from this table before removing the data. One way to archive data is to create a summary table and move the information of interest into the summary table before removing the data from the SYS.AUD$ table. Since the auditing views rely on information from the SYS.AUD$ table, remember that when you remove data from this table, the data will disappear from the audit views as well. Another way to archive the data from this table is to create a copy of the table in another schema and then export that schema. Figure 10.2 shows a possible purge cycle.

Figure 10.2: Data collection and summary cycle

You can remove all the data from the SYS.AUD$ table by issuing the statement:

TRUNCATE TABLE sys.aud$;

or alternatively:

DELETE FROM sys.aud$;

Why would you use DELETE rather than TRUNCATE? As rows of information are inserted into a table, Oracle uses a mechanism called the "high-water mark" to indicate the location of the last row of data in the table. This mark tells Oracle how many blocks to examine during a full table scan. When you issue a DELETE statement, Oracle marks the row or rows you have specified as deleted but does not actually erase the rows from the table. From a performance perspective, if you merely delete all the rows from the audit table, you will not reset the high-water mark for the table. If a full table scan is required to obtain data from the table, performance might slow down substantially as Oracle examines every block of the table — even the "empty" blocks ( those blocks containing rows marked as deleted).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In business, you sometimes need to maintain detailed records about people, places, and things. You may feel that the information you are maintaining is so important to your company that more detailed data is needed to track:

Who created the information
Who has modified the information
What was changed

As you saw in Chapter 10, Oracle provides ways to track information about modifications that have been made to a table — at the table level. However, Oracle does not provide an easy way to keep track of actions that have been performed on specific rows within a table. For that information, you need to develop a customized application.

In this chapter, we will show you a simple, but effective, application you can use for implementing transparent audit trails. Depending on how much auditing you implement, this application may not overly impact your database's performance, and it does not require a lot of complex maintenance. This sample trigger-based application can give you an idea of how you can implement your own audit trail application, or you can use this application either in part or as a complete application to perform whatever auditing you need.

To implement the audit trail application described in this chapter, you need to create the following:

Three tables
Two sequences
One PL/SQL package (with two stored procedures)
Three database triggers per audited table
Three additional columns in each audited table

After that, the application runs itself.

Developers who are writing or modifying applications do not need to be concerned with the audit trail application, except when a new column is added to a table. The performance impact must be assessed on your own system, but can be minimized by selectively implementing some of the components that comprise the audit trail application. Note that you can implement only the components you want, for example:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

To implement the audit trail application described in this chapter, you need to create the following:

Three tables
Two sequences
One PL/SQL package (with two stored procedures)
Three database triggers per audited table
Three additional columns in each audited table

After that, the application runs itself.

Developers who are writing or modifying applications do not need to be concerned with the audit trail application, except when a new column is added to a table. The performance impact must be assessed on your own system, but can be minimized by selectively implementing some of the components that comprise the audit trail application. Note that you can implement only the components you want, for example:

Only INSERT tracking
Only a record of the fact that the transaction occurred (i.e., only the AUDIT_ROW table)
Full UPDATE and DELETE tracking

You do this by commenting out the relevant lines in the trigger code included in the application. The triggers need only be written for the specific tables and columns you want to audit. You need to determine your desired level of detail, acceptable performance impact, and business requirements, and implement the audit trail application accordingly. For more detailed information, see the "About Performance" section later in this chapter.

The approach presented is very effective and easy to implement because:

Each table needs triggers that are written only once.
The triggers call stored procedures to do the actual auditing.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Unquestionably, each of the audit trails you activate in your database add overhead to your system and consume disk space. The following sections provide suggestions you can implement to reduce the amount of overhead to your database performance.

Ideally, you should create a separate tablespace for the audit data. Having the audit data on a separate tablespace will make administration considerably easier. Performance can be improved if you can put the audit data on a separate disk or filesystem from "real" data.

Make sure that you size the INITIAL and NEXT extents properly to:

Avoid excessive dynamic space management and extension
Not run out of extents (MAXEXTENTS)
Avoid fragmentation

You may want to use the parameter AUTOEXTEND on the tablespace datafile for the audit trail storage to minimize the possibility of having a transaction hang due to lack of audit trail space. The AUTOEXTEND parameter enables the tablespace datafile to continue to extend automatically until it reaches a maximum space limit you have declared in the MAXSIZE parameter. The syntax you use to create a tablespace with AUTOEXEND enabled is:

CREATE TABLESPACE audit_data_ts 
  DATAFILE '/my_disk/my_dir/audit_data_ts01.dbf' 
   AUTOEXTEND ON NEXT 10m 
   MAXSIZE UNLIMITED
  DEFAULT STORAGE (INITIAL 100K 
                      NEXT 100K 
                MINEXTENTS 1 
                MAXEXTENTS UNLIMITED 
               PCTINCREASE 10) 
ONLINE;

You will want to purge the audit trail tables on a regular basis. Keep a close watch on the growth of the tables over a period of time to get a feeling for how often you need to clean the tables out. Remember that the goal with the DELETE audits is to be able to recover the deleted information, if necessary. Be sure to export from the audit tables often and keep the exports of the deletion information in a safe place!

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This audit trail will be only an intellectual exercise unless you can use the data collected. At one company we're familiar with, the audit trail application literally saved the day more than once by making deleted data from a critical table recoverable. On one occasion, a new employee, trying to show how quickly she could perform her work, deleted several values from a vital requirements tracking system. Using the information stored in the audit trail application, the deleted information was restored to the appropriate tables almost as quickly as she had deleted the data from them. Since her actions were very random, replacing only the deleted data would have been extremely time-consuming and tedious without this application.

Here is a sample set of SQL*ReportWriter code used to display the complete set of audit trail data. The same queries will work with Oracle Reports.

REM This is the SQL code used in SQL*ReportWriter or Oracle Reports to make
REM an Audit Report.
REM The first query is the parent to the second and third queries.
REM The second and third queries are joined to it by the "raid"
REM field (raid1=raid2, raid1=raid3).
REM Query1
SELECT raid raid1, table_name, rid,
       DECODE(action, 'U','UPDATE', 'D','DELETE') action, user_name,
       TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MM:SS') timestamp
  FROM audit_row
 WHERE user_name LIKE NVL(UPPER(:username),'%')
   AND table_name LIKE NVL(UPPER(:tabname),'%')
   AND action LIKE NVL(UPPER(:act),'%')
   AND rid BETWEEN NVL(:lo_rid,1)
   AND NVL(:hi_rid,999999999999)
   AND timestamp BETWEEN NVL(:lowdate,'01-JAN-80')
   AND NVL(:highdate,SYSDATE)
 ORDER BY user_name, table_name, timestamp;
REM Query 2
SELECT raid raid2, column_name, old_value, new_value
  FROM audit_column
 ORDER BY raid, caid;
REM Query 3
SELECT raid raid3, row_data
  FROM audit_delete
 ORDER BY raid;

Figure 11.1 shows a screen shot of the SQL*ReportWriter Parameter Form customized for use with the report.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Creating one or more of the three triggers presented in this chapter on each table you'd like to audit can be very tedious. The following three scripts can be used to generate SQL-generating SQL code to create the before-insert, after-update, and after-delete triggers for any table you would like to audit. By running these scripts, you can automate the process of script creation for all of the tables for which you want to implement auditing.

At the beginning of each script, you will find a brief purpose statement and any comments about what the script will not handle (if applicable).

The script presented here uses the DUAL table. Heavy use of the DUAL table can lead to serialization in the SGA. You might want to create multiple DUAL tables to spread the load.

The following example generates a before_insert trigger script.

                  
                  REM Purpose: Audit trail before-insert trigger generator.
                  
                  
                  
REM          This script creates a BEFORE-INSERT trigger script for 
REM          a given table for auditing.
SET SPACE 0;
SET VERIFY OFF;
SET NUMWIDTH 4;
SET HEADING OFF;
SET LINESIZE 80;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SET RECSEP OFF;
SET LONG 255;
SET ECHO OFF;
SET TRIMSPOOL ON;
PROMPT -----------------------------------------------------------;
PROMPT Audit Trail BEFORE-INSERT Trigger Creation Script Generator;
PROMPT -----------------------------------------------------------;
-- accept tabowner char PROMPT 'Table Owner:  ';
ACCEPT tabname  char PROMPT 'Table Name:  ';
-- accept filename char PROMPT 'Spool to <filename>:  ';
PROMPT Spool File Name:  &&tabname._bi0.trg;
PROMPT ------------------------------------------------------;
PROMPT Working...;
COLUMN remarks FORMAT a80;
COLUMN col0 FORMAT 999999990 NOPRINT;
COLUMN col1 FORMAT a80;
DEFINE spoolfile = &&tabname._bi0.trg
SPOOL &&spoolfile;
REM --------------------------------------------------------------------
REM This query generates a file header.
REM --------------------------------------------------------------------
SELECT RPAD('rem ' || '&&spoolfile',80,' ') ||
       RPAD('rem ' || 'Generated on ' || sysdate || ' by ' || 
       user || '.',80,' ') ||
       RPAD('rem ' || 'Script to create BI audit trigger for the ' ||
       UPPER('&&tabname') || ' table.',80,' ') || RPAD(' ',80,' ') remarks
FROM dual; 
REM --------------------------------------------------------------------
REM This query generates the trigger text.
REM --------------------------------------------------------------------
SELECT RPAD('create or replace trigger ' || table_name || '_BI0',80,' ') ||
       RPAD('  before insert',80,' ') ||
       RPAD('  on ' || lower(table_name),80,' ') ||
       RPAD('  for each row',80,' ') ||
       RPAD('  when (new.rid is null or new.rid = 0)',80,' ') ||
       RPAD(' ',80,' ') ||
       RPAD('begin',80,' ') ||
       RPAD('  select trunc(' || 'seq_rid.nextval)',80,' ') ||
       RPAD('  into :new.rid',80,' ') ||
       RPAD('  from dual;',80,' ') ||
       RPAD('end;',80,' ') ||
         '/' col1
 FROM user_tables
WHERE table_name = UPPER('&&tabname');
SELECT 0 col0,
       null col1
  FROM dual
 UNION
SELECT 1 col0,
       'exit;' col1
  FROM dual
 ORDER BY 1;
          
SPOOL OFF;
SET SPACE 1;
SET VERIFY ON;
SET NUMWIDTH 10;
SET HEADING ON;
SET PAGESIZE 14;
                  
                  
                  
SET FEEDBACK ON;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A recent news story reported that a company suffered grave financial losses because a disgruntled employee destroyed the entire company's information system. The company spent many weeks and millions of dollars to recover their system. The company had viewed their backup strategies as "adequate" but had not tested their recovery plans thoroughly. When disaster occurred and they tried to recover their data, they found they could not recover all the files they needed. Their standard procedure had been to keep their backup data for one month and then reuse the tapes for new backups. In attempting to recover their data, though, they found that they had to go back further than one month. The data they needed was no longer available.

Even with rigorous security, a company can be vulnerable to data loss. One of the best policies a company can follow is to take a proactive stance in determining what forms of backup will be used to help ensure that the system can be successfully recovered. No matter what form of backups you choose to implement, we recommend you thoroughly test your recovery procedures to ensure that:

You know how to perform all forms of recovery with confidence.
You have completely analyzed your backup and recovery strategies.
You can successfully read your tapes on different tape drives from those used to make them.

The third point is very important. If you have created your backup tapes on one type of tape drive, you might not be able to read them from any other tape drive. One DBA we know intentionally "loses" a tablespace datafile from his system about once a month and has his data center recover that file to ensure that random files can be recovered successfully — he insists they test recovery on the same type of drive and also a different one from the one on which the files were created. If the file can only be recovered from one specific tape drive (perhaps because the tape heads are slightly out of alignment), you may not be able to recover your system when you really need to.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are several different forms of backups that can be performed to ensure the recovery of data to a database. With the release of Oracle8, even more options become available. The backup options now available are:

Cold backups — performed with the database shut down
Hot backups (available for Oracle version 7 forward) — performed with the database up and available
Logical backups (exports) — a snapshot in time, performed with the database up and available
The Enterprise Backup Utility (EBU) — for UNIX version 7 users only
Backup set (new with Oracle8) — performed using the Recovery Manager — with the database up and available
Image copy (new with Oracle8) — performed using the Recovery Manager — with the database up and available

Hot and cold backups capture an image of the database by making a copy of the files comprising the database and saving the copy of the files to another disk on your system or to tape. If you use a file-level hot or cold backup, you'll be able to recover your database to the point in time at which the copies of the files were made. You replace the damaged datafiles on your system with the copies of the files you have made.

Logical backups are created using an Oracle-supplied utility called EXPORT . The utility enables you to capture a snapshot of the database as it appears at the time when you performed the export. Using an export, you can recover just a table or two if you need to, without having to restore the entire database, or you can recover an entire tablespace.

For UNIX platforms using version 7 databases, Oracle provides the Enterprise Backup Utility. This utility is configured to work with a media management product to provide a high-performance, robust backup and restore solution for large database managers. One of the key features of the EBU is its ability to perform online recovery to only the affected areas while enabling unaffected areas to remain available.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle8 provides several new table types, including partitioned tables and indexes, nested tables, advanced queue tables, and index-organized tables. Another new feature in Oracle8 is the ability to define object types. Oracle8 also provides the ability to back up all the different objects that can now be created.

Within an Oracle8 environment, there is a new facility known as the Recovery Manager (RMAN). The Recovery Manager provides flexibility by allowing you to back up an entire database, individual tablespaces, or individual datafiles. The Recovery Manager starts Oracle server processes for the database to be backed up or restored and can write the backups directly to a storage device, as long as you have your media management software integrated with your Oracle software. The Recovery Manager uses a recovery catalog to store information about its tasks and enables automated restore and recovery operations — in parallel, if desired. Reports can be generated of all backup and recovery actions. The Recovery Manager can be accessed either interactively or through batch mode using its command language interpreter (CLI) or through the Oracle Enterprise Manager (see Chapter 13).

The recovery catalog is a repository in which is stored the information about datafile and archive log backup sets and backup pieces, datafile copies, archive redo logs and copies, tablespaces and datafiles, and stored scripts that define sequences of Recovery Manager and SQL scripts.

The information within the catalog for archive logs should be refreshed (referred to as "resynchronization") frequently. In a situation requiring media recovery, the archive logs that have not been cataloged since the last resynchronization prior to the failure must be cataloged before any recovery can be performed. See the Oracle-supplied documentation for more information on resynchronization of archive logs.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many different ways in which a database can be recovered:

As a snapshot in time (from export or file-level backups without archive logging enabled)
To a point in time (from file-level backups plus archive logs)
Until cancel (from file-level backups plus archive logs)

For an entire tablespace, you can restore one or more datafiles from file-level backups. From an export, you can restore an entire schema, a table, or a set of tables. Using the import option "INDEXFILE=<file_name>", you can obtain a script to build an index or set of indexes from an export file.

Here are a few reminders about the behavior of different types of backup and recovery.

Cold file-level backups performed for Oracle7 or Oracle8 databases without the Recovery Manager, and backup sets created using the Oracle8 Recovery Manager without archivelog mode enabled, allow recovery of tablespaces or the entire database to the point in time when the backup was made. If a backup was made on Monday at 1:00 A.M. and the recovery was performed on Thursday, all data entries, DDL statements, and other modifications to the database done from 1:00 A.M. Monday morning until Thursday, when the recovery was required, would be lost.

Cold file-level backups, hot backups, backup sets, and full image copies, with archivelog mode enabled, enable recovery to be performed to a point in time or until cancel. If a backup was made on Monday at 1:00 A.M. and the recovery was desired to Thursday at 2:00 P.M., the database recovery can be performed to the time desired, as long as archive logging is enabled and the archive logs are available.

Exports enable the recovery of individual tables or groups of tables but can only be performed to recover to the point in time at which the export was done. Exports are only a snapshot in time. You cannot use archive logs with an export to recover to a point in time beyond the time when the export was performed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In early versions of the Oracle RDBMS, a very limited utility called the Oracle Diagnostic System ( ODS) was used to help DBAs get some insight into what was happening within their databases. At that time, there was very little a DBA could do to improve performance or easily maintain or replicate a database. As time passed and the Oracle versions presented more and more sophisticated systems, the tool evolved into SQLDBA and then the Server Manager Utility (svrmgr). Today, Oracle packages a personal computer-based, graphical user interface (GUI) toolset called the Oracle Enterprise Manager (OEM) to aid the DBA in managing multiple databases on remote nodes throughout the world via networks.

This chapter examines the ways you can use the OEM to help enforce security in your system. We will look at how the OEM can aid you in performing your job more easily and efficiently by providing:

A way to easily create users, roles, and grants
The ability to create and examine views
An easy way to use the Backup and Recovery Managers

We also pinpoint possible security breaches the OEM might both present and prevent through the CHANGE ROLE function and remote DBA connections.

Oracle Corporation has released a different version of the OEM with each release of the RDBMS since the early versions of 7.3. Since each released version of the OEM varies from the other released versions in several ways, we'll focus here only on release 1.5.0 for Oracle8 version 8.0.4.

The Oracle Enterprise Manager lets DBAs manage one or more databases on one or more platforms in one or more locations — all from the same console. You can easily view all of the objects associated with a particular database or group of databases, and you can perform day-to-day tasks from the central console screen. In the following sections, we'll first examine the general composition of the OEM, then we'll discuss how the OEM can assist you in performing the tasks which will help secure the database.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Oracle Enterprise Manager lets DBAs manage one or more databases on one or more platforms in one or more locations — all from the same console. You can easily view all of the objects associated with a particular database or group of databases, and you can perform day-to-day tasks from the central console screen. In the following sections, we'll first examine the general composition of the OEM, then we'll discuss how the OEM can assist you in performing the tasks which will help secure the database.

The OEM is made up of the following components:

A set of console screens from which you can manage databases and software distribution
A database repository in which information for the tool's use is stored
A set of database tools for performing DBA tasks
A job scheduler
An event management system
A graphical interface to the Recovery Manager tools (not available in Oracle7)
One intelligent agent per node to support communication between the databases on that node and the OEM

From the console, you can:

View the makeup of, and administer to, several databases on one or more nodes at once
Access the various database administration tool manager options
Schedule jobs to run on one or more nodes against one or more databases
Monitor one or more databases for various events and notify designated people by displaying a message back to the OEM console, sending email, and/or paging a specific person or group of people

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The OEM contains a set of database administration tools you can use to perform the normal, day-to-day tasks required of a DBA. As we said at the beginning of this chapter, the OEM is a GUI tool which will help you perform database administration tasks. Although it communicates with the databases using SQL commands (everything communicates with the database through SQL commands), the OEM is not a replacement for SQL*Plus — SQL never goes away.

If you are a new DBA, you will find tools within the OEM toolkit to help you perform your job more efficiently and effectively. If you are a seasoned DBA, you will still find many tools within the product set to help you be a better DBA. We recommend that, if you are a new DBA, you take advantage of the "Show SQL" option available from most of the tool screens to help you learn the SQL commands used to execute the various tasks you will perform with the OEM.

Do not rely solely on the OEM to perform your tasks. If, for whatever reasons, the OEM console becomes unavailable to you, you will have to be able to issue SQL commands explicitly and you'll need a thorough knowledge of those commands so you can administer your databases.

The following sections will examine each utility provided within the DBA toolkit and focus on what options, if any, are provided to aid you in implementing and maintaining database security.

The Backup Manager enables you to perform the backups necessary to protect your system. If you ever have a damaging security breach, sound backups will ensure your ability to recover to an undamaged version of the database.

The latest version of the Oracle Backup Manager provides the ability to:

Use the new Recovery Manager option as well as the backup tasks available in earlier versions of the OEM

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The OEM provides you with the ability to configure a set of jobs to be automatically run on one or more of your databases. You can set up the jobs to run at the same time on each database or at different times. Oracle supplies a list of commonly used jobs to choose from. You can also write your own customized scripts and schedule them to run automatically.

As with all the OEM utilities, the OEM Job Scheduler uses the intelligent agent on each computer node to perform the actual work that has been scheduled. There is only one intelligent agent per node, regardless of how many databases are on that node or how many jobs have been scheduled to be run against those databases.

The default tasks provided with the OEM Job Scheduler utility can be run either one time or on a scheduled basis:

Back up tablespace
Export
Import
Load
Run DBA script
Run SQL*Plus
Shut down database
Start up database
Broadcast message
Run OS command
Run TCL
Deinstall product
Delete package
Distribute package
Install package

Many of these tasks help protect your databases, as we describe in the following sections.

The ability to back up tablespaces on a scheduled basis is a wonderful option that helps ensure you can recover your database to either the last time the tablespaces were backed up or to a point in time after the last backup (if archivelog mode has been enabled). However, if you are going to schedule automatic tablespace backups, you must be sure to also perform a log switch and capture the associated redo log files and control files to be included with the tablespace backup so you can perform an effective recovery.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Event Management System provided by OEM enables you to define events you want the intelligent agent on a platform to watch for within one or more of your databases on that machine. For example, perhaps you would like to catch a high volume of I/O contention occurring within a specific database. The Event Manager can perform that activity.

The Event Management System provides you with a way to proactively monitor for specific events on each of your databases. Many of the monitoring options, like the database UpDown (indicating whether the database is up or down), the alert log being written to, and the number of processes or users exceeded, can be used to help ensure that your database is better protected and more secure.

There are several categories of event types Oracle has predefined and made available; these are broken down by the system events they will affect as follows:

Fault Management events
Space Management events
Resource Management events
Performance Management events

The following sections examine each category, and the events within, to see which events might either enhance or endanger your security system.

You can configure the Event Management System to monitor for the following Fault Management events:

New values being written to the alert log (Alert option)
Cases where a user is being blocked from being able to perform an action (User Blocks)
Whether the database is up or down (UpDown)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

If you've read this far, you've probably concluded that maintaining user accounts can be a tiresome job. Well, you are absolutely correct! Creating a user is easy. Creating a role is easy. Giving grants to roles is easy. Deciding how to mix all of these is sometimes tedious, but it gets done. Doing all of this once or twice is interesting. Doing it a third time is work, and on the fourth request, you will probably be ready to strangle your client. Without some interface to implement small changes, the maintenance of scripts will drive you crazy. You need a tool to help with the management of users and their privileges, and some tools to document what you have done. You really don't want to do it all over from scratch every time!

This chapter discusses an application that was developed for a client to help solve the maintenance problem. Toward the end of the chapter we provide a sample script to illustrate how the information to create the user and role scripts can be extracted from the data dictionary. You really do need scripts of these kinds in order to recover from catastrophic failure.

There are vendor-supplied tools available to do the type of work we describe in this chapter, but often they are too powerful or complex to turn over to the typical user. A simple custom application of the kind we show in this chapter has the advantage of doing exactly what is needed — and nothing more.

What do we want from this application?

The application must allow user accounts to be created and maintained.
It must support the creation of roles and the assignment or removal of privileges to these roles.
It must allow multiple roles to be assigned to a user.
When a role is assigned, the application must allow the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

What do we want from this application?

The application must allow user accounts to be created and maintained.
It must support the creation of roles and the assignment or removal of privileges to these roles.
It must allow multiple roles to be assigned to a user.
When a role is assigned, the application must allow the security user to designate if that role is to be a default role.
In the process of assigning the role, the previously assigned roles must be preserved, and those that are default roles must remain in that state.
In addition to creating the users, roles, and assignments, the application must allow you to review these assignments.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The security maintenance form we discuss in this chapter was developed using the Oracle Developer 2000 product. It was originally developed in version 4.5 of the Forms module of Developer 2000, and has been migrated to version 5.0. This form works only on users, roles, and system privileges, not on table grants. The use of grants was not a requirement for this particular application.

This application uses data dictionary tables and the APP_ROLES table. This table and its function were described in Chapter 9. This table is one that you create, and it is not part of the data dictionary. This is the only table used by the form whose contents you can modify directly. Changes in all of the other sections of the form are handled by PL/SQL procedures.

Figure 14.1 shows the startup display. There are three sections to the form: user maintenance, role maintenance, and role assignment. The latter also has a part in the role maintenance function.

Figure 14.1: Maintenance form initial display

The top four fields are the user maintenance section. Here a user may be created or dropped and the password changed if (when) the user forgets it. The default and temporary tablespace assignments are also made here. You may think from looking at the initial display format that there's no way to drop a user, but that depends on the status of the username entered in the first field. We'll explain more about this in the next section.

If a username is entered that does not exist, a message to that effect appears to the right of the "User to create or modify" field. Let's take an existing user for this example. You can enter the name in the field, or the field may be queried. If you query the field, a pop-up window appears in the upper left corner of the form, as shown in Figure 14.2.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

An application such as the one we've just discussed — one that simplifies the task of maintaining users and roles — does not necessarily help with the documentation of what has been done. That function could be built into the application, but it is not advised. People do make mistakes during development, and documenting the finished product is easier. The term "documentation" in this context means the creation of script files that will recreate the architecture of the database — particularly the assignment of roles to users and the declaration of the user default roles. This documentation is useful for several purposes, but the primary one is to assist with database recovery. If backups are done regularly, these scripts will probably never be used. Murphy, however, seems to work in every computer shop.

Most of the information in the data dictionary is already documented by the scripts the DBA creates. The assignment of roles to users is rather dynamic, particularly in the early phases of development of a system. A script to extract that information from the data dictionary would be handy; we provide one in the following section.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Here we provide a script that will create a file. This file will contain the commands to grant roles to users and define their default roles. All this information is extracted from the data dictionary. The script file CRUSRGRT.SQL is provided as one example of how to create this file. It is a SQL script that writes another SQL script. This one is a little unusual because it combines conventional SQL with PL/SQL code:

doc
  crusrgrt.sql
  Arguments:  None
  Combination SQL and PL/SQL script.  Creates SQL script
  to grant roles to users and define user default roles.
  Output file is mkrolgrt.sql
#
SET TERMOUT OFF ECHO OFF FEEDBACK OFF PAGESIZE 0 VERIFY OFF
SPOOL mkrolgrt.sql
-- Get the granted roles and output a grant ... to ... string.
SELECT 'grant '||granted_role||' to '||grantee||
        DECODE(UPPER(admin_option), 'YES',
        ' with grant option', null)||';'
  FROM dba_role_privs
 WHERE grantee NOT IN ('SYS','SYSTEM','SCOTT','DBA','DBSNMP')
 ORDER BY grantee;
-- Default roles are tricky.  They must all be declared at once, so use a
-- PL/SQL loop to find all of them before writing out the string.
SET SERVEROUTPUT ON
DECLARE
   usrname     varchar2(35);
   lastusr     varchar2(35)  := 'START VALUE';
   cmd         varchar2(400) :=  null;
   CURSOR udr
   IS
    SELECT grantee, granted_role FROM dba_role_privs
     WHERE grantee NOT IN ('SYS','SYSTEM','SCOTT','DBA','DBSNMP')
       AND UPPER(default_role) = 'YES'
     ORDER BY grantee;
  rrec   udr%ROWTYPE
BEGIN
OPEN udr;
LOOP
  FETCH udr 
  INTO rrec;
  IF rrec.grantee != lastusr  -- Has username changed?
     OR udr%NOTFOUND
  THEN     -- process last record
     IF cmd IS NOT NULL 
     THEN
        cmd  := 'Alter user '||lastusr||' default role '||cmd||';';
        DBMS_OUTPUT.PUT_LINE(cmd);
        cmd  := null;           -- Clear the command string
     END IF;
     lastusr := rrec.grantee;  -- Save the current username
  END IF;
  -- Assemble the default roles into a comma separated string
  IF cmd IS NOT NULL
  THEN
     cmd := cmd||', ';
  END IF;
  cmd := cmd||rrec.granted_role;
  IF udr%NOTFOUND
  THEN        -- Loop exit test
    EXIT;
  END IF;
END LOOP;
END;
/
SPOOL OFF
SET TERMOUT ON ECHO ON FEEDBACK ON PAGESIZE 24 VERIFY ON

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Oracle Security Server (OSS) product was introduced with the basic Oracle8 software delivery. Although OSS is new with Oracle8, you can use it with Oracle7 as well. You do not need to purchase anything else from Oracle Corporation to be able to install, configure, and use this product. Why would you want to install and use the OSS? To see more clearly why you would want OSS and what you'd do with it, let's look first at some things you might do to prove your identity.

When you travel from one country to another, you may be required to carry a passport or some other form of identification to prove who you are and where you came from. To obtain the passport, you generally need to go to a government agency carrying a picture of yourself and a birth certificate to prove your identity. You fill out a form and provide your picture and identification, and the agency will issue a passport to you. You then carry the passport with you as you travel and show your passport to any agents who request it. In this way, you will be able to successfully prove who you are and will be permitted to travel anywhere within the area without having to display your passport again.

Basically, the concepts of single sign-on and certificates of authority, which we'll explore in this chapter, are very similar to using a passport to travel from one country to another. Once a user has logged on to a "trusted" system and been authenticated using the OSS, that user can carry the electronic certificate granted by the Oracle Security System and "travel" from one area of computing or database to another without having to explicitly produce a username and password again to access each separate system or area.

This chapter describes the basic use of the Oracle Security Server. We'll first look at the fundamentals of the cryptography concepts that underlie the OSS technology. Then we'll explore the OSS architecture, installation, and configuration. Finally, we'll look at the concepts and use of OSS identities, servers, and server authorization, as well as enterprise authorizations.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Years ago, there was a television show that offered a special decoder ring you could send away for. Each week, there would be another secret message you could decode using your ring. You had to know where the starting point for decoding was but, once you knew where to start, you could decode any message that was sent. You could even send coded messages to your friends and receive messages from them. That decoder ring used a simple form of cryptography. Today's highly secure systems obviously use more complex forms of cryptography to protect their messages and file, but the basic principles are the same.

The code used by the decoder ring was a very simple letter substitution cipher. Even if you did not have the ring, you could decipher the code by listing the alphabet and building your own decoder ring key. For example, if the television host said to "start with M" for the first letter of the message, you would list out your alphabet starting with the letter "M" like this:

M N O P Q R S T U V W X Y Z A B C D E F G H I J K L

Below the letters listed, you would write another alphabet starting with "A" like this:

M N O P Q R S T U V W X Y Z A B C D E F G H I J K L
 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

The message which was presented could now be deciphered. Presented with the message:

NDUZS TQXB. UY MF FTQ AW OADDM.

and looking at the key, you could determine that the hero was at the OK Corral and needed help. Obviously, a code as simple as this is easily broken, but it illustrates the basics of how encryption and decryption work.

More complicated forms of cryptography use an algorithm — a method or procedure for completing a specific task — and one or more keys for the encryption and decryption of data stored on and transmitted via secure systems in the computer world. When you want to transcribe data into a coded form (encode), a computer program (which can be an implementation of an

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As we said in the previous section, keys are actually parameters you reference when you run your program (algorithm) to either encrypt or decrypt a message or a piece of data. Keys were first used with algorithms to enable people to communicate electronically in a more private manner. Originally, the same algorithm was used to both encrypt and decrypt a message. But that approach often proved vulnerable to the sophisticated computer programs hackers wrote to break the encrypted messages. By necessity, keys have had to become more complex, and their use and protection more secure. Different approaches have been used to try to ensure that encrypted material cannot be decrypted by anyone other than the intended receiver. This section describes some of these approaches.

One way for two people who want to communicate privately with each other to do so is for them to ensure that they each have a copy of the same key, referred to as a private key, which they use for both encryption and decryption of their message. But to use this approach, they need to find a way to exchange the key information so no one else can get the key.

If Mary and Ralph want to communicate, they will each need a copy of a key to encrypt and decrypt their messages. Let's say that Ralph and Ed also want to communicate privately. They will need a different key so that Mary cannot read their messages. Now Ralph needs to maintain, protect, and track two different keys — one for communication with Mary and one for communication with Ed. If Ralph wants to communicate with a third or fourth person, his key maintenance will quickly compound.

A number of commonly used cryptography approaches are based on the notion of the sharing of private keys. Two of the best known private key algorithms are the Data Encryption Standard (DES), published in 1975 by the National Institute of Standards and Technology (NIST) and the International Data Encryption Algorithm (IDEA), published in 1990.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The OSS is comprised of several components that use underlying protocols to enable its wide assortment of security features. The features the OSS provides are:

User authentication
Certificates of authority
Digital signatures
Public and private key pairs

We have already discussed, in a general way, the meaning and function of each of these security approaches. This section examines the OSS structure and what protocols are used to support the features.

The OSS is made up of the following components:

The OSS Repository
The OSS Manager
The OSS Authentication Adapter

The person who interacts with and manages the tool is referred to as the security administrator. As we've discussed earlier in the book, the security administrator at a site might be a DBA or someone whose basic job is as a security manager or system administrator. In other words, the security administrator does not have to be a DBA to work with the OSS toolset. Each piece of the OSS — the repository, the Manager, the Authentication Adapter, and the security administrator himself or herself — combines to comprise a certificate authority. The OSS toolset can be used with both Oracle7 — from version 7.3.2 forward — and Oracle8.

Section 15.3.1.1: The OSS Repository

As with some other Oracle products, such as the Oracle Enterprise Manager (OEM) and the Oracle Recovery Manger (RMAN), the Oracle Security Server requires an area of a database in which information can be stored about encrypted private keys as well as certification authorities. The actions supported by the OSS Repository are:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This section explains how to configure and use the Oracle Security Server at your site. The details of software installation are operating-system dependent and will not be discussed here. Please refer to your operating system documentation for installation instructions.

From the physical installation perspective, the Oracle Security Server consists of three components:

A command-line tool, which provides the osslogin (connect OSS) utilities
The Advanced Networking Option Authentication Adapter (see Chapter 17, for more information on ANO)
A repository that stores OSS information

The osslogin command-line tool is activated from the DOS prompt on your Windows NT or Windows 95 system. You type in the utility name and respond to the prompts. Notice that the prompts are in the opposite order from the order in which you enter information when creating a global user. The interaction for the user mary, who we created earlier, would look like the following:

C:\> osslogin
Oracle Security Server for 32-bit Windows : version 2.0.4.0.0 - Production on 21-JUN-98  16:35:13
© Copyright 1997 Oracle Corporation.  All rights reserved.
Oracle Security Server Login Program
Please Enter the following information:
Common Name: mary
Locality: Vienna
State: VA
Organization Unit: MyDept
Organization: MyCompany
Country: US

Once you have entered the last piece of information (Country), the utility will generate a wallet for the user mary.

The section "More about osslogin" provides additional details about the osslogin syntax.

The OSS repository will use the system account and will need the system password during installation. However, interaction with the OSS will be performed using an account created during installation. The account you must log in to is

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Many years ago, in a system administration class one of us attended, the instructor spent an entire afternoon teaching us the step-by-step procedures for breaking into several different models of Digital Equipment Corporation VAX computers. We learned how to "hack" into a computer from an operator's console and how to gain privileged access from a remote terminal. Of course, some ways to break into the computers were easier than others, but all of the models that were current at that time were able to be compromised. The people in the class were amazed that an instructor would so thoroughly teach the art of the break-in. One student expressed this amazement to the teacher. The teacher's reply, which seemed so obvious afterwards, was, "If you don't know all of the ways which someone can use to compromise your system, you won't be able to completely defend that system."

With the explosive way in which the world has embraced the use of the Internet, intranets, and the World Wide Web, we now have more ways in which our systems can be compromised — and there is much more to be learned about defending those systems.

Who's using the Internet today? The large volume of Internet users now includes such diverse populations as private citizens, commercial businesses, universities, hospitals, public institutions, national, state, and local government bodies, and non-profit organizations.

The wealth and volume of information available on the Internet is almost incomprehensible. Information is available on media ranging from newsgroups and electronic mailing lists to product and company information. The United States Government has web sites to supply information about its various organizations and activities. You can "surf" over to the White House home page (https://www.whitehouse.gov/) and gather information from "The Virtual Library" to an "Interactive Citizens' Handbook" to "What's Happening at the White House." Never before in the history of the world have you been able to tap into so much information so easily from the comfort of your own home.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many reasons why more and more businesses and government agencies are joining the ranks of those who have (and provide access to information through) web sites. The major reason to host a web site is to improve communication between employees (through an intranet) or between your company and potential, current, or past customers (through the Internet). The ease of reaching a large number of people with minimum expense is very appealing. The Internet enables small businesses with very limited funds to reach larger audiences of potential customers easily. Large companies can also benefit from the high Internet traffic.

Let's look at some services you and your company might provide via the Internet or an intranet:

Help desks and technical support
Educational opportunities and computer-based training
Sales and unique services
Public announcements and government policies
Publication of reports and scientific data

Some government agencies are even using the Internet to provide employees with notification of "suspense" dates — dates when specific information is due to be delivered to one or more organizations. The volume of topics on which you can find information on the Internet is almost limitless.

Here's an interesting excerpt from Volume 7 — May, 1998 Netscape Netcenter News, "Netscape.htm," an electronic, information document sent out, free of charge, by Netscape Communications Corporation:

"Thanks to everyone who took last month's small-business survey! Here is what you told us:

50 percent of you buy products online
28 percent of you sell products online

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In Chapter 7, we looked at the steps you could take to generate and implement an effective and enforceable security approach. Before you establish a web site, you need to consider the potential risks and benefits involved with hosting such a site. You will want to decide how secure your site really needs to be and how much it will cost, both in actual cash outlay for software and in the number of hours it will take to implement and maintain the degree of security you need to keep your site safe.

As with every area of computing, there are specific potential risks involved with hosting a web site. There are several different forms of losses to your computer system and Oracle database that could be suffered if someone manages to break into your system. Some of these risks are:

Loss of the operating system and its contents from a virus
Loss of data
Financial loss (if another company obtains data from your system, giving them a competitive edge)
Cost in both personnel effort during recovery and delay of access to data if damage occurs
Loss of personal (or personnel) privacy

An intranet can be either stand-alone or connected to the Internet. If your intranet is a stand-alone system, the risk of the system or database being compromised is potentially reduced. If your intranet is connected to the Internet, the risk is much greater. The larger the Internet to which you are connected, the greater the risk becomes. The Internet is very global and many of the sites offer information, trial copies of their software, and other free materials in the hope of gaining your business. Even Oracle Corporation offers trial software and other "goodies" from their web site.

You need to determine the type of information you plan to offer from your site and how much and what forms of access will be permitted to your database. The degree of sensitivity of the data that will be viewed must also be considered. Some sites offer two versions of their site — one site is more secure than the other and offers much more privileged information. Other companies choose to incorporate different levels of access within one site through the use of password-protected areas.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Although Internet technology is fairly old, by computer standards, the options for securing a web site and protecting an Oracle database are relatively young and immature. There are several possible approaches you can implement to help protect your database, including:

Cookies — with the user's IP address encrypted in a form only your web site can decrypt
Firewalls — both packet-filtering and application-level proxies
A security server that issues digitally signed certificates of authority
Access control at the operating system level
Blind faith that there are so many packets "floating around" that yours won't be intercepted or targeted

All these methods (except the last one) are valid approaches for implementing web security. Let's examine each one more closely.

As mentioned in the terminology section earlier in this chapter, cookies are usually small ASCII text files. Originally, cookies were used by the Netscape browser to help track a user's actions through several HTTP requests. The process of keeping track of a user's movements from one HTTP page to another is known as instantiation . The cookie keeps track of the user's movements forward. Then, when a user wants to return to a previous page, the cookie can be used to help guide the browser backwards through previous pages and URLs of interest.

Section 16.3.1.1: Capturing an IP address

Another potential use for cookies would be to have your web site capture and encrypt the user's Internet Protocol (IP) address the first time that user contacts your site. Each time a user re-enters your site and requests information from your database, the IP address your site's software encrypted would be decrypted and compared to the IP address of the user attempting to gain access to your database. Since your software should be the only code that can decrypt your encryption successfully, if the IP address of the user exactly matches the decrypted IP address in the cookie, you can be pretty confident that the user is who he claims to be and that access can be granted. Since the cookie stores the information, you do not have to code any special application to insert or retrieve information from your site's central database for user authentication.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In Chapter 7, we discussed the steps you can take to create and implement a security policy and security plan. When it comes to web site security, one of the steps you can take with the greatest payoff in security is to make your policies clear and available to your users. Here are some ways you can let visitors to your site know what you expect from them and what they can expect from you:

Create and post a security policy screen that each user must acknowledge each time they access your site
Force each user to sign an agreement to observe your security policy — before they can get a logon to your site
Post information about the users' rights when accessing your web site

The policy you post should outline the rules you intend to enforce and the consequences to the user if the rules are broken.

If you post a policy, you will need to ensure that you can enforce that policy. For example, if your intranet policy says that there are sites or newsgroups your employees are not permitted to access, you will have to be able to monitor their activities to ensure that they are not accessing those sites. If you are going to audit user actions, you have an obligation to notify your users of that fact.

You should be sure that you are able to enforce any policies you post. In the case of an intranet, you should try to involve your users in helping you enforce policies. Show them what steps they can take to keep the system and their data safe. The more your site visitors know about what you expect:

The better they can comply with your security requests
The less likely they are to intentionally violate the rules

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The goal of this book has been to provide you with the background and examples to implement a reasonable degree of security within your Oracle database, based on the software Oracle delivers by default. For example, it describes how you can use roles and views to control user access to different areas of the database, limit access to your operating system files, and implement auditing to further protect your data and database.

The topics discussed in this chapter go beyond the basic security available in the standard Oracle database. Oracle Corporation provides several products that offer additional security at an additional cost. Here we provide a brief discussion of these Oracle products so you will have an idea of other options available to protect your databases:

Trusted Oracle (TO)
The Advanced Networking Option (ANO)
The Oracle Application Server (OAS)

Our hope is that we can supply you with enough information so you'll be able to recognize the terms and concepts pertaining to each of the products described. You can get a great deal more information from Oracle Corporation.

Trusted Oracle is a multi-level security (MLS) product used primarily within government agencies where data access is based on security clearance levels. The government security levels are (in increasing degree of security):

Unclassified
Confidential
Secret
Top secret

Normally, in highly secure government agencies, information is restricted by a "need to know" basis. Trusted Oracle is intended to allow you to access only the information at the level your security clearance allows. For example, if you have been granted a clearance level of

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Trusted Oracle is a multi-level security (MLS) product used primarily within government agencies where data access is based on security clearance levels. The government security levels are (in increasing degree of security):

Unclassified
Confidential
Secret
Top secret

Normally, in highly secure government agencies, information is restricted by a "need to know" basis. Trusted Oracle is intended to allow you to access only the information at the level your security clearance allows. For example, if you have been granted a clearance level of secret, you can view information that has been classified at the confidential and secretlevels, but you will not be able to view information at a higher level.

There is one more component to a clearance. You may hold a secret clearance but not be permitted to view specific areas of confidential or secret information because you do not have a need to know that information. In other words, you might be cleared to see information for the ABC program because you are working on that program but not be able to see information for the XYZ program.

There are, therefore, two potential levels of access at play within a single security level:

The actual security clearance you hold
The programs you have a need to access

Restriction to data access is enforced by the Trusted Oracle engine and by stored PL/SQL programs.

We stress the use of Trusted Oracle in conjunction with security clearances because that is how the product is most often implemented. However, there are many organizations that could benefit by using this product to ensure the protection of very sensitive data. For example, a company whose profits depend on keeping formulas protected might implement Trusted Oracle using various company-defined levels of privilege. A pharmaceutical company could set up its database with different levels of access to the formulas that it views as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Security — the protection of data — is one of the primary concerns of any business. This book focuses on how the standard features of Oracle can be used to control access within the database and thus improve the security position of your business.

But what happens outside of the database? Is the data safe on a network (LAN), an intranet, a MAN, a WAN, or the World Wide Web? Probably not. In the military security community, a popular and commonly told story concerns the detection of keystrokes from teletypes. Many years ago, an evaluation team went to a popular electronic parts store and, for only a few dollars, bought a handful of electronics components. When properly assembled, these components became a crude but effective receiver that could be tuned to the frequency radiated by the teletype keyboard. This was an excellent example of the interception of data as it was being entered — even before it had a chance to be encrypted. The story ends with the team visiting the "secure" facility with a full copy of the supposedly classified message that had been transmitted only moments earlier.

A similar situation exists today. Data is not usually encrypted between the workstation and the database. Almost everyone has now heard of the terms sniffer and snooper. These terms pertain to hardware and software that can be located close to, but not necessarily physically attached to, the network. You could use sniffers or snoopers to intercept network packets. With sniffer or snooper technology, an interloper can: intercept, read, modify, or substitute data as it travels through the network. Most dangerously, the interception of data can include usernames and passwords.

Oracle provides several products that help you protect the confidentiality and integrity of your data. These products can also help you authenticate users. The base product is called the Advanced Networking Option (ANO); ANO is an option that must be purchased separately from the default RDBMS software bundle.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Web products are usually implemented with a three-tier configuration if a database is involved. While this usually means three computers — a client PC, a middle-tier computer used to support the application code, and a back-end computer that houses the database, as we discussed in Chapter 8 — it does not have to. These are functional concepts, and two computers (or even one) can run with the three-tier model, although that is not common. A two-tier, client server configuration could be used. Most applications dealing with a database must maintain a constant connection. A web application, on the other hand, is stateless and can connect and disconnect from a database as needed to support the web site users' requests. We'll describe what we mean by the terms "constant" and "stateless" in the following section.

Oracle Corporation provides a product called the Oracle Application Server (OAS), which serves a broad spectrum of applications for web-based interaction. With each new version of the Oracle Application Server, new security features are introduced or current features are enhanced. Therefore, this section provides a very general overview of some basic security features available in the OAS version 3.0 product.

While you are dealing with a database from a web site, the actions of the application are remembered internally by the database. This allows you to either keep what you have done (commit) or undo your work (roll back). The actions you can take rely on the database knowing who you are and what you have done. If you disconnect, the database would have to undo all the work you have not committed before you disconnect. The database must also know that you have modified a record and must prevent other users from modifying the same record. The ability of the database to keep track of your actions — for example:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Because this is the first book devoted exclusively to the topic of Oracle security, we can't direct you to any other books that deal extensively with this topic. However, we've assembled a list of Oracle and security-related books, web sites, newsgroups, and conferences, which we hope will provide you with a starting point for more information on Oracle and security topics.

Technology changes at such a rapid pace that it is hard to keep up. We direct you to Oracle Corporation's web site as a primary starting point for Oracle information. If you have an Oracle support contract, Oracle's Metalink site can provide you with a great deal of in-depth data.

In addition to providing Oracle-specific infomation, we've also included a number of references to some excellent security-related books and web sites. For an extensive bibliography of security references (both online and offline)—particularly those related to UNIX——we direct you to Appendixes D through F of Practical UNIX & Internet Security (cited below). For Windows NT resources, we direct you to Appendix B of Essential Windows NT System Administration (also cited below).

Section A.1: Oracle Books

Section A.2: Security Books

Section A.3: Oracle Electronic References

Section A.4: Security Electronic References

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many Oracle books available on a variety of topics. If an author has published more than one edition of a book, the following list shows the most current edition (e.g., the Oracle8 edition). If you need earlier (e.g., Oracle7) editions, check with your local book seller or with an online source such as https://www.amazon.com to see if they are available.

Becker, Rachel (Editor). Advanced Information Systems Inc., Oracle Unleashed. Sams, 1996.

Bobrowski, Steve. Oracle8 Architecture. Osborne McGraw-Hill (Oracle Press), 1997.

Koch, George, Kevin Loney. Oracle8: The Complete Reference. Osborne McGraw-Hill (Oracle Press), 1997.

Luers, Tom. Essential Oracle7. Sams, 1995.

McCullough, Carol. Oracle8 for Dummies. IDG Books Worldwide, 1997.

Abbey, Michael, Michael Corey. Oracle8: A Beginner's Guide. Osborne McGraw-Hill (Oracle Press), 1997.

Ault, Michael R. Oracle8 Administration and Management. John Wiley & Sons, 1997.

Ault, Michael R. (editor) et al. Oracle DBA Exam Cram, Tests 1 and 2. Coriolis Group, 1998.

Ault, Michael R. (editor) et al. Oracle DBA Exam Cram, Tests 3 and 4. Coriolis Group, 1998.

Brown, Linwood. Oracle Database Administation on Unix Systems. Prentice Hall Computer Books, 1997.

Couchman, Jason. Oracle Certified Professional/Dba Certification Exam Guide. Osborne McGraw-Hill (Oracle Press), 1998.

Ensor, Dave, Ian Stevenson. Oracle Design. O'Reilly & Associates, 1997.

Ensor, Dave, Ian Stevenson. Oracle8 Design Tips. O'Reilly & Associates, 1997

Honour, Edward. Oracle How-To: The Definitive Problem-Solver for Oracle Developers and Database Administrators. Waite Group, 1996.

Lomasky, Brian, David C. Kreines. Oracle Scripts. O'Reilly and Associates, 1998.

Loney, Kevin.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This section contains references to a variety of security topics; this list represents only a fraction of the many works available; we've tried to mention those likely to be helpful to Oracle sites.

Neumann, Peter G. Computer Related Risks. Reading, MA: Addison-Wesley, 1995. (Collected from the Internet RISKS mailing list moderated by Neumann.)

National Research Council. Computers at Risk: Safe Computing in the Information Age. National Academy Press, 1991.

Pfleeger, Charles P. Security in Computing (2nd edition). Prentice Hall, 1996.

Power, Richard. Current and Future Danger: A CSI Primer on Computer Crime and Information Warfare. Computer Security Institute, 1995.

Denning, Peter J. Computers Under Attack: Intruders, Worms and Viruses. ACM Press/Addison-Wesley, 1990.

Ferbrache, David. The Pathology of Computer Viruses. Springer-Verlag, 1992.

Hoffman, Lance J. Rogue Programs: Viruses, Worms and Trojan Horses. Van Nostrand Reinhold, 1990.

Bellovin, Steve and Bill Cheswick. Firewalls and Internet Security. Addison-Wesley, 1994.

Chapman, D. Brent, and Elizabeth D. Zwicky. Building Internet Firewalls . O'Reilly & Associates, 1995.

Hunt, Craig. TCP/IP Network Administration. O'Reilly & Associates, 1992.

Kaufman, Charles, Radia Perlman, and Mike Speciner. Network Security: Private Communications in a Public World. Prentice Hall, 1995.

Schneier, Bruce. Applied Cryptography: Protocols, Algorithms, and Source Code in C (2nd edition). John Wiley & Sons, 1996.

Stallings, William. Network and Internetwork Security: Principles and Practice . Prentice Hall, 1995.

Garfinkel, Simson and Gene Spafford. Practical UNIX & Internet Security

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This section contains references to helpful Oracle-related web sites, user groups, and newsgroups.

A major problem encountered as we composed this list of sites was the ever-changing and expanding realm of electronic references on the World Wide Web. As of the day this list was assembled, it was accurate. However, links come and links go. Therefore, if a link we've listed does not work or fails to provide you with the information you are expecting, don't be too hard on us. It was there when we listed it! But do let us know so we can update the book accordingly.

Section A.3.1.1: Oracle Corporation

The most obvious Oracle link is, of course, the URL for Oracle Corporation. Oracle's links provide great product information and educational listings.

https://www.oracle.com

Section A.3.1.2: OraWorld

The OraWorld site says of itself that it provides "Everything Oracle on the World Wide Web." The site provides many Oracle-related links, scripts, tips, and hints.

https://www.oraworld.com

There are many Oracle user groups throughout the world. The majority of the links to them are provided under the home pages for the various international regions. For the United States, the International Oracle User Group — Americas home page provides links. Likewise, the European Oracle User Group has links for many of the European, Middle East, and African sites. For the Far East, Oracle Corporation provides links to these sites.

Section A.3.2.1: International Oracle User Group - Americas

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This section contains references to helpful security-related web sites and newsgroups.

These web sites are likely to be helpful informational resources. Some will be particularly useful if you have to deal with an attack on your site.

Section A.4.1.1: COAST

COAST (Computer Operations, Audit, and Security Technology) is a multi-project, multi-investigator effort in computer security research and education in the Computer Sciences Department at Purdue University. COAST contains information about software, companies, FIRST teams, archives, standards, professional organizations, government agencies, and FAQs—among other goodies. The WWW hotlist index at COAST is the most comprehensive list of its type available on the Internet at this time. COAST also provides a valuable service to the Internet community by maintaining a current and well-organized repository of the most important security tools and documents on the Internet; you can obtain these via anonymous FTP.

https://www.cs.purdue.edu/coast/coast.html

Section A.4.1.2: FIRST

FIRST (Forum of Incident Response and Security Teams) maintains a large archive of material, including pointers to web pages for other FIRST teams.

https://www.first.org

Section A.4.1.3: CERT-CC

CERT-CC (Computer Emergency Response Team Coordination Center) was founded in response to the Internet worm incident in 1988. CERT-CC acts as a clearinghouse for information, and helps organizations respond to security attacks. You can get on CERT-CC's mailing list for security advisories and fixes, and can obtain archived past advisories via anonymous FTP

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Table of Contents

Section 3.8.1.1: System privileges

Section 5.3.2.1: The Oracle-supplied roles can be moving targets

Section 5.3.2.2: UNLIMITED TABLESPACE access

Section 6.1.1.1: PRODUCT_PROFILE and USER_PROFILE tables

Section 6.2.1.1: Basic rules

Section 7.9.1.1: Pre-employment tracking

Section 7.9.1.2: New hires

Section 10.6.1.1: Enabling and viewing statement-level auditing

Section 15.3.1.1: The OSS Repository

Section 16.3.1.1: Capturing an IP address

Section A.3.1.1: Oracle Corporation

Section A.3.1.2: OraWorld

Section A.3.2.1: International Oracle User Group - Americas

Section A.4.1.1: COAST

Section A.4.1.2: FIRST

Section A.4.1.3: CERT-CC