3 Security Policies and Tips

The idea of security policies includes many dimensions. Broad considerations include regular backups and storing them off-site. Narrow table or data considerations include ensuring that unauthorized access to confidential data, such as employee salaries, is precluded by built-in restrictions on every type of access to the table that contains such data.

This chapter introduces ideas about security policies and offers tips about recommended practices that can tighten security in the following sections:

Introduction to Database Security Policies
Recommended Application Design Practices to Reduce Risk

3.1 Introduction to Database Security Policies

This section briefly introduces security policies. It covers:

Security Threats and Countermeasures
What Information Security Policies Can Cover

3.1.1 Security Threats and Countermeasures

An organization should create a written security policy to enumerate the security threats it is trying to guard against and the specific measures the organization must take. Security threats can be addressed with different types of measures:

Procedural, such as requiring data center employees to display security badges
Personnel-related, such as performing background checks or "vetting" key personnel
Physical, such as securing computers in restricted-access facilities
Technical, such as implementing strong authentication requirements for critical business systems

Consider whether the appropriate response to a threat is procedural, physical, technical, personnel-related, or a combination of many such measures.

For example, one possible security threat is the disruption of critical business systems caused by a malicious person damaging a computer. A physical response to this threat is to secure key business computers in a locked facility. A procedural response is to create system backups at regular intervals. Personnel measures could include background checks on employees who access or manage key business systems.

Oracle Database offers many mechanisms you can use to implement the technical measures of a good security policy. These are discussed in Chapter 7, "Security Policies" and Chapter 14, "Using Virtual Private Database to Implement Application Security Policies".

3.1.2 What Information Security Policies Can Cover

In addition to addressing requirements unique to your environment, you should also design and implement technical measures in your information security policies to address important generic issues, such as the concerns described in Table 3-1.

Table 3-1 Issues and Actions that Security Policies Must Address

Security Concern/Practice	Recommended Actions	References
Establish and maintain application-level security	Attach privileges and roles to each application. Ensure that users cannot misuse those roles and privileges when they are not using the application. Base use of roles on user-defined criteria, such as a user connecting only from a particular IP address, or only through a particular middle tier.	See Ref ^Foot 1
Manage privileges and attributes (system/object/user)	Permit only certain users to access, process, or alter data, including the rights to execute a particular type of SQL statement or to access another user's object. Apply varying limitations on users' access to or actions on objects, such as schemas, tables, or rows, or resources, such as time (CPU, connect, or idle times).	See Ref ^Foot 2
Create, manage, and control roles (database, enterprise)	Create named groups of privileges to facilitate granting them to users, including previously named groups (roles).	See Ref ^Foot 3
Establish the granularity of access control desired	Set up session-based attributes securely. For example, store user attributes (user name, employee number, and so on) to be retrieved later in the session, enabling fine-grained access control. Create security policy functions and attach them to critical or sensitive tables, views, or synonyms used by an application. DML statements on such objects are then modified dynamically, and transparently to the user, to preclude inappropriate access. Enforce fine-grained or label-based access control automatically with policy functions or data and user labels, quickly limiting access to sensitive data, often without additional programming	See Ref ^Foot 4
Establish and manage the use of encryption	Use SSL connections, well-established encryption suites, or PKI certificates for critical or sensitive transmissions and applications.	See Ref ^Foot 5
Establish and maintain security in 3-tier applications	Preserve user identity through a middle tier to the database. Avoid the overhead of separate database connections by proxying user identities (and credentials like a password or certificate) through the middle tier to the database.	See Ref ^Foot 6
Control query access, data misuse, and intrusions	Monitor query access based on specific content or row to detect data misuse or intrusions. Use proxy authentication to support auditing of proxied user connections. Use regular auditing and fine-grained auditing to detect unauthorized or inappropriate access or actions.	See Ref ^Foot 7

^Footnote 1 Discussed under "Creating Secure Application Roles"

^Footnote 2 Discussed under Introduction to Privileges, Understanding User Privileges and Roles, and Granting User Privileges and Roles

^Footnote 3 Discussed under "Introduction to Roles" and Managing User Roles

