MySQL Forums
Forum List  »  PHP

Problems with newy added users after restarting MySQL server
Posted by: Slava Slava
Date: September 20, 2013 05:17PM

I'm a novice in MySql and can't solve problem I've bumped into (Google search gave me no results). The problem is "error 1045 access denied for user" for new users.

I'm writing a small script on PHP that will automatically create new database in MySQL and add two new users with rights only for this database.
Here is the code :
$dbh = new PDO("mysql:host=localhost", $root, $rootpass);

Connecting to MySQL server with superadmin rights.
$dbh->SetAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbh->exec("CREATE DATABASE new_db ;
GRANT ALL PRIVILEGES
ON new_db.*
TO new_db_admin@localhost
IDENTIFIED BY 'password';
GRANT SELECT
ON new_db.*
TO new_db_guest@localhost
IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;"
);

With this script I want automatically create 2 new users for "new_db". First "new_db_admin" to work with database without involving of MySQL superadmin, and second to let users of the site coonect to database. ( IMHO that would be a good idea to give visitors rights "only to read", 'cause it will greatly reduce the possibility of SQL injection.)

After creating new DB and adding new users I create new connection to MySQL server with "new_db_admin" and add new tables. At this level everything works great. There are no problems with connecting to MySQL server from any user (new_db_admin, new_db_guest).

But after restarting MySQL server or restarting my PC I have "error 1045 access denied for user new_db_admin@localhost". Access to MySQL with another new user "new_db_guest" works good.

I tried to create only one user admin or guest. But after restarting MySQL server I got the same "error 1045 access denied for user new_db_admin@localhost" or if it was created only guest : "error 1045 access denied for user new_db_guest@localhost".

I also tried to "FLUSH PARAMETRS" after each creating of new user.

$dbh->exec("CREATE DATABASE new_db ;
CREATE USER new_db_admin@localhost IDENTIFIED BY '12345';
GRANT ALL ON new_db.* TO new_db_admin@localhost IDENTIFIED BY '12345';
FLUSH PRIVILEGES;"
);
$dbh->exec( "CREATE USER guest@localhost IDENTIFIED BY '111';
GRANT SELECT ON new_db.* TO guest@localhost IDENTIFIED BY '111';
FLUSH PRIVILEGES;"
);

Tried to do it with "INSERT INTO mysql.user" but results are the same.

Superadmin of MySQL server in all cases works good.

Could somebody help me to manage this troubles. I need a decision that would allow me to create new DB and new users for this DB automatically without having troubles in future.

ANY HELP WILL BE GREATLY APPRECIATED !

Options: ReplyQuote


Subject
Written By
Posted
Problems with newy added users after restarting MySQL server
September 20, 2013 05:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.