16 Oracle Database Vault Command Rule APIs

The DBMS_MACADM PL/SQL package provides procedures for configuring command rules. .

Only users who have been granted the DV_OWNER or DV_ADMIN role can use these procedures.

CREATE_COMMAND_RULE Procedure

The CREATE_COMMAND_RULE procedure creates a command rule and associates it with a rule set.

Optionally, you can use it to enable the command rule for rule checking with a rule set. In a multitenant environment, you can create both common and local command rules.

Syntax

DBMS_MACADM.CREATE_COMMAND_RULE(
 command         IN VARCHAR2, 
 rule_set_name   IN VARCHAR2, 
 object_owner    IN VARCHAR2, 
 object_name     IN VARCHAR2, 
 enabled         IN VARCHAR2,
 privilege_scope IN NUMBER,
 clause_name     IN VARCHAR2,
 parameter_name  IN VARCHAR2,
 event_name      IN VARCHAR2,
 component_name  IN VARCHAR2,
 action_name     IN VARCHAR2,
 scope           IN NUMBER DEFAULT);

Parameters

Table 16-1 CREATE_COMMAND_RULE Parameters

Parameter Description

command

SQL statement to protect.

See also the following:

rule_set_name

Name of rule set to associate with this command rule.

To find existing rule sets in the current database instance, query the DBA_DV_RULE_SET view, described in DBA_DV_RULE_SET View.

object_owner

Database schema to which this command rule will apply. The wildcard % is allowed, except for the SELECT, INSERT, UPDATE, DELETE, and EXECUTE statements.

To find the available users, query the DBA_USERS view, described in Oracle Database Reference.

See also "Object Owner" in Creating a Command Rule for more information.

object_name

Object to be protected by the command rule. (The wildcard % is allowed. See "Object Name" in Creating a Command Rule for more information about objects protected by command rules.)

To find the available objects, query the ALL_OBJECTS view, described in Oracle Database Reference.

enabled

Specify one of the following options to set the status of the command rule:

  • DBMS_MACUTL.G_YES or ‘y’ (Yes) to enable the command rule (default)

  • DBMS_MACUTL.G_NO or ‘n’ to disable the command rule, including the capture of violations in the simulation log

  • DBMS_MACUTL.G_SIMULATION or ‘s’ to enable SQL statements to execute but capture violations in the simulation log

privilege_scope

Obsolete parameter

clause_name

A clause from the SQL statement that was used to create the command rule. For example, a command rule for the ALTER SESSION SQL statement could have the SET clause as the clause_name parameter.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

parameter_name

A parameter from the clause_name parameter. For example, for an ALTER SESSION command rule, you could set parameter_name to EVENTS if the clause_name is SET.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

event_name

An event that the command rule defines. For example, suppose an ALTER SESSION command rule uses SET for the clause_name and EVENTS as the parameter_name. The event_name could be set to TRACE if you want to track trace events.

Applies only to ALTER SYSTEM and ALTER SESSION command rules that have the parameter parameter set to EVENTS.

component_name

A component of the event_name setting. For example, for a TRACE event, the component_name could be GCS.

Applies only to ALTER SYSTEM and ALTER SESSION command rules that have the parameter parameter set to EVENTS.

action_name

An action of the component_name setting.

Applies only to ALTER SYSTEM and ALTER SESSION command rules that have the parameter parameter set to EVENTS.

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

If you create the common command rule in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

ALTER SYSTEM Command Rule Settings

Table 16-2 describes the ALTER SYSTEM command rule settings.

Table 16-2 ALTER SYSTEM Command Rule Settings

clause_name parameter_name — Parameter Value

ARCHIVE LOG

  • ALLsequence_number

  • CHANGEchange_number

  • CURRENT — N/A

  • GROUPgroup_number

  • LOGFILElog_file_name

  • NEXT — N/A

  • SEQUENCE — N/A

CHECK DATAFILES

N/A — global or local

CHECKPOINT

N/A — global or local

COPY LOGFILE

N/A — N/A

DISTRIBUTED RECOVERY

N/A — enable or disable

DUMP

  • DATAFILE — N/A

  • FLASHBACK — N/A

  • LOGFILE — N/A

  • REDO — N/A

  • TEMPFILE — N/A

  • UNDO — N/A

END SESSION

DISCONNECT SESSION — N/A

KILL SESSION — N/A

FLUSH

BUFFER_CACHE — N/A

GLOBAL CONTEXT — N/A

REDOtarget_db_name

SHARED_POOL — N/A

QUIESCE

QUIESCE RESTRICTED — N/A

UNQUIESCE — N/A

REFRESH

LDAP_REGISTRATION — N/A

REGISTER

N/A — N/A

RESET

initialization_parameter_name — N/A

RESUME

N/A — N/A

SECURITY

RESTRICTED SESSIONenable or disable

SET ENCRYPTION KEY — N/A

SET ENCRYPTION WALLETopen or close

SET

EVENTSevent_string

GLOBAL_TOPIC_ENABLEDtrue or false

initialization_parameter_nameparameter_value

LDAP_REGISTRATION_ENABLEDtrue or false

LDAP_REG-SYNC_INTERVAL — Number

SINGLETASK DEBUG — N/A

