Prev: not able to connect to MySQL
Next: ZipArchive, but without files
From: Tom Barrett on 22 Sep 2010 16:35
Hmm..

I am familiar with PMA. I would for the purpose of this project consider it
too technical for the target user base. The point is to create a GUI layer
that would manage these things.

For example, the 'add client' screen would ask for four things; name,
description, username and password. Then behind the scenes a database would
be created, the user created, permissions granted and a pre-configure set of
tables built (and populated).

My reservations come from security issues (which, as an aside, are also
discussed about PMA), allowing a normal user account CREATE and GRANT on the
database.

Maybe I'm being too fuddy-duddy cautious.
From: Bastien Koert on 22 Sep 2010 16:40
On Wed, Sep 22, 2010 at 4:35 PM, Tom Barrett <tom(a)miramedia.co.uk> wrote:
> Hmm..
>
> I am familiar with PMA. I would for the purpose of this project consider it
> too technical for the target user base. The point is to create a GUI layer
> that would manage these things.
>
> For example, the 'add client' screen would ask for four things; name,
> description, username and password. Then behind the scenes a database would
> be created, the user created, permissions granted and a pre-configure set of
> tables built (and populated).
>
> My reservations come from security issues (which, as an aside, are also
> discussed about PMA), allowing a normal user account CREATE and GRANT on the
> database.
>
> Maybe I'm being too fuddy-duddy cautious.
>

Not at all. What I would suggest is that you create a separate mysql
user that is used exclusively by the script to do the create stuff.
The regular application user account should not have those privileges
at all.

Another option, if immediate response is not required, is to save this
data into the system for a cron script with another user account to
run.

Is there a reason for you not to place all the data in one DB and just
separate them out based on user id, to ensure they only see their own
data?

--

Bastien

Cat, the other other white meat
From: tedd on 23 Sep 2010 13:04
At 9:35 PM +0100 9/22/10, Tom Barrett wrote:
>Hmm..
>
>I am familiar with PMA. I would for the purpose of this project consider it
>too technical for the target user base. The point is to create a GUI layer
>that would manage these things.
>
>For example, the 'add client' screen would ask for four things; name,
>description, username and password. Then behind the scenes a database would
>be created, the user created, permissions granted and a pre-configure set of
>tables built (and populated).
>
>My reservations come from security issues (which, as an aside, are also
>discussed about PMA), allowing a normal user account CREATE and GRANT on the
>database.
>
>Maybe I'm being too fuddy-duddy cautious.

Tom:

No, but from what you've said, I don't think the end user must have
privileges and the ability to create a database and tables. It sounds
more like allowing the user to set up his own admin for acceptable
users -- there's a big difference.

So, what you need to define is what the client and his users want to
do. From that, we can determine what they need.

Cheers,

tedd

--
-------
http://sperling.com/
From: Tom Barrett on 24 Sep 2010 06:19
On 22 September 2010 21:40, Bastien Koert <phpster(a)gmail.com> wrote:

> Not at all. What I would suggest is that you create a separate mysql
> user that is used exclusively by the script to do the create stuff.
> The regular application user account should not have those privileges
> at all.
>

I'm not actually that familiar with DB admin to that extent. I have either
app users with lock+crud on specific databases, or root. As a an aside,
would you know if there is a level of permissions for a user between app and
root that would be 'sensibly secure' (it will be MySQL 5)?


> Another option, if immediate response is not required, is to save this
> data into the system for a cron script with another user account to
> run.
>

This was sort of my first instinct. I ponder writing a small daemon/cron
that queries a database table (client list) and does all the 'build' bits.
The main issue with cron is that the users would want a fairly immediate
response. Seconds is acceptable, but a 5 minute cron might be too slow.


> Is there a reason for you not to place all the data in one DB and just
> separate them out based on user id, to ensure they only see their own
> data
>

For legal reasons. Each client must have separate data. I need to be able to
box up all the client data (containing multiple app instances) and be 100%
sure that I am giving them all their data and nobody else's.

On 23 September 2010 18:04, tedd <tedd.sperling(a)gmail.com> wrote:

> No, but from what you've said, I don't think the end user must have
> privileges and the ability to create a database and tables. It sounds more
> like allowing the user to set up his own admin for acceptable users --
> there's a big difference.
>
> So, what you need to define is what the client and his users want to do.
> From that, we can determine what they need.


Depending on what you mean by 'the client', all the client side things are
fine :)
The web front-end I am working on here is for internal use only. To allow
non-technical people to set up clients and their apps.

The more I look into this, the more I am leaning towards some shell scripts
for client management, invoking them by cron. Then if an immediate response
is needed someone technical will have to manually run the cron job. It looks
like the law of diminishing returns for me to build something really usable.
From: Andrew Ballard on 24 Sep 2010 09:57
On Fri, Sep 24, 2010 at 6:19 AM, Tom Barrett <tom(a)miramedia.co.uk> wrote:
[snip]
> I'm not actually that familiar with DB admin to that extent. I have either
> app users with lock+crud on specific databases, or root. As a an aside,
> would you know if there is a level of permissions for a user between app and
> root that would be 'sensibly secure' (it will be MySQL 5)?

It depends on the app, but phrases like 'sensibly secure' raise
caution flags for me.

I tend to go with the principle of least privilege. Where I currently
work, the admin functions for a web application are usually on an
intranet site that is completely separate from the public site.
Because of this, I have a different database user for each site. In
this case, these are database-only logins unrelated in any way to the
actual machine account used by the web servers.

On our newer development, nearly all table access is managed strictly
through stored procedures (we use SQL Server, but the same would work
for MySQL if you were so inclined), and each database user is only
granted execute permission on the specific procedures necessary for
that role. The only time we grant access directly to a table is in
cases where we just can't get a procedure to do what we need
efficiently or effectively. And, in those cases where I do need to
grant access to a table, I grant permission to only the
columns/operations necessary for that user.

If I encountered a case where I needed to allow a user to make schema
changes as you mentioned in your original post, I would create a
totally separate account -- again with no more permission than
necessary for its intended task. Depending on the needs of the
application, I'd decide whether that account was used by the web
server or via a script scheduled to execute at intervals as several
others have suggested in this thread.

I've not tried this, but you could probably write the logic needed to
create the database objects into a stored procedure. Then, you might
only need to grant permission to that procedure and not grant
permission to CREATE/ALTER anything. That would pretty well guarantee
that the only objects created are the ones you intended.

Andrew
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: not able to connect to MySQL
Next: ZipArchive, but without files