^Footnote 4 Discussed under Chapter 7, "Security Policies", Chapter 14, "Using Virtual Private Database to Implement Application Security Policies" & Chapter 15, "Implementing Application Context and Fine-Grained Access Control"

^Footnote 5 Discussed under Chapter 4, "Authentication Methods"

^Footnote 6 Discussed under Chapter 15, "Implementing Application Context and Fine-Grained Access Control" & Chapter 16, "Preserving User Identity in Multitiered Environments"

^Footnote 7 Discussed under Chapter 15, "Implementing Application Context and Fine-Grained Access Control" & Chapter 16, "Preserving User Identity in Multitiered Environments"; for auditing, Chapter 8, "Database Auditing: Security Considerations"and Chapter 12, "Configuring and Administering Auditing"

The security practices and recommended actions of Table 3-1 are readily implemented using the Oracle features, facilities, and products listed in Table 3-2. Discussions of these terms and products appear in the corresponding chapters (or book) listed in Table 3-2.

Table 3-2 Reference Terms and Chapters for Oracle Features and Products

Reference Terms	Reference Chapters
Application Context	Chapter 15, "Implementing Application Context and Fine-Grained Access Control"
Data Encryption	Chapter 17, "Developing Applications Using Data Encryption"
Fine-Grained Access Control	Chapter 15, "Implementing Application Context and Fine-Grained Access Control"
Fine-Grained Auditing	Chapter 8, "Database Auditing: Security Considerations" Chapter 12, "Configuring and Administering Auditing"
Oracle Label Security	Oracle Label Security Administrator's Guide
Proxy Authentication	Chapter 16, "Preserving User Identity in Multitiered Environments"
End-User Identity Propagation	Chapter 16, "Preserving User Identity in Multitiered Environments"
Secure Application Roles	Chapter 5, "Authorization: Privileges, Roles, Profiles, and Resource Limitations" Chapter 13, "Introducing Database Security for Application Developers"

3.2 Recommended Application Design Practices to Reduce Risk

To avoid or minimize potential problems, use the following recommended practices for database roles and privileges. Each practice is explained in the following sections in detail:

Tip 1: Enable and Disable Roles Promptly
Tip 2: Encapsulate Privileges in Stored Procedures
Tip 3: Use Role Passwords Unknown to the User
Tip 4: Use Proxy Authentication and a Secure Application Role
Tip 5: Use Secure Application Roles to Verify IP Address
Tip 6: Use Application Context and Fine-Grained Access Control

3.2.1 Tip 1: Enable and Disable Roles Promptly

Enable a role only when the application starts, and disable it as soon as the application terminates. To do this, you must take the following approach:

Give each application distinct roles.
For each application, establish one role that contains all the privileges necessary to use the application successfully.
If needed, establish several additional roles that contain only some of these privileges, to provide tighter or less restrictive security to different users or uses of the application
Protect each database role by a password (or by operating system authentication) to prevent unauthorized use.

One role should contain only non destructive privileges associated with the application (SELECT privileges for specific tables or views associated with the application). This read-only role allows an application user to generate custom reports using ad hoc tools, such as SQL*Plus, but disallows modifying table data. A role designed for an ad hoc query tool may or may not be protected by a password (or by operating system authentication).
Use the SET ROLE statement at application startup to enable one of the database roles associated with that application. For a role authorized by a password, the SET ROLE statement within the application must include that password, preferably encrypted by the application. If a role is authorized by the operating system, then the system administrator must set up accounts in advance to provide application users with appropriate operating system privileges.
Disable a previously enabled database role upon application termination.
Grant application users database roles as needed.

Note:
Database roles granted to users can be enabled by users outside the application. Such use is not controlled by application-based security, but it can be controlled by virtual private database. In three-tier systems, using a secure application role prevents users from acquiring the role outside the application.

Additionally, you can use the PRODUCT_USER_PROFILE table to do the following:

