MySQL Permission description

--mysql Permission Description

----------------------2014/06/09

A Permission table

3 permissions tables in MySQL database: User, DB, host

The access process for the permission table is:

1) First from the user table in the host, user, password 3 fields to determine whether the connection of the IP, username, password exists in the table, the existence of authentication;

2) with permission validation, the allocation of permissions is performed in the order of user db Tables_priv Columns_priv. That is, first check the Global Permissions table user, if the corresponding permissions in user is Y, then this user permissions to all databases are Y, will no longer check the DB, Tables_priv,columns_priv, if n, to the DB table to check the specific database for this user, And get the permission of Y in db, if n in db, check the specific table of this database in Tables_priv, get permission y in the table, and so on.

Two MySQL various permissions (27 total)

(The following operations are logged in as root for grant authorization, the [email protected] Login to execute various commands. ）

1. Usage

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

Mysql> Grant Usage on *. * to ' p1′@ ' localhost ' identified by ' 123′;

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

2. Select

You must have SELECT permission before you can use the Select table

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

Mysql> select * from shop;

3. Create

You must have Create permission before you can 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 you grant create routine, execute is automatically granted, and ALTER routine permission is given to the creator of it:

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

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

Grants for [email protected]

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

| 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 this is tables, not table)

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

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

[Email protected] ~]$ 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 the 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 [email protected];

8. Insert

You must have insert permissions before you can 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 before you can 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 ';

[Email protected] ~]$ 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 '

One. 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 delete permission before you can use delete from .... (Delete a record 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

By show database you can only see the databases for certain permissions that you have, unless you have global show databases permissions.

for [email protected] users, there is no access to the MySQL database, so when you log in as a query, you cannot see the MySQL database:

mysql> show databases;

+ —————— –+

| Database |

+ —————— –+

| information_schema|

| PYT |

| Test |

+ —————— –+

. Show view

You must have Show view permission to execute Show create view.

Mysql> Grant Show view on pyt.* to [email protected];

Mysql> Show CREATE View v_shop;

. Index

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

Mysql> Grant Index on pyt.* to [email protected];

Mysql> CREATE index Ix_shop on shop (article);

Mysql> DROP index ix_shop on shop;

Excute.

Perform the functions,procedures that exist

Mysql> call PRO_SHOP1 (0001,@a);

+ ——— +

| Article |

+ ——— +

| 0001 |

| 0001 |

+ ——— +

Mysql> Select @a;

+--+

| @a |

+--+

| 2 |

+--+

Lock tables

You must have lock tables permission before you can use the lock tables

Mysql> grant lock tables on pyt.* to [email protected];

Mysql> lock tables A1 read;

mysql> unlock tables;

References.

With references permissions, a user can use a field from another table as a foreign key constraint on a table.

Reload.

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

Mysql> Grant Reload on pyt.* to [email protected];

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;

Replication Client

Have this permission to query the master server, slave server state.

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 [email protected];

or:mysql> Grant Super on * * to [email protected];

Mysql> Show master status;

+ —————— + ———-+ ———— –+ —————— +

| File | Position | binlog_do_db | binlog_ignore_db |

+ —————— + ———-+ ———— –+ —————— +

| mysql-bin.000006 | 2111 | | |

+ —————— + ———-+ ———— –+ —————— +

Mysql> Show slave status;

Replication slave

With this permission, you can view the binary logs from the server and read from the primary 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 [email protected];

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|

..........................................

Shutdown.

Turn off MySQL:

[Email protected] ~]$ mysqladmin shutdown

Reconnect:

[[email protected] ~]$ MySQL

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

[Email protected] ~]$ Cd/u01/mysql/bin

[Email protected] bin]$./mysqld_safe &

[[email protected] bin]$ MySQL

Grant option

With GRANT option, you can grant your own permissions to other users (limited to the permissions you already own)

Mysql> Grant Grant option on pyt.* to [email protected];

Mysql> Grant Select on pyt.* to [email protected];

. file

You can execute select by having file permissions. into outfile and load data infile ... operation, but do not grant file, process, super permissions to the account outside the administrator, there is a serious security risk.

Mysql> Grant File on *. * to [email protected];

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

A. Super

This permission allows the user to terminate any query, modify the global variable's set statement, and use the change Master,purge MASTER LOGS.

Mysql> Grant Super On * * to [email protected];

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

Process

With this permission, the user can execute the show processlist and kill commands. By default, each user can execute the show processlist command, but only the process of the user is queried.

Mysql> show Processlist;

+--+--+ ——— –+--+ ——— +--+ ——-+ —————— +

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

+--+--+ ——— –+--+ ——— +--+ ——-+ —————— +

| 12 | P1 | localhost | PYT | Query | 0 | NULL | Show Processlist |

+--+--+ ——— –+--+ ——— +--+ ——-+ —————— +

Other than that

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

Mysql> Grant Super on pyt.* to [email protected];

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

Mysql> Grant Super On * * to [email protected];

query ok, 0 rows affected  (0.01 sec)