USE_STORED_OUTLINEStrue , false, or category_name

SHUTDOWN DISPPATCHER

N/A — dispatcher_name

SWITCH LOGFILE

N/A — all or none

SUSPEND

N/A — N/A

TX RECOVERY

N/A — enable or disable

ALTER SESSION Command Rule Settings

Table 16-3 describes the ALTER SESSION command rule settings.

Table 16-3 ALTER SESSION Command Rule Settings

clause_name parameter_name — Parameter Value

ADVISE

N/A — COMMIT, ROLLBACK, or NOTHING

CLOSE DATABASE LINK

N/A — database_link

COMMIT IN PROCEDURE

N/A — ENABLE or DISABLE

GUARD

N/A — ENABLE or DISABLE

ILM

ROW ACCESS TRACKING — N/A

ROW MODIFICATION TRACKING — N/A

LOGICAL REPLICATION

N/A — N/A

PARALLEL DML

N/A — ENABLE, DISABLE, or FORCE

PARALLEL DDL

N/A — ENABLE, DISABLE, or FORCE

PARALLEL QUERY

N/A — ENABLE, DISABLE, or FORCE

RESUMABLE

N/A — ENABLE or DISABLE

SYNC WITH PRIMARY

N/A — N/A

SET

APPLICATION ACTIONaction_name

APPLICATION MODULEmodule_name

CONSTRAINTSIMMEDIATE, DEFERRED, or DEFAULT

CONTAINERcontainer_name

CURRENT SCHEMAschema_name

EDITIONedition_name

ERROR ON OVERLAP TIMETRUE or FALSE

EVENTSevent_string

FLAGGEROFF, FULL, INTERMEDIATE, ENTRY

initialization_parameter_nameparameter_name

INSTANCEinstance_number

ISOLATION_LEVELSERIALIZABLE or READ COMMITTED

ROW_ARCHIVAL_VISABILITYACTIVE or ALL

SQL_TRANSFORMATION_PROFILEprofile_name

STANDBY_MAX_DATA_DELAYNONEnumber

TIME_ZONELOCAL, DBTIMEZONE, or other_value

USE_PRIVATE_OUTLINESTRUE, FALSE, or category_name

USE_STORED_OUTLINESTRUE, FALSE, or category_name

Examples

Simple Command Rules

The following example shows how to create a simple command rule for the SELECT statement on the OE.ORDERS table. This command rule uses no command rules.

BEGIN
 DBMS_MACADM.CREATE_COMMAND_RULE(
  command         => 'SELECT', 
  rule_set_name   => 'Check User Role',
  object_owner    => 'OE', 
  object_name     => 'ORDERS', 
  enabled         => DBMS_MACUTL.G_YES);
END; 
/

This example shows how to create a command rule that checks if users can enable or disable the hr_audit_pol unified audit policy. Note that if the object is a unified audit policy, then you must have AUDIT POLICY, not just AUDIT, for the command parameter.

BEGIN
DBMS_MACADM.CREATE_COMMAND_RULE(
  command          => 'AUDIT POLICY',
  rule_set_name    => 'Check ability to audit',
  object_owner     => '%',
  object_name      => 'hr_audit_pol',
  enabled          => DBMS_MACUTL.G_YES,
  scope            => DBMS_MACUTL.G.SCOPE_LOCAL);
END;
/

ALTER SESSION Command Rule Using the SET Clause

The following example shows how to create an ALTER SESSION command rule that uses the SET clause with the ERROR_ON_OVERLAP_TIME parameter.

BEGIN
 DBMS_MACADM.CREATE_COMMAND_RULE(
  command         => 'ALTER SESSION', 
  rule_set_name   => 'Test ERROR_ON_OVERLAP_TIME for FALSE', 
  object_owner    => '%', 
  object_name     => '%', 
  enabled         => DBMS_MACUTL.G_YES,
 clause_name     => 'SET',
 parameter_name  => 'ERROR_ON_OVERLAP_TIME',
 scope           => DBMS_MACUTL.G_SCOPE_COMMON);
END; 
/

