17.3    Identifying Users with Elevated Privileges

When implementing a security model, your organization should maintain a list of users that have been granted a high level of access to the SAP HANA system. The term high-level access loosely describes having privileges that are inherently risky for a user to possess. Many system privileges within SAP HANA are inherently risky to grant to other users, but other types of privileges can also be risky.

Organizations using SAP HANA can’t entirely avoid granting these privileges to some of their users. However, the system should be scrutinized and frequently audited to ensure that users have the appropriate privileges based on their established job functions and based on the organization’s defined division of duties.

In this section, we’ll identify system, package, and analytic privileges that are inherently risky and list default roles that are inherently risky. In addition, we’ll help you identify user accounts with these privileges using SQL statements.

17.3.1    System Privileges

With a few exceptions, most system privileges are inherently risky because they allow a user to make changes to the system. However, the DATA ADMIN, USER ADMIN, ROLE ADMIN, AUDIT OPERATOR, and INIFILE ADMIN system privileges, in our opinion, are some of the riskiest privileges that can be granted. Users with these system privileges should be limited, and their actions should be routinely audited. Let’s look at each of these system privileges in more detail.

Data Admin

Grantees with the DATA ADMIN system privilege can query all system views. These users can also execute Data Definition Language (DDL) SQL commands against any table in any schema. The DDL commands include items such as ALTER, DROP, and CREATE. This privilege should only be granted when necessary. To identify grantees with this privilege, execute the SQL code shown in Listing 17.1.

SELECT * 
FROM SYS.EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'SYSTEMPRIVILEGE' 
AND PRIVILEGE = 'DATA ADMIN' 
AND GRANTEE NOT IN ('SYSTEM','_SYS_REPO');

Listing 17.1    SQL Code to Identify Grantees with DATA ADMIN System Privilege

Note that the code shown in Listing 17.1 excludes the SYSTEM and _SYS_REPO default accounts from the results.

User Admin

The USER ADMIN system privilege allows a grantee to CREATE, ALTER, or DROP any user within the system and includes actions such as password resets, third-party identity management, and deleting users. In most cases, this privilege should only be granted to users tasked with the administration of user accounts.

To identify grantees with this privilege, execute the SQL code shown in Listing 17.2.

SELECT * 
FROM SYS.EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'SYSTEMPRIVILEGE' 
AND PRIVILEGE = 'USER ADMIN' 
AND GRANTEE NOT IN ('SYSTEM','_SYS_REPO');

Listing 17.2    SQL Code to Identify Grantees with USER ADMIN System Privilege

Note that the code shown in Listing 17.2 excludes the SYSTEM and _SYS_REPO default accounts from the results.

Role Admin

The ROLE ADMIN system privilege allows a grantee to CREATE and DROP standard roles. This privilege also allows the grantee to grant an existing standard role to another grantee, even if the ROLE ADMIN privilege holder is not explicitly assigned the WITH ADMIN option. Therefore, organizations can’t limit which roles can be granted if a user possess the ROLE ADMIN system privileges. In addition, your organization should limit their use of standard roles in favor of repository-based roles. With these risks in mind, this privilege should be granted only when necessary.

To identify grantees with this privilege, execute the SQL code shown in Listing 17.3.

SELECT * 
FROM SYS.EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'SYSTEMPRIVILEGE' 
AND PRIVILEGE = 'ROLE ADMIN' 
AND GRANTEE NOT IN ('SYSTEM','_SYS_REPO');

Listing 17.3    SQL Code to Identify Grantees with ROLE ADMIN System Privilege

Note that the code shown in Listing 17.3 excludes the SYSTEM and _SYS_REPO default accounts from the results.

Audit Operator

The AUDIT OPERATOR system privilege allows a grantee to clear the audit logs, which should only be granted when necessary. Typically, audit log-related system privileges are never assigned to security administrators, which ensures that the security administrator can’t change an audit policy or clear the audit logs to cover his tracks.

To identify grantees with this privilege, execute the SQL code shown in Listing 17.4.

SELECT * 
FROM SYS.EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'SYSTEMPRIVILEGE' 
AND PRIVILEGE = 'AUDIT OPERATOR' 
AND GRANTEE NOT IN ('SYSTEM','_SYS_REPO');

Listing 17.4    SQL Code to Identify Grantees with AUDIT OPERATOR System Privilege

