16    Security Tracing and Troubleshooting

Implementing a security model can be a complex endeavor, and security administrators often find gaps within their security models. In this chapter, we’ll discuss security tracing and effective privileges views, which can help a security administrator identify these gaps.

In the real world, most security models are quite complex. When traversing the complex lineage of roles and their privileges is difficult, SAP HANA offers a security trace option that will capture and list many of the privileges missing from users attempting to interact with SAP HANA. This chapter will outline the steps required to enable a security-related authorization trace and review the trace file results.

In addition to security tracing, we’ll also review the various system views that provide details about the effective rights granted to individual users, and we’ll provide example queries to help bolster your understanding of how these system views can be leveraged.

16.1    Authorization Tracing

An authorization trace is a type of user-specific trace that’s limited to tracing detailed actions conducted by an individual user account. You can configure user-specific traces to record detailed information about a variety of activities within the SAP HANA system. Just about every component of SAP HANA can be traced, and trace information can be leveraged to provide specific information related to an activity within the SAP HANA system. Security administrators can use this trace mechanism to focus on the authorization component of an activity.

Tracing the authorization component provides detailed information related to actions that are both allowed and denied for a given user. For example, an authorization trace will record instances in which a user is not authorized to perform an action. In addition, the authorization trace often records the privileges a user is missing when attempting to conduct an unauthorized action.

Tracing is different from auditing (discussed in Chapter 15). Tracing provides significantly more detail concerning the internal processing of a user’s actions. Because of this level of detail, tracing should only be enabled for a short time and should be coordinated only with troubleshooting activities. An authorization trace not only records the actions of a user but also in many cases provides suggestions on the permissions required when actions are denied. Tracing activities are also recorded only to trace files; the information recorded to the trace file is semistructured and not easily recorded to a database table. Therefore, querying the trace data is difficult.

In contrast, auditing records the specific actions performed, including the action status. Audit data can be queried easily when recorded to a database table but doesn’t provide the same level of detail that tracing can provide. In addition, audit policies typically remain active all the time and aren’t intended to troubleshoot security-related gaps.

In general, you can enable a security trace for an individual user in two ways: The SAP HANA cockpit provides a graphical user interface (GUI) in which an authorization trace can be enabled, and administrators can also use ALTER SYSTEM ALTER CONFIGURATION SQL statements to configure and enable an authorization trace. In the following sections, we’ll explore these options in detail.

16.1.1    Enabling Tracing with the SAP HANA Cockpit

Within the SAP HANA cockpit, you can use the SAP HANA database explorer to configure trace settings and view trace log output. To access the SAP HANA database explorer in the SAP HANA cockpit, log on and click the Browse Database Objects tile on the Home screen, which will launch the SAP HANA database explorer interface, as shown in Figure 16.1.

On the top right of the SAP HANA database explorer, you’ll see connected databases similar to what’s shown in Figure 16.1. However, if no databases are listed, you’ll need to add one. To add an SAP HANA database system, click the large plus sign (+) on the right side of that section, which will launch the Add Databases popup window. The easiest way to add a system is by selecting one from the Cockpit Resources. This shortcut will take the registered system information from the SAP HANA cockpit manager and quickly add a database to the interface. Alternatively, you can add a nonregistered SAP HANA database using the Database Type dropdown list.

Once you have a database connected, right-click the database and click the Trace Configuration option. A new tab titled Trace Configuration will appear to the right of the database systems list, as shown in Figure 16.2.

Figure 16.1    SAP HANA Database Explorer in the SAP HANA Cockpit

Figure 16.2    Trace Configuration Found in the SAP HANA Database Explorer of the SAP HANA Cockpit

Locate the section within the Trace Configuration tab called User-Specific Trace. On the right side of this section, you’ll see an Add button. Click this button to configure the trace settings. The User Specific Trace Configuration window will appear, as shown in Figure 16.3.

Figure 16.3    User-Specific Trace Configuration Tab in SAP HANA Database Explorer of the SAP HANA Cockpit

Within the trace configuration window, you’ll need to configure several options to enable the user-specific trace. In the Context Name field, enter the custom name you want to use for a portion of the trace file that will be generated, for example, “USERAUTHTRACE.” In the Database User field, enter the name of a single database user within the system. Maintaining the Application User field isn’t necessary when performing an authorization trace. Use the Connection ID field to limit the trace to a specific connection ID; otherwise, keep this field blank.

To configure the trace as an authorization trace, set the trace level for authorization. In the section below the Hide components with default tracing option, you’ll see a table with three columns: Component, Default Trace Level, and System Trace Level, as shown at the bottom of Figure 16.3.

To configure an authorization trace, first locate the Authorization option under INDEXSERVER within the Component column. In the field just above the table, enter the search phrase “auth” to narrow the results. In the results, locate the row in which Authorization component is visible. Using the System Trace Level column for the same row, click the cell to reveal a dropdown list. In the dropdown list, choose Debug. Click OK at the bottom of the window to activate the authorization trace. The User-Specific Tracing section should now display User traces and will list the specific trace you just configured, with a pencil and X icon to its left, as shown in Figure 16.4.

Figure 16.4    User-Specific Trace Configuration Section with Active Trace Enabled

Remember to delete a trace when you’ve completed collecting trace data. To delete the trace, click the X icon next to the trace profile name (SYSTEMTRACE, as shown in Figure 16.4).

