PyQT app accessible over network?

[Chris Angelico]

On Mon, Feb 25, 2013 at 5:14 PM, Frank Millman <frank at chagford.com> 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