Note that the code shown in Listing 17.4 excludes the SYSTEM and _SYS_REPO default accounts from the results.

INIFILE Admin

The INIFILE ADMIN system privilege allows a grantee to change any SAP HANA configuration setting. Changes to the system configurations are extremely risky within a production landscape and can also affect operations in nonproduction landscapes. Therefore, this system privilege should only be granted to administrators responsible for implementing system configuration changes.

To identify grantees with this privilege, execute the SQL code shown in Listing 17.5.

SELECT * 
FROM SYS.EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'SYSTEMPRIVILEGE' 
AND PRIVILEGE = 'INIFILE ADMIN' 
AND GRANTEE NOT IN ('SYSTEM','_SYS_REPO');

Listing 17.5    SQL Code to Identify Grantees with INIFILE ADMIN System Privilege

Note that the code shown in Listing 17.5 excludes the SYSTEM and _SYS_REPO default accounts from the results.

17.3.2    Root Package Privileges

When a grantee has root package privileges, that user is assumed to have the granted privileges on all packages within the repository. Identifying users with root package privileges that allow changes to the repository is important. The following package privileges allow for changes to the repository:

• REPO.EDIT_NATIVE_OBJECTS

• REPO.ACTIVATE_NATIVE_OBJECTS

• REPO.MAINTAIN_NATIVE_PACKAGES

The REPO.READ privilege allows a user to view all repository objects without making changes. At the root level, this privilege should be limited to only grantees that need to view all repository objects. Limiting who can see everything is best because this information can be used to construct more sophisticated hacks.

When a grantee has been granted root package privileges, those privileges will be assigned to a special package object named .REPO_PACKAGE_ROOT. Privileges assigned to this object will be inherited by all packages and development artifacts within the repository. We recommend that you limit root package access because the repository often contains repository roles, and users can potentially elevate their own privileges if they can modify a repository role that they’re granted.

To identify grantees with risky root package privileges, execute the SQL code shown in Listing 17.6.

SELECT * 
FROM SYS.EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'REPO' 
AND (PRIVILEGE = 'REPO.EDIT_NATIVE_OBJECTS'
 OR PRIVILEGE = 'REPO.ACTIVATE_NATIVE_OBJECTS' 
 OR PRIVILEGE = 'REPO.MAINTAIN_NATIVE_PACKAGES')
AND OBJECT_NAME = '.REPO_PACKAGE_ROOT'
AND GRANTEE NOT IN ('SYSTEM','_SYS_REPO')
ORDER BY GRANTEE;

Listing 17.6    SQL Code to Identify Grantees with Risky Root Package Privileges

To identify grantees with the root package REPO.READ privilege, execute the SQL code shown in Listing 17.7.

SELECT * 
FROM SYS.EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'REPO' 
AND (PRIVILEGE = 'REPO.READ')
AND OBJECT_NAME = '.REPO_PACKAGE_ROOT'
AND GRANTEE NOT IN ('SYSTEM','_SYS_REPO')
ORDER BY GRANTEE;

Listing 17.7    SQL Code to Identify Grantees with Read-Only Root Package Privilege

17.3.3    Bypass Analytic Privileges

In SAP HANA, an analytic privilege check can be bypassed in two ways. Analytic privileges protect data within the SAP HANA system. To ensure that these privileges haven’t been bypassed, you’ll need to review grantees with the _SYS_BI_CP_ALL analytic privilege and identify information views that have disabled analytic privilege checks.

SYS_BI_CP_ALL

Information views require that all grantees have analytic privileges before the view can be queried. Information views, configured using the classic XML-based analytic privilege model, are automatically assigned to a special system of managed analytic privileges named _SYS_BI_CP_ALL. This analytic privilege provides unrestricted access to all information views using the classic XML-based analytic privilege check.

Grantees with this privilege won’t be subject to any data-level restrictions, even if these users are assigned custom analytic privileges that have restrictions. When a grantee has multiple analytic privileges, they’re effectively combined using an OR condition. For example, if a grantee has an analytic privilege where REGION = 'North' and the _SYS_BI_CP_ALL analytic privilege, the system will generate the effective filter restriction of (1=1) OR (REGION = 'North'). Because the first condition (1=1) is always TRUE, the system ignores the remaining restrictions. Therefore, the _SYS_BI_CP_ALL analytic privileges will always grant access to all data and all information views with a classic XML-based check.

