Detailed answers to MySQL permissions

Detailed answers to MySQL permissions

A Permission table

3 permission tables in the MySQL database: User, DB, host

The access procedure for the permission table is:

1 first from the user table in the host, user, password these 3 fields to determine the connection of IP, user name, password exists in the table, the existence is authenticated;

2 through permission verification, when assigning rights, according to the order of User?db?tables_priv?columns_priv. That is, the Global Permissions table user is checked first, if the corresponding permission in user is Y, then the user has y for all databases, will no longer check db, Tables_priv,columns_priv, and if n, check the database for the user in the DB table. And get the permissions for Y in db, and if DB is N, check the specific table in Tables_priv for this database, get permission y in the table, and so on.

Two MySQL various permissions (a total of 27)

(The following operations are logged as root for grant authorization to log in as P1@localhost to execute various orders.) ）

1. Usage

Connection (login) permission, establish a user, will automatically grant its Usage permissions (default Grant).

Mysql> grant usage on *.* to ' p1′@ ' localhost ' identified by ' 123′;

This permission can only be used for database landings, cannot perform any action, and usage permissions cannot be reclaimed, that is, revoke users cannot delete users.

2. Select

You must have a SELECT permission to use the Select table

Mysql> Grant Select on pyt.* to ' p1′@ ' localhost ';

Mysql> select * from shop;

3. Create

You must have the Create permission to use the CREATE table

Mysql> Grant create on pyt.* to ' p1′@ ' localhost ';

4. Create routine

You must have the Create routine permission to use the {create Alterdrop} {procedurefunction}

Mysql> Grant create routine on pyt.* to ' p1′@ ' localhost ';

When the create routine is granted, execute is automatically granted, ALTER routine permissions to its creator:

Mysql> Show grants for ' p1′@ ' localhost ';

+ ————————————————————————— +

Grants for P1@localhost

+ ———————————————————————— –+

GRANT USAGE on *.* to ' p1′@ ' localhost ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed04b6a265e05aa257′

GRANT SELECT, create, create routine on ' PYT '. * to ' p1′@ ' localhost '

GRANT EXECUTE, ALTER routine on PROCEDURE ' pyt '. ' Pro_shop1 ' to ' p1′@ ' localhost '

+ ————————————————————————————-+

5. Create temporary tables (note that this is tables, not table)

You must have permission to create temporary tables before you can use the Create temporary tables.

Mysql> grant create temporary tables on pyt.* to ' p1′@ ' localhost ';

[Mysql@mydev ~]$ mysql-h localhost-u p1-p PYT

Mysql> Create temporary table TT1 (id int);

6. Create View

You must have the CREATE VIEW permission to use the CREATE view

Mysql> Grant CREATE view on pyt.* to ' p1′@ ' localhost ';

Mysql> CREATE View V_shop as select price from shop;

7. Create user

To use create user, you must have the global Create user permission for the MySQL database or have insert permissions.

Mysql> Grant create user on *.* to ' p1′@ ' localhost ';

or:mysql> grant insert on *.* to P1@localhost;

8. Insert

You must have INSERT permission to use INSERT INTO .... Values ....

9. Alter

You must have ALTER permission before you can use ALTER TABLE

ALTER TABLE shop Modify dealer char (15);

Alter routine

You must have ALTER routine permission to use the {alter drop} {procedurefunction}

Mysql>grant alter routine on pyt.* to ' p1′@ ' localhost ';

Mysql> drop procedure Pro_shop;

Query OK, 0 rows Affected (0.00 sec)

Mysql> revoke alter routine on pyt.* from ' p1′@ ' localhost ';

[Mysql@mydev ~]$ mysql-h localhost-u p1-p PYT

Mysql> drop procedure Pro_shop;

ERROR 1370 (42000): Alter routine command denied to user ' p1′@ ' localhost ' for routine ' pyt.pro_shop '

Update

You must have update permissions before you can use the Update table

mysql> Update shop set price=3.5 where article=0001 and dealer= ' A ';

Delete

You must have permission to delete to use the delete from ... where ... (delete records from a table)

Drop

You must have drop permissions before you can use the drop database db_name; drop table tab_name;

Drop View vi_name; Drop index In_name;

Show Database

The show database only sees databases of certain permissions that you have, unless you have global show databases permissions.

For p1@localhost users, there is no access to the MySQL database, so in this identity login query, unable to see the MySQL database:

mysql> show databases;

+ —————— –+

Database

+ —————— –+

Information_schema

Pyt

Test

+ —————— –+

Show view

You must have Show view permission to perform show create view.

Mysql> Grant Show view on pyt.* to P1@localho