Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Unanswered: Security: Front End vs. Back End

    ** Disclaimer -- I'm not an IS person! I'm an aerospace engineer who is quickly becoming the company's SQL expert

    I'm starting to think about the security scheme for the database I'm developing. Database is in SQL Server. Front Ends could be Access, web pages in a SharePoint site, stand alone clients in C#, or even Excel. We're still working on what option gives us the best user experience for what we need.

    Security only needs to be restricted on a per-table basis, not per column or row. i.e. Finance folks are the only ones that have access to the tables containing finance data, but if they have access they can RW ANYTHING in that table. We have AD in the company, so windows authentication is an option.

    My IS guy said that best practice dictates that all security should be done on the front end, and never in the back end. In fact, he feels that the best solution is to go entirely through SharePoint, and let SharePoint be the security gate keeper - even for front ends that involve Access or Excel.

    I had expected that implementing security through the back end (i.e. SQL server) made the most sense - after all SQL Server seems to have some very nice facilities for users/logins/roles/etc.

    I'd appreciate opinions and/or pointers to online discussions on this subject. As I mentioned up top, I am getting a pretty good handle on database design and SQL in general, but I really have no good feeling for security.
    Carlos

  2. #2
    Join Date
    Oct 2010
    Posts
    5

    security for backend

    Carlson,

    Am sure you already have login password for the user. For you to prevent some expert user for the database is may to use some triggers to prevent data deletion or update.

    see this trigger i used recently

    create TRIGGER updatecustbal ON cust
    INSTEAD OF update,delete
    AS
    BEGIN
    RAISERROR ('No Editing Of this fields',16,1)
    ---RETURN 50000
    END

    Cheers

  3. #3
    Join Date
    Aug 2010
    Posts
    57
    Psigei,
    Thank you for your response. My question is much more fundamental - indeed you cannot be sure that I have a login password for the users.

    My original intent was that all users would either use SQL server authentication or Windows authentication, and these logins/accounts/users/permissions would be handled using SQL server's security facilities (back end).

    What my IS guys is proposing is that there be a single login on SQL Sever, and that all authentication and permissions be instead handled by the client applications (front end).

    I'm trying to determine the pros and cons of these too approaches.

    Carlos

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by carlosn View Post
    My IS guy said that best practice dictates that all security should be done on the front end, and never in the back end. In fact, he feels that the best solution is to go entirely through SharePoint, and let SharePoint be the security gate keeper - even for front ends that involve Access or Excel.
    ...
    I had expected that implementing security through the back end (i.e. SQL server) made the most sense - after all SQL Server seems to have some very nice facilities for users/logins/roles/etc.
    Carlos
    To begin with, security should exist wherever it is needed. Best practice dictates mode of least privilege at all times, in all practical aspects. I tend to favor wrapping all of my application access in to stored procedures, then creating roles in the database dictating who gets to access what stored procedures. Those roles will then be applied to the users that my applications will leverage when connecting to the database. Each role only has access to exactly whatever that application needs to do.

    On the front end, I employ another layer of role-based security that is actually specific to the application, not just database objects the application relies on. For example, "administrative" functionality in my application may mean that the user is granted access to view and manipulate a particular set of forms/screens, but doesn't necessarily mean they have raw access to the database objects that drive said forms.

    Edit: PS, do not use triggers for basic security. It will bite you in the ass in a variety of ways including but not limited to predictability, maintenance and performance.
    Last edited by Teddy; 10-15-10 at 14:37.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2010
    Posts
    10
    SharePoint uses a pretty robust security model, and you can get quite granular. That being said, depending on where you are going after that (i.e. Excel Spreadsheets/Services, Integrated Reporting Services, etc)you will certainly need to consider the other levels.

    As Teddy said, least priveleged methodologies should be used where possible. I'm hoping that's what your IS guy meant when he said don't ever control security on the back end. I'm hoping he meant, lock it down as much as possible here, then on the front end you can use SharePoint to expose read-only data to various users, etc, leaving the priveleged access in between (using a trusted account/data connection, etc).

    I often equate this to file shares for IT folks who are not necessarily DBA's or application folks. In Windows, you can share a folder to people and control access at that share level. And often that's what people do, but if they lock it down at the share level, that merely prevents the unauthorized users from access the data through the share. If they gained access to the underlying file system, and the file system permissions were not set right, they could have all the access they need.

    In the end, I would secure the back end as much as possible while allowing full functionality of the application required (least privileged), then on the front end, set the granular permissions for the end users.

    On the back end another option is to setup views of the data that you want to expose and then use Excel/SharePoint/Access/etc to pull the data from the views. In that case you wouldn't have the writing risk. Hope this helps.

    RG

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's also worth noting that Sharepoint security gives even the most experienced and grizzled administrators epic nightmares when it comes to long term maintainability. Just google "sharepoint security" and add your derisive term of choice for more information.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    IS guys always advise no security on the backend/database. That is so they can access stuff easily. The way I look at it is that it's MY database, and I need to do what I can to protect my baby from all those bad people out there (including front-end developers).

    I'll screw it down tight as hell and make them come ask me for access if they can't do something they want to do. Every aspect of development, front, middle, and back needs to be protected as well as it needs to be.

    Think of your database as if it were your wife as you guide her through the crowd at a beastie boys concert on the Las Vegas strip on New Years Eve.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Aug 2010
    Posts
    57
    Thanks for everyone's input...
    @TallCowboy - I don't think they are advising "no" security. I suppose they are more in line with @Teddy's concept of security where security is needed.

    IS seriously limits user accounts/roles/logins on the server side. Basically they set up an "application" account, and all front ends are supposed to use that. It is then the front end who is responsible for handling individual users and determining their privileges. They keep pushing for SharePoint to be the front-end or at least the middle-man who handles security. Reasoning, as @Richard said, is due to the increased granularity.

    I guess I will have to work some more with it, and see how the implementation actually works out.

Posting Permissions

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