Note that this arrangement doesn’t apply to information views that use SQL-based analytic privileges; in other words, the _SYS_BI_CP_ALL analytic privilege doesn’t apply to information views with the SQL-based check.

To identify grantees with the _SYS_BI_CP_ALL analytic privilege, execute the SQL code shown in Listing 17.8.

SELECT * 
FROM EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'ANALYTICALPRIVILEGE' 
AND OBJECT_NAME = '_SYS_BI_CP_ALL' 
AND PRIVILEGE = 'EXECUTE' 
AND GRANTEE NOT IN ('SYSTEM','MODELING', 'CONTENT_ADMIN');

Listing 17.8    SQL Code to Identify Grantees with Risky Analytic Privileges

Two default roles grant the SYS_BI_CP_ALL analytic privilege: MODELING and CONTENT_ADMIN. These roles should never be used within a security model because of their inherent risk. To identify grantees with these roles, execute the SQL code shown in Listing 17.9.

SELECT * 
FROM EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'ANALYTICALPRIVILEGE' 
AND OBJECT_NAME = '_SYS_BI_CP_ALL' 
AND PRIVILEGE = 'EXECUTE'
AND GRANTEE NOT IN ('SYSTEM')
AND GRANTOR IN ('MODELING', 'CONTENT_ADMIN')
AND GRANTOR_TYPE = 'ROLE';

Listing 17.9    SQL Code to Identify Grantees with Risky Default Roles

Information Views with No Analytic Privilege Checks

To identify information views with no analytic privilege checks enabled, you must query table ACTIVE_OBJECT in the _SYS_REPO schema. For example, to identify information views with disabled analytic privilege checks, you can execute the SQL code shown in Listing 17.10.

SELECT *
FROM "_SYS_REPO"."ACTIVE_OBJECT" 
WHERE 
( UPPER("CDATA") LIKE  UPPER('%checkAnalyticPrivileges="false"%') 
AND 
"OBJECT_SUFFIX" 
IN ('analyticview', 'calculationview', 'attributeview'));

Listing 17.10    SQL Code to Identify Information Views with Risky Analytic Privilege Check Settings

Any information view with its analytic privilege check disabled can be queried by any grantee with the SELECT privilege on the _SYS_BIC schema or the SELECT privilege on the associated column view. In addition, no filter restrictions can be applied to the information view. Therefore, your organization should maintain a listing of insecure information views and document the reasons for their configured states.

In addition to listing information views without an analytic privilege checks, you can also list those with different analytic privilege check types. To list all information views with either classic XML-based analytic privilege checks or with SQL-based analytic privilege checks, execute the SQL code shown in Listing 17.11.

SELECT 'XML BASED AP CHECK' AS AP_TYPE, *
FROM "_SYS_REPO"."ACTIVE_OBJECT" 
WHERE 
( UPPER("CDATA") LIKE UPPER('%applyPrivilegeType="ANALYTIC_PRIVILEGE"%'))
AND "OBJECT_SUFFIX" 
IN ('analyticview', 'calculationview', 'attributeview')

UNION ALL

SELECT 'SQL BASED AP CHECK' AS AP_TYPE, *
FROM "_SYS_REPO"."ACTIVE_OBJECT" 
WHERE 
( UPPER("CDATA") LIKE UPPER('%applyPrivilegeType="SQL_ANALYTIC_PRIVILEGE"%'))
AND "OBJECT_SUFFIX" 
IN ('analyticview', 'calculationview', 'attributeview'); 

Listing 17.11    SQL Code to List All Information Views by Analytic Privilege Type

17.3.4    Default Standard Roles

In general, standard users shouldn’t be granted the default roles included with the SAP HANA installation, some of which grant high privilege levels. In this section, we’ll discuss two of these roles: CONTENT_ADMIN and MODELING.

Content Admin

The CONTENT_ADMIN role is intended for grantees that manage the development repository and related areas of the system. We don’t recommend using this role because of the following risks associated with granting this role:

• Many of this role’s system, object, and package privileges are granted with the Grantable to Others option enabled, which allows a grantee with the CONTENT_ADMIN privilege to grant many privileges to other grantees. These actions are typically reserved for security administrators, not lead developers.

• This role grants the _SYS_BI_CP_ALL analytic privilege, which bypasses all data security settings implemented on applicable information views. You should use caution when granting this privilege to any user.

• This role grants full root package privileges and the ability to grant those privileges to other grantees, which is risky in most scenarios, but especially when repository-based roles have been implemented. Users with the CONTENT_ADMIN role could elevate their privileges by modifying existing repository-based roles.

To identify grantees with this role, execute the SQL code shown in Listing 17.12.

SELECT * FROM 
SYS.GRANTED_ROLES 
WHERE 
ROLE_NAME = 'CONTENT_ADMIN' 
AND GRANTEE NOT IN ('SYSTEM');

Listing 17.12    SQL Code to Identify Grantees with CONTENT_ADMIN Role

Modeling

The MODELING role is intended for grantees that work within the development repository and related areas of the system. We don’t recommend using this role because of the following risks associated with granting this role:

• This role grants the _SYS_BI_CP_ALL analytic privilege, which bypasses all data security settings implemented on applicable information views. Organizations should use caution when granting this privilege to any user.

• This role grants root package privileges for native objects, which is risky in most scenarios, but especially when repository-based roles have been implemented. Users with the MODELING role could elevate their privileges by modifying existing repository-based roles.

To identify grantees with this role, execute the SQL code shown in Listing 17.13.

SELECT * FROM 
SYS.GRANTED_ROLES 
WHERE 
ROLE_NAME = 'MODELING' 
AND GRANTEE NOT IN ('SYSTEM');

Listing 17.13    SQL Code to Identify Grantees with MODELING Role

17.3.5    WITH GRANT or WITH ADMIN

When a grantee is granted a privilege, an additional option allows the grantee to also grant the same privilege to another user or role. The option is named WITH GRANT or WITH ADMIN when using SQL to grant privileges. When using a graphical user interface (GUI), the option is named Grantable to Others. Only grantees that manage security should have this additional privilege option.

To identify grantees with this additional capability, execute the SQL code shown in Listing 17.14.

SELECT DISTINCT "GRANTEE", "GRANTEE_TYPE", "PRIVILEGE", "IS_GRANTABLE"
FROM "SYS"."GRANTED_PRIVILEGES" 
WHERE "IS_GRANTABLE" = 'TRUE'
AND GRANTEE 
NOT IN ('PUBLIC','SYSTEM','_SYS_REPO','SYS','_SYS_AFL','_SYS_DI_SU')
AND GRANTEE NOT LIKE '_SYS%'
AND GRANTEE NOT LIKE 'SYS_%'
AND GRANTEE NOT LIKE 'USR_%'
AND GRANTEE NOT LIKE 'AFL__%'
AND GRANTEE NOT LIKE 'SBSS_%'
AND GRANTEE NOT LIKE 'XSSQLCC_%'
ORDER BY 1 DESC;

Listing 17.14    SQL Code to Identify Grantees Who Can Grant Privileges

Note that the code shown in Listing 17.14 attempts to remove many of the internal system-generated accounts because SAP controls the privileges for these accounts. However, if your organization creates users following a naming convention similar to the one SAP uses, then the code shown in Listing 17.14 will need to be modified.

In newer versions of SAP HANA, multiple grantees are generated with a name that appears to be a random combination of letters and numbers. These accounts should be ignored; they’re managed by the system or are disabled.

17.3.6    Trace, Dump File, and Debug Access

While it a database administrator may need to review trace files and to debug SQL often, security information can be revealed when a high level of trace is enabled. SAP states that the default trace levels do not expose such information. However, if the trace settings are modified and configured to record a deeper level of activity, traces can contain compromising information. Therefore, we recommend you follow these rules:

• Only enable high level tracing when required and disable tracing quickly after the debugging information is captured.

• Have a security team review the trace files for confidential security information. This data should be masked or removed from the file.

• Delete trace files from the OS and other file systems once the analysis is completed.

You must know which users have the system privileges necessary to change trace settings and to audit for any configuration settings that increase tracing levels. The following SQL will help you identify the grantees that have these system privileges:

SELECT * FROM GRANTED_PRIVILEGES WHERE PRIVILEGE='DEBUG' OR PRIVILEGE='ATTACH DEBUGGER' or PRIVILEGE='TRACE ADMIN';