Looking to see if anyone has integrated MS SQL Database access to Via L&G v6.9.1 P10. We are in the process of developing the queries to pull in the raw data, however, we are getting hung up when it comes to integrating in Via, specifically as it relates to Server and DB Principals.
I'm not an engineer or developer, so apologies if I butcher the technicalities. From my understanding of the various securables (https://technet.microsoft.com/en-us/library/ms191465.aspx), a given individual may have a server login that may or may not grant some level of server permission, and may or may not have privileges in 1 to many databases on the server.
Our initial thought was to integrate each database as an application resource, however, quickly realizing that the complexity of the SQL Server security model doesn't play nicely with out of box L&G collection, review and provisioning processes.
Taking an example of a SQL Server with 5 databases:
- A user has access to 5 databases, a revoke from a single database should only request that that database principle is disabled.
- If the user only has access to a single database, and no permissions at the server level, both the database and server principals should be disabled.
- If a user with access to a single database as well as server permissions, and requires their database access be removed, only the database principal should be disabled, if we assume the server permissions were retained.
These are a few scenarios that describe the complexities we've found with the integration design. While I can envision ways to collect the data, I'm not seeing the end user experience or review/provisioning processes to be positive without significant training and manual processes layered on top of the L&G functionality.
Has anyone found a way to elegantly integrate that results in a easy to understand (de)provisioning and review process?
Thanks,
Bill
v6.9.1 P10
As far as I know, there's not a real easy way of gathering this information. I've tasked our database administrators to pull all access information (local accounts/groups, logon, user security, roles, schema, etc) down to a table level I believe. They've created a job that will grab everything off of many SQL boxes and dump it into a centralized repository (read only).
I am about to start the first stages of testing, so I'll be sure to let you know how it goes. I am not certain if you were to purchase the full-blown StealthAudit product, if it would have that capability; but fortunate that our DBA's had the bandwidth to create a script.
I am thinking of breaking this up into several collectors:
As far as provisioning access, I plan on keeping manual fulfillment for the time being.
I hope to have some data collected before 10/31/16, so I'll make another post with some findings. If you have any feedback or thoughts, please let me know.