16.1.2    Enabling Tracing with SQL

User traces also can be configured using specific SQL statements. The trace configuration is stored in the global.ini configuration file. Using the ALTER SYSTEM ALTER CONFIGURATION SQL syntax, you can make configuration changes to this file and enable a user-specific trace.

The first configuration option that must be specified is the trace user name. To configure the specific user, you must create a trace profile with the sql_user option. The trace profile name is a concatenation of the static term traceprofile_ followed by the user-specific profile name. The profile name will be used with other settings and will be appended to the trace file name. The following SQL syntax can be used to construct the required SQL code for your environment:

alter system alter configuration ('global.ini','SYSTEM') 
SET ('traceprofile_<Profile Name>', 'sql_user') = '<User Name>' 
with reconfigure;

The <Profile Name> variable must be replaced with the name of the trace profile, and the <User Name> variable must be replaced with the name of a user within the SAP HANA system. The following example demonstrates the SQL for the trace profile named USERAUTHTRACE for the user TEST_SVC:

alter system alter configuration ('global.ini','SYSTEM') 
SET ('traceprofile_USERAUTHTRACE', 'sql_user') = 'TEST_SVC' 
with reconfigure;

Next, you must specify the trace level. The following SQL syntax can be used to construct the required SQL for your environment:

alter system alter configuration ('global.ini','SYSTEM') 
SET ('traceprofile_<Profile Name>', 'authorization') = 'debug' 
with reconfigure;

The <Profile Name> variable must be replaced with the name of the trace profile, which is the same profile name used in the previous example. The profile name must match the name used in other settings. The following example demonstrates the SQL code for the trace profile named USERAUTHTRACE:

alter system alter configuration ('global.ini','SYSTEM') 
SET ('traceprofile_USERAUTHTRACE', 'authorization') = 'debug' 
with reconfigure;

Remember to disable the trace when you’ve finished collecting trace details. To disable the trace profile with the name USERAUTHTRACE, use the following two SQL statements. The first statement removes the setting for a specific user:

alter system alter configuration ('global.ini', 'SYSTEM') 
unset ('traceprofile_USERAUTHTRACE', 'sql_user') with reconfigure;

The second statement removes the trace-level setting:

alter system alter configuration ('global.ini', 'SYSTEM') 
unset ('traceprofile_USERAUTHTRACE', 'authorization') with reconfigure;

Using SQL is a quick and easy way to enable an authorization trace within the SAP HANA system. In the following section, we’ll explore the process used to locate and review the trace files generated when the trace is active.

16.1.3    Viewing the Trace File in the SAP HANA Cockpit

Once the trace is enabled, you’ll need to perform a test using the user specified within the trace configuration. For example, you can log on as the user and attempt to recreate the action being denied. Conducting the troubleshooting exercise again is important for guaranteeing that the denied actions will be recovered in the trace file. Once you’ve concluded the troubleshooting, you can view the contents of the generated trace files.

Within SAP HANA database explorer of the SAP HANA cockpit, you’ll see trace files within the folder structure under a connected database in the Database Diagnostic Files folder. Under this folder, a subfolder will contain the host name of the SAP HANA server or servers. Expand the server host name node to reveal additional subfolders. The subfolders at this level will contain the name of the services active on the SAP HANA host, as shown in Figure 16.5. Click on each folder to reveal the trace files in the section below the search bar. Use the search bar to filter the list of trace files presented.

Figure 16.5    Viewing Trace Files within the Database Diagnostic Files Folder Node in the SAP HANA Database Explorer of the SAP HANA Cockpit

Using the Search field, you can narrow the list of trace files to those generated by the authorization trace. Fortunately, the trace profile name you used during the trace configuration also can be used to locate the files. For example, you can enter “SYSTEMTRACE” to narrow the list of files to those containing our authorization trace details.

Trace files use a file extension of .trc, which stands for trace. Once you’ve located the trace file in the results, click the file, which will open a text viewer tab to the right of the database connection pane. A new tab will appear, titled with the name of the trace file. The file will default to the last lines generated. Figure 16.6 shows an example of the data found in a trace file.

Figure 16.6    Detailed Data within Authorization Trace File

Once the file is open, The detailed data within the file isn’t always easy to decipher when the Debug trace level is specified, but typically you can press (Ctrl)+(F) to open a Find dialog window within the text viewer.

Using the Find dialog window, you locate the trace details related to your security model troubleshooting. In many cases, the phrases Return Not Authorized and Missing are located near the exact action and privilege being denied. You can also search for the specific SQL you executed, the name of the object you interacted with, or the privileges that you expect to be missing.

Using the Debug trace level can result in hundreds of thousands of lines during troubleshooting. If your trace file is too large for browser-based searching, right-click the trace file and choose the Download option to download the file. Once downloaded, you can open the file within a text-viewing application that offers more enhanced search capabilities.

When you’re done reviewing the trace file, don’t forget to disable the user-specific trace using the options described earlier in Section 16.1.

Tracing alone doesn’t always provide all the information you need. In addition to tracing, SAP HANA offers several system-managed catalog views that provide the effective privileges for a grantee. These views can be queried using SQL statements and filter conditioning within the WHERE clause. In the next section, we’ll look at each of these views in more detail.