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
Ensuring recoverability of your corporate data
Enabling more complex forms of security such as data encryption, digital signatures, and single sign-on
Supporting Web site structures and database access
In this book, we divide the examination of the components of an Oracle system into two main areas—Oracle operating system files (the physical entities) and Oracle database objects (the logical entities).
This section summarizes the physical (external) components of an Oracle system — the Oracle system files, the database processes, and the System Global Area. We’ll describe these components in greater detail in Chapter 2.
The following files are significant from a security point of view. It is vital that you protect these files to the full extent allowed by your system:
- The physical files that make up the database tablespaces
These include the redo log files, control files, archive log files, datafiles[1] for data, datafiles for the rollback segments, datafiles for the indexes, etc.
- The initialization parameter file
This file contains Oracle’s initialization parameters. In your system, the file has the name
INIT<DATABASE SID>.ORA, whereDATABASE SIDis the system identifier (SID) of your database. In this book, we generally refer to it as theINIT.ORAfile.- The configuration file
The configuration file, called
CONFIG.ORA, contains configuration information about the database. The information includes the location of the control files, the database block size, the location of the various dump files, etc.- Network configuration files
The network configuration files are used for client interaction with the database. These files include (but are not limited to)
LISTENER.ORA,SQLNET.ORA,TNSNAMES.ORA, etc.- The Oracle distribution files
The Oracle distribution files include all of the code that is delivered and that you install on your system in order to build, interact with, and maintain Oracle databases. You need to keep in mind that there are some usernames and passwords embedded in the Oracle distribution files.
- Backup files
Backup files are copies of the database that you create either through the Oracle-supplied backup utilities or by making copies of the files using operating system utilities. Backups should include all of the files listed here plus any configuration management files.
The Oracle detached processes and the System Global Area (SGA) are not files, but are included here because they are controlled from the operating system level and are part of a running Oracle database.
- The Oracle detached processes
The detached processes include (but are not limited to) the process monitor (PMON), the system monitor (SMON), the database writer (DBWR), the log writer (LGWR), and other processes; the particular set depends on the way you configure your database.
- The System Global Area (SGA)
The SGA is a set of memory-resident structures shared by the processes that interact with a database. Among the set of memory-resident structures in the SGA are the database buffer cache, the redo log buffer, the shared pool, and the data dictionary cache.
The following list summarizes the main internal database objects; Oracle protects and maintains these entities directly, and uses them to enforce security in your system. We’ll describe these entities in greater detail in Chapter 3, and explain how they’re used in practical ways in later chapters.
- Users
Database accounts created by the DBA. Users are given privileges to connect to a database to perform work. Users might (but do not have to) own objects. Privileges are granted to users by the DBA, by an authorized user, or by the object’s owner based on the user’s required tasks.
- Schema
- Privileges
Mechanisms given to users to enable actions to be performed on either data or objects. There are two levels of privileges: system-level privileges and object-level privileges.
- Roles
A group of privileges or objects that can be used to pass one or more privileges to one or more users. Roles can be hierarchical—that is, a role can be granted to another role.
- Profiles
Two different forms of profiles exist within an Oracle database: the product profile table, used to control access to SQL, SQL*Plus, and PL/SQL; and the system resource profile, used to control various resources a user might consume. Included within the system resource profile are parameters you can set to enable the new Oracle8 password utility features.
The rest of the internal components are all objects. The word “object,” as we use it in this book, refers to any item owned by a user. A more strict definition of an object would be “an entity which takes up space in a database.” The objects include:
- Tables
- Triggers
Stored programs associated with a table. A trigger is executed when the event on which it is based occurs. The events that will “fire” a trigger are commands that perform INSERT, UPDATE, or DELETE actions.
- Views
Views are used within an Oracle database to enable just a subset of information to be extracted from a table or group of tables. Views provide a definition of what data is to be retrieved and how. A view essentially “rides” on top of one or more tables and acts as a filter to enable users to see only a subset of the columns within the table or tables. Views are actually “quasi-objects” because they do not store data.
- Stored programs
Programs written in PL/SQL can be stored in a compiled form within the database. There are two types of stored programs: procedures and functions. A function must return a value, while a procedure does not have to return a value.
- Synonyms
Other names for an object—think of synonyms as nicknames. A synonym is used in an Oracle database to provide location transparency of objects and object owners.
Oracle provides a special set of
views you can use to “see into” the database internal
data dictionary. The data dictionary contains the description of all
of the objects in the database—tables, views, stored programs,
etc. These views, as a whole, are referred to as the data
dictionary. Using the data dictionary, you will be able to
view and track all of the objects that exist in your database. The
data dictionary is described in detail in Chapter 4. Within that chapter, you’ll see how the
data dictionary views are created, and you’ll examine the views
which are of particular importance to you from a security
perspective. We’ll also look at a special view called
DICTIONARY. This view houses the name and description of each of the
data dictionary views to which a user has been granted access.
We’ll explain the composition of the file used to create the
data dictionary, and you will see how the views are associated with
the underlying database tables.
Before you ever install the Oracle source code for the first time, you will want to consider the steps you must take to ensure that unauthorized people don’t have access to that system code. How can you protect the physical files that comprise your Oracle database system from the operating system level? You can place the files in a set of directories which are owned by an operating system account that belongs to a special group. No one outside of the special group would be allowed to interact with the files on more than a “read-only” basis. Most operating systems provide a mechanism to establish permissions on files to either enable or disable various forms of access to those files, for example:
On a Windows NT system, you can use the
caclscommand or Windows NT Explorer to set the system file protection.On a UNIX system, you can specify permission masks using the
chmodandchownutilities.On an OpenVMS system, you can use a
set protectioncommand to enable or disable file-level interaction.
Chapter 8, suggests approaches you can use to begin to protect your database from the operating system level. Likewise, you will want to protect the application code that interacts with the database.
There are a number of facilities available within the database that you can use for security. Oracle supplies default roles and user accounts in the database that simplify the assignment of privileges to users. The Oracle-supplied roles and users are described in Chapter 5. If you decide to use these mechanisms, you must carefully evaluate how that decision could help or hurt your overall security policies and plan.
From within the database, there are many other mechanisms you can use to protect your company’s data. You can grant to or revoke from users access to objects, like tables and views, within your database. There are different levels and forms of access to the data within the database tables, which you can control by creating views and then permitting only selected users access to those views. On a more automated basis, you can create triggers and procedures to perform actions behind the scenes. A few possible tasks a trigger or procedure might perform include:
Populate another table with information based on the actions being taken on the primary table
Remove information from another table based on the actions being taken on the primary table
Capture information about the data before it is modified
Issue an alert to let someone know a table has been modified
Oracle provides several different mechanisms you can use to authorize connection to an Oracle database. Among the connection mechanisms are:
Username and password
An account that relies on operating system validation to enable database access
Remote access, with or without a username and password
If you create an account for a user, you can either create the
account with a specific
password or create
the account with the keywords IDENTIFIED
EXTERNALLY
. If you create the account with a
password, the user will type his username and password to connect to
the database. If you create the account with the keywords IDENTIFIED
EXTERNALLY, the user will only have to type in a username and
password to access the operating system. From there, the user will
simply use the “/” command to access the database. The
third form of access is through the use of a remote client via any of
the following:
SQL*Net
Net8 (for Oracle8)
A third-party connection such as ODBC, using a tool such as Microsoft Access, SQL*Plus, or the Oracle-supplied Oracle Enterprise Manager (OEM) utility (for DBA access only)
With Oracle8, a password utility is provided to enable you to control the complexity and composition of passwords as well as the length of time a password will remain valid (password aging). Another new feature, the user profile, lets you control how often a user may reuse various passwords over a period of time (password history). You can either pre-expire a user’s password or set the password to expire after a specific length of time. You can lock an Oracle account either automatically (because there have been too many failed login attempts) or explicitly (because you want to prevent access to a specific account). Chapter 6, discusses the password features.
As we mentioned earlier, availability is a key tenet of computer security. In every computer system, backup facilities are integral to keeping your system and data available for use. You can’t protect against every form of data loss. Backup and recovery facilities keep a loss from becoming a disaster.
The level of protection you need to provide for your database will depend on many different elements. If your system is a development environment, for example, you might not have to provide as rigid a security implementation as you would use on a full-scale production system unless you are migrating production data back to your development system for testing purposes. Under those circumstances, you will need to determine if the data being migrated must be filtered to block sensitive data from being compromised.
For a production system, you will need to consider the following when you begin to plan your backup strategy:
How much and what type of information your company can afford to lose
What specific data would cause the most severe problems to your company if it were lost
How long your system could be offline (down) while data was being recovered
How much money your company is willing to spend to ensure that you have redundant systems
All of these issues are relative. Your backup and recovery approach will be dictated, in part, by your responses to these considerations. Regardless of how rigid or flexible your backup strategies need to be, you must be sure that you’ve implemented a backup and recovery structure that will give you the best protection for your environment within your corporate budget constraints. In Chapter 12, we look at a variety of backup and recovery issues and implementations.
Suppose that you’ve adopted the basic measures necessary to protect your Oracle files—you’ve established roles, privileges, views, triggers, and procedures to protect your Oracle database objects, and you’ve created and implemented your backup and recovery strategies, policies, plan, and procedures. What’s left? In recent years, more and more organizations have begun to use strong forms of encryption to protect their data. As hackers have devised more clever ways to compromise your computer systems and databases, the industry has responded by devising better and more complex ways to protect your systems.
We all know the story of the hungry wolf who disguised himself as a
sheep to fool the shepherd and mingle with the unsuspecting herd. He
was then able to easily kill a sheep for his dinner. The
“sheep’s clothing” idea is alive and well, and it
threatens your company’s data. Intruders have various ways of
intercepting your data or even assuming your online identity. Using
programs known as
sniffers
and spoofers, intruders can monitor your
Internet traffic and capture your username or password for future
use. They also have found ways of disguising their messages so they
appear to originate from your own Internet address. There are also
many other ways, both brute force and automated, of guessing
passwords and thereby gaining access to your computer and database.
There are now software tools you can use to protect your system from these intrusions. One way to protect your database is through the use of software that certifies a user really is who he says he is. Once a user has obtained an electronic credential attesting to his authenticity, the user can access any database for which he has been approved. Provided with the base Oracle8 software, a utility called the Oracle Security Server (OSS) Manager (see Chapter 15) enables you to generate and implement the following forms of security:
- Certificate of authority
A user credential that says that a user is really who he claims to be
- Digital signature
An encrypted section of code that authenticates that a message was sent by a specific user
- Single sign-on
The ability to connect once to a system presenting a username and password and be granted access to other systems or databases based on your ability to have gained access to the original system; once you’ve been authenticated, you won’t need to present any further usernames or passwords
In the base product set, Oracle does not provide software for the encryption or decryption of data. However, Oracle does provide—for an additional fee—a product called the Advanced Networking Option (ANO), which can be used for that purpose. Products that are available at an additional price and can be used to further enhance your site’s security are described in Chapter 17.
With the explosive way the world has embraced the use of the Internet, intranets, and the World Wide Web, we now have more ways our systems can be compromised, and there is much more to be learned about defending those systems.
There are many reasons why more and more businesses and government agencies are joining the ranks of those who host, or at least use, 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 volume of Internet traffic.
Because of the high interest and activity in the web arena, we’ve included some brief information about web security in Chapter 16. Within its base software, Oracle does not provide any security utilities specifically for use with your web site. However, Oracle does have a product called the Oracle Application Server available for an additional fee (see Chapter 17). The Oracle Application Server works very well with the security protocols built in to the Oracle database. It also has its own stand-alone security.
[1] The term “datafiles” (all one word) is used by Oracle to designate certain components of a database tablespace; we follow that convention in this book. In a few places, you may also find references to “data files” (two words) that refer to actual data within a file.
Get Oracle Security now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
