How to Create MySQL Databases and Users with Permissions

We going to discuss about creating MySQL database and  users with different permissions to manage our database. Assume that, your going to create database named  linuxdb and user named linuxuser.

This will create a database "linuxdb" and user "linuxuser", as of now
this mysql user doesn't have any password. If you want to create mysql
user with password then use this query.
 

Permission Settings:

We need to map this user to our database with appropriate permissions.
 
Give permission with password,
 
Above query will give access to all the database, if want to make it more specific then add database name in place of *.* like this,
 
Note: Another asterisk (*) after database name refers all tables in that database, you may provide table name there incase you don't want give permisson to the whole database.

All GRANT queries we have seen now will give 'linuxuser' total control on databases, this means 'linuxuser' can select,alter,delete,drop,insert,update etc..

To create restricted access, you need to specify allowable permissions in place of "ALL" in grant query and these are permissions:

SELECT ,INSERT ,  UPDATE ,  DELETE , CREATE , DROP , FILE , REFERENCES, INDEX , ALTER , CREATE TEMPORARY TABLES,  CREATE VIEW , SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE

Query given below is an example of setting permissions with few options to "linuxuser".
 
to be more specific,

Revoking Permissions:

You've given permissions to the wrong user or in need of removing certain permissions to a user, then use 'revoke' query.
 
Replace "ALL"  in the above query with permission key we discussed above to remove only certain permissions.

Deleting Database and Users:

I think you'll have fair amount of understanding about MySQL DDL & DCL queries by reading my article this much, so following queries will become self explanatory for you.
 
Note : The '%' character is a wildcard that will match any host