3
Security Policies and Tips

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

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

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

Introduction to Database Security Policies

This section briefly introduces security policies. It covers:

• Security Threats and Countermeasures
• What Information Security Policies Can Cover

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
• Physical, such as securing computers in restricted-access facilities
• Technical, such as implementing strong authentication requirements for critical business systems
• Personnel-related, such as performing background checks or "vetting" key personnel

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

For example, one possible security threat is 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. Chapter 7, "Security Policies" and Chapter 13, "Using Virtual Private Database to Implement Application Security Policies".

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 following concerns:

Table 3-1  Issues and Actions for Policies to Address

Security Concern/Practice	Recommended Actions	References
Establish & 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 & 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 (username, employee number, and so on) to be retrieved later in the session, enabling fine-grained access control. Create security policy functions & 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 & user labels, quickly limiting access to sensitive data, often without additional programming	See Ref Foot 4
Establish & manage the use of encryption	Use Secure Socket Layer (SSL) connections, well-established encryption suites, or PKI certificates for critical/sensitive transmissions/applications.	See Ref Foot 5
Establish & 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

1 <<<role independence and Application Security>>> "Creating Secure Application Roles" 2 <<<privileges & attributes (system/object/user)>>> Introduction to Privileges, Understanding User Privileges and Roles, & Granting User Privileges and Roles 3 <<<Create, manage, and control roles (database, enterprise)>>> "Introduction to Roles" & Managing User Roles 4 <<<Establish the granularity of access control desired)>>>Chapter 7, "Security Policies", Chapter 13, "Using Virtual Private Database to Implement Application Security Policies" & Chapter 14, "Implementing Application Context and Fine-Grained Access Control" 5 <<<Establish & manage the use of encryption>>>Chapter 4, "Authentication Methods" 6 Insert Reference for <<<Establish & maintain security in 3-tier applications>>>Chapter 14, "Implementing Application Context and Fine-Grained Access Control" & Chapter 15, "Preserving User Identity in Multitiered Environments" 7 <<<Control query access, data misuse, and intrusions>>>Chapter 14, "Implementing Application Context and Fine-Grained Access Control" & Chapter 15, "Preserving User Identity in Multitiered Environments"; for auditing, Chapter 8, "Database Auditing: Security Considerations"and Chapter 11, "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, in alphabetical order. Discussions of these terms and products appear in the corresponding chapters (or book) listed in that table:

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

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

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 Role to Verify IP Address
• Tip 6: Use Application Context and Fine-Grained Access Control

Tip 1: Enable and Disable Roles Promptly

Enable the proper 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 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, 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 nonetheless 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:

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

Tip 2: Encapsulate Privileges in Stored Procedures

Restrict users' ad hoc query tools from exercising application privileges, by encapsulating those privileges into stored procedures. Grant users execute privileges on those 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.

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, which does improve 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 deliberately to misuse privileges without using the application code (malicious users). Since 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.

Tip 4: Use Proxy Authentication and a Secure Application Role

In three-tier systems, enabling a role is possible only when the user accesses the database through a middle-tier application. This requires the use of 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.

Tip 5: Use Secure Application Role to Verify IP Address

The secure application role package can use additional information in the user session to restrict access, such as the user's original IP address.

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.

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.