Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

5.3 Implementing Security

One
of the most important aspects of managing the Oracle database
effectively in a multiuser environment is the creation of a security
scheme to control access to and modification of the database. In an
Oracle database you grant security clearance to individual users or
database roles, as described in the following sections.

Security management is typically performed at three different levels:

Database level

Operating system level

Network security level

At the operating system level, DBAs should have the ability to create
and delete files related to the database, whereas typical database
users should not. Oracle includes operating system-specific security
information as part of its standard documentation set. In many large
organizations, DBAs or database security administrators work closely
with computer system administrators to coordinate security
specifications and practices.

Database security specifications control user database access and
place limits on user capabilities through the use of
username/password pairs. Such specifications may limit the allocation
of resources (disk and CPU) to users and mandate the auditing of
users. Database security at the database level also provides control
of the access to and use of specific schema objects in the database.

5.3.1 Usernames, Privileges, Groups, and Roles

The DBA or database
security administrator creates
usernames
to provide valid user identifiers that can be used to connect to the
database. Two user accounts are automatically created as part of the
installation process and are assigned the DBA role:
SYS and SYSTEM. (The DBA role is
described in a subsequent section.)

Each database username has a
password
associated with it that prevents unauthorized access. Oracle can
check to ensure that passwords:

Are at least four characters long

Differ from the username

Do not match any word on an internal list of simple words

Differ from the previous password (if there is one) by at least three
characters

Oracle can check for these characteristics each time a password is
created or modified as part of enforced security policies.

Once a user has successfully logged into the database, that
user's access is restricted based on
privileges,
which are the rights to execute certain SQL statements. Some
privileges may be granted systemwide (such as the ability to delete
rows anywhere in the database) or may apply only to a specific schema
object in the database (such as the ability to delete rows in a
specific table).

Roles
are named groups of privileges and may be created, altered, or
dropped. In most implementations, the DBA or security administrator
creates usernames for users and assigns roles to specific users,
thereby granting them a set of privileges. This is most commonly done
today through the Oracle Enterprise Manager (EM) console, described
previously in this chapter. For example, you might grant a role to
provide access to a specific set of applications, such as
"human resources," or you might
define multiple roles so that users assigned a certain role can
update hourly pay in the human resources applications, while users
assigned other roles cannot.

Every database has a pseudo-role named PUBLIC that includes every user. All
users can use privileges granted to PUBLIC. If database links are
created using the keyword PUBLIC, they will be visible to all users
who have privileges to the underlying objects for those links and
synonyms. Note that as database vulnerability seems to be an
increasing concern, you may want to consider limited privileges for
the PUBLIC role.

5.3.2 Security Privileges

Four basic
security
privileges are applied to the data in an Oracle database:

SELECT

Perform queries

INSERT

Put rows into tables or views

UPDATE

Update rows in tables or views

DELETE

Remove rows from tables, table partitions, or views

In addition to these data-specific privileges, several other
privileges apply to the objects within a database schema:

CREATE

Create a table in a schema

DROP

Remove a table in a schema

ALTER

Alter tables or views

All of these privileges can be handled with two simple SQL
statements. The GRANT statement gives a particular
privilege to a user or role, while the REVOKE statement takes away a specific
privilege. You can use GRANT and REVOKE to modify the privileges for
an individual or a role. You can use either of these statements with
the keyword PUBLIC to issue or revoke a privilege for all database
users.

5.3.3 The DBA Role, SYSDBA, and SYSOPER

The DBA role is one of the most
important default roles in Oracle. The DBA role includes most system
privileges. By default, it is granted to the users
SYS and SYSTEM, both created at database
creation time. Base tables and data dictionary views are stored in
the SYS schema. SYSTEM schema tables are used for administrative
information and by various Oracle tools and options. Therefore, it is
strongly recommended that you create at least one more administrative
user.

The DBA role does not include basic database administrative tasks
included in the SYSDBA or SYSOPER system privileges. Therefore,
SYSDBA or SYSOPER should be
specifically granted to administrators. They will CONNECT AS either
SYSDBA or SYSOPER to the database and will have access to a database
even when it is not open. SYSDBA privileges can be granted to users
by SYS or by other administrators with SYSDBA privileges. When
granted, the SYSDBA privileges allow a user to perform the following
database actions from the command line of SQL*Plus or the
browser-based iSQL*Plus, or through
EM's point-and-click interface:

STARTUP

Start up a database instance.

SHUTDOWN

Shut down a database instance.

ALTER DATABASE OPEN

Open a mounted but closed database.

ALTER DATABASE MOUNT

Mount a database using a previously started instance.

ALTER DATABASE BACKUP CONTROLFILE

Start a backup of the control file. However, backups are more
frequently done through RMAN today, as described in Section 5.5 later
in this chapter.

ALTER DATABASE ARCHIVELOG

Specify that the contents of a redo log file group must be archived
before the redo log file group can be reused.

ALTER DATABASE RECOVER

Apply logs individually or start automatic application of the redo
logs.

CREATE DATABASE

Create and name a database, specify datafiles and their sizes,
specify logfiles and their sizes, and set parameter limits.

DROP DATABASE

Delete a database and all of the files included in the control file.

CREATE SPFILE

Create a server parameter file from a text initialization (INIT.ORA)
file.

RESTRICTED SESSION

Allow connections to databases started in Restricted mode. Restricted
mode is designed for activities such as troubleshooting and some
types of maintenance, similar to what SYS can do.

Administrators connected as SYSOPER can perform a more limited set of
statements: STARTUP and SHUTDOWN, CREATE SPFILE, ALTER DATABASE OPEN
or MOUNT or BACKUP, ALTER DATABASE ARCHIVELOG, ALTER DATABASE
RECOVER, and also the RESTRICTED SESSION privilege.

Database administrators are authenticated using either operating
system authentication or a password file. The
CONNECT INTERNAL syntax supported in
earlier releases of Oracle is no longer available. When operating
system authentication is used, administrative users must be named in
the OSDBA or OSOPER defined groups. For password file authentication,
the file is created with the ORAPWD utility. Users are added by SYS
or by those having SYSDBA privileges.

With each release of Oracle, fewer default users and passwords in combination are automatically created during database and options installation and creation. Regardless, we recommend that you reset all default passwords that are documented in Oracle.

5.3.4 Auditing Security, Policies, and Policy Violations

If
you're suspicious about the actions of a particular
user and are considering reducing that user's level
of privileges, you may want to make use of Oracle's
audit capabilities to audit that user's actions at
the statement level, privilege level, or schema object level.
Auditing can also gather data about database activities for planning
and tuning purposes. Auditing of connections with administrative
privileges to an instance and audit records recording database
startup and shutdown occur by default.

You can also audit sessions at the user level, which captures some
basic but extremely useful statistics such as the number of logical
I/Os, the number of physical I/Os, and the total time logged on. As
noted previously, gathering performance statistics is low in terms of
overhead, and Oracle Database 10g automatically
gathers statistics in populating the Automatic Workload Repository
(AWR).

Audit records always contain the following information:

Username

Session identifier

Terminal identifier

Name of schema object accessed

Operation performed or attempted

Completion code of the operation

Date and timestamp

The records may be stored in a data
dictionary table (AUD$ in the SYS schema), which is also called the
database audit trail, or in an
operating system audit trail.

To turn on auditing, set the AUDIT_TRAIL
parameter in the initialization file. To generate audit information,
you must set this parameter and specify the desired auditing options
with the AUDIT
statement. A typical use of the AUDIT statement is the auditing of a
specific schema object (e.g., a table) for a certain action (e.g.,
UPDATE) to see who has been modifying that table.

Oracle9i added
fine-grained auditing, which enabled selective audits of SELECT
statements with bind variables based on access of specified columns.
Oracle Database 10g adds extended SQL
support for fine-grained auditing. You can now perform granular
auditing of queries, UPDATE, INSERT, and DELETE operations through
SQL.

How can you quickly identify potential threats?
Enterprise Manager
10g's visual interface to a policy framework in the
EM repository aids management of security in the Oracle Database 10g
release. Security policies or rules are built and stored in a policy
library. Violations of rules are reported as critical, warning, or
informational through the EM interface. Out of the box, security
violations are checked on a daily basis. Policies may be adjusted
according to business demands, and violations can be overridden when
they are reported.

5.3.5 View-Based Security

You can think of
views as virtual tables defined by queries
that extract or derive data from physical
"base" tables. Because you can use
them to create different representations of the data for different
groups of users, you can use views to present only the rows or
columns that a certain group of users should be able to access.

For example, in a human resources application, users from the HR
department may have full access to the employee base table, which
contains basic information such as employee names, work addresses,
and work phone numbers, as well as more restricted information such
as Social Security numbers, home addresses, and home telephone
numbers. For other users in the company, you'll want
to hide more personal information by providing a view that shows only
the basic information.

