MySQL permission _ MySQL

MySQL permission bitsCN.com

I. permission table

Three permission tables in the mysql database: user, db, host

The access process of the permission table is:

1) Check whether the connected IP address, user name, and password exist in the table from the host, user, and password fields in the user table. if the connection exists, authentication is performed;

2) for permission allocation through permission verification, the permission is allocated in the order of user defined db into tables_priv into columns_priv. Check the global permission table first.

User. if the corresponding permission in the user is Y, the user's permissions to all databases are Y and the database will not be checked,

Tables_priv, columns_priv; if it is N, check the database corresponding to this user in the db table and obtain the permission Y in the db. if it is N in the db, check

Query the specific table corresponding to the database in tables_priv, and obtain the permission Y in the table, and so on.

II. various MySQL permissions (27 in total)

(The following operations are grant authorization for login as root, and run various commands as p1 @ localhost .)

1. usage

Connection (login) permission. when a user is created, the usage permission is automatically granted (default ).

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

This permission can only be used for database login and cannot perform any operations. the usage permission cannot be revoked, that is, the REVOKE user cannot delete the user.

2. select

You must have the 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 {create | alter | drop} {procedure | function}

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

When the create routine is granted, the EXECUTE and alter routine permissions are automatically 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 '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 the create temporary tables permission to 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 the INSERT permission.

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

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

8. insert

You must have the insert permission before using insert ..... Values ....

9. alter

You must have the alter permission to use alter table.

Alter table shop modify dealer char (15 );

10. alter routine

You must have the 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 update permission to use the update table.

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

12. delete

You must have the delete permission before using delete from .... Where .... (Delete records in the table)

13. drop

Drop database db_name; drop table tab_name;

Drop view vi_name; drop index in_name;

14. show database

You can only view DATABASES with certain permissions through show database, unless you have the global show databases permission.

For the p1 @ localhost user, the user does not have permission for the mysql database, so the mysql database cannot be viewed during login query using this identity:

Mysql> show databases;

+ ------- +

| Database |

+ ------- +

| Information_schema |

| Pyt |

| Test |

+ ------- +

15. show view

You must have the show view permission 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 the 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 |

+ --- +

| 1, 0001 |

| 1, 0001 |

+ --- +

Mysql> select @;

+ -- +

| @ A |

+ -- +

| 2 |

+ -- +

18. lock tables

You must have the lock tables permission to use the lock tables

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

Mysql> lock tables a1 read;

Mysql> unlock tables;

19. references

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

20. reload

You must have the reload permission before you can 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 'p1' @ 'localhost ';

Query OK, 0 rows affected (0.00 sec)

Mysql> flush tables;

21. replication client

With this permission, you can query the status of the 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;

22. 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

Close 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 your permissions to other users (only the permissions you have)

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

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

25. file

If you have the file permission, you can execute select .. into outfile and load data infile... But do not grant the file, process, and super permissions to accounts other than administrators. this poses a serious security risk.

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

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

26. super

This permission allows the user to terminate any query, modify the SET statement of the global variable, and use change master and purge master logs.

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

Mysql> purge master logs before 'MySQL-bin.000006 ′;

27. process

With this permission, you can run the show processlist and KILL commands. By default, each user can execute the show processlist command, but can only query the user's processes.

Mysql> show processlist;

+ -- + ---- + -- + --- + ------ +

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

+ -- + ---- + -- + --- + ------ +

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

+ -- + ---- + -- + --- + ------ +

In addition,

Management permissions (such as super, process, file, etc.) cannot be specified for a database. on must be followed *.*

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)

This article is from the "technology" blog

BitsCN.com