Grant Peel wrote:
Hi all,

This is my first post to this list so bear with me if I ramble on a bit. I have been reading as much as I can on this subject and am yet to feel confident about an answer so I thought I would throw it at this list and see what comes back.

One thing I should mention up front is I am in no way a SQL power user, although, I have enough reference material that I can work my way through most problems.

We run a small web hosting and design service and have been offering MySQL as part of the basic setup for quite a while without any problems.

One of the things we offer it PHPMyAdmin which has server us well so far. Our clients seem to use it as a browsing tool more that anything, but there are a few that might be called 'Power Users" that use it for more SQL intense purposes. (FYI, I am using the PMA user, who only has 'select' privileges to the mysql users table).

Recently, I noticed something that caused me to reflect on the privileges that I currently have setup on the server, and was wondering if I could get some feedback and/or some light tutoring on the topic of privileges.

   It is perhaps prudent to start with some setup info:

FreeBSD 6.2 - Not using jails.
MySQL 4.1
PHP 4.4 - Soon to be upgraded to 5.x
Apache 2.2
PHPMyAdmin 2.11.0

What I am looking for at the end of this discussion is if my current privileges are secure enough, and, give all users the maximum functionality within the security model.

All mysql users are alos unix users. When I setup the accounts, I use scripts to setup the mysql user at the same time the UNIX users is setup. That having been said, Users can not log into a shell of the operating system, the unix user is setup to give ftp access, and access to thier back end admin panel.

   When a new mysql user is setup:

1. the user is assinged a password but is given no privileges in the mysql 'users' table, but is allowed to connect from localhost so thier scripts can run;
   sampleuser localhost XXXXXXXXXXXXXXXXXXXXXXX None


2. his/her blank database is setup, and that user is given access to is from localhost, although I have a few users that have requested, and were granted connections (via the 'hosts' table) remote access.

sampleuser_database sampleuser localhost Select | Insert | Update | Delete | Create | Drop | Grant | References | Index | Alter


I suspect that I want to remove the 'Grant' access from the privileges above. Here is where my confusion escelates....

If i remove it, can the user still add tables?

I suspect that I should consider removing the 'Drop' privilege. If I do that will the user be able to drop tables that he/she created? i.e. ones that reside in thier own database?

Hopefully, you all can see my confusion.

At the end of the day, I gues the question is: What privileges can I grant each user that will only allow them to completely manange thier own database without allowing them access to others databases?

or

What priveleges should I grant users at setup time?


There is no way I'd have a setup like this with other users databases in the same mysql instance.

I'd use openzv or similar to virtualise each account.

Dave


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to