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 |
---|---|---|
|
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 four formats: 1) CURL request 2) Query Workbench 3) CBQ shell 4) SDK |
1) 2) 3) 4) |
|
Assigned name (IP address) of the server where the request ran.
|
|
|
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. |
|
Optional auditing fields for statements:
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 WorkBench. UI-generated queries have the prefix
|
Required auditing fields for API requests:
Field | Description | Example |
---|---|---|
|
Exact date and time of the access event in UTC format. |
|
|
Source/User from basic authentication fields of request. |
|
|
The API method call, either |
|
|
The number representing the API result. |
|
|
If an error occurred, this will contain information on the error. |
|
|
Number for the API auditing code. |
|
|
The API request location. |
|
|
Description of the event type. |
|
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 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 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
-
Read
-
Write
-
DCP-Read
-
DCP-Write
-
-
FTS Service
-
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 |
|
Field "stat": optional, string, for input parameter {stat} if present. |
28690 |
|
|
28691 |
|
Field "name": optional, string, for input parameter {name} if present. Do not audit POST requests. |
28692 |
|
Field "request": optional, string, for input parameter {request} if present. Do not audit POST requests. |
28693 |
|
|
28694 |
|
|
28695 |
|
|
28696 |
|
|
28697 |
|
|
28698 |
|
|
28699 |
|
|
28700 |
|
|
28701 |
|
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 |
|
Field "request": optional, string, for input parameter {request} if present. Do not audit POST requests. |