Results 1 to 2 of 2

Thread: SQL Server database client security framework.

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    59

    SQL Server database client security framework.

    I am starting development on a SQL Server Client that will allow a user edit and retrieve records that they they create. These records will live in a single database with other users.

    Here are the basic requirements:

    1. Based on the users login, control what records they have access to.
    2. Based on the users login, control whether or not they can even use the client.
    3. Based on the users login, control what CRUD operations they can perform.


    Are these functions that should be administered on the server side by creating users and roles, or could it be done within the database with the appropriate tables etc. Such as having a table of containing user names, passwords, privilege ratings, etc. Then based on what information is returned from that table, create variables that can be used in a where clause in a select statement to restrict what records are returned.

    As I mentioned earlier, I am in the planning stage, so I am not tied into any particular way of thinking.

    Thanks,

    Bob Holahan

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

    Re: SQL Server database client security framework.

    I like the make my User-Interface control record access.

    Like a drop down for what buildings can be seem in a school district. Or a drop down of what "types" of batches can be looked at.

    Then you have times where an admin user can see all STAFF. So a STAFF drop down on a form makes sense - and if a user can only see themselves that drop down is appropriately limited to a single user (or whatever the business requirement is).

    With all that said I've always put the majority of my permissions control into tables in the SQL database itself.

    [sidenote] This also makes the portability of the DB a whole lot better - file this under disaster recovery [/sidenote]

    I am a proponent of 100% stored procedure access to my MS SQL databases. This means I usually have parameters to feed the WHERE clause. And those parameters are going to come from the UI.

    Giving permission to EXECUTE SPROCS would use MS SQL permissions (usually through WINDOWS AD GROUPS assigned to DATABASE roles).

    If there are times I need to expose a VIEW to EXCEL for users I can easily JOIN out to these permissions tables (using SYSTEM_USER) and properly limit the WHERE clause to what the users can see.

    Note in this case MS SQL permissions are also exploited to FIRST OFFER ACCESS for this VIEW to certain users (again usually through WINDOWS AD GROUPS assigned to DATABASE roles).

    *** 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

Tags for this Thread

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