SQL++ Auditing

  • reference
    +
    SQL++-related activities can be audited, by Couchbase Server.

    Understanding SQL++ Auditing

    This section provides specific information on Couchbase Server auditing as it relates to SQL++. For a general description of configuring auditing with Couchbase Web Console, see Authorization.

    Couchbase Server provides auditing for SQL++-related activities such as the following:

    • Authenticating

    • Starting and stopping the Query Service

    • Editing Query Service settings

    • Executing SQL++ statements

    • Non-query API requests

    SQL++-related activities are logged whether they are executed by a person or by an application running on behalf of a person. Auditing occurs at the level of requests, rather than of operations. Thus, when a request arrives with a SELECT query, only the SELECT query itself is logged: the associated subsidiary operations performed by the Data and Index Services are not logged.

    Auditing causes a reduction in SQL++ query-performance. This is in the range of 9% to 17% of queries performed per second: the exact reduction depends on query-size, and on the amount of auditing that has been enabled. Large queries and minimal auditing cause less performance-reduction.

    Auditing can be configured by means of Couchbase Web Console: see the information provided in Manage Auditing. To capture SQL++-related events, use the Query and Index Service panel. Events available to be audited include ones issued through the SDK, the Query workbench, and the Query Shell.

    Audit Log Format

    The audit records are written in JSON format to match the format used for Admin Auditing to allow easy integration with downstream auditing tools for audit log analysis. The syslog format will allow for integration with third party SIEM tools, such as QRadar.

    Required auditing fields for executed statements:

    Field Description Example

    timestamp

    Exact date and time of the access event in UTC format.

    2018-02-09T14:52:35.163-08:00

    real_userid

    Source/User from basic authentication fields of request.

    "source":"local",

    "user":"Administrator"

    requestId

    UUID of request, generated by the SQL++ server.

    aee53bf0-d009-4015-8a1d-efec74f2cd74

    statement

    The actual SQL++ query that was executed.

    SELECT * FROM `travel-sample`

    isAdHoc

    TRUE for statements made directly.

    FALSE for prepared statements.

    TRUE

    userAgent

    To identify the type of user by a combination of the User-Agent and CB-User-Agent headers in one of the four formats:

    1) CURL request

    2) Query Workbench

    3) CBQ shell

    4) SDK

    1) curl/7.43.0

    2) Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36 (Couchbase Query Workbench (5.1.0-1434-enterprise))

    3) Go-http-client/1.1 (CBQ/2.0)

    4) couchbase-java-client/2.5.2 (git: 2.5.2, core: 1.5.2) (Mac OS X/10.11.6 x86_64; Java HotSpot(TM) 64-Bit Server VM 1.8.0_101-b13)

    node

    Assigned name (IP address) of the server where the request ran.

    local for unclustered nodes.

    local

    status

    Status of the request, as success or failed or stopped.

    success

    metrics

    The elapsed time (ms), execution time (ms), result count, and result size (MB).

    "elapsedTime":"7.599684ms",

    "executionTime":"7.507755ms",

    "resultCount":0,

    "resultSize":0

    id

    Number for the audit event type.

    28672

    name

    The SQL++ command or REST API request type.

    SELECT

    description

    Description of the event type.

    A SQL++ SELECT statement was executed

    Optional auditing fields for statements:

    Field Description Example

    namedArgs

    Names and values of name arguments.

    $val and $user

    positionalArgs

    Array of values of positional arguments.

    $1 and ?

    clientContextId

    Captured from the client_context_id parameter of the SQL++ query API.

    May be used to distinguish between user-generated queries and UI-generated queries from the Query WorkBench.

    UI-generated queries have the prefix INTERNAL- in this field.

    The client context ID has no security guarantees. The parameter can be set by any user in any request and is not verified in the server, so it should not be relied upon for security purposes.

    Required auditing fields for API requests:

    Field Description Example

    timestamp

    Exact date and time of the access event in UTC format.

    2018-02-09T14:52:35.163-08:00

    real_userid

    Source/User from basic authentication fields of request.

    "source":"local","user":"Administrator"

    httpMethod

    The API method call, either GET, PUT, DELETE, POST

    GET

    httpResultCode

    The number representing the API result.

    200

    errorMessage

    If an error occurred, this will contain information on the error.

    User does not have credentials to run queries accessing the system tables.

    id

    Number for the API auditing code.

    28689

    name

    The API request location.

    /admin/ping

    description

    Description of the event type.

    An HTTP request was made to the API at /admin/ping.

    Examples

    To reduce disk usage and improve performance, the log files are as compact as possible.

    When viewed through Query Workbench, the logs are formatted and indented for easier reading.

    Example 1: Execute SELECT * FROM orders via a CURL statement.

    {"timestamp":"2018-02-09T14:52:35.163-08:00","real_userid":{"source":"local","user":"Administrator"},"requestId":"aee53bf0-d009-4015-8a1d-efec74f2cd74","statement":"SELECT * FROM orders","isAdHoc":true,"userAgent":"curl/7.43.0","node":"local_node","status":"success","metrics":{"elapsedTime":"7.599684ms","executionTime":"7.507755ms","resultCount":0,"resultSize":0},"id":28672,"name":"SELECT statement","description":"A N1QL SELECT statement was executed”}

    To make the log entry easier-to-read:

    1. Copy the log entry to the clipboard

    2. Open Query Workbench

    3. Type SELECT

    4. Paste the query from the clipboard

    5. Click the Execute button

    [
      {
        "$1": {
          "description": "A N1QL SELECT statement was executed",
          "id": 28672,
          "isAdHoc": true,
          "metrics": {
            "elapsedTime": "7.599684ms",
            "executionTime": "7.507755ms",
            "resultCount": 0,
            "resultSize": 0
          },
          "name": "SELECT statement",
          "node": "local_node",
          "real_userid": {
            "source": "local",
            "user": "Administrator"
          },
          "requestId": "aee53bf0-d009-4015-8a1d-efec74f2cd74",
          "statement": "SELECT * FROM orders",
          "status": "success",
          "timestamp": "2018-02-09T14:52:35.163-08:00",
          "userAgent": "curl/7.43.0"
        }
      }
    ]

    Example 2: Execute DELETE FROM orders WHERE priority = 6 via a CURL statement

    {"timestamp":"2018-02-09T14:52:55.786-08:00","real_userid":{"source":"local","user":"Administrator"},"requestId":"ded68ae3-d964-4d87-b1c2-70cf72041c6b","statement":"DELETE FROM orders WHERE priority = 6","isAdHoc":true,"userAgent":"curl/7.43.0","node":"local_node","status":"success","metrics":{"elapsedTime":"8.884558ms","executionTime":"8.853976ms","resultCount":0,"resultSize":0},"id":28678,"name":"DELETE statement","description":"A N1QL DELETE statement was executed"}
    [
      {
        "$1": {
          "description": "A N1QL DELETE statement was executed",
          "id": 28678,
          "isAdHoc": true,
          "metrics": {
            "elapsedTime": "8.884558ms",
            "executionTime": "8.853976ms",
            "resultCount": 0,
            "resultSize": 0
          },
          "name": "DELETE statement",
          "node": "local_node",
          "real_userid": {
            "source": "local",
            "user": "Administrator"
          },
          "requestId": "ded68ae3-d964-4d87-b1c2-70cf72041c6b",
          "statement": "DELETE FROM orders WHERE priority = 6",
          "status": "success",
          "timestamp": "2018-02-09T14:52:55.786-08:00",
          "userAgent": "curl/7.43.0"
        }
      }
    ]

    Example 3: Make an HTTP GET method from an /admin/ping API request.

    {"timestamp":"2018-02-09T14:53:10.856-08:00","real_userid":{"source":"internal","user":"unknown"},"httpMethod":"GET","httpResultCode":200,"errorMessage":"","id":28697,"name":"/admin/ping API request","description":"An HTTP request was made to the API at /admin/ping."}
    [
      {
        "$1": {
          "description": "An HTTP request was made to the API at /admin/ping.",
          "errorMessage": "",
          "httpMethod": "GET",
          "httpResultCode": 200,
          "id": 28697,
          "name": "/admin/ping API request",
          "real_userid": {
            "source": "internal",
            "user": "unknown"
          },
          "timestamp": "2018-02-09T14:53:10.856-08:00"
        }
      }
    ]

    Audit Rotation

    The auditing Rotation parameters can be only one of the following:

    Audit Log Rotation Type Examples

    Time-based (days)

    7 (for weekly); 30 (for monthly).

    Size-based (MB)

    10 (for 10 MB); 10000 (for 10 GB).

    Audit Failure Semantics

    When the audit target fails, the auditing system can be set to one of the following:

    Failure Response Type Description

    Ignore

    Continue the action without firing an audit record.

    Block

    Cancel the operation.

    Log Reuse

    This option is for out-of-space failures:

    • Time-Based: Limit audit logs to the specified number of recent days.

    • Size-Based: Limit audit log size to the specified number of megabytes.

    If an audit record attempt fails in the query engine, an error message will be printed to the query.log file.

    Audit Trail Protection

    To prevent unauthorized modification of the audit service configuration, the auditing system restricts access to configuring only to Full and Local User Security Administrators.

    Audit records are immutable since the auditing system prevents changes of audit event records once written.

    Once archived, audit data is deleted from Couchbase, and the file space is recovered.

    The cbcollect_info utility does not collect audit logs.

    Audit Event Types

    Below is the list of all events that are captured in the audit logs.

    1. System clock modifications as captured in the operating system audit log

    2. Disabling Auditing

    3. Enabling Auditing, with audit settings written

    4. Login, both Success and Failure

    5. Logout, both Success and Failure

    6. Data access operations

      a. Query Service Event ID

      ALTER INDEX

      28683

      BUILD INDEX

      28684

      CREATE INDEX

      28681

      CREATE PRIMARY INDEX

      28688

      DELETE

      28678

      DROP INDEX

      28682

      EXPLAIN

      28673

      GRANT

      28685

      INFER

      28675

      INSERT

      28676

      MERGE

      28680

      PREPARE

      28674

      REVOKE

      28686

      SELECT

      28672

      UNRECOGNIZED

      28687

      UPDATE

      28679

      UPSERT

      28677

      b. API Request Event ID

      /admin/active_requests

      28692

      /admin/clusters

      28701

      /admin/completed_requests

      28702

      /admin/config

      28698

      /admin/indexes/active_requests

      28694

      /admin/indexes/completed_requests

      28702

      /admin/indexes/prepareds

      28693

      /admin/ping

      28697

      /admin/prepareds

      28691

      /admin/settings

      28700

      /admin/ssl_cert

      28699

      /admin/stats

      28689

      /admin/vitals

      28690

    7. Audit-Archive

    8. System-Backup

    9. Data service

      1. Read

      2. Write

      3. DCP-Read

      4. DCP-Write

    10. FTS Service

      1. FTS-Read

    11. Analytics audit events

      Items that will not be captured in the audit logs:

      • API calls that are not statements

      • API requests sent to URLs the query engine does not service

      • API requests which are handled by the autonomic functionality of the HTTP server

    API Auditing Codes

    Audit records will be issued by the query engine for requests to its secondary APIs. This does not include the main URL used for queries (/query/service) but does include all other URLs the query engine listens to.

    There will be a separate audit record code for each registered URL. The mapping from URLs to audit record codes is given below. Some URLs require extra fields, as noted.

    Audit Code API Remarks

    28689

    /admin/stats

    /admin/stats/{stat}

    Field "stat": optional, string, for input parameter {stat} if present.

    28690

    /admin/vitals

    28691

    /admin/prepareds

    /admin/prepareds/{name}

    Field "name": optional, string, for input parameter {name} if present.

    Do not audit POST requests.

    28692

    /admin/active_requests

    /admin/active_requests/{request}

    Field "request": optional, string, for input parameter {request} if present.

    Do not audit POST requests.

    28693

    /admin/indexes/prepareds

    28694

    /admin/indexes/active_requests

    28695

    /admin/indexes/completed_requests

    28696

    /debug/vars

    28697

    /admin/ping

    28698

    /admin/config

    28699

    /admin/ssl_cert

    28700

    /admin/settings

    28701

    /admin/clusters

    /admin/clusters/{cluster}

    /admin/clusters/{cluster}/nodes

    /admin/clusters/{cluster}/nodes/{node}

    Field "cluster": optional, string, for input parameter {cluster} if present.

    Field "node": optional, string, for input parameter {node} if present.

    Field "body": PUT/POST only, JSON representation of cluster or node from request body.

    28702

    /admin/completed_requests

    /admin/completed_requests/{request}

    Field "request": optional, string, for input parameter {request} if present.

    Do not audit POST requests.