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]
