db permission management & audit system
Note
This project is part of the "100 Commits" competition, which challenges participants to commit to their projects by making at least one commit every day for 100 consecutive days.
Do you know what permissions are currently set on your databases? Do you know when which permission was granted? Has someone accidentally granted too many permissions?
All authorization configuration should be saved in the authorization management system. Based on this configuration, the system automatically grants and revokes permissions. Even if the administrator grants permission directly in the database, the system will automatically receive it and send a notification about this fact. If the permission is added in the configuration, the system will automatically transfer it to the database.
A full audit with real-time permissions, not a post-factum analysis Granting permissions faster than those broadcast by Active Directory groups
The system is to be a central repository for various databases and will provide a unified system for granting permissions. Facilitates the transfer of permissions between different environments (DEV, TEST, PROD)
- This is PoC only for SQL Server
- Project is written as DacPack project
- SQL Server 2022 ( https://www.microsoft.com/pl-pl/sql-server/sql-server-downloads https://info.microsoft.com/ww-landing-sql-server-2022.html?culture=en-us&country=us)
- DataTools (https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver16)
- Visual Studion (unfortunately scripts for running without VS are not yet prepared)
- Deploy Project DataGuard (local.publish.xml)
- Deploy Project DataGuard.dbtest (local.publish.xml)
Unit test are writtent in T-SQLt framework (https://tsqlt.org/)
Quickest way to execute all test is
EXEC [tSQLt].[RunAll]
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Dur(ms)|Result |
+--+-------------------------------------------------------------------+-------+-------+
|1 |[test].[test conf.tDatabase if record change update LastModifiedOn]| 10|Success|
|2 |[test].[test conf.tDatabase try change CreatedBy] | 8|Success|
|3 |[test].[test conf.tDatabase try change CreatedOn] | 8|Success|
|4 |[test].[test database DataGuard existing] | 8|Success|
|5 |[test].[test database TestDataTest not existing] | 3|Success|
----------------------------------------------------------------------------------------
Test Case Summary: 5 test case(s) executed, 5 succeeded, 0 skipped, 0 failed, 0 errored.
----------------------------------------------------------------------------------------
On current state of project, you can get some middle information
USE [DataGuard]
GO
EXEC [dbo].[pGetLoginDiff]
EXEC [dbo].[pGetUserDiff] @DatabaseName = DataGuard