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
> 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?
> 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.