PyQT app accessible over network?

Discussion in 'Python' started by Monte Milanuk, Feb 21, 2013.

  1. Hello all,

    New guy here, with a kind of general question. Hopefully its not too
    silly...

    I've been working at learning python off and on now for a while, with a
    couple programs in mind as a goal - kind of specialized stuff that I
    can't seem to find a good match for already available, competitor
    records, score-keeping & results for an amateur sports tournament. Many
    places use some cobbled-together Excel spreadsheet, which has its
    limitations. Others use an antiquated DOS-style application written in
    PowerBasic that has issues of its own.

    Probably 98-99% of the time the match administration would be done by a
    single individual on a single PC, which seems like it would be nearly
    ideal for a desktop application implemented in PyQt4 or similar. The
    problem is (as usual) those edge cases where there are enough
    volunteers/resources to have more than one person doing data entry
    (maybe 2-3 in practice, but lets say 10-12 for arguments sake to pad
    things a bit).

    What I was wondering is what would be a good way of handling this with a
    PyQt app? Build the desktop app first, and add some sort of
    functionality to enable a lightweight web server and framework for the
    additional data entry 'clients'? Or would it be better to create
    dedicated PyQt client apps to connect to the PC/laptop running the
    'main' application? Should I go a different direction entirely, with a
    complete self-hosted webapp built on a framework like web2py?

    As you can probably tell, I have only a vaguely fuzzy idea of 'how' at
    this point... but I would like to be able to proceed with some
    confidence that as I get further down the road I'm not going to run into
    a dead-end and have to start over down a different path.

    Thanks,

    Monte
    Monte Milanuk, Feb 21, 2013
    #1
    1. Advertising

  2. > I've been working at learning python off and on now for a while, with
    > a couple programs in mind as a goal - kind of specialized stuff that
    > I can't seem to find a good match for already available, competitor
    > records, score-keeping & results for an amateur sports tournament.


    So you want to develop a database application. That's a standard case.

    > Probably 98-99% of the time the match administration would be done by
    > a single individual on a single PC, which seems like it would be
    > nearly ideal for a desktop application implemented in PyQt4 or
    > similar. The problem is (as usual) those edge cases where there are
    > enough volunteers/resources to have more than one person doing data
    > entry (maybe 2-3 in practice, but lets say 10-12 for arguments sake
    > to pad things a bit).


    PostgreSQL and the frameworks mentioned below don't care for the number
    of clients. You could buy a zSeries (or whatever they are called now)
    from IBM and serve thousands of clients simultaneously if you needed to.

    > What I was wondering is what would be a good way of handling this
    > with a PyQt app? Build the desktop app first, and add some sort of
    > functionality to enable a lightweight web server and framework for
    > the additional data entry 'clients'?


    No, you just implement a GUI in whatever GUI framework you want (PyQt,
    PyGTK, wxPython) and use a client-server RDBMS for storage.

    No web-nonsense gadgetry required with bloated cursor-animation
    "browsers" etc..

    For the storage I recommend PostgreSQL, for the client GUI, there are
    several frameworks available:

    using PyQt (& Sqlalchemy):
    Pypapi: www.pypapi.org
    Camelot: www.python-camelot.com
    Qtalchemy: www.qtalchemy.org

    using PyGTK:
    Sqlkit: sqlkit.argolinux.org (also uses Sqlalchemy)
    Kiwi: www.async.com.br/projects/kiwi
    Glom: www.glom.org

    using wxPython:
    Dabo: www.dabodev.com
    Defis: sourceforge.net/projects/defis (Russian only)
    GNUe: www.gnuenterprise.org

    Pypapi, Camelot, Sqlkit and Dabo seem to be the most active and best
    documented/supported ones.

    Sincerely,

    Wolfgang
    Wolfgang Keller, Feb 22, 2013
    #2
    1. Advertising

  3. Yes, I am looking at a database-centric application. I know that the
    'larger' databases such as PostgreSQL, MySQL, etc. would not have any
    problem handling that small amount of traffic.

    My concern is that using postgres or mysql for this would be akin to
    using a sledgehammer to swat a fly, when sqlite could most likely handle
    the load well enough (I think) since the handful of people doing data
    entry would rarely (if ever) be trying to write to the same record.
    That would be the whole point of having multiple people doing data entry
    in this situation - each one handling a different competitors entry form
    or submitted scores.

    My other reason for wanting one 'central' app is that there are various
    functions (setting up the tournament, closing registration, editing
    scores, finalizing results) that I really *don't* want the
    satellite/client apps to be able to do. My personal view is that sort
    of thing needs to be handled from one point, by one person (the match
    director or chief stats officer, depending on the size of the event).

    That is why I was looking at things in terms of having one central app
    that handles the database, whether locally via sqlite or postgres or
    whatever, but have the clients access go through that main application
    in order to ensure that all they have is a limited set of CRUD abilities
    for competitor registration and entering scores.

    Thanks for the links... some of those I was already aware of (Camelot,
    Dabo) but some of the others are new (QtAlchemy, etc). Should make for
    interesting reading!

    Thanks,

    Monte
    Monte Milanuk, Feb 22, 2013
    #3
  4. Monte Milanuk

    Alec Taylor Guest

    Monte: I noticed you mentioned web2py; that would be my recommendation.

    You also mention different features being available to different
    users; perfect use-case for web2py's built-in RBAC.

    Scalability: Go with Postgres, MySQL; or considering how much data
    you're talking about, even SQLite would be a close enough fit!

    Another advantage of sticking to the web that hasn't been mentioned so
    far is agnostic interoperability.

    E.g.: you can CRUD on your TV (e.g.: if it runs Android); or on your
    phone (e.g.: if you use twitter-bootstrap; which web2py comes with out
    of the box; but is usable in any framework)

    On Sat, Feb 23, 2013 at 3:50 AM, Monte Milanuk <> wrote:
    > Yes, I am looking at a database-centric application. I know that the
    > 'larger' databases such as PostgreSQL, MySQL, etc. would not have any
    > problem handling that small amount of traffic.
    >
    > My concern is that using postgres or mysql for this would be akin to using a
    > sledgehammer to swat a fly, when sqlite could most likely handle the load
    > well enough (I think) since the handful of people doing data entry would
    > rarely (if ever) be trying to write to the same record. That would be the
    > whole point of having multiple people doing data entry in this situation -
    > each one handling a different competitors entry form or submitted scores.
    >
    > My other reason for wanting one 'central' app is that there are various
    > functions (setting up the tournament, closing registration, editing scores,
    > finalizing results) that I really *don't* want the satellite/client apps to
    > be able to do. My personal view is that sort of thing needs to be handled
    > from one point, by one person (the match director or chief stats officer,
    > depending on the size of the event).
    >
    > That is why I was looking at things in terms of having one central app that
    > handles the database, whether locally via sqlite or postgres or whatever,
    > but have the clients access go through that main application in order to
    > ensure that all they have is a limited set of CRUD abilities for competitor
    > registration and entering scores.
    >
    > Thanks for the links... some of those I was already aware of (Camelot, Dabo)
    > but some of the others are new (QtAlchemy, etc). Should make for
    > interesting reading!
    >
    >
    > Thanks,
    >
    > Monte
    >
    > --
    > http://mail.python.org/mailman/listinfo/python-list
    Alec Taylor, Feb 22, 2013
    #4
  5. On 02/22/2013 08:57 AM, Alec Taylor wrote:
    > Monte: I noticed you mentioned web2py; that would be my recommendation.
    >
    > You also mention different features being available to different
    > users; perfect use-case for web2py's built-in RBAC.
    >
    > Scalability: Go with Postgres, MySQL; or considering how much data
    > you're talking about, even SQLite would be a close enough fit!
    >
    > Another advantage of sticking to the web that hasn't been mentioned so
    > far is agnostic interoperability.
    >
    > E.g.: you can CRUD on your TV (e.g.: if it runs Android); or on your
    > phone (e.g.: if you use twitter-bootstrap; which web2py comes with out
    > of the box; but is usable in any framework)
    >



    Web2py does seem pretty attractive in that it seems to come with a lot
    of functionality rolled in already. It seems to be pretty easy to
    deploy... since this would be more of a case where the volunteer match
    directors are not necessarily computer gurus, and something that can
    literally run from a USB stick on nearly any computer has its benefits.
    I've seen some examples (I think) of twitter-bootstrap in some other
    demos of flask, and it looked reasonably attractive without being too
    over the top. web2py's DAL seems fairly straight-forward too. Looks
    like I may have to get more fluent in CSS & javascript, though...
    Monte Milanuk, Feb 22, 2013
    #5
  6. On 02/22/2013 02:49 PM, Monte Milanuk wrote:
    > Web2py does seem pretty attractive in that it seems to come with a lot
    > of functionality rolled in already. It seems to be pretty easy to
    > deploy... since this would be more of a case where the volunteer match
    > directors are not necessarily computer gurus, and something that can
    > literally run from a USB stick on nearly any computer has its benefits.
    > I've seen some examples (I think) of twitter-bootstrap in some other
    > demos of flask, and it looked reasonably attractive without being too
    > over the top. web2py's DAL seems fairly straight-forward too. Looks
    > like I may have to get more fluent in CSS & javascript, though...


    If you just use web2py to implement the database calls and business
    logic, and to implement a simple, clean API (RPC really) for the clients
    to talk to, then you can still use your non-web UI tools like PyQt. But
    as an added bonus you can do a web interface as well. You'll have
    flexibility either way. A client is a client, whether it's web-bases
    and running on the same server, or a remote app using RPC over HTTP.

    I think all web-based apps should expose a web service (an API). that
    way you have flexibility to do a variety of front-ends. Normal web
    browser, mobile browser, a standalone app (think android or iphone).

    As far as doing client/server stuff with just a database engine, unless
    you have tight control over the environment end to end, from a security
    pov, it's not a good idea to expose the database engine itself to the
    internet. Better to put a restricted web services API in front of it
    that handles all the authorization needs (access-control) on the
    detailed level that you require.
    Michael Torrie, Feb 22, 2013
    #6
  7. On Fri, 22 Feb 2013 08:50:05 -0800, Monte Milanuk <>
    declaimed the following in gmane.comp.python.general:


    > My concern is that using postgres or mysql for this would be akin to
    > using a sledgehammer to swat a fly, when sqlite could most likely handle
    > the load well enough (I think) since the handful of people doing data
    > entry would rarely (if ever) be trying to write to the same record.
    > That would be the whole point of having multiple people doing data entry
    > in this situation - each one handling a different competitors entry form
    > or submitted scores.
    >


    Problem: SQLite3 (and M$ JET/Access) are considered "file server"
    databases. Each instance of the program accessing the database is
    directly opening the database file(s). While SQLite3 has a fairly
    complex locking system, the normal locking is NOT "per record". Instead
    it allows for multiple readers to be active at once; the first
    connection/cursor to attempt to write anything will block any new
    attempts to read, and will be blocked until all other active readers
    exit (and none of those other readers can attempt to write). When there
    are no other open readers, the writer can finish and commit changes.

    > That is why I was looking at things in terms of having one central app
    > that handles the database, whether locally via sqlite or postgres or
    > whatever, but have the clients access go through that main application
    > in order to ensure that all they have is a limited set of CRUD abilities
    > for competitor registration and entering scores.
    >

    At which point you've essentially written the conflict management
    that a client/server system already provides.
    --
    Wulfraed Dennis Lee Bieber AF6VN
    HTTP://wlfraed.home.netcom.com/
    Dennis Lee Bieber, Feb 23, 2013
    #7
  8. On Sat, Feb 23, 2013 at 11:20 AM, Dennis Lee Bieber
    <> wrote:
    > Problem: SQLite3 (and M$ JET/Access) are considered "file server"
    > databases. Each instance of the program accessing the database is
    > directly opening the database file(s). While SQLite3 has a fairly
    > complex locking system, the normal locking is NOT "per record". Instead
    > it allows for multiple readers to be active at once; the first
    > connection/cursor to attempt to write anything will block any new
    > attempts to read, and will be blocked until all other active readers
    > exit (and none of those other readers can attempt to write). When there
    > are no other open readers, the writer can finish and commit changes.


    Also MySQL, when using the default MyISAM back-end. In contrast,
    PostgreSQL uses MVCC to permit lock-free reading in most cases (you
    keep reading the version you can "see", and a writer happily tinkers
    with a new version; until the writer COMMITs, its version is invisible
    to you). There's more overhead to the PostgreSQL system, but it scales
    better with multiple writers. (MySQL is primarily designed for dynamic
    web sites, where there are thousands of readers but only (relatively)
    occasional writers.)

    ChrisA
    Chris Angelico, Feb 23, 2013
    #8
  9. Monte Milanuk

    Alec Taylor Guest

    On Sat, Feb 23, 2013 at 10:37 AM, Michael Torrie <> wrote:
    > On 02/22/2013 02:49 PM, Monte Milanuk wrote:
    >> Web2py does seem pretty attractive in that it seems to come with a lot
    >> of functionality rolled in already. It seems to be pretty easy to
    >> deploy... since this would be more of a case where the volunteer match
    >> directors are not necessarily computer gurus, and something that can
    >> literally run from a USB stick on nearly any computer has its benefits.
    >> I've seen some examples (I think) of twitter-bootstrap in some other
    >> demos of flask, and it looked reasonably attractive without being too
    >> over the top. web2py's DAL seems fairly straight-forward too. Looks
    >> like I may have to get more fluent in CSS & javascript, though...

    >
    > If you just use web2py to implement the database calls and business
    > logic, and to implement a simple, clean API (RPC really) for the clients
    > to talk to, then you can still use your non-web UI tools like PyQt. But
    > as an added bonus you can do a web interface as well. You'll have
    > flexibility either way. A client is a client, whether it's web-bases
    > and running on the same server, or a remote app using RPC over HTTP.
    >
    > I think all web-based apps should expose a web service (an API). that
    > way you have flexibility to do a variety of front-ends. Normal web
    > browser, mobile browser, a standalone app (think android or iphone).
    >
    > As far as doing client/server stuff with just a database engine, unless
    > you have tight control over the environment end to end, from a security
    > pov, it's not a good idea to expose the database engine itself to the
    > internet. Better to put a restricted web services API in front of it
    > that handles all the authorization needs (access-control) on the
    > detailed level that you require.
    > --
    > http://mail.python.org/mailman/listinfo/python-list


    Michael Torrie: Have seen a few PyWt examples in alpha if that's what
    you describing…

    But there would still be more implementation overhead then just using
    e.g.: SQLFORM(db.table_name) to create a CRUD form.

    I don't see any disadvantage of using web2py for everything; unless
    we're talking decentralised infrastructure in which case a queuing
    mechanism would likely be better; and have each client implement a
    server as well. (thus still no use-case for Qt).

    Also SQLite has a number of excellent features, namely 2 file deployments.

    So it's very portable. Otherwise for postgres or mysql you'd probably
    need to package in your own silent installer (which admittedly isn't
    overly difficult; but is quite involved)…

    On Sat, Feb 23, 2013 at 8:49 AM, Monte Milanuk <> wrote:
    > Looks like I may have to get more fluent in
    > CSS & javascript, though...


    Understanding how `style` attributes work, how to use FireBug (or
    Chrome Dev Tools); and finding a good javascript widget library (e.g.:
    from Twitter Bootstrap) should be more than enough for your project.

    In fact; it's been enough for almost all my projects!

    (though now I'm moving to AngularJS will need to get more involved on
    the js front :p)
    Alec Taylor, Feb 24, 2013
    #9
  10. > My concern is that using postgres or mysql for this would be akin to
    > using a sledgehammer to swat a fly,


    I wouldn't use MySQL for anything that requires anything else than
    "select".

    And PostgreSQL has extremely spartanic resource requirements in the
    default configuration. It runs on Linux on hardware where (the
    most recent) Windows alone wouldn't run.

    > My other reason for wanting one 'central' app is that there are
    > various functions (setting up the tournament, closing registration,
    > editing scores, finalizing results) that I really *don't* want the
    > satellite/client apps to be able to do.


    Easy, you simply restrict access rights to the corresponding tables
    for the individual users. Any halfway decent database application
    framework will allow to configure the application correspondingly for
    each user.

    Sincerely,

    Wolfgang
    Wolfgang Keller, Feb 24, 2013
    #10
  11. > As far as doing client/server stuff with just a database engine,
    > unless you have tight control over the environment end to end, from a
    > security pov, it's not a good idea to expose the database engine
    > itself to the internet. Better to put a restricted web services API
    > in front of it that handles all the authorization needs
    > (access-control) on the detailed level that you require.


    Excuse me but that's bullshit.

    PostgreSQL is definitely more secure than any self-made RPC protocol
    with a self-made "web" server on top of SQLite that re-invents what
    PostgreSQL provides "out of the box" and much more efficient that http
    could ever do it. Experience with security of PostgreSQL servers exposed
    to "the internet" has been capitalised for much more than a decade now.
    You won't get anywhere close to that level of security (and reliability)
    with your private selfmade webnonsense anytime soon.

    And if there's anything that all those scriptkiddies know their way
    with it's http servers.

    Sincerely,

    Wolfgang
    Wolfgang Keller, Feb 24, 2013
    #11
  12. On Mon, Feb 25, 2013 at 1:31 AM, Wolfgang Keller <> wrote:
    >> As far as doing client/server stuff with just a database engine,
    >> unless you have tight control over the environment end to end, from a
    >> security pov, it's not a good idea to expose the database engine
    >> itself to the internet. Better to put a restricted web services API
    >> in front of it that handles all the authorization needs
    >> (access-control) on the detailed level that you require.

    >
    > Excuse me but that's bullshit.


    I don't use the term but I absolutely agree with the sentiment. Of
    course, if you're assuming a MySQL setup, then yes, exposing the
    database engine directly would have risks. But I grew up with DB2, and
    there were MANY ways in which you could control exactly what people
    could do (views and stored procedures being the two easiest/most
    commonly used) - to the extent that one of the recommended
    organizational structures was to have the end-user login actually *be*
    the database connection credentials, and to have your fancy app just
    connect remotely. There's a guarantee that someone who logs in as a
    non-administrator cannot access administrative functionality.
    PostgreSQL has all those same features, packaged up in an open source
    system; MySQL has a philosophical structure of "user logs in to app,
    but app logs in to database as superuser regardless of user login".

    ChrisA
    Chris Angelico, Feb 24, 2013
    #12
  13. On 24/02/2013 16:58, Chris Angelico wrote:

    [...]

    > MySQL has a philosophical structure of "user logs in to app,
    > but app logs in to database as superuser regardless of user login".
    >


    Out of curiosity, is there anything wrong with that approach?

    The project I am developing is a business/accounting application, which
    supports multiple database systems - at this stage, PostgreSQL, MS SQL
    Server, and sqlite3.

    I use exactly the philosophy you describe above. If I relied on the
    RDBMS's internal security model, I would have to understand and apply
    each one separately.

    Any comments will be appreciated.

    Frank Millman
    Frank Millman, Feb 25, 2013
    #13
  14. On Mon, Feb 25, 2013 at 5:14 PM, Frank Millman <> wrote:
    > On 24/02/2013 16:58, Chris Angelico wrote:
    >> MySQL has a philosophical structure of "user logs in to app,
    >> but app logs in to database as superuser regardless of user login".

    >
    > Out of curiosity, is there anything wrong with that approach?
    >
    > The project I am developing is a business/accounting application, which
    > supports multiple database systems - at this stage, PostgreSQL, MS SQL
    > Server, and sqlite3.
    >
    > I use exactly the philosophy you describe above. If I relied on the RDBMS's
    > internal security model, I would have to understand and apply each one
    > separately.


    Fundamentally no; it's a viable approach, as evidenced by the success
    of MySQL and the myriad applications that use it in this way. It's a
    matter of damage control and flexibility. Suppose your web server were
    to be compromised - there are so many exploits these days that can
    result in files on the server being unexpectedly read and transmitted
    to the attacker. Your database superuser password (or, let's hope,
    "just" database admin) is compromised, and with it the entire
    database. This also forces you to treat the web application (usually
    PHP scripts) as back-end.

    In contrast, if you control permissions in the database itself, you
    can actually treat the application as the front-end. You can happily
    deploy it, exactly as-is, to untrusted systems. Sure, your typical PHP
    system won't ever need that, but when you write something in Python,
    it's much more plausible that you'd want to run it as a desktop app
    and connect remotely to the database. It's flexibility that you may or
    may not use, but is still nice to have.

    Most RDBMSes have a broadly similar permissions system; at any rate,
    no more different than the ancillaries (PostgreSQL has the "SERIAL"
    type (which is a shortcut for INTEGER with a default value and an
    associated SEQUENCE object), MySQL has AUTO_INCREMENT, etc, etc - if
    you're going to support all of them, you either go for the lowest
    common denominator, or you have different code here and there anyway).
    You control access of different types to different named objects;
    reading requires SELECT privilege on all tables/views read from,
    editing requires INSERT/UPDATE, etc. For finer control than the table,
    just deny all access to the table and grant access to a view. For more
    complicated stuff ("edits to this table must have corresponding
    entries in the log"), either triggers or stored procedures can do the
    job.

    It may take a lot of work to get the permissions down to their
    absolute minimum, but one easy "half-way house" would be to create a
    read-only user - SELECT permission on everything, no other perms. Not
    applicable to all situations, but when it is, it's an easy way to
    manage the risk of compromise.

    I'm sure others can weigh in with a lot more detail.

    ChrisA
    Chris Angelico, Feb 25, 2013
    #14
  15. On 25/02/2013 08:35, Chris Angelico wrote:
    > On Mon, Feb 25, 2013 at 5:14 PM, Frank Millman <> wrote:
    >> On 24/02/2013 16:58, Chris Angelico wrote:
    >>> MySQL has a philosophical structure of "user logs in to app,
    >>> but app logs in to database as superuser regardless of user login".

    >>
    >> Out of curiosity, is there anything wrong with that approach?
    >>
    >> The project I am developing is a business/accounting application, which
    >> supports multiple database systems - at this stage, PostgreSQL, MS SQL
    >> Server, and sqlite3.
    >>
    >> I use exactly the philosophy you describe above. If I relied on the RDBMS's
    >> internal security model, I would have to understand and apply each one
    >> separately.

    >
    > Fundamentally no; it's a viable approach, as evidenced by the success
    > of MySQL and the myriad applications that use it in this way. It's a
    > matter of damage control and flexibility. Suppose your web server were
    > to be compromised - there are so many exploits these days that can
    > result in files on the server being unexpectedly read and transmitted
    > to the attacker. Your database superuser password (or, let's hope,
    > "just" database admin) is compromised, and with it the entire
    > database. This also forces you to treat the web application (usually
    > PHP scripts) as back-end.
    >


    [snip much valuable food for thought]

    >
    > I'm sure others can weigh in with a lot more detail.
    >


    Thanks for the input, Chris - much appreciated.

    I don't have a lot of experience in this area, but it is a very
    important topic and I have applied my mind to the issues as best I can,
    so I would appreciate a critique of my current approach.

    The main app is written in python. It is designed to run on a server. It
    could be on the same server as the database or not - the person setting
    up the system supplies the connection parameters.

    The app runs a web server (cherrypy) which anyone can connect to via a
    browser, with a valid userid and password. User credentials are stored
    in the database, and the system has its own mapping of which users (or
    rather roles) have access to which tables. The front end is written in
    Javascript.

    So to refer to your two concerns of damage control and flexibility, the
    second one does not really apply in my case - I would never want the
    main app to run on a desktop.

    Regarding security, obviously it is a concern. However, the various user
    ids and passwords have to be stored *somewhere*, and if it is
    compromised I would have thought that they would be equally vulnerable.

    There is one idea I think is worth looking into, when I have time. I
    subscribe to the 'getmail' mailing list, and for a long time the
    maintainer has resisted pressure to encrypt the mailbox password in the
    configuration file, on the grounds that if the password is vulnerable,
    the encryption method is equally vulnerable, so it would give a false
    sense of security. However, he has recently been persuaded of the merits
    of using something called a 'keyring'. I don't know much about it, but
    it is on my list of things to look at some time.

    All comments welcome.

    Frank
    Frank Millman, Feb 25, 2013
    #15
  16. On Mon, 25 Feb 2013 17:35:44 +1100, Chris Angelico <>
    declaimed the following in gmane.comp.python.general:


    > It may take a lot of work to get the permissions down to their
    > absolute minimum, but one easy "half-way house" would be to create a
    > read-only user - SELECT permission on everything, no other perms. Not
    > applicable to all situations, but when it is, it's an easy way to
    > manage the risk of compromise.
    >

    I think I'd recommend that even this read permission be limited to
    the tables required by the application... Wouldn't want someone to
    "accidentally" read the database user account tables, would we?

    MySQL's permission system, as I recall (I'm too lazy to grab one of
    the five MySQL reference books on my shelf) can be set for "database",
    "table", and "column" levels. (Setting permissions at the column level
    would be painful, IMO -- especially if one has a goodly number of tables
    with lots of fields; Creating a view and using a table level restriction
    may be better -- not sure if MySQL views honor the access restrictions
    though)

    --
    Wulfraed Dennis Lee Bieber AF6VN
    HTTP://wlfraed.home.netcom.com/
    Dennis Lee Bieber, Feb 25, 2013
    #16
  17. On Mon, 25 Feb 2013 10:02:07 +0200, Frank Millman <>
    declaimed the following in gmane.comp.python.general:


    > The app runs a web server (cherrypy) which anyone can connect to via a
    > browser, with a valid userid and password. User credentials are stored
    > in the database, and the system has its own mapping of which users (or
    > rather roles) have access to which tables. The front end is written in
    > Javascript.
    >


    <snip>

    > Regarding security, obviously it is a concern. However, the various user
    > ids and passwords have to be stored *somewhere*, and if it is
    > compromised I would have thought that they would be equally vulnerable.
    >

    Which maps fairly directly to the MySQL (and likely other DBMS)
    access control. If you are already storing UserID/passwords in a
    (restricted access) table -- you might as well make them the native
    database user accounts and use the database restriction controls to
    limit access to database/table/column... Roles may be trickier if a
    single userID is allowed to act in different roles (but then, if a user
    can specify which role they are acting as, nothing prevents them from
    always picking the most capable role, so just give them the accesses for
    the highest role they are allowed).

    --
    Wulfraed Dennis Lee Bieber AF6VN
    HTTP://wlfraed.home.netcom.com/
    Dennis Lee Bieber, Feb 25, 2013
    #17
  18. On Tue, Feb 26, 2013 at 10:26 AM, Dennis Lee Bieber
    <> wrote:
    > On Mon, 25 Feb 2013 17:35:44 +1100, Chris Angelico <>
    > declaimed the following in gmane.comp.python.general:
    >> It may take a lot of work to get the permissions down to their
    >> absolute minimum, but one easy "half-way house" would be to create a
    >> read-only user - SELECT permission on everything, no other perms. Not
    >> applicable to all situations, but when it is, it's an easy way to
    >> manage the risk of compromise.
    >>

    > I think I'd recommend that even this read permission be limited to
    > the tables required by the application... Wouldn't want someone to
    > "accidentally" read the database user account tables, would we?


    Of course; once you have the concept of divided access levels, you can
    take it whereever you like. But some systems don't even HAVE "database
    user account tables" as such; look at this site:

    http://rosuav.com/1/

    That's an old PHP-based site of mine, originally done in MySQL, now
    using PostgreSQL but not as yet moved off PHP. In index.php, the
    database connection has read-only access; there's a separate page that
    lets me log in using higher database credentials, and thus gain the
    power to add/edit entries. It's fine for the read-only user to have
    access to every table, because there's really only one table (not
    counting statistics).

    ChrisA
    Chris Angelico, Feb 26, 2013
    #18
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jane Davis

    Network Service account over network

    Jane Davis, Jun 22, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    424
    Kevin Spencer
    Jun 22, 2005
  2. Replies:
    7
    Views:
    6,675
    Alex Molochnikov
    Feb 23, 2005
  3. ulysses
    Replies:
    4
    Views:
    751
    Werner Schiendl
    Oct 22, 2003
  4. R.A.M.
    Replies:
    4
    Views:
    546
    Jesse Houwing
    Oct 6, 2007
  5. Michael Torrie

    Re: PyQT app accessible over network?

    Michael Torrie, Feb 21, 2013, in forum: Python
    Replies:
    0
    Views:
    156
    Michael Torrie
    Feb 21, 2013
Loading...

Share This Page