In this example:

  • rule_set_name: The ALTER SESSION SQL statement ERROR_ON_OVERLAP_TIME session parameter must be set to either TRUE or FALSE. You can create a rule set that checks if this setting. For example, for the rule:

    EXEC DBMS_MACADM.CREATE_RULE('RULE_TRUE', 'UPPER(PARAMETER_VALUE) = ''TRUE''');

    The rule set that is used with this rule can be similar to the following:

    BEGIN
     DBMS_MACADM.CREATE_RULE_SET(
      rule_set_name    => 'Test ERROR_ON_OVERLAP_TIME',
      description      => 'Checks if the ERROR_ON_OVERLAP_TIME setting is TRUE or FALSE',
      enabled          => DBMS_MACUTL.G_YES,
      eval_options     => DBMS_MACUTL.G_RULESET_EVAL_ALL,
      audit_options    => DBMS_MACUTL.G_RULESET_AUDIT_FAIL + DBMS_MACUTL.G_RULESET_AUDIT_SUCCESS,
      fail_options     => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
      fail_message     => 'false error on overlaptime',
      fail_code        => 20461,
      handler_options  => DBMS_MACUTL.G_RULESET_HANDLER_FAIL,
      handler          => '',
      is_static        => false);
    END;
    /
    EXEC DBMS_MACADM.ADD_RULE_TO_RULE_SET('Test ERROR_ON_OVERLAP_TIME', 'RULE_TRUE');
  • object_owner and object_name must be set to % for ALTER SESSION and ALTER SYSTEM command rules.

  • enabled uses the DBMS_MACUTL.G_YES constant to enable the command rule when it is created.

  • clause_name sets the ALTER SESSION command rule to use the SET clause of the ALTER SESSION PL/SQL statement.

  • parameter_name is set to the ERROR_ON_OVERLAP_TIME parameter of the SET clause.

  • scope uses the DBMS_MACUTL.G_SCOPE_COMMON constant to set the command rule to be a common command rule. This command rule will be in the application root of a multitenant environment, so the user running this procedure must be in the CDB root. Any rules or rule sets that are associated with this command rule must be common.

    If you were creating the command rule locally, you would set scope to DBMS_MACUTL.G_SCOPE_LOCAL. In that case, the user who runs this procedure must be in the PDB in which the command rule will reside. To find the existing PDBs, you can query the DBA_PDBS data dictionary view. Any rules or rule sets that are associated with this command rule must be local.

ALTER SYSTEM Command Rule Using the CHECKPOINT Clause

This example shows how to create an ALTER SYSTEM command rule that users the CHECKPOINT clause. To have the command rule test for the CHECKPOINT setting, you must create a rule set and rule, similar to the ALTER SESSION command rule in the previous example. In this example, the parameter setting is not specified because the CHECKPOINT setting does not have parameters.

