N1QL Auditing

N1QL-related activities can be audited, by Couchbase Server.

Understanding N1QL Auditing

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

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

  • Authenticating

  • Starting and stopping the Query Service

  • Editing Query Service settings

  • Executing N1QL statements

  • Non-query API requests

N1QL-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 N1QL 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 Auditing. To capture N1QL-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 N1QL server.

aee53bf0-d009-4015-8a1d-efec74f2cd74

statement

The actual N1QL 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 N1QL command or REST API request type.

SELECT

description

Description of the event type.

A N1QL 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 N1QL 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 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.