Oracle Essentials Oracle Database 11g (30 page)

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 possible in Oracle Database 10
g
and later versions when the Oracle Internet Directory is also used.

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 command 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 application security is sometimes accomplished is by encapsulating privileges in stored procedures. Instead of granting direct access to the various tables for an application, youcan 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, youmight 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.

146

|

Chapter 6: Oracle Security, Auditing, and Compliance

If youattach the keyword AUTHID CURRENT_USER to a stored procedure when it is 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.

Distributed Database and Multitier Security

All the security features available for standard Oracle databases are also available for the distributed database environment, which is covered in
Chapter 13.
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.

Distributed security management

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.

Enterprise Manager 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. 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.

Oracle Identity Management, described earlier in this chapter, can be used to manage security across multiple platforms and security systems.

Multitier security

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 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. Security in middle-tier applications is controlled by applications’ privileges and the preservation of client identities through all three tiers.

Security

|

147

Using multiple tiers, as with large applications or web-based applications, can also call for proxy authentication. The application connects to code in the middle tier, which accesses the database through a proxy, frequently through shared connections. Some databases associate security with a session, which means that sessions must be reestablished when the user identity changes. This limitation makes the multitier approach harder.

Oracle separates authentication from sessions, so the use of a proxy in the middle tier is feasible. A single session can support different users with different identities.

Prior to Oracle 10
g
Release 2, the only way to take advantage of this capability was by using the OCI interface, which was code-intensive. With Release 2, this limitation was lifted, so standard SQL and SQL tools, such as SQL*Plus, could use proxy authentication.

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, IIOP/SSL, and between thin JDBC clients and the database. Encryption algorithms supported include RC4_

40, RC4_56, RC4_128, RC4_256, DES, DES_40, 3DES112, 3DES168, AES128, AES192, and AES256. Communications packets are protected against data modification, transaction replay, and removal through use of MD5 and SHA-1 algorithms.

Transparent Data Encryption (described in the next section) is included as part of the Advanced Security Option beginning with Oracle Database 10
g
Release 2. Transparent Data Encryption provides an easy way to encrypt data in the database, and the network data encryption option of ASO protects the data during transmission to the client.

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, 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 10
g
added authentication capabilities for users who have Kerberos credentials, and enables Kerberos-based authentication across database links.

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
148

|

Chapter 6: Oracle Security, Auditing, and Compliance

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. 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.

Encryption

The previous sections of this chapter all deal with the need to protect access to data in the Oracle database. There may be times when youwant to take the extra step of protecting the actual data values from unauthorized viewing by encrypting the data.

Oracle has provided data encryption for several releases, but Oracle Database 10
g
Release 2 included a significant new feature called Transparent Data Encryption.

Prior to the introduction of this feature, encrypted data stored in the Oracle database had to be decrypted by an application before it could be used. This scenario caused a number of limitations, the most prominent being that decryption of data was done by applications. If youwanted to start encrypting a particular piece of data, you would have to change all data access routines in every application that used the data. This limitation alone made it difficult to consider adding encryption to existing data.

With Transparent Data Encryption, the database does the work of encrypting and decrypting data automatically. Data sent to the database is encrypted by Oracle, and data requested from the database is decrypted. No additional code is required in an application, which means that youcan encrypt existing data without changing any of your SQL access statements.

Oracle Database 11
g
allows youto encrypt entire tablespaces (described in

Chapter 4)
with Transparent Data Encryption, and this feature should reduce management overhead for this feature.

Secure Backup

The security features described in previous sections give you the tools you need to keep the data in your Oracle database secure. But what about when the data leaves your Oracle database—for example, when you perform the necessary maintenance step of backing up the data?

Recent events have shown that lost backup tapes are a reality, and backup tapes can be stolen. Secure Backup, released between Oracle Database 10
g
Release 2 and Oracle Database 11
g
, automatically encrypts your backup data. The data can be decrypted only by the source database, so even if a backup tape is lost or stolen, the recipient will not be able to see your data.

Security

|

149

Auditing

The Oracle database gives you the ability to restrict unauthorized access to your valuable data. However, your security is only as good as your implementation, and people do make mistakes. In addition, youmay want to understand what type of activities—legitimate or not—are taking place with your data. The ability to audit database activity can address both of these issues.

Oracle’s audit capabilities let you track actions at the statement level, privilege level, or schema object level for the entire database or particular users. 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 in the previous chapter, gathering performance statistics is low in terms of overhead, and Oracle Database 10
g
and later releases automatically gather 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.

Oracle9
i
added fine-grained auditing, which enabled selective audits of SELECT

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

In Oracle Database 11
g
, auditing is turned on by default, and the AUDIT_TRAIL initialization parameter is set to DB. Privileges audited by default include:

• ALTER ANY PROCEDURE

• ALTER ANY TABLE

• ALTER DATABASE

150

|

Chapter 6: Oracle Security, Auditing, and Compliance

• ALTER PROFILE

• ALTER SYSTEM, ALTER USER

• AUDIT SYSTEM

• CREATE ANY JOB, CREATE ANY LIBRARY, CREATE ANY PROCEDURE,

CREATE ANY TABLE, CREATE EXTERNAL JOB, CREATE PUBLIC DB

LINK, CREATE SESSION, CREATE USER

• DROP ANY PROCEDURE, DROP ANY TABLE, DROP PROFILE, DROP

USER

• EXEMPT ACCESS POLICY

• GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE, and GRANT

ANY ROLE

Compliance