January 28, 2022 at 3:34 pm
We have 2 databases Database1 and Database2 on the same server box that very tightly coupled.
Database2 has views or sprocs that use the <database1>.schema.<objectname> naming convention to pull data from Database1. Lots of data in Database1 and Database2 are PHI data thus the need for data protection and security.
We have a request to allow "users" to retrieve non-PHI data ( users will be sql logins, AD groups, Windows groups) . These "users" CANNOT view any PHI data because their job requirements do not involve patients.
We are considering the options below:
Con: there is a DatabaseZero that is the publisher to Database2, the subscriber. We will have too many schema changes in 3 databases.
2. Set up views in a new Database3 and grant connect and select view to those users
Con: Views in Database3 referring to db objects in Database2 can be linked to Database1 so these "users" may require read role in Database1 and Database2.
We are still exploring the options and would like to hear comments and opinions or shared thoughts from anyone who has faced the same requirement.
Thank you much in advance!
January 28, 2022 at 10:12 pm
My approach is to design the database with restrictions in place to begin with. Use roles to restrict what data a user is allowed to access. I expect (ie hope) that a user who has access to SOME PHI doesn't have access to ALL PHI.
My goal with permissions is that I am not building special objects for different people to see (such as new views so people without access to PHI can access my system), but instead have the permissions in place to protect the data. The advantage to this approach is that if someone without PHI access gets moved to a position where they can have PHI access, your responsibility is just to put them in the proper groups in SQL and they will see the new (to them) data.
If I understand right, you previously had a system where anyone who had access to the system would have access to the PHI. To me this sounds like a HUGE security risk. Since the database doesn't consist of ONLY PHI data, what I would do first is remove all objects from being able to be access from PUBLIC (if that is allowed), then create roles for the different levels of access and grant permissions on the objects based on the role. For example, if you have a secretary, they may need access to the patients name, address, doctor, and phone number (possibly more info, but I expect a secretary doesn't need full medical history information). So you'd have a role for Secretary and all Secretaries would be in that role. That role would have access to the required information and nothing extra. So no prescription information or medical history or allergies, etc. MAYBE a Secretary should have view access to allergies but not update or delete permissions - so make sure that those permissions are restricted. Then repeat for all roles that need access to things.
I personally wouldn't create special views for users UNLESS those views are required for the role. For example, if your table contains names, addresses, and phone numbers of patients and you need someone to be able to access Names and Addresses but not phone numbers, a view would be a good way to do that or data masking. But like you said, data masking would require a lot of work.
One thing I am confused about your setup though is you have Database 2 as being replicated data from Database 1 which is pushing data into DatabaseZero. If this is the case, why do you need Database 2 to pull any data from Database 1 with a cross database query? Or am I reading your post wrong?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 2, 2022 at 2:24 pm
I agree with you totally, Views is the way to go. What we are trying to avoid is having to grant permissions at each database object which is tedious therefore not fail proof.
Thank you!
Database 1 and Database 2 are "partners sharing data" via 3-node naming convention <database>.Schema.TableName. Both of them are reporting databases.
DatabaseZero is an OLTP database so it keeps data from production user transactions then publishes transactional data via MSFT replication to Database1.
February 2, 2022 at 5:25 pm
Why not put all the views you want to expose in a single schema, and then just grant rights to the schema?
February 17, 2022 at 7:11 pm
We will try that approach. Thanks for the advice!
February 17, 2022 at 7:45 pm
As you have the data in a separate database ownership chaining is not going to to work.
I would be inclined only to give the users access to SPs in the View/SP database without having any permissions to the database with the tables. The SPs can then be signed with a certificate and a certificate user created in the table database which only has the permisssions the SP requires. (ie The users only have access to data via the SPs they have been given EXEC permissions for.) The details are in Erland Sommarskog's excellent paper:
https://www.sommarskog.se/grantperm.html
ps Another option would be to use transactional replication to move the data the users are allowed to see to another database and then just give the users permissions for the tables in that database.
February 17, 2022 at 9:29 pm
You can explicitly set DB_CHAINING on (ALTER DATABASE ... SET DB_CHAINING ...) for selected dbs if you prefer. You might want to consider that in this specific case.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply