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

    Arrow Db Design: Defining an Access Control Table

    An application I am developing has data on various cities.
    Access to the data needs to be based on a list of cities defined for each client.
    A client can have:

    • access to all cities in a country AND / OR
    • access to all cities in a state / region AND / OR
    • access to select cities in any state or country.


    ---------------

    So far, I have come up with this (a simplified view of the tables with only the
    essential columns pertaining to this question is presented below):

    Country table:
    ==============
    idCountry | Name
    ==============

    State / Region table:
    ======================
    idState | idCountry | Name
    ======================

    City Table:
    ====================
    idCity | idState | Name
    ====================

    Client Table:
    =============
    idClient | Name
    =============

    Access Definition Table:
    ===============================
    idAcd | idClient | Country | State | City
    ===============================

    Note: Country, State and City are boolean and indicate HOW access has been
    defined. For example, if a client has been defined access by country and state,
    the columns would be flagged true and the application code would then check
    the access control tables for country and state (see below) to determine the list
    of cities to which access has been granted.

    Access Country Table:
    ====================
    idAcc | idAcd | idCountry
    ====================

    Access State table:
    ==================
    idAcs | idAcd | idState
    ==================

    Access City table:
    ==================
    idAcy | idAcd | idCity
    ==================

    -------------

    I am not sure if this is the right approach. Is their a better solution?
    Attached Thumbnails Attached Thumbnails kl.gif  
    Last edited by Mysti; 08-17-10 at 08:18. Reason: Prettify

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why bother with the booleans? All they effectively state are "there are related rows in the respective table". You might as well dump the booleans and go straight to the tables in the first place.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2010
    Posts
    4
    Quote Originally Posted by pootle flump View Post
    Why bother with the booleans? ... You might as well dump the booleans and go straight to the tables in the first place.
    Without the booleans, the app would have to scan all three tables every time. I thought this was more optimal. Is this a flawed approach in db design?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - the app will scan nothing.

    The database will return data as required. If the tables are properly indexed then there would be a nominal overhead in the event there are no relevant rows. If not properly indexed then the database would scan the entire table however you would properly index it eh?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2010
    Posts
    4
    I was not clear -
    The booleans are to guide the application code on which tables to query. For example, if a client has been granted access based on Country and State, the application code can then query only Access Country and Access State tables, and ignore the Access City table.

    Without the boolean flags, the application code will have to query all three tables every time. That slight overhead can be avoided.
    Last edited by Mysti; 08-17-10 at 16:45.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No, you were perfectly clear.
    You've replaced one overhead with another whilst also tightly coupling your application and database and introducing the possibility of update anomalies.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Pootle Flump is correct, but there seems to be a bit of a communication problem.

    Making your application aware of security criteria does two bad things. First, it means that the application has to actively participate in any changes to security so that if you miss a check ANYWHERE in the application then your security system is compromised. More importantly, your application will become much more complex which will make it difficult to maintain and will also mean that you'll have to manage your code very differently.

    By putting all of the security/access logic into the database, you have a single point of reference which is easier to manage and maintain than the application. You can build a single view that will show what cities a given user can access, use that view in an INNER JOIN to limit the cities returned by any other SELECT statements you need to build, and be done. A tiny fraction of the amount of work, cleaner application code, and a faster running application... You'll like it much better than trying to "roll your own" repeatedly!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Aug 2010
    Posts
    4
    Quote Originally Posted by pootle flump View Post
    You've replaced one overhead with another whilst also tightly coupling your application and database and introducing the possibility of update anomalies.
    Ok. Thanks to Pat Phelan's explanation, I now understand what you were trying to convey. I'd appreciate your inputs on querying too ...

    Quote Originally Posted by Pat Phelan View Post
    ... You can build a single view that will show what cities a given user can access, use that view in an INNER JOIN to limit the cities returned by any other SELECT statements you need to build, and be done. A tiny fraction of the amount of work, cleaner application code, and a faster running application...
    Thanks for taking the time to explain Pootle Flump's comment.
    I am a developer, and new to db designing. In the last part of your comments (quoted above) you addressed querying.

    Currently, this is how the app works -

    1. Users inputs a client id and city.
    2. The application checks the db for information on the city and retains the following data: idCountry, idState, idCity, Country Name, State Name, City Name.

    3. The application then verifies the client id in the database and retrieves the boolean flags in the Access Definition table (using a SELECT and JOIN query on Client and Access Definition table).

    4. Then, based on the flags it checks the Access Country, Access State and Access City table with the previously retained data (see 2) with a SELECT query on each (if required).

    5. If the client has access to the city data, the application extracts that from the db and outputs it to the user (along with the Country, State and City name from 1).

    Based on the suggestions here, I do plan to alter and drop the boolean flags. What would then be the optimal way of querying the db?

    (P.S: The db server used is MySQL 5 with innodb engine and I've read that its performance with Views isn't that great. So it might be better to ignore them altogether.)
    Last edited by Mysti; 08-18-10 at 18:30.

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
  •