Public role (msdb)
Job details
| Name: |
Public role (msdb) |
| Platform: |
Sqlserver |
| Category: |
Security |
| Premium package: |
Security and compliance package |
| Description: |
Checks that the public role in the msdb database is not granted access to SQL Agent proxies. |
| Long description: |
Checks that the public role in the msdb database is not granted access to SQL Agent proxies. SQL Agent proxies define a security context in which a job step can run. |
| Version: |
1.2 |
| Default schedule: |
16 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 public role in the msdb database is granted access to SQL Agent proxies. |
| revoke public login from proxy |
NO |
If set to “YES“ the alert will disable the login by running “ALTER LOGIN [login_name] DISABLE“. |
| history threshold |
365 |
The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this monitoring job is to ensure that the public role in the msdb database does not have access to SQL Agent proxies.
- Why: This job is important because SQL Agent proxies define a security context in which a job step can run. The access provided to public roles can potentially lead to unauthorized database operations or security loopholes, which may impact the integrity and security of the SQL Server environment.
- Manual Checking: You can check this manually in the database by issuing these SQL commands:
SELECT sp.name FROM msdb.dbo.sysproxylogin spl
JOIN msdb.sys.database_principals dp
ON dp.sid = spl.sid
JOIN msdb.dbo.sysproxies sp
ON sp.proxy_id = spl.proxy_id
WHERE principal_id = USER_ID('public')
Implementation Details
- Dependency Objects:
- “dbw_msdb_public_role_proc” as the main procedure.
- “dbw_msdb_public_role_info” as the current status table.
- “dbw_msdb_public_role_histr” as the history table.
- Code Implementation:
- Various SQL commands and procedures are implemented to monitor and optionally revoke the permissions of the public role if found accessing SQL Agent proxies.
Presentation in dbWatch
| Task Name: |
msdb public role |
| Description: |
The table shows SQL Agent proxies that the public role in the msdb database is granted access to. |
| __. Proxy Name: |
(Name of the proxy) |
| __. Date Discovered: |
(Date when the access was recorded) |
Monitoring Job Features
- Features:
- Monitoring access to SQL Agent proxies by the public role.
- Ability to revoke access if set via parameters.
- Storing history of the access for review.
- Automated cleanup of historical data based on set threshold.
- On-call security update and compliance table update depending on the findings.
Compliance and Upgrades
- Compliance:
- The job is intended to maintain access control and compliance regarding the security standards for SQL Server environments.
- Upgrade Path
- Modifications in the stored procedures to ensure compatibility and enhancement based on the SQL Server version upgrades or dbWatch updates. These include updates in logging, access revocation logic, and exception handling.
By employing this dbWatch job, organizations can significantly strengthen their SQL Server database security posture by continually monitoring and controlling access to critical SQL Agent functionalities.