Oracle® Database Security Guide 10g Release 2 (10.2) Part Number B14266-01 |
|
|
View PDF |
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:
This section briefly introduces security policies. It covers:
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".
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 |
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" |
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 4: Use Proxy Authentication and a Secure Application Role
Tip 6: Use Application Context and Fine-Grained Access Control
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"
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
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.
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.
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.
See Also:
Chapter 14, "Using Virtual Private Database to Implement Application Security Policies"
Chapter 15, "Implementing Application Context and Fine-Grained Access Control"
Oracle Database SQL Reference for information about SYS_CONTEXT ('userenv', 'proxy_user')
and SYS_CONTEXT ('userenv', proxy_userid')
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.