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

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`

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

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.`

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:

Copy the log entry to the clipboard
Open Query Workbench
Type SELECT
Paste the query from the clipboard
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 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.

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.

System clock modifications as captured in the operating system audit log
Disabling Auditing
Enabling Auditing, with audit settings written
Login, both Success and Failure
Logout, both Success and Failure

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

Audit-Archive
System-Backup
Data service
1. Read
2. Write
3. DCP-Read
4. DCP-Write
FTS Service
1. FTS-Read
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.