mysql create database and user from non root user

Question

I have a non root user in mysql database. It has grant, create, create user permissions. I need to create a new database and user using that account. Then i need to grant basic usage permissions to newly created user on the newly created database..

I was able to create a new database and user using the non root user. Now when it comes to granting permission for newly created user on newly created database using the non root user i get permission denied for non-root user on newly created user... Any workaround?

Included from OP's comments:

Executing:

SHOW GRANTS FOR 'non_root_account_name'@'his_host'

Resulted the following:

 GRANT CREATE, INDEX, ALTER, CREATE USER 
    ON *.* 
    TO 'non_root_account_name'@'localhost' 
       IDENTIFIED BY PASSWORD 'password' 
       WITH GRANT OPTION  
 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
       DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, SHOW VIEW 
    ON non_root_account_name.* 
    TO 'non_root_account_name'@'localhost' 
       WITH GRANT OPTION`  
 GRANT SELECT 
    ON mysql`.* 
    TO 'non_root_account_name'@'localhost'`

Hi Pablo, Nope. I am actually executing the queries with php. So, i would be logging in to mysql through the non root user. So, when should flush be executed? — Amal
– Amal, Commented Mar 29, 2014 at 13:35
Just after you changed some privilege. Open the MySQL console as root and execute FLUSH PRIVILEGES — Pablo Francisco Pérez Hidalgo
– Pablo Francisco Pérez Hidalgo, Commented Mar 29, 2014 at 13:44
Um.... The thing is that i am doing all the setup using php script.. So, i need everything to be done over the connection established from my script... — Amal
– Amal, Commented Mar 29, 2014 at 13:46
If you restart your mysql service you will get the same result — Pablo Francisco Pérez Hidalgo
– Pablo Francisco Pérez Hidalgo, Commented Mar 29, 2014 at 13:54

Ravinder Reddy · Accepted Answer · 2014-03-29 13:26:27Z

2

As per documentation on GRANT privileges :

To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

Meaning, your account to grant privileges to other users, it should have a GRANT OPTION privilege.

Connect as root and re-execute grant on your account to grant the above privilege.

Example:

GRANT
  -- list of privileges here
  -- to your account name @ host
  WITH GRANT OPTION

answered Mar 29, 2014 at 13:26

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Amal Over a year ago

Hi, I am actually executing the queries with php. So, i would be logging in to mysql through the non root user. I have double check and the account has grant privilege

Ravinder Reddy Over a year ago

You mean the non root user has grant option privilege?

Ravinder Reddy Over a year ago

Can you please add results of the statement SHOW GRANTS FOR 'non_root_account_name'@'his_host';

Amal Over a year ago

Sure.. This is the output

GRANT CREATE, INDEX, ALTER, CREATE USER ON *.* TO     'non_root_account_name'@'localhost' IDENTIFIED BY PASSWORD 'password'   WITH GRANT OPTION

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, SHOW VIEW ON non_root_account_name.* TO 'non_root_account_name'@'localhost' WITH GRANT OPTION GRANT SELECT ON mysql.* TO 'non_root_account_name'@'localhost'

Collectives™ on Stack Overflow

mysql create database and user from non root user

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related