List:General Discussion« Previous MessageNext Message »
From:David Campbell Date:November 4 2007 8:19pm
Subject:Re: Virtual Hosting Privileges
View as plain text  
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

Thread
Virtual Hosting PrivilegesGrant Peel4 Nov
  • Re: Virtual Hosting PrivilegesKevin Waterson4 Nov
  • Re: Virtual Hosting PrivilegesDavid Campbell4 Nov
    • Re: Virtual Hosting PrivilegesMichael Dykman7 Nov