Use technical means to limit the DBA's dangerous operations-oracle Database Vault

Overview

It is well known that during peak business hours, certain operations against Oracle databases are risky, such as modifying the table structure, modifying instance parameters, and so on, which, if not adequately evaluated and understood by the effects of these operations, are likely to cause failures, which can result in application errors, which in effect cause the database service to be unavailable.

Also, at non-business peaks, some seemingly risky operations can have serious consequences, such as not modifying the table structure by the administrative process, if the table is exactly part of the Oracle Goldengate replication group, modifying the source-side structure without notifying the person involved in Ogg, Without the same operation on the target side, and without the DDL Replication feature Open, the replication process will fail, resulting in inconsistent data and, in some scenarios, a serious production accident.

At present, the traditional approach is to emphasize management, whether customers or service providers are constantly emphasizing the system and norms, the hope from the system construction and the professional quality of engineers to start, to prevent the DBA of this random dangerous operation.

But, after all, the management system is "soft", the hope rests on the engineer consciously abide by the system and "self-cultivation" on, and can not guarantee foolproof.

The security components provided by Oracle can be used to limit, block, and prevent such random and dangerous operations and to ensure that the management system is adhered to in a technical way.

About Oracle Database Vault

We're talking about one of the security components of the Oracle database: Oracle DB Vault (DV), which has the primary function of protecting sensitive data and separation of responsibilities.

DV protection of sensitive data mainly through the realm (security domain), realm can be easily understood as a collection of sensitive data, DV through the realm configuration to specify whether users can access the realm protected data, if in the DV does not give access, Even SYSDBA does not have access to the realm-protected data, which is the core feature of DV, but not the focus of this article.

DV also has a very important function, command Rules, can be determined by certain conditions, allow or prevent database users to execute DDL, DML and DCL commands, and for privileged users, including sysdba are valid. This feature is exactly what we need to limit DBAs.

If you want to learn more about the capabilities of DV, you can visit the Oracle website: http://www.oracle.com/technetwork/database/options/database-vault/index-085211.html

The minimum supported database version for Oracle DB Vault is 9.2.0.8, which is a separate installation package in the early stages. Starting with 11g, Oracle's database installation media contains this component, and users who want to use this component need to check the database Vault option at the time of installation. In addition to installing the relevant software components, you also need to create the relevant database objects when you create the database.

Database Vault can be configured, managed, or managed via a Web management interface using related stored procedures, and in the early days, you must install EM to use the Web management interface, from 11GR2 onwards. The Dbcontrol of the database itself can also be managed by the Web interface.

In addition to the realm and command rules mentioned earlier, there are two concepts to be introduced, one is factor (authentication factor) and the other is rule sets (ruleset).

Factor (authentication factor) is a factor that can be used to make conditional judgments, such as the client hostname, client IP, etc., Oracle built-in some common Factor, the user can also create their own factor,factor can be an expression, It can also be a return value for a stored procedure.

Rule sets is simply a set of judging conditions, similar to where the SQL is judged, when the rule set's judging condition returns True, DV allows the user to access the data or execute a specific command. The rule in rule sets can be judged by reference to factor.

Example 1: Allow only the drop command to be executed during non-business hours

This example is the simplest and does not require the use of factor, only the rule sets and command rules can be used. We use database user test to demonstrate:

To login to the DV Administration page:

Create a rule set with the name "Can not drop table under Business Time" and select any true to mean that any one of the rules in the rule set (judging condition) is true and the rule set evaluates to True. In fact, all true is equivalent to and,any true equivalent to or

The two rule is also well understood, is to determine whether the current time is business time, here, in order to facilitate the experiment, the business time is defined as 11:45~11:55, this rule set to determine the current time, if the current time is not within the business time, the ruleset returns True.

Then create a command Rule, such as:

This command rule means that when the specified rule Set returns true, the table under the drop test user is allowed, or even the owner of the SYSDBA or table does not have permission to drop table.

Effect:

Other command rule settings like the ALTER TABLE we want to control are similar.

Example 2: Only allow users to log in to a database using a specific tool (APP)

In practice, we often encounter situations where application developers have passwords for application users, and they can connect to production libraries with tools such as sql*plus or PL/SQL developer, which can happen if a mix of production libraries and test libraries is made. The best solution is to restrict the tools used by the application user, should only allow the middleware to connect with this user, and no other tools allow the connection.

This example will use the factor, first we create a factor, take the user session module:

Log in to the database with Sql*plus and verify the value that this factor takes out:

The way to refer to Factor is dvf.f$+factor name, in Linux native login, module is shown above, Telnet on Windows, the value of module is "SQLPLUS. EXE ".

The rule Set is created below, named "Limit Sql*plus",

Note is "any True"

Create rule:

To create a command Rule:

Under this rule, users other than Sys,system,dv_manager, whether local or remote, cannot log on with Sql*plus.

Log in as normal with SQL Developer:

Example 3: Using the Dual key security feature

In a realistic scenario, we want the DBA to obey the system, such as notifying the Ogg stakeholders before modifying the table structure. Or, in order to increase security, the DBA must be approved by the boss for significant operations. DV can use the dual key function to meet this requirement.

Simply put, we can write a stored procedure that determines whether the person who needs to be notified in the process is online and is allowed to perform the appropriate action if it is online. And the person who needs to be notified, as long as the access to connect database permissions on the line, his (her) login action becomes an authorization or notification after confirmation.

Specific steps:

First, the DV administrator authorizes the user to access the dictionary view and write the stored procedure:

Sql> GRANT CREATE PROCEDURE to Dv_manager;

Grant succeeded.

Sql> GRANT SELECT on v_$session to Dv_manager;

Grant succeeded.

We assume that the authorized user is "Boss", while the user performing the operation is "TEST", the corresponding decision to determine whether the boss online stored procedures are as follows:

CREATE OR REPLACE FUNCTION check_boss_logged_in

return VARCHAR2

Authid Definer as

V_session_number number: = 0;

V_allow Varchar2 (Ten): = ' TRUE ';

V_deny Varchar2 (Ten): = ' FALSE ';

BEGIN

SELECT COUNT (*) into V_session_number

From SYS. V_$session

WHERE USERNAME = ' BOSS ';

IF v_session_number > 0

Then RETURN V_allow;

ELSE

RETURN V_deny;

END IF;

END check_boss_logged_in;

/

Use the DV administrator to create this function, and then authorize the Dvsys:

Sql>grant EXECUTE on check_boss_logged_in to Dvsys;

Create Rule Set:

Name:dual Key

Evaluation Options:any True

The rules are as follows:

To create a command Rule:

The effect of this command rule is that if the test user wants to alter the table owner as test, the boss user must be online at the same time, otherwise error, no permissions. If someone else modifies the table under test user, this limit is not affected.

The final effect:

Boss User is not online, then test user ALTER TABLE error

Only when the test user notifies the boss user, or according to the process, got the boss user approval, the Boss user uses the login database this action to represent the confirmation, the test user can modify the table structure:

Use technical means to limit the DBA's dangerous operations-oracle Database Vault