Results 1 to 21 of 21

Thread: Security Question: MS SQL direct access using windows authentication

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2005
    Posts
    431

    Security Question: MS SQL direct access using windows authentication

    Note: My background is in web based solutions. This question is about the highest possible security for a user directly running an app with direct postgreSQL/MSSQL access.

    Lets say you have a windows application (VBA macro, vb6, c#, etc) that communicates directly with MS SQL using windows authentication (or direct access to psotgreSQL using ODBC drivers).

    Lets say there are 500 users and 100 tables.

    Many of those tables contain sensitive information that can only be read by the user who the record/row belongs to.

    Is there a clean easy way to get this done? Id imagine a DBA would need to write many queries to put in policies for each table with specific access? For example something in the spirit of 'CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);' so a manager can only select rows belonging to them?

    Is this the correct way to handle these situations? Seems like a messy situation. Especially if you have the permissions in another table (ex: tblClient has a 1 to many relationship with tblUsersWhoCanWorkOnClients that determines if the user should even be allowed to read any specific row in table client). Wouldn't those type of situations cause slower queries the more complex the policies you create?

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Security Question: MS SQL direct access using windows authentication

    I'd rather look at the GRANT's (hint, hint)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Security Question: MS SQL direct access using windows authentication

    But that's what DBMSs are good at... security... Things like SQL Server are built for that kind of thing. You create a login, you create a user, you create a role, you associate the user with that role, then your grant or deny that role access to what ever objects it should have access to (or not in the case of a deny). So, no, it doesn't slow anything down. As soon as the user establishes a connection, all they can see is the objects they have been granted access to. So it's checked and enforeced right fomr the beginning, it's not like it's checked on the fly. It's enforced from the start.
    But it is at the object level. Tables is as far as you can go with it. You can't control the level of access down to a row. That's too finite. So if you're looking to enforce security at the row level, then either you need ot look for some other mechanism (and that will likely slow you down) or your architecture is wrong from the start.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Security Question: MS SQL direct access using windows authentication

    Quote Originally Posted by techgnome View Post
    But that's what DBMSs are good at... security... Things like SQL Server are built for that kind of thing. You create a login, you create a user, you create a role, you associate the user with that role, then your grant or deny that role access to what ever objects it should have access to (or not in the case of a deny). So, no, it doesn't slow anything down. As soon as the user establishes a connection, all they can see is the objects they have been granted access to. So it's checked and enforeced right fomr the beginning, it's not like it's checked on the fly. It's enforced from the start.
    But it is at the object level. Tables is as far as you can go with it. You can't control the level of access down to a row. That's too finite. So if you're looking to enforce security at the row level, then either you need ot look for some other mechanism (and that will likely slow you down) or your architecture is wrong from the start.

    -tg
    Not quite right.
    You can GRANT access columnwise (like Selecting HomeAdress of Employee, but not his Salary). --> If you remember some months ago, Elroy had a similar question.
    But i agree: I wouldn't know of a mechanism granting/denying access to rows on the serverside
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Security Question: MS SQL direct access using windows authentication

    There is row level security in SQL Server (from 2016 on). It requires functions created and using those function on a call to the select statement. If the function says you have access you see the row if not you get nothing.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Security Question: MS SQL direct access using windows authentication

    Quote Originally Posted by Zvoni View Post
    Not quite right.
    You can GRANT access columnwise (like Selecting HomeAdress of Employee, but not his Salary). --> If you remember some months ago, Elroy had a similar question.
    But i agree: I wouldn't know of a mechanism granting/denying access to rows on the serverside
    Honestly... no I don't remember that at all... I'll have to look it up sometime. Surprises me though because it sounds like the kind of thing that my previous job would have employed. Clearly they didn't.

    Quote Originally Posted by GaryMazzone View Post
    There is row level security in SQL Server (from 2016 on). It requires functions created and using those function on a call to the select statement. If the function says you have access you see the row if not you get nothing.
    IS the security on the row though? Or is it on the function, and the function is simply acting as a gatekeeper? If it's simply a gatekeeper and it's the one that's doing the looking at something and determining if the row can be returned, then that's the "other mechanism" I was talking about and could lead to performance issues. It's certainly doable depending on how things are setup.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Security Question: MS SQL direct access using windows authentication

    Quote Originally Posted by techgnome View Post
    Honestly... no I don't remember that at all... I'll have to look it up sometime. Surprises me though because it sounds like the kind of thing that my previous job would have employed. Clearly they didn't.
    -tg
    Found it (Post 5): https://www.vbforums.com/showthread....VB6)-questions
    Yes, for MySQL, wqweto responds two posts later, that it's valid for MS SQL, too
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Security Question: MS SQL direct access using windows authentication

    It is on the row. the function acts like part of the where clause (automatically added) . It is a bit of a pain to setup especially if the tables already exist and you are trying to add it to existing stuff
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Security Question: MS SQL direct access using windows authentication

    If you are not granting direct access to the database, but are requiring all users (except DBA Admins) to use a front end of some sort, then you can create your own security file system for the data and related functions. You have Users, User Groups that you assign Users to, and various Reporting or data Update functions that you assign to each User Group. Users only have permission (Read/Run, Write/Change, Delete) to the functions assigned to the User Group that they belong to.

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Security Question: MS SQL direct access using windows authentication

    I would make VIEW's that contain the security in the WHERE clauses and then grant rights to those VIEW's - maybe even through schemas

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Security Question: MS SQL direct access using windows authentication

    i would think a handfull of stored procedures using CURRENT_USER to interact (CRUD) with only rows of that user and limiting access for all users to only those SPROCs would do it.
    this implies that you include an 'owner' column in every table. with this design it will never ever be possible to access other peoples records without taking ownership. so you will never be able to have a group of people allowed to access more than their own records which in practice will cause problems in a professional environment i think.

  12. #12
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Security Question: MS SQL direct access using windows authentication

    more elaborated, one could use user/group lookup table and the groupname in the owner column to allow group permissions....
    a non clustered index on the owner columns may be usefull
    I think this would not even impact performance that much.

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    May 2005
    Posts
    431

    Re: Security Question: MS SQL direct access using windows authentication

    Thanks for the replies guys. This is more hypothetical at this point.

    Quote Originally Posted by techgnome View Post
    So if you're looking to enforce security at the row level, then either you need ot look for some other mechanism (and that will likely slow you down) or your architecture is wrong from the start.

    -tg
    As mentioned, I am a web dev, so this level of control is easy to implement when the user does not have direct DB access. This 'role' based security at the DB level seems limiting. For example lets say you have a tables called [tblClients, tblCases, tblAppointments, tblNotes, etc] but you want each of your case workers to only have access to their own clients data? In other words, if Bob is assigned to 10 clients I want Bob to only access that clients data and its associated data in the other tables. I don't want him to be able to query anything else.

    Or I might have secretary/intake person who can access all clients, but only certain information (columns). For example secretary might need to know the names/address/appointments, but wont need to know private/sensitive meeting notes.

    Or maybe even a super user with near global access might have access to nearly everything, expect for certain blacklist clients (ex: in a mental health organization this would allow 'Bobby' to exist in the system but appear nowhere for his brother 'Paul' who is a manager with super user rights. Allowing 'Bobby' to be in the system without the risk of 'Paul' ever accidentally even knowing he is in the system).

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Security Question: MS SQL direct access using windows authentication

    See my post #4 and post #7 for Column-Wise GRANT's

    everything else i leave to the others
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Security Question: MS SQL direct access using windows authentication

    As I said that is possible using Row-Level security in SQL Server 2016 and up. You write a function that is used when you access a table that basically adds a where clause to you query. You can see only the rows that are set for your security (i.e. your own rows or your subordinates rows). As I said it is not fun to implement but would do what you want
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  16. #16
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Security Question: MS SQL direct access using windows authentication

    Gary,
    i'm still trying to wrap my mind around that row-level-"GRANT"
    The only way i could fathom that to work how you described would be (for lack of better terms):
    1) that Function works basically like a BEFORE SELECT-Trigger passing/grabbing the Statement and Invoker to the Function (adding the WHERE-Clause)
    2) an invisible column in that table where the GRANT is stored (in whatever form) --> Checking against the Invoker


    Now if an Application uses an "Application"-User (vs. a "real" Database-User)..... let the games begin.... :-)
    Last edited by Zvoni; Jun 10th, 2021 at 09:13 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  17. #17
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Security Question: MS SQL direct access using windows authentication

    You need a column added to the table along with the function. Each user would need something.... i.e. the app can still be the .Net user but a user needs to log in with their own credentials. Those would be used in the where clause:

    See: https://docs.microsoft.com/en-us/sql...l-server-ver15

    Zvoni this is how I make my living (as a SQL Server DBA) and as I said in a newly designed DB this would be easier to implement. I have done this before and it is a pain but does work
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  18. #18
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Security Question: MS SQL direct access using windows authentication

    I have written enterprise applications for decades and putting "user" info into a WHERE clause is common. Sometimes it's user roles info so I can filter rows. Fiscal Years, Departments - whatever.

    But if I am allowing a user to work with my MS SQL database using Management Studio, then they are basically in the back door. Having to craft SQL "security" based on standard MS SQL roles and what not would be a nightmare!

    The OP states this:

    direct postgreSQL/MSSQL access
    What does that mean? Running MS ACCESS with wide open view of the ODBC world of a SQL server?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Security Question: MS SQL direct access using windows authentication

    Quote Originally Posted by GaryMazzone;[URL="tel:5525011"
    5525011[/URL]]You need a column added to the table along with the function. Each user would need something.... i.e. the app can still be the .Net user but a user needs to log in with their own credentials. Those would be used in the where clause:

    See: https://docs.microsoft.com/en-us/sql...l-server-ver15

    Zvoni this is how I make my living (as a SQL Server DBA) and as I said in a newly designed DB this would be easier to implement. I have done this before and it is a pain but does work
    Thx Gary,
    so i was actually pretty close with my guess
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  20. #20

    Thread Starter
    Hyperactive Member
    Join Date
    May 2005
    Posts
    431

    Re: Security Question: MS SQL direct access using windows authentication

    Quote Originally Posted by Zvoni View Post
    See my post #4 and post #7 for Column-Wise GRANT's

    everything else i leave to the others
    Yes I saw that, thanks. Although I never used them, I was aware of those function before I posted. It just seems like the opposite of how I code (instead of having the back end code perform the authorizations/security, you need to put all the critical security in the DB).


    Quote Originally Posted by szlamany View Post
    I have written enterprise applications for decades and putting "user" info into a WHERE clause is common. Sometimes it's user roles info so I can filter rows. Fiscal Years, Departments - whatever.

    But if I am allowing a user to work with my MS SQL database using Management Studio, then they are basically in the back door. Having to craft SQL "security" based on standard MS SQL roles and what not would be a nightmare!

    The OP states this:



    What does that mean? Running MS ACCESS with wide open view of the ODBC world of a SQL server?
    Yes, basically. In this specific case its a MS Access app that grew, so they started using postgresql database as their backend. This MS acess app went from something very small, to something much larger with many users. So I started wondering how this can be handled properly and securely. In this specific app the users are added to the DB in specific roles, then the user interacts with the DB with an MS Access app. The MS Access app has other layers of 'security'. But obviously MS access apps are not secure, and a malicious user could directly connect to the DB using either something like pgAdmin, or if that was blocked, then they can just make their own MS access app.

    In your environment using MS SQL, aren't you in the same boat? What would stop one of your users from installing Management studio and connecting directly? Or perhaps writing their own app that bypasses some of the limitations in your front end?

    As someone who works on the web I am at a complete loss at how this type of connection can be secure without either:
    1. Spending lots of time to create functions and roles with specific access/grants (some of these grants/functions would need to perform multiple joins behind the scenes).
    2. Not allow direct connections, and have some sort of servlet/service/ORM tool between the front end and DB that handles the security.


    To clarify, I am just trying to learn. Nothing needs to change as of this moment.

    edit: Typos.
    Last edited by Zeratulsdomain; Jun 11th, 2021 at 04:52 PM.

  21. #21
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Security Question: MS SQL direct access using windows authentication

    Quote Originally Posted by Zeratulsdomain View Post
    In your environment using MS SQL, aren't you in the same boat? What would stop one of your users from installing Management studio and connecting directly?
    When I first migrated my clients to the PC world, I used VB6 and MS SQL. For security all database interaction was through stored procedures and users were only granted EXECUTE permission on those objects specifically. They could not see tables, they could not alter data directly - only through execution of my CRUD SPROCS.

    Of course, they could execute these SPROCS without my user-interface controlling things such as the Fiscal Year they were in, or some kind of other restriction.

    All these SPROCS had the same 9 "standard" parameters that allowed for things like managing the state of the UI. The first parameter of all them was the "CONNECTION ID" that was assigned when the UI first connected to the DB. My intention was to use this ID value on each subsequent call to validate that a "real user from my UI" was doing the execution. This never materialized. If one of my clients pushed, I would have made the effort.

    btw - all my clients run the same "business logic agnostic" UI...

    I've now migrated my clients from that VB6 app to a rich-internet application (JS front end, AJAX/JSON to .Net backend).

    When they first login to this RIA, I assign a GUID at the backend and retain that on the server (and of course hand that back to the client). Each AJAX/JSON call made from the UI to the backend carries that GUID, which is checked for. If not found, or expired, that DB call is rejected.

    I am also considering retaining other "facts" about the user on the server. Such as the Fiscal Year they are allowed to see, or other restrictions like that. By doing this I do not allow the client to specify values "outside" the allowed range, further securing my back end from unauthorized AJAX calls. This thought came to mind when one of my clients had a website they were using checked by a penetration testing company. One of the findings by that company was that the user that logged in could be spoofed in subsequent data requests (along with a slew of other bad findings - SQL injection, cross site scripting, user accounts enumerable).

    I deal with lots of PI and PHI, my clients have to adhere to FERPA and HIPAA privacy requirements, and that falls on me to respect on the tech side.
    Last edited by szlamany; Jun 12th, 2021 at 09:48 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width