Create MySQL Database AND Grant Privileges simultaneously

Question

Problem: I have a program that backs-up databases by creating a copy of a live database and then epoch time stamps it.

query = `CREATE DATABASE IF NOT EXISTS myDb-${dateStamp} `;

When I later try to delete that newly created database with the same credentials as used to create it result is Access denied for user.

Is there a query that can do the following?

create a table \ Schema with a user account
simultaneously give full privileges to the creating user
creating user does not have root MySQL privileges

nbk · Accepted Answer · 2020-05-15 16:09:27Z

1

enable multi query and write them all in your query variable. separated by semicolon, that will execute them sequentially. but never simultaneous

query = "CREATE DATABASE IF NOT EXISTS myDb-${dateStamp};  GRANT ALL ON  myDb-${dateStamp}.* TO 'ROOT'@'localhost'; CREATE USER 'TESTUSER'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my-strong-password-h'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON  myDb-${dateStamp}.* TO 'TESTUSER'@'localhOST'; USE myDb-${dateStamp}; CREATE TABLE Tablex SELECT * FROM Table1;"

And so on.

Mysql doesn't mind a singe query or multiple, as long the basic language like php can handles multi querys.

Or you do them one by one

answered May 15, 2020 at 16:09

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

Create MySQL Database AND Grant Privileges simultaneously

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related