In many situations, creating a Virtual Private Database (VPD) or
leveraging the Label Security Option, described in subsequent
sections of this chapter, provide a more secure means of restricting
access to certain data.

5.3.6 Fine-Grained Access Control

Implementing
security can be a very time-consuming process, especially if you want
to base security on an attribute with a wide range of values. A good
example of this type of situation in the human resources scenario
previously described would be the need to limit the data an HR
representative can see to only the rows relating to employees that he
supports. Here you're faced with a situation in
which you might have to define a view for every HR representative,
which might mean many different views. And if
you want to grant write access for a
representative's own employees and read access for
other employees, the situation gets even more complex. The smaller
the scope, or
grain,
of the access control you desire, the more work is involved in
creating and maintaining the security privileges.

Oracle offers a type of security that you can
use to grant this type of fine-grained access control
(FGAC). Security policies
implemented as PL/SQL functions can be associated with tables or
views enabling creation of a Virtual Private Database. A
security policy returns a condition that's
dynamically associated with a particular SQL statement, which
transparently limits the data that's returned. In
the HR example, suppose that each representative supports employees
with a last name in a particular alphabetic range, such as A through
G. The security policy would return a WHERE clause, based on a
particular representative's responsibilities, that
limits the rows returned. You can keep the range for each
representative in a separate table that is dynamically queried as
part of the security policy function. This simplifies management of
allowable access if roles and responsibilities change frequently.

You can associate a security policy with a particular view or table
by using the built-in PL/SQL package DBMS_RLS, which also allows you to
refresh, enable, or disable a security policy.

Oracle Database 10g VPD is even more fine-grained, enabling enforced
rewrites when a query references a specific column. Performance of
queries in VPD implementations is also improved in Oracle Database
10g through the support of parallel query. Fine-grained security can
also be based on the type of SQL statement issued. The security
policy previously described could be used to limit UPDATE, INSERT,
and DELETE operations to one set of data, but allow SELECT operations
on a different set of data. For a good description of FGAC through
PL/SQL, see Oracle PL/SQL Programming by Steven
Feuerstein and Bill Pribyl (see Appendix B for details).

5.3.7 Label Security Option

The Oracle Label Security Option eliminates
the need to write VPD PL/SQL programs to enforce row-level label
security where sensitivity labels are desired. These collections of
labels, label authorizations, and security enforcement options can be
applied to entire schema or to specific tables.

Sensitivity labels are defined based on a user's
need to see and/or update data. They consist of a level denoting the
data sensitivity, a category or compartment that further segregates
the data, and a group used to record ownership (which may be
hierarchical in nature) and access.

Standard group definitions given to users provide them access to data
containing those group labels. Inverse groups in the data can be used
to define what labels a user must have in his profile in order to
access it.

Policies are created and applied, sensitivity labels are defined, and
user labels are set and authorized through a policy manager tool
accessible through EM. You can also add SQL predicates and label
functions and manage trusted program units, Oracle VPD fine-grained
access control policies, and VPD application contexts. Label Security
policy management is now possible in Oracle Database 10g when using
the Oracle Internet Directory.

5.3.8 Security and Application Roles and Privileges

Applications can involve
data and logic in many different schemas with many different
privileges. To simplify the issues raised by this complexity, roles
are frequently used in applications. Application roles have all the
privileges necessary to run the applications, and users of the
applications are granted the roles necessary to execute them.

Application roles may contain privileges that should be granted to
users only while they're running the application.
Application developers can place a SET ROLE statement at the beginning of
an application to enable the appropriate role and disable others only
while the application is running. Similarly, you can invoke a DBMS_
SESSION.SET_ROLE procedure from PL/SQL.

Another way to accomplish application security is by
encapsulating privileges in stored
procedures. Instead of granting direct access to the various tables
for an application, you can create stored procedures that provide
access to the tables and grant access to the stored procedures
instead of the tables. For example, instead of granting INSERT
privileges for the EMPLOYEE table, you might create and grant access
to a stored procedure called HIRE_EMPLOYEE that accepts as parameters
all the data for a new employee.

When you run a stored procedure normally, the procedure has the
access rights that were granted to the owner of the procedure; that
owner is the schema in which the procedure resides. If a particular
schema has access to a particular database object, all stored
procedures that reside in that schema have the same rights as the
schema. When any user calls one of those stored procedures, that user
has the same access rights to the underlying data objects that the
procedure does.

