Shulou(Shulou.com)05/31 Report--

This article mainly explains "the summary of knowledge related to MySQL permissions". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "summary of knowledge related to MySQL permissions"!

I. permission table

Three permission tables in mysql: user, db, host

The access process of the permission table is:

1) determine whether the connected IP, user name and password exist in the table from the three fields of host, user and password in the user table, and pass authentication if they do

2) after the permission verification, the permission is assigned according to the order of user  db  tables_priv  columns_priv. Check the global permissions table first

User, if the corresponding permission in user is Y, then this user has Y on all databases and will no longer check db

If tables_priv,columns_priv; is N, check the specific database corresponding to this user in the db table and get the permission of Y in db; if N in db, check

Check the specific table corresponding to this database in tables_priv, get the permission Y in the table, and so on.

2. MySQL various permissions (a total of 27)

(the following operations are to log in as root for grant authorization, and to log in as p1@localhost to execute various commands. )

1. Usage

Connect (login) permission, establish a user, will automatically grant its usage permission (default grant).

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

This permission can only be used to log in to the database and cannot perform any operation; and the usage permission cannot be reclaimed, that is, the REVOKE user cannot delete the user.

2. Select

You must have the permission of select to use select table

Mysql > grant select on pyt.* to'p1 localhost'

Mysql > select * from shop

3. Create

You must have the permission of create to use create table

Mysql > grant create on pyt.* to'p1 localhost'

4. Create routine

You must have permission to create routine before you can use {create | alter | drop} {procedure | function}

Mysql > grant create routine on pyt.* to'p1 localhost'

When create routine is granted, EXECUTE is automatically granted, and ALTER ROUTINE permissions are granted 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 'p1inclusive localhost' |

| | GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro _ shop1` TO 'p1cm localhost' |

+-- +

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

You must have the permission of create temporary tables before you can use 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 permission of create view to use 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 global CREATE USER permissions for my, or INSERT permissions.

Mysql > grant create user on *. * to 'p1The localhost'

Or: mysql > grant insert on *. * to p1@localhost

8. Insert

You must have permission to use insert to use insert into. .. Values... .

9. Alter

You must have the permission of alter to use alter table

Alter table shop modify dealer char (15)

10. Alter routine

You must have alter routine permission to use {alter | drop} {procedure | function}

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'

11. Update

You must have the permission of update to use update table

Mysql > update shop set price=3.5 where article=0001 and dealer='A'

12. Delete

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

13. Drop

You must have the permission of drop to use drop database db_name; drop table tab_name

Drop view vi_name; drop index in_name

14. Show database

Through show database, you can only see databases with certain permissions you have, unless you have global SHOW DATABASES permissions.

For p1@localhost users, they do not have permission to the mysql database, so when logging in to query under this identity, they cannot see the mysql database:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | pyt |

| | test |

+-+

15. Show view

You must have show view permissions to execute show create view.

Mysql > grant show view on pyt.* to p1@localhost

Mysql > show create view v_shop

16. Index

You must have index permission to execute [create | drop] index

Mysql > grant index on pyt.* to p1@localhost

Mysql > create index ix_shop on shop (article)

Mysql > drop index ix_shop on shop

17. Excute

Execute the existing Functions,Procedures

Mysql > call pro_shop1 (0001)

+-- +

| | article |

+-- +

| | 0001 |

| | 0001 |

+-- +

Mysql > select @ a

+-+

| | @ a |

+-+

| | 2 |

+-+

18. Lock tables

You must have lock tables permission to use lock tables

Mysql > grant lock tables on pyt.* to p1@localhost

Mysql > lock tables A1 read

Mysql > unlock tables

19. References

With REFERENCES permission, users can use a field of another table as a foreign key constraint of a table.

20. Reload

You must have reload permission to execute flush [tables | logs | privileges]

Mysql > grant reload on pyt.* to p1@localhost

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Mysql > grant reload on *. * to 'p1The localhost'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush tables

21. Replication client

With this permission, you can query the status of master server and slave server.

Mysql > show master status

ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation

Mysql > grant Replication client on *. * to p1@localhost

Or: mysql > grant super on *. * to p1@localhost

Mysql > show master status

+-+-- +-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+-- +-+

| | mysql-bin.000006 | 2111 | |

+-+-- +-+

Mysql > show slave status

twenty-two。 Replication slave

With this permission, you can view the slave server and read binary logs from the master server.

Mysql > show slave hosts

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

Mysql > show binlog events

ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation

Mysql > grant replication slave on *. * to p1@localhost

Mysql > show slave hosts

Empty set (0.00 sec)

Mysql > show binlog events

+-+-+

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-+

| | mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log |

Binlog ver: 4 | mysql-bin.000005 | 98 | Query | 1 | 197 | use `mysql`; create

Table A1 (I int) engine=myisam |

...

23. Shutdown

Turn off MySQL:

[mysql@mydev ~] $mysqladmin shutdown

Reconnect:

[mysql@mydev ~] $mysql

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (2)

[mysql@mydev ~] $cd / u01/mysql/bin

[mysql@mydev bin] $. / mysqld_safe &

[mysql@mydev bin] $mysql

24. Grant option

With grant option, you can grant the permissions you have to other users (only those you already have)

Mysql > grant Grant option on pyt.* to p1@localhost

Mysql > grant select on pyt.* to p2@localhost

25. File

Only with file permission can you execute select.. into outfile and load data infile... Operation, but do not grant file, process, super permissions to accounts other than the administrator, which has serious security risks.

Mysql > grant file on *. * to p1@localhost

Mysql > load data infile'/ home/mysql/pet.txt' into table pet

twenty-six。 Super

This permission allows the user to terminate any query; modify the SET statement of the global variable; and use CHANGE MASTER,PURGE MASTER LOGS.

Mysql > grant super on *. * to p1@localhost

Mysql > purge master logs before 'mysql-bin.000006'

twenty-seven。 Process

With this permission, users can execute SHOW PROCESSLIST and KILL commands. By default, each user can execute the SHOW PROCESSLIST command, but only the processes of this user can be queried.

Mysql > show processlist

+-+-+

| | Id | User | Host | db | Command | Time | State | Info |

+-+-+

| | 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |

+-+-+

In addition,

Administrative permissions (such as super,process,file, etc.) cannot specify a database, and on must be followed by *. *

Mysql > grant super on pyt.* to p1@localhost

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Mysql > grant super on *. * to p1@localhost

Query OK, 0 rows affected (0.01 sec)

At this point, I believe you have a deeper understanding of the "summary of knowledge related to MySQL permissions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.