Give user permission to drop and create specific databases

grant privileges separately for a user account in MySQL.

When specifying the database name and table name, separate them with a. (period) and no spaces. This will give the root user fine-grain control over certain data.

Also, replace the PERMISSION_TYPE value with the kind of access you want to grant to your new user account.

Here are the most used commands in MySQL:

CREATE — enable users to create a database or table

SELECT — permit users to retrieve data

INSERT — let users add new entries in tables

UPDATE — allow users to modify existing entries in tables

DELETE — enable users to erase table entries

DROP — let users delete entire database tables

NOTE: Using the ALL PRIVILEGES permission type from before will allow all of the permissions listed above.

To use any of these options, simply replace PERMISSION_TYPE with the appropriate keyword. To apply multiple privileges, separate them with a comma. For example, we can assign CREATE and SELECT to our non-root MySQL user account with this command:

GRANT CREATE, SELECT ON * . * TO 'user_name'@'localhost';

Sometimes, you might come across a situation where you need to revoke given privileges from a user. You can do so by entering:

REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘user_name’@‘localhost’;

For example, to withdraw all privileges for our non-root user we should use:

REVOKE ALL PRIVILEGES ON * . * FROM 'user_name'@'localhost';

Finally, you can entirely delete an existing user account by using the following command:

DROP USER ‘user_name’@‘localhost’;

Don't forget to FLUSH PRIVILEGES;

In order to find what privileges have already been granted to a MySQL user, you can use the SHOW GRANTS command:

SHOW GRANTS FOR 'user_name'@'localhost';

Let’s start by making a new user within the MySQL shell:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

To provide a specific user with permission, you can use this framework:

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost'

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;