SQL++ Auditing
- Capella Operational
- reference
SQL++-related activities can be audited, by Couchbase Capella.
Understanding SQL++ Auditing
This section provides specific information on Couchbase Capella auditing as it relates to SQL++. For a general description of auditing with Couchbase Capella, see Audit Events.
Couchbase Capella provides auditing for SQL++-related activities such as the following:
-
Authenticating
-
Starting and stopping the Query Service
-
Editing Query Service settings
-
Executing SQL++ statements
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.
You can configure auditing by means of the Management REST API: see Manage Audits. You can audit Query and Index service events that are issued through the Query tab, the cbq shell, and the SDK.
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.
| Field | Description | Example |
|---|---|---|
|
Exact date and time of the access event in UTC format. |
|
|
Source/User from basic authentication fields of request. |
|
|
UUID of request, generated by the SQL++ server. |
|
|
The actual SQL++ query that was executed. |
|
|
|
|
|
To identify the type of user by a combination of the User-Agent and CB-User-Agent headers in one of the following formats:
|
|
|
Assigned name (IP address) of the server where the request ran. |
<redacted> |
|
Status of the request, as |
|
|
The elapsed time (ms), execution time (ms), result count, and result size (MB). |
|
|
Number for the audit event type. |
|
|
The SQL++ command or REST API request type. |
|
|
Description of the event type. |
|
| Field | Description | Example |
|---|---|---|
|
Names and values of name arguments. |
|
|
Array of values of positional arguments. |
|
|
Captured from the May be used to distinguish between user-generated queries and UI-generated queries from the Query tab. UI-generated queries have the prefix |
| 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. |
Examples
To reduce disk usage and improve performance, the log files are as compact as possible.
To make the log entry easier-to-read, use a formatting utility such as jq.
Execute SELECT * FROM orders.
json{
"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"
}
Execute DELETE FROM orders WHERE priority = 6.
json{
"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"
}
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:
|
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 Capella, and the file space is recovered.
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 — see Query and Index Service Events in the Server documentation
-
Audit archive
-
System backup
-
Data service:
-
Read
-
Write
-
DCP-Read
-
DCP-Write
-
-
Search service:
-
FTS-Read
-
-
Analytics audit events