rupertrealbear Posted October 14, 2008 Share Posted October 14, 2008 os Linux Kernel version 2.6.18-53.el5.028stab051.1 WebHostManager version 11.15.0 cPanel version 11.18.6 Apache 1.3.41 MySQL version 4.1.22 PHP version 5.2.5 Hi I wanted to see LOCK TABLES actually preventing another user from reading or writing to a table in my VPS (account name "charlie"). The following test seemed not to lock anything (the second user was always able to read and write to teh test table):- I created a table in a database charlie_gs, inserting one record CREATE DATABASE `gs`; CREATE TABLE `testlock` (`stuff` varchar(50) NOT NULL default ''); INSERT INTO `charlie_gs`.`testlock` (`stuff`) VALUES ('anything'); I created a user tstlk_can with LOCK TABLES priviledge on the database charlie_gs and UPDATE and SELECT priviledges on the new table testlock; I then created a user tstlk_can with only UPDATE and SELECT priviledges on the new table testlock GRANT USAGE ON * . * TO 'tstlk_can'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; GRANT LOCK TABLES ON `charlie\_gs` . * TO 'tstlk_can'@'localhost'; GRANT SELECT, UPDATE ON `charlie_gs`.`testlock` TO 'tstlk_can'@'localhost'; GRANT USAGE ON * . * TO 'tstlk_cant'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; GRANT SELECT, UPDATE ON `charlie_gs`.`testlock` TO 'tstlk_cant'@'localhost'; FLUSH PRIVILEGES ; My guess was that by creating two distinct connections (in a session controlled by PHP script), user tstlk_can could invoke LOCK TABLES and prevent user tstlk_cant from reading or writing table testlock as the following <?php header("Cache-Control: no-cache"); $link1 = mysql_connect("localhost","tstlk_can","",true); mysql_select_db("charlie_gs",$link1); $changeSQL = "UPDATE testlock SET stuff = 'Lock SUCCESSFUL: stuff as originally changed by tstlk_can'"; mysql_query($changeSQL,$link1); $lockSQL = "LOCK TABLES testlock AS tstlkRDLK READ, testlock AS tstlkWRLK"; mysql_query($lockSQL); $link2 = mysql_connect("localhost","tstlk_cant","",true); mysql_select_db("charlie_gs",$link2); #when un-commented, the following should unlock the tables implicitly #mysql_close($link1); $changeSQL = "UPDATE testlock SET stuff = 'Lock UNSUCCESSFUL: stuff changed by tstlk_cant'"; mysql_query($changeSQL,$link2); $affected = mysql_affected_rows($link2); $str = $changeSQL."<br>No of rows affected by UPDATE: ".strval($affected)."<br>"; $rs = mysql_query("SELECT stuff FROM testlock"); if ($rs) { $row = mysql_fetch_row($rs); $str .= $row[0]; } else $str .= "tstlk_cant could not SELECT on testlock"; ?> <HTML> <HEAD> <TITLE>testlock</TITLE> </HEAD> <BODY> <h1><?php echo $str; ?> </h1> </BODY> </HTML> I wondered if there might be some silent disabling when privileges are set in PHPMyAdmin rather than through cPanel? Hope someone can sehd light (I have probably assumed something wrong!) Regards rupertrealbear Quote Link to comment Share on other sites More sharing options...
fenway Posted October 14, 2008 Share Posted October 14, 2008 "LOCK TABLES" isn't valid mysql syntax -- it's LOCK TABLE -- so I'm confused. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.