Oracle Essentials Oracle Database 11g (29 page)

This is most commonly done today through the Oracle Enterprise Manager (EM) console, described in
Chapter 5.
For example, youmight 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 pseudorole named PUBLIC that includes every user. All users can use privileges granted to PUBLIC. For example, 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. As we describe in the
“Auditing”

section of this chapter, the privilege CREATE PUBLIC DB LINK is now audited. As database vulnerability is an increasing concern, you may want to consider limited privileges for the PUBLIC role.

Identity Management

No amount of security can overcome the handicap of poor security administration.

The more complex the administration tasks that are being performed, the more likely it is that errors will occur, leaving security holes in your system. In situations where youwant to centrally control access to a number of databases, Oracle Identity Management can provide a solution by storing user information and their authorization in a LDAP directory such as the Oracle Internet Directory (OID). For example, you might use OID to authorize SYSDBA and SYSOPER connections.

Security Privileges

Four basic types of database operations can be limited by security privileges in an Oracle database:

• SELECT to perform queries

• INSERT to put rows into tables or views

• UPDATE to update rows in tables or views

• DELETE to 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 to create a table in a schema

• DROP to remove a table in a schema

• ALTER to alter tables or views

Security

|

141

All of these privileges can be handled with two simple SQL commands. The GRANT

command gives a particular privilege to a user or role, while the REVOKE command takes away a specific privilege. Youcan use GRANT and REVOKE to modify the privileges for an individual or a role. You can also grant the ability to regrant privileges to others. Youcan use either of these commands with the keyword PUBLIC to issue or revoke a privilege for all database users.

Another security privilege, EXECUTE, allows users to run a PL/SQL procedure or function. By default, the PL/SQL routine runs with the security privileges of the user who compiled the routine. Alternately, you can specify that a PL/SQL routine run with what is termed
invoker’s rights
, which means that the routine is run with the security privileges of the user who is invoking the routine.

Special Roles: DBA, SYSDBA, and SYSOPER

Your Oracle database comes with three special roles defined. 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. A number of other administrative users also exist, as consistent with the specific Oracle features deployed.

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 by logging into Oracle Enterprise Manager’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 the
“Backup and Recovery”
section in

Chapter 5.

142

|

Chapter 6: Oracle Security, Auditing, and Compliance

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 privilege

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 commands: STARTUP and SHUTDOWN, CREATE SPFILE, ALTER DATABASE OPEN

or MOUNT or BACKUP, ALTER DATABASE ARCHIVELOG, ALTER DATABASE

RECOVER, as well as 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 are automatically created during database installation and creation.

Regardless, it is generally recommended practice to reset all default passwords that are documented in Oracle.

Policies

A
policy
is a way to extend your security framework. You can specify additional requirements in a policy that are checked whenever a user attempts to activate a role.

Policies are written in PL/SQL and can be used, for example, to limit access to a particular IP address or to particular hours of the day.

Since the release of Oracle Database 10
g
Oracle Enterprise Manager has featured a visual interface to a policy framework in the EM repository that aids management of database security. Security policies or rules are built and stored in a policy library.

Security

|

143

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.

Restricting Data Access

There are situations in which a user will have access to a table, but not all of the data in the table should be viewed. For example, you might have competing suppliers looking at the same tables. Youmay want them to be able to see the products they supply and the total of all products from suppliers, but not detailed information about their competitors. There are a number of ways to do this, as we’ll describe in the following sections, using other examples from Human Resources (HR).

View-based security

Youcan think of
views
as virtual tables defined by queries that extract or derive data from physical
base tables
. 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 an HR 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.

Creating a virtual private database or leveraging the Label Security Option, described in subsequent sections of this chapter provide a more secure means of restricting access to certain data.

Fine-grained access control

Implementing security is a critical but 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 HR 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, many different views, views that would have to change every time an HR representative left or joined the company. And if youwant 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 youdesire, the more work is involved in creating and maintaining the security privileges.

144

|

Chapter 6: Oracle Security, Auditing, and Compliance

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 (VPD).

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 youto refresh, enable, or disable a security policy.

Oracle Database 10
g
and newer database releases feature a VPD that 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 10
g
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 group of data. For a good description of FGAC through PL/SQL, please refer to
Oracle PL/SQL Programming
by Steven Feuerstein and Bill Pribyl and
Oracle PL/SQL for DBAs
by Arup Nanda and Steven Feuerstein (O’Reilly; see
Appendix B for details).

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. The collections of labels, label authorizations, and security enforcement options can be applied to entire schemas 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.

Security

|

145