Professional Web Applications Themes

Multiple users question (for a newbie) - MySQL

I'm in commercial aviation and I keep all my logbook in a MySQL database. I've written some scripts to pull the data out in the formats I like etc. but so far it's just a local system. Ideally, I'd like to put it on the web and then let other people use it. However, in my database there are 3 tables: aircraft airfields maindata The maindata one contains, as the name suggests, all of my flights, with the other tables holding extended information about the aircraft and airfields I use day-to-day. If I were to let other people use this ...

  1. #1

    Default Multiple users question (for a newbie)

    I'm in commercial aviation and I keep all my logbook in a MySQL
    database. I've written some scripts to pull the data out in the
    formats I like etc. but so far it's just a local system. Ideally, I'd
    like to put it on the web and then let other people use it.

    However, in my database there are 3 tables:
    aircraft
    airfields
    maindata

    The maindata one contains, as the name suggests, all of my flights,
    with the other tables holding extended information about the aircraft
    and airfields I use day-to-day.

    If I were to let other people use this system, would I:
    a) Create another maindata table for each user (called maindata1,2
    etc. for example)
    or
    b) Just keep everything in one table and have a unique reference for
    each flight entry?

    I think a is the right answer, but if someone could tell me
    definitely, I would be much obliged.
    The other tables are shared data anyway so would be used by all.

    Robin Guest

  2. #2

    Default Re: Multiple users question (for a newbie)

    Robin wrote:
     

    Much is up to your skills and what rights you have on the database.

    The advantage with A is that each user can make changes (update/delete)
    without it causing problems for the other users, if editing at the same time.

    B has the advantage that it's a lot more easy to search for data, with locking
    the table when making changes, would prevent that the data would be messed up
    (this way you are at the level of A, but you need to do checks in your scripts
    and retry after the table lock has been removed).

    Most people would go with B, I think, but not everyone will be thinking of
    locking the table.


    --

    //Aho
    J.O. Guest

  3. #3

    Default Re: Multiple users question (for a newbie)

    On 21 Apr, 16:21, "J.O. Aho" <net> wrote: 

    >
    > Much is up to your skills and what rights you have on the database.
    >
    > The advantage with A is that each user can make changes (update/delete)
    > without it causing problems for the other users, if editing at the same time.
    >
    > B has the advantage that it's a lot more easy to search for data, with locking
    > the table when making changes, would prevent that the data would be messed up
    > (this way you are at the level of A, but you need to do checks in your scripts
    > and retry after the table lock has been removed).
    >
    > Most people would go with B, I think, but not everyone will be thinking of
    > locking the table.[/ref]

    Thanks for the quick reply!
    If, theoretically, my scripting skills were so amazing that, say,
    20,000 people wanted to use my database, I suppose A would have to be
    the method otherwise the data table would be locked out most of the
    time and people couldn't enter their data?
    Also if you are trying to keep data private, would it be possible for
    anyone with read access to a single table to read everyone else's
    data, by doing a select * query (or similar)?

    Robin Guest

  4. #4

    Default Re: Multiple users question (for a newbie)

    Robin wrote: 
    >> Much is up to your skills and what rights you have on the database.
    >>
    >> The advantage with A is that each user can make changes (update/delete)
    >> without it causing problems for the other users, if editing at the same time.
    >>
    >> B has the advantage that it's a lot more easy to search for data, with locking
    >> the table when making changes, would prevent that the data would be messed up
    >> (this way you are at the level of A, but you need to do checks in your scripts
    >> and retry after the table lock has been removed).
    >>
    >> Most people would go with B, I think, but not everyone will be thinking of
    >> locking the table.[/ref]
    >
    > Thanks for the quick reply!
    > If, theoretically, my scripting skills were so amazing that, say,
    > 20,000 people wanted to use my database, I suppose A would have to be
    > the method otherwise the data table would be locked out most of the
    > time and people couldn't enter their data?[/ref]

    If they try to edit at the same time (creating new data usually don't cause
    any problems, it's when you edit/delete data that things can go really wrong).

     

    If you have everything in a table, then all who has read access to the table
    can read everything anyone else has written (assuming they have direct access
    to the database), but most systems has a frontend and it's in those cases the
    frontend that checks who has access to what.

    Just take a look at a portal like www.power.org, even if all the userdata is
    stored in the same table, you don't have access to read other users data.

    --

    //Aho
    J.O. Guest

  5. #5

    Default Re: Multiple users question (for a newbie)

    Robin wrote: 

    Keep everything in one table.

    Comments about everyone having read access to it are immaterial. You're
    only going to have one MySQL user (or maybe two) accessing the table
    itself - that's going to be the one the script uses. And it will have
    access to all tables, anyway.

    For instance - you don't think Amazon creates a separate table for every
    person's shopping cart, do you?

    The way to do this is to have a user id column in your maindata table,
    and a user's table. The user's table has an internal id, their login,
    (encrypted) password and other info you want.

    Then use your scripting language to keep people from altering (and
    displaying, if you wish) data that isn't theirs.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  6. #6

    Default Re: Multiple users question (for a newbie)

    >I'm in commercial aviation and I keep all my logbook in a MySQL 

    Making table names (or parts of them) reflect a piece of data is
    almost always a mistake. (There might be an exception for security
    issues if it is necessary for the user to directly access MySQL,
    but that doesn't apply here.) Add a column for user name. Any
    unique indexes will probably include the user name, or they'll be
    an auto-increment key unique across the whole table. Is there any
    reason for a user to legitimately access data that's NOT his? (For
    example, general stats on recorded flights by airport? Stats on
    types of planes used?) That's much harder with separate tables.
     

    Since you are providing access to this data via the web, only one
    MySQL user (the one used by the web site) is likely to access MySQL.
    You create web users via another table (the "users" table) and your
    web page does user authentication. Most queries will qualify the
    results by user.

    Locking the table should not be a big issue. If I understand your
    model, only one user should be allowed to edit any given record,
    and if you only let each user log in once, there will be no conflicts.
    You shouldn't lock the whole table: use transactions. And you
    shouldn't attempt to hold a lock on a record between displaying a
    record on a web page and submitting changes to it: that can cause
    a complete lockup of your system even if it has only ONE user when
    your OS crashes or your ISP connection drops or the user goes on
    vacation.

    Gordon Guest

  7. #7

    Default Re: Multiple users question (for a newbie)

    Robin schrieb: 

    No, locking is usually done on a by-row level.
    If you use transactions with the proper "isolation level", the database
    will automatically do all the locking for you. (You'll need InnoDB for
    that.)
     

    Only if you allow them to issue SQL queries directly.
    Most of the time, you'll present them a web frontend and take care not
    to leak data that they shouldn't see.

    You can give each user specific rights for each table; however, mysql
    doesn't seem to be optimized for that use case, as the manual warns that
    the database will slow down if you use anything but per-database
    permissions. (I think it needs extra queries in the permissions tables
    for each query that the frontend issues.)
    OTOH letting mysql doing the permission checking has the advantage that
    you have a whole lot less to worry about security-wise, and if you do
    the permission checking yourself, you'll incur just the same overhead on
    the application side.
    OT3H creating tables from a web frontend means you have to be
    extra-careful with that particular operation, to avoid that some
    anonymous nobody can fill your harddisk with almost-empty tables. And
    you'll have to make sure that the per-user tables don't produce name
    collisions with the shared tables - easy enough with the three tables
    that you have right now, but can become quite a problem when you have
    two dozen or more tables.

    Regards,
    Jo
    Joachim Guest

  8. #8

    Default Re: Multiple users question (for a newbie)

    On 21 Apr, 20:30, Joachim Durchholz <org> wrote: 
    >
    > No, locking is usually done on a by-row level.
    > If you use transactions with the proper "isolation level", the database
    > will automatically do all the locking for you. (You'll need InnoDB for
    > that.)

    >
    > Only if you allow them to issue SQL queries directly.
    > Most of the time, you'll present them a web frontend and take care not
    > to leak data that they shouldn't see.
    >
    > You can give each user specific rights for each table; however, mysql
    > doesn't seem to be optimized for that use case, as the manual warns that
    > the database will slow down if you use anything but per-database
    > permissions. (I think it needs extra queries in the permissions tables
    > for each query that the frontend issues.)
    > OTOH letting mysql doing the permission checking has the advantage that
    > you have a whole lot less to worry about security-wise, and if you do
    > the permission checking yourself, you'll incur just the same overhead on
    > the application side.
    > OT3H creating tables from a web frontend means you have to be
    > extra-careful with that particular operation, to avoid that some
    > anonymous nobody can fill your harddisk with almost-empty tables. And
    > you'll have to make sure that the per-user tables don't produce name
    > collisions with the shared tables - easy enough with the three tables
    > that you have right now, but can become quite a problem when you have
    > two dozen or more tables.[/ref]

    Thanks for all the replies, everyone. Looks like I've got a lot of
    reading to do (InnoDB? Not even sure what table type I've got). One
    table seems to be the consensus then, without letting people write
    their own queries

    Robin

    Robin Guest

Similar Threads

  1. NEWBIE QUESTION - do you need different versions toaccomodate for users connections
    By kathleen@excelsior.edu in forum Macromedia Flash Flashcom
    Replies: 2
    Last Post: August 12th, 07:17 PM
  2. Question about internet users vs localy loged on users
    By Carrol in forum Windows Server
    Replies: 8
    Last Post: August 22nd, 08:37 AM
  3. multiple users question.
    By noone@nowhere.com in forum Macromedia Flash
    Replies: 0
    Last Post: November 7th, 07:10 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139