AnsweredAssumed Answered

MS SQL Server Collection

Question asked by William May on Sep 27, 2016
Latest reply on Oct 10, 2016 by William May

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:

  1. A user has access to 5 databases, a revoke from a single database should only request that that database principle is disabled.
  2. 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.
  3. 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

Outcomes