[drupal-devel] Re: drupal-devel Digest, Vol 34, Issue 65
What? With all the talk about making sure Drupal works in a shared hosting environment (ex. sessions stored in the DB instead of /tmp), you can't be serious. drupal-devel-request@drupal.org wrote:
Date: Mon, 24 Oct 2005 00:27:40 +0200 From: Gerhard Killesreiter <killesreiter@physik.uni-freiburg.de> Subject: [drupal-devel] Fun with mysql grants To: drupal-devel@drupal.org
Hi there! I just want to point out that as of last week we need a new database grant to run Drupal with mysql (and maybe pgsql too, no idea). Steven's search patch uses temporary tables for which you need the CREATE TEMPORARY TABLES permission. I am not sure, but I think this permission is not a default permission so we should make it clear that this permission is required in big, bold, blinking letters. And keep the flamethrower ready. Cheers, Gerhard
On Sun, 23 Oct 2005, David K Norman wrote:
What? With all the talk about making sure Drupal works in a shared hosting environment (ex. sessions stored in the DB instead of /tmp), you can't be serious.
I am German, I am always dead serious. I have no idea if and how this grant might affect Drupal's users to run it on shared hosting. I suspect that there will probably some hosters that cause problems - probably the same that cause problems with the LOCK TABLE permission on MySQL 4. This would be only another reason to recommend users to change hosters to such hosters that can do an upgrade from Mysql 3 to 4 _and_ read the upgrade docs. I suggest that you or somebody else who is interested in the issue simply tries to run current Drupal HEAD on such a hosting account and reports back. Cheers, Gerhard
I have no idea if and how this grant might affect Drupal's users to run it on shared hosting. I suspect that there will probably some hosters that cause problems - probably the same that cause problems with the LOCK TABLE permission on MySQL 4. This would be only another reason to recommend users to change hosters to such hosters that can do an upgrade from Mysql 3 to 4 _and_ read the upgrade docs.
I suggest that you or somebody else who is interested in the issue simply tries to run current Drupal HEAD on such a hosting account and reports back.
FWIW, The default value for MySQL's Create_tmp_table_priv is 'N'. Many shared hosting providers create a single administrative user with a GRANT ALL. While this is a bad idea, users on such a provider will not see any difference. Other hosting providers create administrative users and also site- specific users with minimal permissions. Odds are good that sites on these hosts will throw errors. We are in this category, and because roughly 1/3 of all sites we host use Drupal, we added the LOCK TABLES privilege to the list of site-user permissions. Frankly, I'm not too excited about adding yet another privilege to the list. So: +1 on using "big, bold letters" to highlight this issue in the docs +1 on donning flame-retardant attire and readying the fire extinguisher And because I'm a fan of the "too many settings!" dialog gridlock, I'll pose a question: can the advanced search features be optional? Allie Micka pajunas interactive, inc. http://www.pajunas.com scalable web hosting and open source strategies
On Sun, 23 Oct 2005, Allie Micka wrote:
I suggest that you or somebody else who is interested in the issue simply tries to run current Drupal HEAD on such a hosting account and reports back.
FWIW, The default value for MySQL's Create_tmp_table_priv is 'N'.
Thanks, was afraid that would be the case.
Many shared hosting providers create a single administrative user with a GRANT ALL. While this is a bad idea, users on such a provider will not see any difference.
Yep.
Other hosting providers create administrative users and also site- specific users with minimal permissions. Odds are good that sites on these hosts will throw errors. We are in this category, and because roughly 1/3 of all sites we host use Drupal, we added the LOCK TABLES privilege to the list of site-user permissions. Frankly, I'm not too excited about adding yet another privilege to the list.
What is the problem with that permission? Use of storage space in /tmp?
So: +1 on using "big, bold letters" to highlight this issue in the docs +1 on donning flame-retardant attire and readying the fire extinguisher
And because I'm a fan of the "too many settings!" dialog gridlock, I'll pose a question: can the advanced search features be optional?
Yes, you can use trip_search instead. Or find a way to refactor the query in search.module. :) Look for occurrences of db_query_temporary. Cheers, Gerhard
I suggest that you or somebody else who is interested in the issue simply tries to run current Drupal HEAD on such a hosting account and reports back.
FWIW, The default value for MySQL's Create_tmp_table_priv is 'N'.
I tried the following on two of my shared accounts. Both give me the create temporary table. mysql> create temporary table x(y int(1)); Query OK, 0 rows affected (0.04 sec) mysql> select * from x; Empty set (0.00 sec) mysql> insert into x values (1); Query OK, 1 row affected (0.00 sec) mysql> insert into x values (1); Query OK, 1 row affected (0.00 sec) mysql> insert into x values (1); Query OK, 1 row affected (0.00 sec) mysql> insert into x values (1); Query OK, 1 row affected (0.00 sec) mysql> select * from x; +------+ | y | +------+ | 1 | | 1 | | 1 | | 1 | +------+ 4 rows in set (0.00 sec) mysql> drop table x; It worked on both hosts without a problem. My concern would be performance, not availability. Then again, I say "my", since some may not have that privilige just like the LOCK TABLE issue that comes up every so often.
On 24/10/05, Khalid B <kb@2bits.com> wrote:
I tried the following on two of my shared accounts. Both give me the create temporary table.
Trying the same, I had to prefix the table name with my username, the same as any databases I have. That shouldn't be a problem, but it's worth noting. -- David Carrington
On Oct 23, 2005, at 8:01 PM, Gerhard Killesreiter wrote:
Frankly, I'm not too excited about adding yet another privilege to the list.
What is the problem with that permission? Use of storage space in / tmp?
Frankly? Laziness. We're happy to give any privilege to any user based on a single customer's criteria. But making a change that affects hundreds of current sites and setting a default that affects thousands of future sites is nontrivial. It means lots of time sifting through security reports and understanding all of the benefits and implications of such a change. This is especially challenging when you don't know the exact nature of each application on the host, and the best policy is to start with an absolute minimum set of privileges and loosen them only as required. For example, there were security advisories for MySQL's CREATE TEMPORARY TABLE functionality earlier this year. These are closed now, but not having that permission available to 100's of web apps during that window of opportunity was pretty handy. Hosts will may spend the time on a question like this, which is expensive and unrewarding in a competitive marketplace. Or they'll just effect the change or leave it entirely up to users and/or GRANT ALL, which is irresponsible. Or they'll refuse, which leads to many drupal support questions (see http://drupal.org/search/node/lock +tables ) and an overall barrier for Drupal. The benefits may outweigh the costs, but there will be costs. Unrelated, are these temporary tables being dropped? What happens when pconnect is in use? Allie Micka pajunas interactive, inc. http://www.pajunas.com scalable web hosting and open source strategies
Op maandag 24 oktober 2005 02:25, schreef Allie Micka:
So: +1 on using "big, bold letters" to highlight this issue in the docs +1 on donning flame-retardant attire and readying the fire extinguisher
Drupal is healthy enough as community. If there are enough peopel who REALLY need to do without that permission they will eventually develop something themselves. Its a scratch-your-own-itch-thing, IMO. Bèr -- PGP ber@webschuur.com http://www.webschuur.com/sites/webschuur.com/files/ber_webschuur.asc PGP berkessels@gmx.net http://www.webschuur.com/sites/webschuur.com/files/ber_gmx.asc
Allie Micka wrote:
FWIW, The default value for MySQL's Create_tmp_table_priv is 'N'.
Other hosting providers create administrative users and also site- specific users with minimal permissions. Odds are good that sites on these hosts will throw errors. We are in this category, and because roughly 1/3 of all sites we host use Drupal, we added the LOCK TABLES privilege to the list of site-user permissions. Frankly, I'm not too excited about adding yet another privilege to the list.
While it's annoying to have to add another permission, giving customers create temporary table privileges really ought to be fairly standard. It poses no security threats and has no obvious downsides other than possibly allowing the customer to shoot themselves in the foot by filling their database quota in one more way (they can already insert data into existing tables to fill it, after all). The upside is that intelligent clients and software will use temporary tables properly, and those tables will disappear when no longer needed instead of sitting around cluttering things up. However, this is just another aspect of the problem with anyone developing Drupal making any kind of assumption about what "most" hosting providers' environments look like. Drupal already assumes that the web server UID has write access to the file system, which is false at many hosting providers -- and at the providers where it is true, it is often an ignorant security oversight on the provider's part, not a carefully planned arrangement. The problem is there is no one size that fits all. Moreover, the more sophisticated the installation and operation of a piece of software, the more likely it is to collide with these hosting provider incompatibilities. Most of the time we should aim for the least common denominator, but sometimes that is too low and we have to painfully choose to do things which will exclude or make difficult the use of Drupal at some few hosting providers. -- chrisxj
participants (7)
-
Allie Micka -
Bèr Kessels -
Chris Johnson -
David Carrington -
David K Norman -
Gerhard Killesreiter -
Khalid B