Mysql permission Learning

 

1. Create a new user:

 

Create user mark @ localhost identified by '20140901 ';

2. delete a user:

Drop user mark @ localhost;

3. Modify the User Name:

Rename user mark @ localhost to mark1 @ localhost;

4. Change the user password:

Set password for mark @ localhost = password ('20140901 ');

5. grant permissions:

(1) grant table permissions: select, insert, delete, update, references, create, alter, index, drop, all or all privileges.

Use mysql;

Grant select on user to mark @ localhost;

(2) Granting database permissions: select, insert, delete, update, references, create, alter, index, drop, create temporary tables, create view, show view, create routine, alter routine, execute routine, lock tables, all or all privileges.

Grant select on mysql. * to mark @ localhost;

(3) grant user Permissions: create user, show databases

Grant create user on *. * to mark @ localhost;

Grant create, alter, drop on *. * to mark @ localhost;

6. Permission transfer and Restriction

(1) Add the with grant option at the end of the grant statement, indicating that the user has the permission granted to other users, regardless of whether other users have the permission.

Grant select on mysql. user to mark @ localhost with grant option;

After you log on to the database with mark again, mark can grant the same permissions to other users.

(2) The with clause can also assign practical restrictions to a user.

Max_queries_per_hour 1 The number of database queries per hour is 1.

Max_connections_per_hour 1 Number of database connections per hour.

Max_updates_per_hour 1 The number of database times per hour.

Max_user_connections 1: the maximum number of users connecting to mysql at the same time.

For example:

Grant select on mysql. user to mark @ localhost with max_queries_per_hour 1;

7. revoke permissions.

Revoke select on mysql. user from mark @ localhost;