BEGIN
 DBMS_MACADM.CREATE_COMMAND_RULE(
  command         => 'ALTER SYSTEM', 
  rule_set_name   => 'Test CHECKPOINT Setting', 
  object_owner    => '%', 
  object_name     => '%', 
  enabled         => DBMS_MACUTL.G_YES,
 clause_name     => 'CHECKPOINT',
 parameter_name  => '',
 scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

ALTER SESSION Command Rule Using the SET Clause

The following ALTER SESSION command rule uses the SET clause to specify an event_name and component_name. You can only use the event_name, component_name, and action_name parameters if the clause_name parameter specifies SET.

BEGIN
 DBMS_MACADM.CREATE_COMMAND_RULE(
  command         => 'ALTER SESSION', 
  rule_set_name   => 'Check Trace Events', 
  object_owner    => '%', 
  object_name     => '%', 
  enabled         => DBMS_MACUTL.G_YES,
  clause_name     => 'SET',
  parameter_name  => 'EVENTS',
  event_name      => 'TRACE',
  component_name  => 'GCS',
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

See also ALTER SESSION and ALTER SYSTEM Command Rules for conceptual information about this topic.

CREATE_CONNECT_COMMAND_RULE Procedure

The CREATE_CONNECT_COMMAND_RULE procedure creates a CONNECT command rule that you can associate with a user and a rule set.

In a multitenant environment, you can create both common and local command rules.

Syntax

DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(
 user_name       IN VARCHAR2, 
 rule_set_name   IN VARCHAR2, 
 enabled         IN VARCHAR2, 
 scope           IN NUMBER DEFAULT);

Parameters

Table 16-4 CREATE_CONNECT_COMMAND_RULE Parameters

Parameter Description

user_name

User to whom the CONNECT command rule will apply. If you enter the % wildcard, then the CONNECT command rule will be applied to every database user.

In a multitenant environment, if you execute this procedure in the root, then specifying % applies to all common users. If you run the procedure in a PDB, then it applies to all local and common users who have access to this PDB. If there are two command rules, one common and one local, and they both apply to the same object, then both must evaluate successfully for the operation to succeed.

In a multitenant environment, ensure that this user is common if the CONNECT command rule is common, and local or common if the CONNECT command rule is local.

To find existing database users in the current instance, query the DBA_USERS view, described in Oracle Database Reference.

rule_set_name

Name of rule set to associate with this command rule. In a multitenant environment, ensure that this rule set is common if the CONNECT command rule is common, and local if the CONNECT command rule is local.

To find existing rule sets in the current database instance, query the DBA_DV_RULE_SET view, described in DBA_DV_RULE_SET View.

enabled

Specify one of the following options to set the status of the command rule:

  • DBMS_MACUTL.G_YES or ‘y’ (Yes) to enable the command rule (default)

  • DBMS_MACUTL.G_NO or ‘n’ to disable the command rule, including the capture of violations in the simulation log

  • DBMS_MACUTL.G_SIMULATION or ‘s’ to enable SQL statements to execute but capture violations in the simulation log

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

If you create the common CONNECT command rule in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

Examples

The following example shows how to create a common CONNECT command rule in a multitenant environment. This command rule will be in the CDB root, so the user who runs this procedure must be in the CDB root. Any user names or rule sets that are associated with this command rule must be common.

BEGIN
 DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(
  rule_set_name   => 'Allow Sessions', 
  user_name       => 'C##HR_ADMIN', 
  enabled         => DBMS_MACUTL.G_SIMULATION,
  scope           => DBMS_MACUTL.G_SCOPE_COMMON);
END; 
/

This example is a local version of the preceding example. The user who runs this procedure must be in the PDB in which the local CONNECT command rule will reside. To find the available PDBs, run the show pdbs command. Any rule sets that are associated with this command rule must be local. The user can be either common or local.

BEGIN
 DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(
 rule_set_name   => 'Allow Sessions', 
 user_name       => 'PSMITH', 
 enabled         => DBMS_MACUTL.G_SIMULATION,
 scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

CREATE_SESSION_EVENT_CMD_RULE Procedure

The CREATE_SESSION_EVENT_CMD_RULE procedure creates a command rule that you can associate with session events, based on the ALTER SESSION statement.

In a multitenant environment, you can create both session event common and local command rules.

Syntax

DBMS_MACADM.CREATE_SESSION_EVENT_CMD_RULE(
 rule_set_name   IN VARCHAR2, 
 enabled         IN VARCHAR2,
 event_name      IN VARCHAR2 DEFAULT,
 component_name  IN VARCHAR2 DEFAULT,
 action_name     IN VARCHAR2 DEFAULT, 
 scope           IN NUMBER DEFAULT,
 pl_sql_stack    IN BOOLEAN DEFAULT);

Parameters

Table 16-5 CREATE_SESSION_EVENT_CMD_RULE Parameters

Parameter Description

rule_set_name

Name of the rule set to associate with the command rule. In a multitenant environment, ensure that this rule set is common if the session event command rule is common, and local if the command rule is local.

To find existing rule sets in the current database instance, query the DBA_DV_RULE_SET view, described in DBA_DV_RULE_SET View.

enabled

Specify one of the following options to set the status of the command rule:

  • DBMS_MACUTL.G_YES or ‘y’ (Yes) to enable the command rule (default)

  • DBMS_MACUTL.G_NO or ‘n’ to disable the command rule, including the capture of violations in the simulation log

  • DBMS_MACUTL.G_SIMULATION or ‘s’ to enable SQL statements to execute but capture violations in the simulation log

event_name

An event that the command rule defines. This setting enables the command rule to correspond with an ALTER SESSION SET EVENTS event_name statement. For example, to track trace events, you would set event_name to TRACE.

component_name

A component of the event_name setting. Example settings are DV, OLS, or GCS.

You can find valid component names by issuing ORADEBUG DOC COMPONENT RDBMS as user SYS. The output displays parent and child components, which you can use for the component_name setting. For example, both XS (parent) and XSSESSION (child of XS) are valid component names. If you select the parent component, then the command rule applies to it and the child components.

action_name

An action of the component_name setting

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

If you create the common command rule in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

pl_sql_stack

When simulation mode is enabled, specifies whether to record the PL/SQL stack for failed operations. Enter TRUE to record the PL/SQL stack, FALSE to not record. The default is FALSE.

Examples

The following example shows how to create a common session event command rule in a multitenant environment. This command rule will be in the application root, so the user running this procedure must be in the CDB root. Any user names or rule sets that are associated with this command rule must be common.

BEGIN
 DBMS_MACADM.CREATE_SESSION_EVENT_CMD_RULE(
  rule_set_name   => 'Allow Sessions', 
  event_name      => 'TRACE',
  component_name  => 'DV',
  action_name     => 'CURSORTRACE',
  enabled         => DBMS_MACUTL.G_SIMULATION,
  scope           => DBMS_MACUTL.G_SCOPE_COMMON);
END; 
/

This example shows how to create a session event for the 47998 trace event. This example will records the PL/SQL stack for failed operations.

BEGIN  
 DBMS_MACADM.CREATE_SESSION_EVENT_CMD_RULE(
  rule_set_name   => 'Allow Sessions',
  event_name      => '47998',
  enabled         => 'y',
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL,
  pl_sql_stack    => TRUE); 
END; 
/

CREATE_SYSTEM_EVENT_CMD_RULE Procedure

The CREATE_SYSTEM_EVENT_CMD_RULE procedure creates a command rule that you can associate with system events, based on the ALTER SYSTEM statement.

In a multitenant environment, you can create both ALTER SYSTEM common and local command rules.

Syntax

DBMS_MACADM.CREATE_SYSTEM_EVENT_CMD_RULE(
 rule_set_name   IN VARCHAR2, 
 enabled         IN VARCHAR2,
 event_name      IN VARCHAR2 DEFAULT,
 component_name  IN VARCHAR2 DEFAULT,
 action_name     IN VARCHAR2 DEFAULT, 
 scope           IN NUMBER DEFAULT
 pl_sql_stack    IN BOOLEAN DEFAULT);

Parameters

Table 16-6 CREATE_SYSTEM_EVENT_CMD_RULE Parameters

Parameter Description

rule_set_name

Name of the rule set to associate with the command rule. In a multitenant environment, ensure that this rule set is common if the system event command rule is common, and local if the command rule is local.

To find existing rule sets in the current database instance, query the DBA_DV_RULE_SET view, described in DBA_DV_RULE_SET View.

event_name

An event that the command rule defines. This setting enables the command rule to correspond to an ALTER SYSTEM SET EVENTS event_name statement. For example, to track trace events, you would set event_name to TRACE.

component_name

A component of the event_name setting. Example settings are DV, OLS, or GCS.

You can find valid component names by issuing ORADEBUG DOC COMPONENT RDBMS as user SYS. The output displays parent and child components, which you can use for the component_name setting. For example, both XS (parent) and XSSESSION (child of XS) are valid component names. If you select the parent component, then the command rule applies to it and the child components.

action_name

An action of the component_name setting

enabled

Specify one of the following options to set the status of the command rule:

  • DBMS_MACUTL.G_YES or ‘y’ to enable the command rule (default)

  • DBMS_MACUTL.G_NO or ‘n’ to disable the command rule, including the capture of violations in the simulation log

  • DBMS_MACUTL.G_SIMULATION or ‘s’ to enable SQL statements to execute but capture violations in the simulation log

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

If you create the common command rule in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

pl_sql_stack

When simulation mode is enabled, specifies whether to record the PL/SQL stack for failed operations. Enter TRUE to record the PL/SQL stack, FALSE to not record. The default is FALSE.

Example

The following example shows how to create a common system event command rule in a multitenant environment. This command rule will be in the application root, so the user running this procedure must be in the CDB root. Any user names or rule sets that are associated with this command rule must be common.

BEGIN
 DBMS_MACADM.CREATE_SYSTEM_EVENT_CMD_RULE(
  rule_set_name   => 'Enabled', 
  event_name      => 'TRACE',
  component_name  => 'GSIPC',
  action_name     => 'HEAPDUMP',
  enabled         => DBMS_MACUTL.G_YES,
  scope           => DBMS_MACUTL.G_SCOPE_COMMON);
END; 
/

DELETE_COMMAND_RULE Procedure

The DELETE_COMMAND_RULE procedure drops a command rule declaration.

Syntax

DBMS_MACADM.DELETE_COMMAND_RULE(
 command         IN VARCHAR2, 
 object_owner    IN VARCHAR2, 
 object_name     IN VARCHAR2,
 clause_name     IN VARCHAR2,
 parameter_name  IN VARCHAR2 DEFAULT,
 event_name      IN VARCHAR2 DEFAULT,
 component_name  IN VARCHAR2 DEFAULT,
 action_name     IN VARCHAR2 DEFAULT,
 scope           IN NUMBER DEFAULT); 

Parameters

Table 16-7 DELETE_COMMAND_RULE Parameters

Parameter Description

command

SQL statement the command rule protects.

To find available command rules, query the DBA_DV_COMMAND_RULE view, described in DBA_DV_COMMAND_RULE View

object_owner

Database schema to which this command rule applies.

To find the available users in the current database instance, query the DBA_USERS view, described in Oracle Database Reference.

object_name

Object name. The wildcard % is allowed.

To find the available objects in the current database instance, query the ALL_OBJECTS view, described in Oracle Database Reference.

clause_name

A clause from the SQL statement that was used to create the command rule.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

parameter_name

A parameter from the clause_name parameter.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

event_name

An event that the command rule defines.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

component_name

A component of the event_name setting.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

action_name

An action of the component_name setting.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

Examples

When you drop a command rule, you must omit the rule_set_name and enabled parameters, and ensure that the rest of the parameters match the settings that were used the last time the command rule was updated. You can check the most recent settings by querying the DBA_DV_COMMAND_RULE data dictionary view.

For example, suppose you created the following command rule:

BEGIN
 DBMS_MACADM.CREATE_COMMAND_RULE(
  command         => 'SELECT', 
  rule_set_name   => 'Enabled',
  object_owner    => 'OE', 
  object_name     => 'ORDERS', 
  enabled         => DBMS_MACUTL.G_YES,
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

To drop this command rule, use the most of same parameters as shown here, but omit rule_set_name and enabled.

BEGIN
 DBMS_MACADM.DELETE_COMMAND_RULE(
  command         => 'SELECT', 
  object_owner    => 'OE', 
  object_name     => 'ORDERS', 
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

The following example shows how to delete an ALTER SESSION command rule.

BEGIN
 DBMS_MACADM.DELETE_COMMAND_RULE(
  command         => 'ALTER SESSION', 
  object_owner    => '%', 
  object_name     => '%',
  clause_name     => 'SET',
  parameter_name  => 'EVENTS',
  event_name      => 'TRACE',
  component_name  => 'GCS',
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/

DELETE_CONNECT_COMMAND_RULE Procedure

The DELETE_CONNECT_COMMAND_RULE procedure deletes a CONNECT command rule that had been created with the CREATE_CONNECT_COMMAND_RULE procedure.

Syntax

DBMS_MACADM.DELETE_CONNECT_COMMAND_RULE(
 user_name       IN VARCHAR2, 
 scope           IN NUMBER DEFAULT);

Parameters

Table 16-8 DELETE_CONNECT_COMMAND_RULE Parameters

Parameter Description

user_name

User to whom the CONNECT command rule applied.

To find this user, query the OBJECT_OWNER field of the DBA_DV_COMMAND_RULE view.

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

Example

BEGIN
 DBMS_MACADM.DELETE_CONNECT_COMMAND_RULE(
  user_name       => 'PSMITH',
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

DELETE_SESSION_EVENT_CMD_RULE Procedure

The DELETE_SESSION_EVENT_CMD_RULE procedure deletes a session command rule that was associated with events.

Syntax

DBMS_MACADM.DELETE_SESSION_EVENT_CMD_RULE(
 event_name      IN VARCHAR2 DEFAULT,
 component_name  IN VARCHAR2 DEFAULT,
 action_name     IN VARCHAR2 DEFAULT, 
 scope           IN NUMBER DEFAULT);

Parameters

Table 16-9 DELETE_SESSION_EVENT_CMD_RULE Parameters

Parameter Description

event_name

An event that the session event command rule defines.

DBA_DV_COMMAND_RULE View for a information about existing command rules

component_name

A component of the event_name setting

action_name

An action of the component_name setting

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

Example

The following example shows how to delete a common session event command rule in the application root a multitenant environment. The user running this procedure must be a common user in the CDB root. When you specify the parameters, ensure that they match exactly the parameters that were used the last time the command rule was updated. To find the current settings of the command rule, query the DBA_DV_COMMAND_RULE view, described in DBA_DV_COMMAND_RULE View

BEGIN 
DBMS_MACADM.DELETE_SESSION_EVENT_CMD_RULE(
 event_name      => '47999',
 scope           => DBMS_MACUTL.G_SCOPE_COMMON);
END;
 /

DELETE_SYSTEM_EVENT_CMD_RULE Procedure

The DELETE_SYSTEM_EVENT_CMD_RULE procedure deletes a system command rule that was associated with events.

Syntax

DBMS_MACADM.DELETE_SYSTEM_EVENT_CMD_RULE(
 event_name      IN VARCHAR2 DEFAULT,
 component_name  IN VARCHAR2 DEFAULT,
 action_name     IN VARCHAR2 DEFAULT, 
 scope           IN NUMBER DEFAULT);

Parameters

Table 16-10 DELETE_SYSTEM_EVENT_CMD_RULE Parameters

Parameter Description

event_name

An event that the system event command rule defines.

See DBA_DV_COMMAND_RULE View for a information about existing command rules.

component_name

A component of the event_name setting

action_name

An action of the component_name setting

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

Examples

The following example shows how to delete a common system event command rule in the application root of a multitenant environment. The user running this procedure must be a common user in the CDB root. When you specify the parameters, ensure that they match exactly the parameters that were used the last time the command rule was updated. To find the current settings of the command rule, query the DBA_DV_COMMAND_RULE view, described in DBA_DV_COMMAND_RULE View

BEGIN
 DBMS_MACADM.DELETE_SYSTEM_EVENT_CMD_RULE(
 event_name      => 'TRACE',
 component_name  => 'DV',
 action_name     => '',
 scope           => DBMS_MACUTL.G_SCOPE_COMMON);
END; 
/

UPDATE_COMMAND_RULE Procedure

The UPDATE_COMMAND_RULE procedure updates a command rule declaration.

In a multitenant environment, you can update both common and local command rules.

Syntax

DBMS_MACADM.UPDATE_COMMAND_RULE(
 command         IN VARCHAR2, 
 rule_set_name   IN VARCHAR2, 
 object_owner    IN VARCHAR2, 
 object_name     IN VARCHAR2, 
 enabled         IN VARCHAR2,
 privilege_scope IN NUMBER,
 clause_name     IN VARCHAR2,
 parameter_name  IN VARCHAR2 DEFAULT,
 event_name      IN VARCHAR2 DEFAULT,
 component_name  IN VARCHAR2 DEFAULT,
 action_name     IN VARCHAR2 DEFAULT,
 scope           IN NUMBER DEFAULT,
 pl_sql_stack    IN BOOLEAN DEFAULT);

Parameters

Table 16-11 UPDATE_COMMAND_RULE Parameters

Parameter Description

command

Command rule to update

See also the following:

rule_set_name

Name of rule set to associate with this command rule.

To find existing rule sets in the current database instance, query the DBA_DV_RULE_SET view, described in Oracle Database Vault Data Dictionary Views.

object_owner

Database schema to which this command rule applies.

To find the available users, query the DBA_USERS view, described in Oracle Database Reference. See also "Object Owner" in Creating a Command Rule for more information.

object_name

Object name. (The wildcard % is allowed. See "Object Name" in Creating a Command Rule for more information about objects protected by command rules.)

To find the available objects, query the ALL_OBJECTS view, described in Oracle Database Reference.

enabled

Specify one of the following options to set the status of the command rule:

  • DBMS_MACUTL.G_YES or ‘y’ to enable the command rule (default)

  • DBMS_MACUTL.G_NO or ‘n’ to disable the command rule, including the capture of violations in the simulation log

  • DBMS_MACUTL.G_SIMULATION or ‘s’ to enable SQL statements to execute but capture violations in the simulation log

privilege_scope

Obsolete parameter

clause_name

A clause from the SQL statement that was used to create the command rule. For example, a command rule for the ALTER SESSION SQL statement could have the SET clause as the clause_name parameter.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

parameter_name

A parameter from the clause_name parameter. For example, for an ALTER SESSION command rule, you could set parameter_name to EVENTS if the clause_name is SET.

Applies only to command rules for ALTER SYSTEM and ALTER SESSION.

event_name

An event that the command rule defines. For example, for an ALTER SESSION command rule that uses SET for the clause_name and EVENTS as the parameter_name, then the event_name could be set to TRACE.

Applies only to ALTER SYSTEM and ALTER SESSION command rules that have the parameter parameter set to events.

component_name

A component of the event_name setting. For example, for a TRACE event, the component_name could be GCS.

Applies only to ALTER SYSTEM and ALTER SESSION command rules that have the parameter parameter set to events.

action_name

An action of the component_name setting. For example, if component_name is set to GCS, then the action_name setting could be DISK HIGH.

Applies only to ALTER SYSTEM and ALTER SESSION command rules that have the parameter parameter set to events.

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

If you update the common command rule in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

pl_sql_stack

When simulation mode is enabled, specifies whether to record the PL/SQL stack for failed operations. Enter TRUE to record the PL/SQL stack, FALSE to not record.

Examples

The following example shows how to create a simple command rule that protects the HR.EMPLOYEES schema.

BEGIN
 DBMS_MACADM.UPDATE_COMMAND_RULE(
  command         => 'SELECT', 
  rule_set_name   => 'Enabled', 
  object_owner    => 'HR', 
  object_name     => 'EMPLOYEES', 
  enabled         => DBMS_MACUTL.G_SIMULATION,
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

This example shows how to update a more complex command rule, which is based on the ALTER SESSION SQL statement.

BEGIN
 DBMS_MACADM.UPDATE_COMMAND_RULE(
  command         => 'ALTER SESSION', 
  rule_set_name   => 'Enabled', 
  object_owner    => '%', 
  object_name     => '%', 
  enabled         => 's',
  clause_name     => 'SET',
  parameter_name  => 'EVENTS',
  event_name      => 'TRACE',
  component_name  => 'GCS',
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/

UPDATE_CONNECT_COMMAND_RULE Procedure

The UPDATE_CONNECT_COMMAND_RULE procedure updates a CONNECT command rule that had been created with the CREATE_CONNECT_COMMAND_RULE procedure.

Syntax

DBMS_MACADM.UPDATE_CONNECT_COMMAND_RULE(
 user_name       IN VARCHAR2, 
 rule_set_name   IN VARCHAR2, 
 enabled         IN VARCHAR2, 
 scope           IN NUMBER DEFAULT);

Parameters

Table 16-12 UPDATE_CONNECT_COMMAND_RULE Parameters

Parameter Description

user_name

User to whom the CONNECT command rule will apply. If you enter the % wildcard, then the CONNECT command rule will be applied to every database user.

In a multitenant environment, if you execute this procedure in the root, then specifying % applies to all common users. If you run the procedure in a PDB, then it applies to all local and common users who have access to this PDB. If there are two command rules, one common and one local, and they both apply to the same object, then both must evaluate successfully for the operation to succeed.

In a multitenant environment, ensure that this user is common if the CONNECT command rule is common, and local or common if the CONNECT command rule is local.

To find existing command rules, query the DBA_DV_COMMAND_RULE view, described in DBA_DV_COMMAND_RULE View.

To find existing database users in the current instance, query the DBA_USERS view, described in Oracle Database Reference.

rule_set_name

Name of rule set to associate with this command rule. In a multitenant environment, ensure that this rule set is common if the CONNECT command rule is common, and local if the CONNECT command rule is local.

To find existing rule sets in the current database instance, query the DBA_DV_RULE_SET view, described in DBA_DV_RULE_SET View.

enabled

Specify one of the following options to set the status of the command rule:

  • DBMS_MACUTL.G_YES or ‘y’ to enable the command rule (default)

  • DBMS_MACUTL.G_NO or ‘n’ to disable the command rule, including the capture of violations in the simulation log

  • DBMS_MACUTL.G_SIMULATION or ‘s’ to enable SQL statements to execute but capture violations in the simulation log

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

If you update the common command rule in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

Example

BEGIN
 DBMS_MACADM.UPDATE_CONNECT_COMMAND_RULE(
  rule_set_name   => 'Allow Sessions', 
  user_name       => 'PSMITH',
  enabled         => 'DBMS_MACUTL.G_YES',
  scope           => DBMS_MACUTL.G_SCOPE_LOCAL);
END; 
/

UPDATE_SESSION_EVENT_CMD_RULE Procedure

The UPDATE_SESSION_EVENT_CMD_RULE procedure updates a session event command rule, based on the ALTER SESSION statement.

In a multitenant environment, you can update both common and local session event command rules.

Syntax

DBMS_MACADM.UPDATE_SESSION_EVENT_CMD_RULE(
 rule_set_name   IN VARCHAR2, 
 enabled         IN VARCHAR2,
 event_name      IN VARCHAR2 DEFAULT,
 component_name  IN VARCHAR2 DEFAULT,
 action_name     IN VARCHAR2 DEFAULT, 
 scope           IN NUMBER DEFAULT,
 pl_sql_stack    IN BOOLEAN DEFAULT);

Parameters

Table 16-13 UPDATE_SESSION_EVENT_CMD_RULE Parameters

Parameter Description

rule_set_name

Name of the rule set to associate with the command rule. In a multitenant environment, ensure that this rule set is common if the session event command rule is common, and local if the command rule is local.

To find existing rule sets in the current database instance, query the DBA_DV_RULE_SET view, described in DBA_DV_RULE_SET View.

enabled

Specify one of the following options to set the status of the command rule:

  • DBMS_MACUTL.G_YES or ‘y’ to enable the command rule (default)

  • DBMS_MACUTL.G_NO or ‘n’ to disable the command rule, including the capture of violations in the simulation log

  • DBMS_MACUTL.G_SIMULATION or ‘s’ to enable SQL statements to execute but capture violations in the simulation log

event_name

An event that the command rule defines. This setting enables the command rule to correspond with an ALTER SESSION SET EVENTS event_name statement. For example, to track trace events, you would set event_name to TRACE.

component_name

A component of the event_name setting. Example settings are DV, OLS, or GCS.

You can find valid component names by issuing ORADEBUG DOC COMPONENT RDBMS as user SYS. The output displays parent and child components, which you can use for the component_name setting. For example, both XS (parent) and XSSESSION (child of XS) are valid component names. If you select the parent component, then the command rule applies to it and the child components.

action_name

An action of the component_name setting

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

If you update the common command rule in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

pl_sql_stack

When simulation mode is enabled, specifies whether to record the PL/SQL stack for failed operations. Enter TRUE to record the PL/SQL stack, FALSE to not record.

Example

The following example shows how to update a common session event command rule in a multitenant environment. This command rule is in the application root, so the user running this procedure must be in the CDB root. Any user names or rule sets that are associated with this command rule must be common.

BEGIN
 DBMS_MACADM.UPDATE_SESSION_EVENT_CMD_RULE(
  rule_set_name => 'Allow Sessions',
  event_name    => '47999',
  enabled       => DBMS_MACUTL.G_NO,
  scope         => DBMS_MACUTL.G_SCOPE_COMMON); 
END; 
/

UPDATE_SYSTEM_EVENT_CMD_RULE Procedure

The UPDATE_SYSTEM_EVENT_CMD_RULE procedure updates a system event command rule, based on the ALTER SYSTEM statement.

In a multitenant environment, you can update both common and local session event command rules.

Syntax

DBMS_MACADM.UPDATE_SYSTEM_EVENT_CMD_RULE(
 rule_set_name   IN VARCHAR2, 
 enabled         IN VARCHAR2,
 event_name      IN VARCHAR2 DEFAULT,
 component_name  IN VARCHAR2 DEFAULT,
 action_name     IN VARCHAR2 DEFAULT, 
 scope           IN NUMBER DEFAULT,
 pl_sql_stack    IN BOOLEAN DEFAULT);

Parameters

Table 16-14 UPDATE_SYSTEM_EVENT_CMD_RULE Parameters

Parameter Description

rule_set_name

Name of the rule set to associate with the command rule. In a multitenant environment, ensure that this rule set is common if the system event command rule is common, and local if the command rule is local.

To find existing rule sets in the current database instance, query the DBA_DV_RULE_SET view, described in DBA_DV_RULE_SET View.

enabled

Specify one of the following options to set the status of the command rule:

  • DBMS_MACUTL.G_YES or ‘y’ to enable the command rule (default)

  • DBMS_MACUTL.G_NO or ‘n’ to disable the command rule, including the capture of violations in the simulation log

  • DBMS_MACUTL.G_SIMULATION or ‘s’ to enable SQL statements to execute but capture violations in the simulation log

event_name

An event that the command rule defines. This setting enables the command rule to correspond to an ALTER SYSTEM SET EVENTS event_name statement. For example, to track trace events, you would set event_name to TRACE.

component_name

A component of the event_name setting. Example settings are DV, OLS, or GCS.

You can find valid component names by issuing ORADEBUG DOC COMPONENT RDBMS as user SYS. The output displays parent and child components, which you can use for the component_name setting. For example, both XS (parent) and XSSESSION (child of XS) are valid component names. If you select the parent component, then the command rule applies to it and the child components.

action_name

An action of the component_name setting

scope

For a multitenant environment, determines how to execute this procedure. The default is local. Options are as follows:

  • DBMS_MACUTL.G_SCOPE_LOCAL (or 1) if the command rule is local in the current PDB

  • DBMS_MACUTL.G_SCOPE_COMMON (or 2) if the command rule is in the application root

If you update the common command rule in an application root and want it visible to the associated PDBs, then you must synchronize the application. For example:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

pl_sql_stack

When simulation mode is enabled, specifies whether to record the PL/SQL stack for failed operations. Enter TRUE to record the PL/SQL stack, FALSE to not record.

Example

The following example shows how to update a common system event command rule in a multitenant environment. This command rule is in the application root, so the user running this procedure must be in the CDB root. Any user names or rule sets that are associated with this command rule must be common.

BEGIN
 DBMS_MACADM.UPDATE_SYSTEM_EVENT_CMD_RULE(
 rule_set_name   => 'Disabled', 
 event_name      => 'TRACE', 
 component_name  => 'DV',
 enabled         => 'n',
 scope           => DBMS_MACUTL.G_SCOPE_COMMON);
END; 
/