I think I have the same problem as you! When I recover the mysql data, it happens. Then I found its DEFINER user was not the one I have set. Just use:
SELECT DEFINER FROM information_schema.views WHERE table_schema='labtech' LIMIT 10;
to show the DEFINER , you will find it was the older user. To modify it, I think you should look at this link on dba.stackexchange.com.
The problem here is very straightforward. Look at the order of things:
mysql -u$myuser -p$mypass -rs -e "CREATE USER '$dbuser'@'localhost' IDENTIFIED BY '$dbpass';";
mysql -u$myuser -p$mypass -rs -e "GRANT USAGE ON *. * TO '$dbuser'@'localhost' IDENTIFIED BY '$dbpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
mysql -u$myuser -p$mypass -rs -e "CREATE DATABASE IF NOT EXISTS $dbname ;";
mysql -u$myuser -p$mypass -rs -e "GRANT ALL PRIVILEGES ON $dbname . * TO '$dbuser'@'localhost';";
mysql -u$myuser -p$mypass -rs -e "FLUSH PRIVILEGES ;";
The first three(3) lines cannot be performed by $myuser
because . A superuser (such as root@localhost
) must perform the first three lines.
Here is something additional: Only root@localhost
can perform FLUSH PRIVILEGES;
. However, it is not even necessary because the GRANT
command internally performs FLUSH PRIVILEGES;
.
With these things in mind, this is how you can create new users with its own database:
rootuser=root
rootpass=rootpassword
myuser=creator
mypass=xxxx
dbuser=newuser
dbname=newdb
mysql -u$rootuser -p$rootpass -rs -e "CREATE USER '$dbuser'@'localhost' IDENTIFIED BY '$dbpass';";
mysql -u$rootuser -p$rootpass -rs -e "GRANT USAGE ON *.* TO '$dbuser'@'localhost' IDENTIFIED BY '$dbpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
mysql -u$rootuser -p$rootpass -rs -e "CREATE DATABASE IF NOT EXISTS $dbname ;";
mysql -u$rootuser -p$rootpass -e "GRANT ALL PRIVILEGES ON $dbname . * TO '$dbuser'@'localhost';";
If you seriously do not want to user root@localhost
, create the user as follows:
Step 01) As root@localhost
, login to mysql and run
SELECT PASSWORD('creatorpassword');
This will return a 41-character MD5-like string.
Step 02) As root@localhost
, login to mysql and run
SET SQL_LOG_BIN=0;
GRANT ALL PRIVILEGES ON *.* to creator@localhost IDENTIFIED BY PASSWORD '41-character MD5-like string';
This creates the user and sets his password. The first line SET SQL_LOG_BIN=0
simply prevents the command from being recorded in the binary logs if you have binary logging enabled.
Step 03) Remove the commands from the audit history of mysql.
Every time you login to mysql, the commands are recorded in a file called .mysql_history
. Run ths
cd
vi .mysql_history
Once you enter into vi
, run /SELECT PASSWORD
, hit enter. This will place you at the line where the command to print the PASSWORD function from text was done. Simply hit dd
and ZZ
, and you are done.
Going forward, you can user creator@localhost with the same rights as root@localhost and you have hidden the password well. You will just have to maintain its integrity.
Best Answer
this isn't a rights problem. mysql doesn't support variables as object names (where objects are tables, databases, columns, etc.). you will have to use prepared statements and build your command as a string. see http://forums.mysql.com/read.php?98,152150,152161#msg-152161
for example, your PREPARE statements need to look like:
not that this is NOT injection safe. make sure your have sanitized your "parameters" (database name, user name) before using these statements.
also, you're granting your privilege wrong.
TO '@newdbuser'
assigns the privilege to users connecting from a computer callednewdbuser
. you need to put the@
sign after the username.