Oracle Security | O'Reilly Media

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 ta

Table of Contents

Section 3.8.1.1: System privileges