XMLWordPrintable

    Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: EXASolution 5.0
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Explanation:
      Hide

      Auditing

      The best way to protect your data against misuse is to invest in appropriate database design and counter-measures, such as :

      • implementation of a user management process that distinguishes between users and administrators,
      • design and development of a comprehensive rights management system,
      • granting of permissions only if required,
      • limiting and securing third-party access
      • separation of data and views: data will be stored separately and only accessed by the user through the views.

      Additionally it is important to log and audit data access so as to identify the data misuse early. At the same time you have to consider the protection of privacy, because the logs will contain personal data.

      Database Auditing techniques

      There are three major techniques for database auditing:

      • Trace-based
        This technique is usually built natively into the DBMS. You have to define which objects have to be audited, therefore the database schema will need to be modified. Performance issues and insufficient granularity of audit control, especially for reads are major drawbacks of this method.
      • Scan Transaction Logs
        Here, some (3rd party) software scans and parses transaction logs. If you manage to disable transaction logging, no information will be captured. Moreover, transaction logs are intended to be retained for a sort time (for database recovery), whereas auditing data has a long-term retention period. Transaction logging itself can slow down the database.
      • Proactive Monitoring of DB operations
        This technique captures all SQL requests as they are made. Proactive audit monitoring does not require transaction logs, does not require database schema modification, and should incur only minimal overhead.

      Auditing in EXASolution

      Because of EXASolution's focus on high performance data warehousing we decided to implement the third technique.

      We found the following basic conditions to typically apply to data warehouses:

      • Performance is the most important factor and should not be impacted by auditing
      • The comprehensive right management system of EXASolution should be configured to prohibit the user to change data with exception of his or her own.
      • The user should access data via views, whereby the corresponding data is "read only" accessible. or even not accessible at all.
      • Data should be changed by a centralized process, which logs the changes itself for the purpose of reporting.

      Auditing can be switched on via EXAoperation. This can be done to any time.

      The data will be logged into special system tables, you can analyse the auditing data within the database.

      • EXA_SYSTEM_EVENTS
        logs system events such as database start and stop, backup, etc. This will always be done.
      • EXA_DBA_AUDIT_SESSIONS
        lists all sessions, if the auditing is switched on in EXAoperation.
      SESSION_ID ID of the session
      LOGIN_TIME Login time
      LOGOUT_TIME Logout time
      USER_NAME User name
      CLIENT Client application used by the user
      DRIVER Driver used by the user
      ENCRYPTED Flag indicating whether the connection is encrypted
      HOST Computer name or IP address from which the user has logged-in
      OS_USER User name under which the user logged into the operating system of the computer from which the login came
      OS_NAME Operating system of the client server
      SUCCESS TRUE: Login was successfully,
        FALSE: Login failed (e.g. because of a wrong password)
      ERROR_CODE Error code if the login failed
      ERROR_TEXT Error text if the login failed
      • EXA_DBA_AUDIT_SQL
        lists all executed SQL statements if the auditing is switched on in EXAoperation.
      SESSION_ID Id of the session
      STMT_ID Sequentially numbered ID of the statement within a session
      COMMAND_NAME Name of the statement (e.g. SELECT, COMMIT, MERGE etc.)
      COMMAND_CLASS Class of statement (e.g. DQL, TRANSACTION, DML etc.)
      START_TIME Start time of the statement
      STOP_TIME Stop time of the statement
      CPU CPU utilization in percent
      TEMP_DB_RAM_PEAK Maximum usage of temporary DB memory of the query in MB (cluster wide)
      HDD_READ Hard disk read ratio in MB per second (per node, averaged over the duration). If this value is larger than 0, then data had to be loaded into the main memory.
      HDD_WRITE Hard disk write ratio in MB per second (per node, averaged over the duration). This column reflects only the data written during a COMMIT. For other statements its value is NULL.
      NET Network traffic ratio in MiB per second (sum of send/receive, per node, averaged over the duration)
      SUCCESS TRUE: Statement was executed successfully,
        FALSE: Statement failed (e.g. with a data exception)
      ERROR_CODE Error code if the statement failed
      ERROR_TEXT Error text if the statement failed
      SCOPE_SCHEMA Schema where the user was located
      PRIORITY Priority group
      NICE NICE attribute
      RESOURCES Allocated resources in percent
      ROW_COUNT Number of result rows for queries, or number of affected rows for DML and DDL statements
      EXECUTION_MODE EXECUTE : Normal execution of statements
        PREPARE : Prepared phase for prepared statements
        CACHED: Query which accesses the Query Cache
        PREPROCESS : Execution of the Preprocessor script
      SQL_TEXT SQL text limited to 2,000,000 characters
      Show
      Auditing The best way to protect your data against misuse is to invest in appropriate database design and counter-measures, such as : implementation of a user management process that distinguishes between users and administrators, design and development of a comprehensive rights management system, granting of permissions only if required, limiting and securing third-party access separation of data and views: data will be stored separately and only accessed by the user through the views. Additionally it is important to log and audit data access so as to identify the data misuse early. At the same time you have to consider the protection of privacy, because the logs will contain personal data. Database Auditing techniques There are three major techniques for database auditing: Trace-based This technique is usually built natively into the DBMS. You have to define which objects have to be audited, therefore the database schema will need to be modified. Performance issues and insufficient granularity of audit control, especially for reads are major drawbacks of this method. Scan Transaction Logs Here, some (3rd party) software scans and parses transaction logs. If you manage to disable transaction logging, no information will be captured. Moreover, transaction logs are intended to be retained for a sort time (for database recovery), whereas auditing data has a long-term retention period. Transaction logging itself can slow down the database. Proactive Monitoring of DB operations This technique captures all SQL requests as they are made. Proactive audit monitoring does not require transaction logs, does not require database schema modification, and should incur only minimal overhead. Auditing in EXASolution Because of EXASolution's focus on high performance data warehousing we decided to implement the third technique. We found the following basic conditions to typically apply to data warehouses: Performance is the most important factor and should not be impacted by auditing The comprehensive right management system of EXASolution should be configured to prohibit the user to change data with exception of his or her own. The user should access data via views, whereby the corresponding data is "read only" accessible. or even not accessible at all. Data should be changed by a centralized process, which logs the changes itself for the purpose of reporting. Auditing can be switched on via EXAoperation. This can be done to any time. The data will be logged into special system tables, you can analyse the auditing data within the database. EXA_SYSTEM_EVENTS logs system events such as database start and stop, backup, etc. This will always be done. EXA_DBA_AUDIT_SESSIONS lists all sessions, if the auditing is switched on in EXAoperation. SESSION_ID ID of the session LOGIN_TIME Login time LOGOUT_TIME Logout time USER_NAME User name CLIENT Client application used by the user DRIVER Driver used by the user ENCRYPTED Flag indicating whether the connection is encrypted HOST Computer name or IP address from which the user has logged-in OS_USER User name under which the user logged into the operating system of the computer from which the login came OS_NAME Operating system of the client server SUCCESS TRUE: Login was successfully,   FALSE: Login failed (e.g. because of a wrong password) ERROR_CODE Error code if the login failed ERROR_TEXT Error text if the login failed EXA_DBA_AUDIT_SQL lists all executed SQL statements if the auditing is switched on in EXAoperation. SESSION_ID Id of the session STMT_ID Sequentially numbered ID of the statement within a session COMMAND_NAME Name of the statement (e.g. SELECT, COMMIT, MERGE etc.) COMMAND_CLASS Class of statement (e.g. DQL, TRANSACTION, DML etc.) START_TIME Start time of the statement STOP_TIME Stop time of the statement CPU CPU utilization in percent TEMP_DB_RAM_PEAK Maximum usage of temporary DB memory of the query in MB (cluster wide) HDD_READ Hard disk read ratio in MB per second (per node, averaged over the duration). If this value is larger than 0, then data had to be loaded into the main memory. HDD_WRITE Hard disk write ratio in MB per second (per node, averaged over the duration). This column reflects only the data written during a COMMIT. For other statements its value is NULL. NET Network traffic ratio in MiB per second (sum of send/receive, per node, averaged over the duration) SUCCESS TRUE: Statement was executed successfully,   FALSE: Statement failed (e.g. with a data exception) ERROR_CODE Error code if the statement failed ERROR_TEXT Error text if the statement failed SCOPE_SCHEMA Schema where the user was located PRIORITY Priority group NICE NICE attribute RESOURCES Allocated resources in percent ROW_COUNT Number of result rows for queries, or number of affected rows for DML and DDL statements EXECUTION_MODE EXECUTE : Normal execution of statements   PREPARE : Prepared phase for prepared statements   CACHED: Query which accesses the Query Cache   PREPROCESS : Execution of the Preprocessor script SQL_TEXT SQL text limited to 2,000,000 characters
    • Category 1:
      Database Administration

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: