Results 1 to 6 of 6

Thread: Administrating Database with Sensitive Info

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,187

    Administrating Database with Sensitive Info

    I am going to be working on a new database application that acts as a tracking system for tracking packages. The basic requirements I have right now are implying using WinForms as a data entry tool and a website for the third parties in asp.net to track the actual packages (possibly later with ability to also data enter from the web site). My question is concerning administrating the underlying database. I have typical fields for example:

    Date Entered - The time the item comes into the system - 1/16/15 10:15:00 AM
    Package Type - The type of item picked up - LARGE PARCEL
    Item Status - The current status of the item - PICKED UP
    Item Status Updated On - The last time a status change was made - 1/16/15 10:30:00 AM
    ***Assigned Driver - Who is assigned to pick up the item - JOE SMITH

    The last field in asterisks (Assigned Driver) should be visible on the WinForms app, but not on the asp.net website. This is because personally identifiable information from the company data entering should not be shown on the website (third parties are using this to track their items). Ultimately, there actually may be many more fields in the database that should also follow that logic.

    My question is how would others typically handle this? I am thinking that I could still just design the queries in ASP.NET to not pull the sensitive fields. It was also suggested that I could create a separate table which is derived from the 'Full' table so to speak in order to effectively make sure that data will not be available. What do you think?
    Last edited by jayinthe813; Jan 16th, 2015 at 08:57 AM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,975

    Re: Administrating Database with Sensitive Info

    There's no reason to split the data into two tables... unless you have drivers in another table anyways, and the driver field on the package is actually a DRIVERID field that is a FKey to the Drivers table... that would be the suggested setup. As for retrieving and displaying the information - multiple quereis... the winforms has one set of requiremetns, so the query executed from there should be done accordingly. from the web side, it has a different set of requirements, so it should have it's own query to pull the data.

    Or, you can use one query that pulls the same things, but only display just the fields you want. Personally, I try to only pull just the fields I need. I do break that on a case by case basis as the need arises though. So it's more of a guideline than a rule.

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

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,502

    Re: Administrating Database with Sensitive Info

    What I generally do in situations like this is create separate database users/groups (in this case one for the Winforms app, and one for the web app), and separate stored procedures to suit their requirements (so the web app would only have access to the non-personal info), and set up the permissions so that the users only have access to the SP's that they need - and not the other SPs, or the tables themselves.

    To make it easier to manage, you can put the SP's into schemas for each group, and assign permissions to the schema rather than the individual SP's.

  4. #4
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,291

    Re: Administrating Database with Sensitive Info

    Quote Originally Posted by si_the_geek View Post
    What I generally do in situations like this is create separate database users/groups (in this case one for the Winforms app, and one for the web app), and separate stored procedures to suit their requirements (so the web app would only have access to the non-personal info), and set up the permissions so that the users only have access to the SP's that they need - and not the other SPs, or the tables themselves.
    +1

    use a separate user for the web stuff and cut down the permissions for this user so that he can only run the handful SP he needs and nothing more, no selects, no inserts etc.

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2012
    Location
    Tampa, FL
    Posts
    1,187

    Re: Administrating Database with Sensitive Info

    I am fairly new to using databases outside MS Access. This app will either be based on MYSQL or SQL Server. This means I am new to SP and using a "real" database, but I guess the major difference is that the SP logic is literally stored on the server and somehow you can assign a SP to a permission level (based on user). That makes sense. I guess at the end of the day you are just shifting where the query statement resides from the front end and instead are just passing parameters?

    For doing record inserts from something like the winforms app in regards to security, does it make better sense to keep those as inserts via a Stored Procedure called directly by the application or is it preferable to deal with something like a web service that does the uploading?

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,502

    Re: Administrating Database with Sensitive Info

    Quote Originally Posted by jayinthe813 View Post
    I guess at the end of the day you are just shifting where the query statement resides from the front end and instead are just passing parameters?
    Correct... and the bonus points to it include that you can change both apps without having to touch either of them directly, you can change the database structure without having to re-write the apps (only the SP's).

    For doing record inserts from something like the winforms app in regards to security, does it make better sense to keep those as inserts via a Stored Procedure called directly by the application or is it preferable to deal with something like a web service that does the uploading?
    If you are limiting the security so they only have access to particular SP's, having SP's for Inserts etc are a good way to go... I don't really see a need to add a web service to that, as it doesn't have much effect on the security in that setup.

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