Can someone confirm -- Minimum MySQL Privileges?
I was glancing through the install instructions today, and noticed that we tell people to GRANT ALL privileges which seems a bit... excessive. I searched through core and came up with the following list of required privileges: Data ==== SELECT INSERT UPDATE DELETE Structure ========= CREATE ALTER INDEX DROP CREATE TEMPORARY Admin ===== LOCK TABLES Could someone take a look and let me know... Am I missing any? Are there things here that shouldn't be? -Angie P.S. I know we're only supposed to work on 4.7 RC stuff, but this is for some training stuff I'm doing to hopefully get more developers to work on Drupal, including testing and reviewing patches to help the 4.7 RC! ;)
On 13 Mar 2006, at 07:20, Angie Byron wrote:
I was glancing through the install instructions today, and noticed that we tell people to GRANT ALL privileges which seems a bit... excessive.
I searched through core and came up with the following list of required privileges:
Your list looks complete to me. -- Dries Buytaert :: http://www.buytaert.net/
Data ==== SELECT INSERT UPDATE DELETE
Structure ========= CREATE ALTER INDEX DROP CREATE TEMPORARY
Admin ===== LOCK TABLES
Considering the .install files that have been introduced, won't this table just be "User" and "Admin"? LOCK TABLES are used in db_next_id - i.e. in most INSERTs, thus becoming a user privilege. Similarly, I believe CREATE TEMPORARY will be used everytime (?) in the search module, again a user privilege. Cheers -K
In includes/database.mysql.inc there is this pesky function called db_next_id() that users need ... It has: LOCK TABLES sequences WRITE and REPLACE INTO sequences ... This is needed for normal operation (creating nodes, users registering, ...etc.) On 3/13/06, Angie Byron <drupal-devel@webchick.net> wrote:
I was glancing through the install instructions today, and noticed that we tell people to GRANT ALL privileges which seems a bit... excessive.
I searched through core and came up with the following list of required privileges:
Data ==== SELECT INSERT UPDATE DELETE
Structure ========= CREATE ALTER INDEX DROP CREATE TEMPORARY
Admin ===== LOCK TABLES
Could someone take a look and let me know... Am I missing any? Are there things here that shouldn't be?
-Angie
P.S. I know we're only supposed to work on 4.7 RC stuff, but this is for some training stuff I'm doing to hopefully get more developers to work on Drupal, including testing and reviewing patches to help the 4.7 RC! ;)
Dries Buytaert wrote:
Your list looks complete to me.
Cool, thanks Dries! Khalid B wrote:
In includes/database.mysql.inc there is this pesky function called db_next_id() that users need ...
It has: LOCK TABLES sequences WRITE and REPLACE INTO sequences ...
This is needed for normal operation (creating nodes, users registering, ...etc.)
Yeah, I couldn't find reference to a specific permission for "REPLACE" -- according to http://dev.mysql.com/doc/refman/5.1/en/replace.html it seems to take its permissions from a combination of INSERT and DELETE. "LOCK TABLES" is already added to the list, so I think we're good there. Karthik wrote:
Considering the .install files that have been introduced, won't this table just be "User" and "Admin"?
Sorry? I didn't quite understand this... Maybe what people are getting confused about is the heading for each group of permissions. This was taken from PHPMyAdmin, as it's part of a tutorial I'm doing that uses PHPMyAdmin. So disregard the headings "Data," "Structure," and "Admin" and focus more on the actual permissions themselves. :) Unless you mean I can further refine this to make it so that you only have certain permissions on certain tables or in certain instances (such as upgrading/module installation, which need the more 'advanced' CREATE, ALTER, INDEX, DROP, etc. permissions that 'normal' operations don't have)? But Drupal uses the same MySQL user for everything so I'm not sure how we could do that. -Angie
Khalid B wrote:
In includes/database.mysql.inc there is this pesky function called db_next_id() that users need ...
It has: LOCK TABLES sequences WRITE and REPLACE INTO sequences ...
This is needed for normal operation (creating nodes, users registering, ...etc.)
Yeah, I couldn't find reference to a specific permission for "REPLACE" -- according to http://dev.mysql.com/doc/refman/5.1/en/replace.html it seems to take its permissions from a combination of INSERT and DELETE. "LOCK TABLES" is already added to the list, so I think we're good there.
No. You missed the point. Normal users *need* LOCK TABLE privilege, not just admins.
No. You missed the point.
Normal users *need* LOCK TABLE privilege, not just admins.
Yeah, further down in my response I brought this up... the "Admin" thing is just how PHPMyAdmin categorizes the permission. Sorry for the confusion! -Angie
On Mon, 2006-03-13 at 12:10 -0500, Khalid B wrote:
Khalid B wrote:
In includes/database.mysql.inc there is this pesky function called db_next_id() that users need ...
It has: LOCK TABLES sequences WRITE and REPLACE INTO sequences ...
This is needed for normal operation (creating nodes, users registering, ...etc.)
Yeah, I couldn't find reference to a specific permission for "REPLACE" -- according to http://dev.mysql.com/doc/refman/5.1/en/replace.html it seems to take its permissions from a combination of INSERT and DELETE. "LOCK TABLES" is already added to the list, so I think we're good there.
No. You missed the point.
Normal users *need* LOCK TABLE privilege, not just admins.
Khalid, In a mysql context, LOCK TABLE is an admin privilege... W
Khalid, In a mysql context, LOCK TABLE is an admin privilege... W
That is the way it should be. But for Drupal on MySQL (by far the most common configuration out there), LOCK TABLE is needed by the user to do normal things. We thought about separating privileges by "user" and "admin" where "admin" would be the one needing to upgrade the database (i.e. CREATE, ALTER, ...etc.) Even in this case, we would still need LOCK TABLE for the normal "user", until we found a satisfactory way to do auto increment AND get the last added ID. My recollection is that we are OK unless we are using persistent connections ...
On Mar 13, 2006, at 9:10 AM, Khalid B wrote:
LOCK TABLES sequences WRITE
CivicSpace has been getting a complaints from users since CiviCRM introduced a lock table requirement to CivicSpace. We should investigate why many cheap hosting companies do not allow lock table permissions, and find out how widely this will effect Drupal upgrading to 4.7. Kieran
Kieran Lal wrote:
We should investigate why many cheap hosting companies do not allow lock table permissions, and find out how widely this will effect Drupal upgrading to 4.7.
One reason might be because the MySQL manual has LOCK TABLES permissions classified as a "server administration" permission, alongside stuff like SHUTDOWN and SUPER which you would never want to give a "peon" user: http://dev.mysql.com/doc/refman/4.1/en/privileges-provided.html CREATE TEMPORARY TABLES has this classification as well, although it seems like most support requests for 4.7 have been about hosts not providing LOCK TABLES permission. -Angie
On Mon, 2006-03-13 at 14:51 -0800, Kieran Lal wrote:
On Mar 13, 2006, at 9:10 AM, Khalid B wrote:
LOCK TABLES sequences WRITE
CivicSpace has been getting a complaints from users since CiviCRM introduced a lock table requirement to CivicSpace. We should investigate why many cheap hosting companies do not allow lock table permissions, and find out how widely this will effect Drupal upgrading to 4.7.
Kieran
1) their control panel software does not do it by default. 2) ignorance 3) draconian security methodology.
>> > > > LOCK TABLES sequences WRITE >> > > > > 1) their control panel software does not do it by default. > 2) ignorance > 3) draconian security methodology. resources. in theory a lock could get stuck and consume resources. I do not know whether it happens with mysql i am talking of theoires.
On Mar 13, 2006, at 2:51 PM, Kieran Lal wrote:
On Mar 13, 2006, at 9:10 AM, Khalid B wrote:
LOCK TABLES sequences WRITE
CivicSpace has been getting a complaints from users since CiviCRM introduced a lock table requirement to CivicSpace. We should investigate why many cheap hosting companies do not allow lock table permissions, and find out how widely this will effect Drupal upgrading to 4.7.
Apparently it's not CiviCRM that is causing the lock tables. Anyway some people have been complaining about lock table permissions. I'll research and report back. Kieran
Kieran
Anyway some people have been complaining about lock table permissions. I'll research and report back. Ok, apparently it's in all versions of Drupal. Kieran
On Mar 13, 2006, at 4:51 PM, Kieran Lal wrote:
CivicSpace has been getting a complaints from users since CiviCRM introduced a lock table requirement to CivicSpace. We should investigate why many cheap hosting companies do not allow lock table permissions, and find out how widely this will effect Drupal upgrading to 4.7.
Don't worry, users will have much bigger problems than LOCK TABLE when they upgrade to 4.7 ;) As mentioned in this thread, you now also need CREATE TEMPORARY TABLE permission (search). And for even basic admin use (installing a module), CREATE, DROP, ALTER and friends are also required. See: http://lists.drupal.org/archives/development/2005-10/msg00531.html http://lists.drupal.org/archives/development/2006-01/msg00676.html Allie Micka pajunas interactive, inc. http://www.pajunas.com scalable web hosting and open source strategies
participants (8)
-
Allie Micka -
Angie Byron -
Darrel O'Pry -
Dries Buytaert -
Karoly Negyesi -
Karthik -
Khalid B -
Kieran Lal