MySQL user management and authorization management

MySQL User and Rights management

The mysqld process loads the table,host,db,table_privs,clumn_privs,procs_privs,proxies_privs into memory when it is started.

User account:

User name + Host

User name: within 16 characters.

Host:

Host Name: MyTest

IP Address: 172.168.1.20

Network address: 172.168.1.0/255.255.0.0

Wildcard characters:

172.168.%.%

172.168.1.2__

%.qq.com, note If it is a domain name, then MySQL needs to parse, if you cancel the resolution, you can start the MYSQLD service, add parameter--skip-name-resolve.

Permissions are divided into the following categories:

1. Global level.

2. Library level.

3. Table level

4. Column level.

5. Stored procedures and stored functions.

To create a user:

1.create user [email protected] '% ' identified by ' mytest '; The method automatically notifies MySQL to read the authorization table.

2.grant all on mysql.* to [email protected] '% ' identified by ' mytest ';

3.insert into Mysql.user, this method needs to execute the flush privlieges, which triggers MySQL to re-read user rights information.

mysql> CREATE DATABASE mytest;

ERROR 1044 (42000): Access denied for user ' mytest ' @ ' localhost ' to database ' MyTest ' (Before unauthorized)

---------------------------------------------------------

Give the user authorization in a different window.

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

Query OK, 0 rows Affected (0.00 sec)

---------------------------------------------------------

mysql> CREATE DATABASE mytest;

Query OK, 1 row affected (0.01 sec)

mysql> use mytest;

Database changed

Mysql> CREATE TABLE MYTAB1 (ID int not NULL auto_increment primary Key,name char (20));

Query OK, 0 rows affected (0.03 sec)

---------------------------------------------------------

Give the user authorization in a different window.

Mysql> Grant Select,insert on mytest.* to [email protected];

Query OK, 0 rows Affected (0.00 sec)

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

Query OK, 0 rows Affected (0.00 sec)

---------------------------------------------------------

After you sign in again, you can perform insert and update on the table.

Mysql> ALTER TABLE MYTAB1 add age tinyint;

ERROR 1142 (42000): ALTER command denied to user ' mytest ' @ ' localhost ' for table ' MYTAB1 '

mysql> \ r

Connection id:22

Current Database:mytest

Mysql> ALTER TABLE MYTAB1 add age tinyint;

Query OK, 3 rows affected (0.02 sec)

Records:3 duplicates:0 warnings:0

To view authorization information:

Mysql> Grant all on mysql.* to [e-mail protected] '% ' identified by ' mytest ';

Query OK, 0 rows affected (0.07 sec)

Mysql> Show grants for [email protected] '% ';

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

| Grants for [email protected]% |

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

| GRANT USAGE on *. mytest ' @ '% ' identified by PASSWORD ' *58f4612c3598d20a3c51a37d7b2643bf15806832 ' |

| GRANT all Privileges "MySQL". * to ' mytest ' @ '% ' |

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

2 rows in Set (0.00 sec)

Grant all on table db.mytest to [email protected] '% ';

Grant all on function db.mytest to [email protected] '% ';

With_option:

GRANT OPTION

| Max_queries_per_hour count//hourly maximum number of queries, 0 means no limit.

| Max_updates_per_hour count//hourly maximum number of updates, 0 means no limit.

| Max_connections_per_hour count//hour maximum number of connections, 0 means no limit.

| Max_user_connections count//maximum number of simultaneous connections, 0 means no limit.

To authorize a field:

Mysql> Grant Update (age) on MYTEST.MYTAB1 to [email protected];

Query OK, 0 rows Affected (0.00 sec)

Grant Superuser Privileges:

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

Query OK, 0 rows Affected (0.00 sec)

Revoke permissions:

Mysql> Show grants for [email protected];

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

| Grants for [email protected] |

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

| GRANT SUPER on * * to ' mytest ' @ ' 127.0.0.1 ' identified by PASSWORD ' *58f4612c3598d20a3c51a37d7b2643bf15806832 ' |

| GRANT SELECT, INSERT, CREATE, ALTER on ' mytest '. * to ' mytest ' @ ' 127.0.0.1 ' |

| GRANT UPDATE (age) on ' mytest '. ' Mytab1 ' to ' mytest ' @ ' 127.0.0.1 ' |

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

3 Rows in Set (0.00 sec)

Mysql> Revoke select on mytest.* from [email protected];

Query OK, 0 rows Affected (0.00 sec)

To delete a user:

Drop user name @ host.

To rename a user:

Rename user [email protected] hostname to [email protected] host name;

mysql> rename user [email protected] to [email protected];

Query OK, 0 rows Affected (0.00 sec)

Initialize MySQL password:

Restart the Mysqld service by setting--skip-grant-tables and--skip-networking:

[[Email protected] ~]# service mysqld stop

Shutting down MySQL ... success!

[Email protected] ~]# mysqld_safe--skip-grant-tables--skip-networking--user=mysql &

To modify all user passwords:

mysql> Update Mysql.user set Password=password (' MySQL ');

Query OK, 4 rows Affected (0.00 sec)

Rows Matched:4 Changed:4 warnings:0

To restart the MySQL service:

[Email protected] ~]# service mysqld restart

Shutting down mysql.140803 23:09:12 mysqld_safe mysqld from PID File/usr/local/mysql/data/idc131.pid ended

success!

Starting MySQL. success!

[1]+ done Mysqld_safe--skip-grant-tables--skip-networking--user=mysql

[Email protected] ~]#

[Email protected] ~]#

[Email protected] ~]# mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.5.38-log MySQL Community Server (GPL)

Copyright (c), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

Note: The authorization form is not read when the service starts, so the password cannot be changed in the following ways:

mysql> Set password [email protected] ' localhost ' =password (' 123 ');

Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' [email protected] ' Localho St ' =password (' 123 ') ' at line 1

This article is from the "Webseven" blog, so be sure to keep this source http://webseven.blog.51cto.com/4388012/1535124