May 25, 2021 at 8:28 am
I have a dev team who have a number of sql databases used as a back end for mostly excel front end.
This originally came from an excel spreadsheet until it got made into sql databases.
At first they were not to concerned about security and decided to give access to a domian group logon that contains basically all the organisations user. Over time they have put more sensitive information into the databases and have realised that anyone in the organisation can view the information is they were to use another tool, power BI or SSMS due to the domain logon.
They say that they still need to use the domain logon as everyone needs access but want to secure the sensitive information on certain tables but still be accessible to their queries from excel.
How am I able to implement this in SQL Server?
May 25, 2021 at 8:33 am
They say that they still need to use the domain logon as everyone needs access but want to secure the sensitive information on certain tables but still be accessible to their queries from excel.
How am I able to implement this in SQL Server?
I do not fully understand what you are asking for here. You want the data in certain tables to be inaccessible, unless it's from Excel? That does not seem 'secure' to me.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 25, 2021 at 8:40 am
Yes, it makes no sense to me really.
This is what they have said, they have sensitive information in certain tables, but they still want to be able to connect from their front end in excel and run their queries against the tables.
I have tried to tell them that this seems impossible to do both these things at the same time but they will not listen and I was wondering if there is a way I have missed.
They have suggested Signing Stored Procedures with a Certificate but I am concerned they will want to try this with all their code placed in SPs, removing the domain logon, and this would create numerous amounts of admin to maintain all of them.
May 25, 2021 at 8:51 am
OK, I can't think of any reasonable way of doing that! I'll leave it for others to chip in.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 25, 2021 at 8:52 pm
You'll need to create two AD groups, one for standard users, who should not see the sensitive columns, and one for privileged users, who can.
Drop the 'all logins' group from the SQL instance and grant access to both new groups. Grant SELECT, INSERT, etc. permissions as necessary - you can grant SELECT permission on a table to a group and also deny SELECT access to specific columns, or simply deny SELECT, etc. on the table to the group.
DENY overrides GRANT, so if a user is a member of both groups, they will not be able to see the DENY'd columns.
You have a lot of flexibility if you split your users into groups. You can also use Roles, which are a collection of permissions that can be applied to a user by adding them to the Role, to be more granular and still make things easy on yourself.
For example you could define Roles such as Data Entry, Auditor, Manager, and Analyst and assign different permissions to different Roles.
Eddie Wuerch
MCM: SQL
May 26, 2021 at 9:07 am
You'll need to create two AD groups, one for standard users, who should not see the sensitive columns, and one for privileged users, who can.
Drop the 'all logins' group from the SQL instance and grant access to both new groups. Grant SELECT, INSERT, etc. permissions as necessary - you can grant SELECT permission on a table to a group and also deny SELECT access to specific columns, or simply deny SELECT, etc. on the table to the group.
DENY overrides GRANT, so if a user is a member of both groups, they will not be able to see the DENY'd columns.
You have a lot of flexibility if you split your users into groups. You can also use Roles, which are a collection of permissions that can be applied to a user by adding them to the Role, to be more granular and still make things easy on yourself.
For example you could define Roles such as Data Entry, Auditor, Manager, and Analyst and assign different permissions to different Roles.
I can't see how this addresses the 'data in certain tables to be inaccessible, unless it's from Excel' requirement, please explain how you are suggesting that be implemented. This is a limit on the application used to access the data, not on the login/user.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply