Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Allow granular targeting of the SQL Audit Log per User #100467

Closed
kevin-v-ngo opened this issue Apr 3, 2023 · 0 comments · Fixed by #101658
Closed

Allow granular targeting of the SQL Audit Log per User #100467

kevin-v-ngo opened this issue Apr 3, 2023 · 0 comments · Fixed by #101658
Assignees
@THardy98
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)

Comments

@kevin-v-ngo
Copy link

kevin-v-ngo commented Apr 3, 2023 •
edited by RoachietheSupportRoach
Loading

This issue tracks the enhancement to SQL Audit Logs that allows users to enable and disable auditing by logging per User/Role. Today, all SQL Activity statements are logged regardless of the statement type or role.

Not all database activity always interests auditors (e.g., non-human users); sifting and querying through a high-volume of logs becomes an unnecessary difficult task. Logging also becomes more expensive than necessary where the logging infrastructure must scale as the workload scales.

Epic: CRDB-7444

Jira issue: CRDB-26452

gz#16574
@kevin-v-ngo kevin-v-ngo added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Apr 3, 2023
@THardy98 THardy98 mentioned this issue May 26, 2023
Merged
@craig craig bot closed this as completed in f8556ee Jun 1, 2023
@blathers-crl blathers-crl bot mentioned this issue Jun 1, 2023
Merged
This was referenced Jun 5, 2023
Merged
Merged
craig bot pushed a commit that referenced this issue Jun 6, 2023
Merge #103759
530e2ef 
103759: sql: add `sql.log.user_audit.reduced_config.enabled` to support single role checks for role-based audit logs r=THardy98 a=THardy98

Part of: #100467

This change introduces the `sql.log.user_audit.reduced_config.enabled`
cluster setting. When enabled, this cluster setting computes a "reduced"
audit configuration based on the user's current role memberships and the
current value for the `sql.log.user_audit` cluster setting. The
"reduced" audit configuration is computed at the _first SQL event emit
by the user after the setting is enabled_. Enabling the cluster setting
allows us to compute the audit configuration once at session start,
instead of computing at each SQL event (providing ~5% increase in
throughput). The tradeoff is that changes to the audit configuration
(user role memberships or cluster setting configuration) are not
reflected within session. Users will need to start a new session to see
these changes in their auditing behaviour.

Release note (sql change): Introduce new cluster setting
`sql.log.user_audit.reduced_config.enabled`. When enabled, this cluster
setting computes a "reduced" audit configuration based on the user's
current role memberships and the current value for the
`sql.log.user_audit` cluster setting. The "reduced" audit configuration
is computed at the _first SQL event emit by the user after the setting
is enabled_. Enabling the cluster setting allows us to compute the audit
configuration once at session start, instead of computing at each SQL
event (providing ~5% increase in throughput). The tradeoff is that
changes to the audit configuration (cluster
setting) and the user's role memberships are not reflected within session. Users will need
to start a new session to see these changes in their auditing behaviour.

Co-authored-by: Thomas Hardy <[email protected]>
This was referenced Jun 6, 2023
Merged
Merged
craig bot pushed a commit that referenced this issue Jun 8, 2023
Merge #104383
1824811 
104383: auditloggingccl: migrate role-based audit logging as a CCL feature r=THardy98 a=THardy98

Part of: #100467

This change moves the existing role-based audit logging logic to be
consumed as a CCL (enterprise) feature.

The notable changes here are:
- moving the audit logging cluster settings (i.e. `sql.log.user_audit`
  and `sql.log.user_audit.reduced_config.enabled`) to the `ccl` package.
Consequently, these cluster settings will only exist when the use has a
CCL license (free or paid)
- gating the `sql.log.user_audit` cluster setting behind the enterprise
  CCL license. This was done by adding an enterprise CCL license check
in the `sql.log.user_audit` validation function. Users will be unable to
change this cluster setting (and thereby will not be able to enable/configure
role-based audit logging) unless they have an enterprise CCL license
-  the addition of function hooks to be used at CCL initialization,
   namely:
   - `ConfigureRoleBasedAuditClusterSettings`: used to add a
     `SetOnChange` hook to `sql.log.user_audit`
   - `UserAuditLogConfigEmpty`: used to check whether the audit logging
     cluster setting is empty
   - `UserAuditReducedConfigEnabled`: used to check whether the reduced
     audit configuration is enabled (note: regular users are still able
to enable/disable this cluster setting, but it will take no effect as
they will have no way to enable role-based audit logging)
   - `UserAuditEnterpriseParamsHook`: used to retrieve parameters
     necessary for enterprise license checks within the the
`sql.log.user_audit` cluster setting validation function

Release note (sql change): Role-based audit logging is now a CCL
(enterprise) feature. Only enterprise CCL users will be able to
configure role-based audit logging using the `sql.log.user_audit`
cluster setting.

Co-authored-by: Thomas Hardy <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@THardy98 THardy98

Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: introduce role-level audit logging THardy98/cockroach
2 participants
@THardy98 @kevin-v-ngo