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.
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.
Deleting Database and Users:
Note : The '%' character is a wildcard that will match any host