On Nov 4, 2007 3:19 PM, David Campbell <[EMAIL PROTECTED]> wrote: > > 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] > >
I think we are looking for something more like GRANT ALL on theuserdatabase.* to [EMAIL PROTECTED] IDENTIFIED BY 'whatever' The 'localhost' is just a guess.. substitute the network name of you web server as seen from the database. This will give each user 100% control of their own db except for having no privilege to GRANT, and no control over anyone else. I have used this plan for years without issue. -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
