SAP HANA – SQL Access Control Statements

Hello Everyone,

In this article, we are going to look at different SQL Access Control Statements (ACS) available in SAP HANA along with their syntax and examples.

 

Different statements available in SAP HANA are,

ALTER AUDIT POLICY
ALTER CREDENTIAL
ALTER REMOTE SOURCE
ALTER SAML PROVIDER
ALTER USER
CREATE AUDIT POLICY
CREATE CREDENTIAL
CREATE USER
CREATE REMOTE SOURCE
CREATE ROLE
CREATE SAML PROVIDER
DROP AUDIT POLICY
DROP CREDENTIAL
DROP REMOTE SOURCE
DROP ROLE
DROP SAML PROVIDER
DROP USER
GRANT
REVOKE

Let’s look at details about each statement below.

ALTER AUDIT POLICY

ALTER AUDIT POLICY:

  • The ALTER AUDIT POLICY statement enables or disables an audit policy. <policy_name> must specify an existing audit policy.
  • Only database users with the system privilege AUDIT ADMIN are allowed to alter an audit policy. Users with this privilege can alter any audit policy, regardless of if they are the creator of the policy.
  • When an audit policy is created, it is created in the disabled state. Therefore the audit policy has to be enabled to make its audit actions take effect.
  • An audit policy can be disabled and enabled as often as required.
  • One or more audit trail targets could be specified for an audit policy at the time of creation (see CREATE AUDIT POLICY) or after creation.

The allowed audit trail targets are:

  • SYSLOG: uses the system syslog
  • TABLE: stores audit information in database table. The audit log is accessible using AUDIT_LOG system view
  • CSV: stores audit information as comma-separated values in a text file. Should be used only for testing purposes.

Syntax: ALTER AUDIT POLICY <policy_name> <audit_mode> | <set_audit_trail_type> | <reset_audit_trail_type>

Configuration Parameters: The following configuration parameters for auditing are stored in global.ini, in the section auditing configuration:

Global_auditing_state ( ‘true’ / ‘false’ ):

  • Regardless of the number of enabled audit policies, auditing will only occur if the global_auditing_state configuration parameter is set to true. The default is ‘false’.

 Default_audit_trail_type ( ‘SYSLOGPROTOCOL’ / ‘CSTABLE’ / ‘CSVTEXTFILE’ ):

  • Specifies how auditing results will be stored.
  • SYSLOGPROTOCOL: uses the system syslog.
  • CSTABLE: stores audit information in database table. The audit log is accessible using AUDIT_LOG system view.
  • CSVTEXTFILE: stores audit information as comma-separated values in a text file. This option should be used only for testing purposes.

Default_audit_trail_path:

  • Specifies the file path where the CSVTEXTFILE should be stored.

Emergency_audit_trail_type:

  • Specifies the audit trail target(s) for the events with severity EMERGENCY.

Alert_audit_trail_type:

  • Specifies the audit trail target(s) for the events with severity ALERT.

Critical_audit_trail_type:

  • Specifies the audit trail target(s) for the events with severity CRITICAL.

Multiple audit trail targets could be specified for parameters emergency_audit_trail_type, alert_audit_trail_type, and critical_audit_trail_type by separating the trail type names with comma.

  • To store the auditing information for events with severity EMERGENCY to both Syslog and database table, set the value of emergency_audit_trail_type to ‘CSTABLE,SYSLOGPROTOCOL’
  • The parameters above can be selected in the M_INIFILE_CONTENTS monitoring view if the user has the required system privileges.
  • The parameters can only be observed in a monitoring view when they have been explicitly defined.
Below views under SYS schema gives information about AUDIT POLICIES in SAP HANA.
AUDIT_POLICIES: All audit policies and their states.
M_INIFILE_CONTENTS: Database system configuration parameters.
AUDIT_LOG: Audit log.

Only users with system privilege CATALOG READ, DATA ADMIN or INIFILE ADMIN can view the content of the M_INIFILE_CONTENTS view. For all other database users this view will be empty.

Examples:

  1. Run the below statement to enable audit policy SAP_STUDENT_INSERT_AUDIT in SAP HANA.

Ans: ALTER AUDIT POLICY SAP_STUDENT_INSERT_AUDIT ENABLE

  1. Run the below statement to set SYSLOG and TABLE as audit trail targets for same audit policy.

Ans: ALTER AUDIT POLICY SAP_STUDENT_INSERT_AUDIT SET TRAIL TYPE SYSLOG, TABLE

  1. Run the below statement to reset trail type.

Ans: ALTER AUDIT POLICY SAP_STUDENT_INSERT_AUDIT RESET TRAIL TYPE

  1. Run the below statement to disable audit policy in SAP HANA.

Ans: ALTER AUDIT POLICY SAP_STUDENT_INSERT_AUDIT DISABLE

ALTER CREDENTIAL

ALTER CREDENTIAL:

  • The ALTER CREDENTIAL statement modifies an existing component-specific or application-specific credential. Only the credentials can be changed using this command.
  • Each user has the privilege to alter their own credentials.

Syntax:  ALTER CREDENTIAL FOR [USER <user_name>] COMPONENT <component_id> PURPOSE <purpose_def> TYPE <type_def> USING <using_param>

Below view under SYS schema gives information about CREDENTIAL information.
CREDENTIALS: Credentials defined for users and component

Example:

  1. Run the below statement to change the credential password.

Ans: ALTER CREDENTIAL FOR COMPONENT ‘INTERNAL_APP’ PURPOSE ‘COMPANY_MASTER_MACHINE’ TYPE ‘PASSWORD’ USING ‘Password1#’

Check Also

Capture

SAP HANA – SQL Session Management Statements

Hello Everyone, In this article, we are going to look at different SQL Session Management …

Leave a Reply

Your email address will not be published. Required fields are marked *

nineteen + 2 =