 | | | Virtual Hosting Privileges | Virtual Hosting Privileges 2007-11-06 - By Michael Dykman
Back
On Nov 4, 2007 3:19 PM, David Campbell <dave@(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/mysql?unsub=mdykman@(protected)
>
>
I think we are looking for something more like
GRANT ALL on theuserdatabase.* to theuser@(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
- mdykman@(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/mysql?unsub=mysql@(protected)
|
|
|