1

I'm looking for the best way to secure access to the data stored into an SQL Server database (accessed via stored-procedures).

Company's users are split into 3 entities with associated Windows security groups: G_SUBSIDIARY1, G_SUBSIDIARY2, and G_HOLDING.

Each application is associated with a Windows security group: G_APPLICATION1, G_APPLICATION2, G_APPLICATION3...

These security groups are mostly used to control access to shared folders dedicated to each entity and application.

The stored-procedures should filter data depending on who request them.
As an example to access data in table APPLICATION1_DATA you must be a member of group G_APPLICATION1.
And depending on your entity you will access a different subset of the data (discriminator is a column with the entity to which the data belongs).

To apply authorization I was thinking of relying only on Windows security groups and checking with the IS_MEMBER function if the current user (authenticated via Windows authentication) belongs to each: IS_MEMBER("COMPANY\G_APPLICATION1") and IS_MEMBER("COMPANY\G_SUBSIDIARY1").
The main advantages I see is that there is only one object to manage, and if later users are added or removed this is transparent on the database side.

But I wonder if there is any drawbacks with this method, and if on the contrary there would be some advantages to use SQL Server roles in addition to these security groups.
It would add some maintenance to keep them up-to-date but it might be worth it...

Pragmateek
  • 13,174
  • 9
  • 74
  • 108
  • 1
    Does your SQL Server use AD Authentication? And does your application run as the Windows user? The nice thing about using the security inside SQL server is that if the user is in a group that doesn't have access to the object or is denied from that object, then they can't even see that it's there, even if it's linked in an object they do have access to. It does require that new database objects are given the appropriate privileges, but permissions can get very granular. And just letting the app access the database relies on future developers appropriately limiting database access. – Shawn Sep 25 '18 at 17:27
  • 1
    To be clearer, SQL Server can use Windows Login or Mixed-Mode, that allows both Windows and SQL logins. Drawback to SQL login is that it is a single password for potentially multiple users. Not good for auditing. Plus, SQL login is managed inside of the SQL server itself. It really depends on what you want to protect and how. – Shawn Sep 25 '18 at 17:35
  • @Shawn If by "**AD Authentication**" you mean what is called "**Windows Authentication**" in SSMS connection popup then yes. :) Yes the applications (mainly Excel spreadsheets) are run with the user's identity. OK so you mean that Windows authentication would be more lenient, that's not really an issue. As data retrieval is managed by stored-procedures, applications are not critical to enforce security, even if they are cracked, no data will be retrieved as the stored-procedure would reject the call or limit the data to what the user can see. – Pragmateek Sep 25 '18 at 17:38
  • Yes you're right, we currently have some SQL Server authentication and we can't easily manage the security, as when you log in with a SQL Server login the stored-procedures can't know who is the real Windows user behind it. It's why we are migrating to Windows authentication to enforce a stronger and more granular security. And yes you're right for auditing this is far better too, thanks for mentionning it. :) – Pragmateek Sep 25 '18 at 17:42
  • 1
    Just noticed your line "The stored-procedures should filter data depending on who request them.". What version of SQL server? And is the data in multiple tables or all in one table? Are you the database manager or do you have a different team working on app and db? Security will require a lot of coordination between the two. You may still have only one object to manage, but it will require some front-end setup. – Shawn Sep 25 '18 at 17:42
  • @Shawn It's **SQL Server 2008**, but I guess what's your point: **Row Level Security**? :) We will implement it ourselves in stored-procedures with simple **joins**. Data for both entities are all in the same table which has a discriminator column. And even if later there is multiple tables we can adapt the stored-procedures easily, we have full control on their code. We are two separated teams and yes you guess right, this is not always easy to get our changes implemented on time, it's why having to setup Windows security groups once without having to update the database would be nice. – Pragmateek Sep 25 '18 at 17:48
  • 1
    Using Windows security is much simplified by using Active Directory groups, then you give those AD groups logins to SQL Server and allow that user access to appropriate resources. If the application uses a service account (which it probably does, or should) then it will be who is accessing your database. If each application has it's own service account, then you can limit database access based on who has access to that application, but I don't think it will pass the actual user through to the database. That user is probably controlled through it's database connection properties. – Shawn Sep 25 '18 at 18:09
  • Security Groups get very complicated very quickly and make a great example for white-board use. – Shawn Sep 25 '18 at 18:10
  • 1
    Also, SQL 2016+ is able to actually limit individual rows in a table to a specific user, but I think it may require some hackery to do in SQL 2008. – Shawn Sep 25 '18 at 18:10
  • @Shawn Thanks for confirming that **AD Groups** are a good choice. The applications don't use dedicated accounts, they simply run on the users' workstations with their identities. So instead of creating an account per application the idea would be to leverage its users' AD group used to secure the application's folder. And indeed in this way we are sure we get the final user's identity on the database side for a finer control. We should have a limited set of security groups: one per entity (so 3), and one per application. As we use stored-procedures we can easily mimic row-level security. – Pragmateek Sep 25 '18 at 20:11
  • 1
    You might want to look at using SQL Views also. You can get pretty granular in how you need to limit the data a user has access to, and Views do a good job of extracting things away. AD Groups provide a good place to manage the users. And then the AD groups are used in SQL to access specific resources. I'm not sure if an application connecting to a database can pass the AD user though; the app usually has its own db service account. I think there's a way to do it, I just haven't dug yet. This is an issue that I'm actually looking at right now., so if you find a solution I'll be talking to you. – Shawn Sep 25 '18 at 20:40
  • I've considered views but it would create duplicates as the code would be the same except discriminator value `SUBSIDIARY1/SUBSIDIARY2/HOLDING` and AFAIK we can't pass parameters to views so SPs are perfect for our use-case, and they already exist. The applications run with the users' identities and will connect to the database using "Windows authentication", so their identities will be passed to the stored-procedures. Not sure an account per app is a good idea, exept if there is no access restriction, only anonymous connections. In business context I've only seen Windows authentication. – Pragmateek Sep 26 '18 at 08:28
  • 1
    We currently use duplicated views. But they use the application's account to connect to SQL. The application is restricted by AD account. I _think_ it is possible to pass the AD account through the application's SQL connection abd through our reporting solution, but I haven't had a chance to really dig into it yet. – Shawn Sep 26 '18 at 15:15
  • Indeed we've though of injecting the user's identity into the connection string but it is too easy to spoof it as the VBA code is in an XLA. :-/ – Pragmateek Sep 26 '18 at 19:38
  • If you find a solution that doesn't sacrifice security, let me know. This is something I definitely need to put more noggin time into. – Shawn Sep 26 '18 at 20:36
  • We'll follow the **Windows authentication** and **AD groups** path, I'll try to post some feedback here. :) – Pragmateek Sep 27 '18 at 12:53

0 Answers0