Specify what roles to enable when a user starts SQL*Plus. This functionality is similar to that of a precompiler or Oracle Call Interface (OCI) application that issues a SET ROLE statement to enable specific roles upon application startup.
Disable the use of the SET ROLE statement for SQL*Plus users, thereby restricting such users to only the privileges associated with the roles enabled when SQL*Plus starts.
Enable other ad hoc query and reporting tools to restrict the roles and commands that each user can use while running that product.
See Also:
- "Ways to Use Application Context with Fine-Grained Access Control"
- "Creating Secure Application Roles"
- The appropriate tool manual, such as the SQL*Plus User's Guide and Reference, which contains information about how to create and how to use the PRODUCT_USER_PROFILE table

3.2.2 Tip 2: Encapsulate Privileges in Stored Procedures

Restrict ad hoc query tools from exercising application privileges, by encapsulating these privileges into stored procedures. Grant users execute privileges on these procedures rather than issuing direct privilege grants to the users, so that the privileges cannot be used outside the appropriate procedure.

Users can then exercise privileges only in the context of well-formed business applications. For example, consider authorizing users to update a table only by executing a stored procedure, rather than by updating the table directly. By doing this, you avoid the problem of the user having the SELECT privilege and using it outside the application.

3.2.3 Tip 3: Use Role Passwords Unknown to the User

Grant privileges through roles that require a password unknown to the user. For privileges that the user should exercise only within an application, enable the role by a password known only by the creator of the role. Use the application to issue a SET ROLE statement. Since the user does not have the role password, it must either be embedded in the application or retrievable from a database table by a stored procedure.

Hiding the password discourages users from trying to use the privileges without using the application. This improves security, but it is not foolproof.

Security by obscurity is not a good security practice. It protects against lazy users who merely want to bypass the application, even though they could, with access to the application code, potentially find the password. It does not protect against users who want to deliberately misuse privileges without using the application code (malicious users). Because malicious users can decompile client code and recover embedded passwords, you should only use the embedded password method to protect against lazy users.

Retrieving the role password from a database table is a bit more secure. It requires that the user uncover what stored procedure to use, gain EXECUTE permission on that procedure, execute it, and retrieve the password. Only then could the user use the role outside of the application.

3.2.4 Tip 4: Use Proxy Authentication and a Secure Application Role

To enable a role in three-tier systems, the user must access the database through a middle-tier application that requires proxy authentication and a secure application role.

Proxy authentication distinguishes between a middle tier creating a session on behalf of a user and the user connecting directly. Both the proxy user (the middle tier) and the real user information are captured in the user session.

A secure application role is implemented by a package, performing desired validation before allowing a user to assume the privileges in the role. When an application uses proxy authentication, the secure application role package validates that the user session was created by proxy. If the user is connecting to the database through an application, the role can be set, but if the user is connecting directly, it cannot.

Consider a situation in which you want to restrict use of an HR administration role to users accessing the database (by proxy) through the middle tier HRSERVER. You could create the following secure access role:

CREATE ROLE admin_role IDENTIFIED USING hr.admin;

Here, the hr.admin package performs the desired validation, permitting the role to be set only if it determines that the user is connected by proxy. The hr.admin package can use SYS_CONTEXT ('userenv', 'proxy_userid'), or SYS_CONTEXT (userenv', 'proxy_user'). Both return the ID and name of the proxy user (HRSERVER, in this case). If the user attempts to connect directly to the database, the hr.admin package will not allow the role to be set.

3.2.5 Tip 5: Use Secure Application Roles to Verify IP Address

The secure application role package can use additional information in the user session to restrict access, such as the original IP address of the user. You should never use IP addresses to make primary access control decisions, because IP addresses can be spoofed. You can, however, use an IP address to increase access restrictions after using other criteria for the primary access control decision.

For example, you may want to ensure that a user session was created by proxy for a middle-tier user connecting from a particular IP address. Of course, the middle tier must authenticate itself to the database before creating a lightweight session, and the database ensures that the middle tier has privilege to create a session on behalf of the user.

Your secure application role package could validate the IP address of the incoming connection. Before allowing SET ROLE to succeed, you can to ensure that the HRSERVER connection (or the lightweight user session) is coming from the appropriate IP address by using SYS_CONTEXT (userenv',' 'ip_address') . Doing so provides an additional layer of security.

3.2.6 Tip 6: Use Application Context and Fine-Grained Access Control

In this scenario, you combine server-enforced fine-grained access control and, through application context, session-based attributes.