Show SQL schema names example

You can use a session-level policy to find and display an SQL schema name. This example shows how to move the schema name to the GDM_CONSTRUCT_INSTANCE.APP_USER_NAME table and column so that it is available for reports.

This policy contains one rule with two rule actions. The rule actions work as follows:
  1. Copy the prefix :SC= and the value of DB USER into GDM_CONSTRUCT_INSTANCE.APP_USER_NAME.
  2. Extract the schema name from SQL statements and copy it to GDM_CONSTRUCT_INSTANCE.APP_USER_NAME too.
    Note: If the second rule action does not find the MATCH_PATTERN, the policy does not override the result of the first action.
  • Session level criteria:
    • Client IP address = 10.10.10.10
    • Server IP address = 20.20.2020
    • Database type = SAP HANA
  • Rule actions:
    1. TRANSFORM APP USER NAME
      • Source = DB USER
      • Output format = :SC=(.*)
    2. TRANSFORM APP USER NAME
      • Source = STATEMENT
      • Request type = SQL
      • Search prefix = SELECT
      • Search pattern = .M_DATABASE
      • Match pattern = .*(.*)\M_DATABASE
      • Output format = :SC=\1

SR language example

SR_POLICIES
{
         IF (CLIENT_IP = '10.10.10.10' SERVER_IP = '20.20.20.20' DB_TYPE = 'SAP_HANA') 
        {
                TRANSFORM_APP_USER SOURCE = DB_USER OUTPUT_FORMAT = ':SC=(.*)'

                TRANSFORM_APP_USER REQ_TYPE = SQL SEARCH_PREFIX = 'SELECT' SEARCH_PATTERN = '.M_DATABASE' 
                SOURCE = STATEMENT  MATCH_PATTERN = '.* (.*)\M_DATABASE' OUTPUT_FORMAT = ':SC=\1'
        }
}