Guest database users
Job details
| Name: |
Guest database users |
| Platform: |
Sqlserver |
| Category: |
Security |
| Premium package: |
Security and compliance package |
| Description: |
Checks that CONNECT permission (in every non system database) are revoked for the guest user. |
| Long description: |
Checks that CONNECT permission (in every non system database) are revoked for the guest user so that a login is not able to access database information without being mapped to a database user explicitly. |
| Version: |
1.2 |
| Default schedule: |
12 1 1 * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ |
engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name |
Default value |
Description |
| return status |
1 |
Return status value (ALARM – 2, WARNING – 1, or OK – 0) when CONNECT permission for the guest (in all non system databases) are not revoked. |
| revoke CONNECT |
NO |
If set to “YES“ the alert will revoke “CONNECT“ permission for guest user in every non system database by running “REVOKE CONNECT FROM guest”. |
| history threshold |
365 |
The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this job is to ensure that the CONNECT permission for the guest user is appropriately managed across all non-system databases.
- Why: This job is critical as it helps maintain database security by ensuring that unauthorized users do not have access rights. If the guest user retains CONNECT permission, it could lead to unauthorized access and potential data breaches.
- Manual checking: You can check the CONNECT permission status for the guest user manually by running the following SQL commands:
SELECT name, permission_name, state_desc FROM [DatabaseName].sys.database_permissions WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('guest')
Details of Implementation
- The main component of this monitoring job is a stored procedure named “dbw_guest_db_user_proc” which:
- Checks whether the guest user has CONNECT permission in each non-system database.
- Depending on the configuration, it may revoke the CONNECT permission to enhance security.
- Updates monitoring tables to log current permission settings and changes.
- Two other essential tables (“dbw_guest_database_user_info” and “dbw_guest_database_user_histr”) are involved:
- “dbw_guest_database_user_info” captures current permissions information.
- “dbw_guest_database_user_histr” maintains a history of checks and changes over time.
| Tables |
Description |
| “dbw_guest_database_user_info” |
Stores current permission settings |
| “dbw_guest_database_user_histr” |
Logs historical data of permission checks |
Monitoring and Reporting
- The dbWatch report template generates visualizations and reports describing:
- The databases where the GUEST user has CONNECT permission.
- Historical data showing the times each database was checked for CONNECT permissions.
- “Guest database user“ is the main title used for presenting this information, outlined in presentations within the job template.
Operational Details
- Default Schedule: The job is scheduled to run monthly, further ensuring that the permissions are regularly reviewed and managed.
- Cleanup Operations: If any part of the job fails, clean-up operations are triggered to ensure there is no residual or half-completed data processing left, which could affect subsequent checks.
- Dependencies and Executions:
- The successful implementation of changes, if any (like revoking permissions), depends on the configurations set within the dbWatch Control Center parameters, specifically whether the “revoke CONNECT“ parameter is set to “YES“.
Overall, this dbWatch job plays a crucial role in maintaining the security posture of databases by managing and reporting on the CONNECT permissions of guest database users across server databases.