DB-04

Control Description

Management performs a periodic review of HANA database user access to verify users have only necessary privileges for their roles. Inappropriate or excessive access is removed and findings documented and retained

Risk

Failure to periodically review and remediate HANA database user access may result in users retaining excessive or inappropriate database privileges, enabling unauthorized direct data access, modifications, and bypass of application controls, leading to undetected fraud or data corruption

Implementation Details

Periodically run queries along the lines of the following to identify and isolate the population that matters most: human users, custom service accounts, and non-standard IDs that hold dangerous administrative or security-altering privileges. Note: SYSTEM account filtered out as controlled in other controls

Query 1: Direct High-Risk Privileges (Active Users Only)

SELECT
t_user.user_name,
t_user.user_deactivated,
t_user.last_successful_connect,
t_priv.object_type,
t_priv.privilege,
t_priv.schema_name,
t_priv.grantor
FROM sys.users AS t_user
JOIN sys.granted_privileges AS t_priv
ON t_priv.grantee = t_user.user_name
WHERE t_priv.privilege IN (
'USER ADMIN', 'ROLE ADMIN', 'BACKUP ADMIN', 'AUDIT ADMIN',
'AUDIT OPERATOR', 'ALTER', 'CREATE ANY', 'DEBUG',
'DELETE', 'DROP', 'INSERT', 'CREATE STRUCTURED PRIVILEGE',
'STRUCTURED PRIVILEGE ADMIN', 'DATA ADMIN', 'DATABASE ADMIN'
)
AND t_priv.grantee_type = 'USER'
AND t_user.user_deactivated = 'FALSE'
AND t_user.user_name NOT LIKE '_SYS%'
AND t_user.user_name NOT LIKE 'SYS%'
AND t_user.user_name NOT LIKE 'SAP%'
AND t_user.user_name NOT LIKE 'SBSS%'
AND t_user.user_name NOT LIKE 'TEL_ADMIN%'
AND t_user.user_name NOT LIKE 'XSSQL%'
AND t_user.user_name NOT IN ('SYS', 'SYSTEM');

Query 2: Role-Based High-Risk Privileges (Active Users Only)
This query identifies active, non-system accounts that inherit dangerous administrative capabilities because they belong to roles holding those high-risk privileges.

SELECT
t_user.user_name,
t_user.user_deactivated,
t_user.last_successful_connect,
t_role.role_name,
t_role.grantor
FROM sys.users AS t_user
JOIN sys.granted_roles AS t_role
ON t_role.grantee = t_user.user_name
WHERE t_role.role_name IN (
SELECT t_priv.grantee
FROM sys.granted_privileges AS t_priv
WHERE t_priv.privilege IN (
'USER ADMIN', 'ROLE ADMIN', 'BACKUP ADMIN', 'AUDIT ADMIN',
'AUDIT OPERATOR', 'ALTER', 'CREATE ANY', 'DEBUG',
'DELETE', 'DROP', 'INSERT', 'CREATE STRUCTURED PRIVILEGE',
'STRUCTURED PRIVILEGE ADMIN', 'DATA ADMIN', 'DATABASE ADMIN'
)
AND t_priv.grantee_type = 'ROLE'
)
AND t_user.user_deactivated = 'FALSE'
AND t_user.user_name NOT LIKE '_SYS%'
AND t_user.user_name NOT LIKE 'SYS%'
AND t_user.user_name NOT LIKE 'SAP%'
AND t_user.user_name NOT LIKE 'SBSS%'
AND t_user.user_name NOT LIKE 'TEL_ADMIN%'
AND t_user.user_name NOT LIKE 'XSSQL%'
AND t_user.user_name NOT IN ('SYS', 'SYSTEM');

Test Procedures

1. Test of Design (ToD)
• Inquiry: Inquire of IT management and database security administration to understand the design of the periodic user access review process, including the defined review frequency (e.g., quarterly or semi-annually), the roles responsible for performing the review, and the escalation paths for removing excessive access.

• Policy Inspection: Inspect the corporate access control or user review policy to verify that it explicitly mandates periodic reviews of database privileges, defines the scope of covered systems (SAP HANA), and outlines documentation requirements.

• Procedure & Script Review: Inspect the methodology or SQL extraction scripts used to pull the access population. Verify that the design correctly isolates active human/service accounts, checks both direct and role-based high-risk privileges (e.g., USER ADMIN, DATABASE ADMIN, DROP, DELETE), and properly filters out internal system accounts and schemas (_SYS%, SYS, SYSTEM, SAP%).

2. Test of Operating Effectiveness (ToE)
• Population & Sample Selection: Obtain a complete list of scheduled review periods for the audit period and select a representative sample of access reviews (e.g., specific quarters or semi-annual review cycles).

• Review Execution & Management Sign-Off: Inspect the completed review reports, sign-off logs, or ticketing records for the sampled periods to confirm that:

• The review was performed by designated management personnel on schedule.

• The evaluated population accurately reflected active users and their assigned high-risk privileges at the time of the review.

• Remediation & Revocation Verification: For any inappropriate, dormant, or excessive access identified during the review process, inspect associated user modification tickets, change logs, or database execution records (e.g., REVOKE statements or account deactivations) to verify that the access was removed in a timely manner.

• Evidence Retention: Confirm that the review findings, corresponding sign-offs, and remediation evidence have been archived and retained in a secure repository in compliance with corporate data retention policies.