For example, suppose there is a schema called HR_REP. This schema has
write access to the EMP table. Any stored procedure in the HR_REP
schema also has write access to the EMP table. Consequently, if you
grant a user access to a stored procedure in the HR_REP schema, that
user will also have write access to the EMP table regardless of her
personal level of security privilege. However, she will have access
only through the stored procedures in the schema.

One small but vitally important caveat applies to access through stored procedures: the security privilege must be directly granted to the schema, not granted by means of a role.

If you attach the keyword AUTHID CURRENT_USER to a stored
procedure when it's compiled, security restrictions
will be enforced based on the username of the user invoking the
procedure, rather than the schema that owns the stored procedure (the
definer of the procedure). If a user has access to a particular
database object with a particular privilege, that user will have the
same access through stored procedures compiled with the AUTHID
CURRENT_USER.

5.3.9 Distributed Database and Multi-Tier Security

All the security features available for undistributed Oracle
databases are also available for the distributed
database
environment, which is covered in Chapter 12. However, the distributed
database environment introduces additional security considerations.
For example, user accounts needed to support server connections must
exist in all of the distributed databases forming the system. As
database links (which define connections between distributed database
instances) are created, you will need to allow the user accounts and
roles needed at each site.

For large implementations, you may want to configure
global
authentication across these distributed databases for users and
roles. Global authentication allows you to maintain a single
authentication list for multiple distributed databases. Where this
type of external authentication is required,
Oracle's Advanced Security Option, discussed in the
next section, provides a solution.

In typical three-tier implementations, the Oracle Application Server
runs some of the application logic, serves as an interface between
the clients and database servers, and provides much of the Oracle
Identity Management (OIM) infrastructure. The
Oracle Internet Directory (OID) provides
directory services running as applications on an Oracle database. The
directory synchronization service, provisioning integrated service,
and delegated administrative service are part of OID. The Oracle
Database 10g certificate authority (an X.509 v3 certificate
authority) and single sign-on reside in the Application Server.
Security in middle-tier applications is controlled by
applications' privileges and preserving client
identities through all three tiers.

EM is commonly used to configure valid application users to
Oracle's LDAP-compliant OID server. A user who
accesses an application for which he is not authenticated is
redirected to a login server. Once there, he is prompted for a
username and password that are checked against the OID server. A
cookie is returned and the user is redirected from the login server
to the application.

5.3.10 Advanced Security Option

The Oracle Advanced Security Option (ASO), formerly
known as the Advanced Networking Option
(ANO), is used in distributed environments linked via Oracle Net in
which there are concerns regarding secure access and transmission of
data. This option specifically provides data encryption during
transmission to protect data from unauthorized viewing over Oracle
Net, as well as Net/SSL, and between thin JDBC clients and the
database. Encryption algorithms supported include RC4_40, RC4_56,
RC4_128, RC4_256, DES_40, 2-Key Triple-DES (3DES), and 3-Key 3DES.
Communications packets are protected against data modification,
transaction replay, and removal through use of MD5 and SHA-1
algorithms.

ASO also provides support for a variety of identity authentication
methods to ensure that user identities are accurately known.
Third-party authentication services
supported include Kerberos, Cybersafe, RADIUS, and DCE. RADIUS
enables support of third-party authentication devices including smart
cards and token cards. Public Key Infrastructure (PKI)
authentication, popular for securing Internet-based e-commerce
applications, uses X.509 v3 digital certificates and can leverage
Entrust Profiles stored in Oracle Wallets. Oracle Database 10g adds
authentication capabilities for users who have Kerberos credentials,
and enables Kerberos-based authentication across database links.

Oracle
Wallets can store multiple certificates in a variety of formats,
including X.509 certificates in PKCS #12 format. Oracle Wallets are
managed through a Wallet Manager accessible through EM and support
the Triple-DES (3DES) algorithm for securing the wallets. User
wallets may be stored and retrieved from the Oracle Internet
Directory. A user migration tool is included to migrate database
users to OID.

In a typical scenario, the Oracle Enterprise Security Manager
configures valid application users to the LDAP-compliant OID server.
An X.509 certificate authority creates private key pairs and
publishes them in Oracle wallets (through Oracle Wallet Manager) to
the LDAP directory.

A user who wants to log in to a database server will need a
certificate and a private key, which can be retrieved from that
user's password-protected wallet, which resides in
the LDAP directory. With Oracle Database 10g, users and servers can
be provisioned using keys of up to 4096 bit that can be honored at
runtime. When the user's key on the client device is
sent to the database server, it is matched with the paired key
retrieved by the server via SSL from the LDAP directory and the user
is authenticated to use the database.