MySQL Password management and authorization

Catalogue

1 managing the root user password

2 user authorization and revocation of rights

3 Installing the Graphics management tool

1 Managing the root user password

1.1 Change Password

Method 1: Use Mysqladmin

This method needs to know the original password of MySQL

Command format:

mysqladmin-hlocalhost-uroot-p password "New password"

Method 2: Need to log on to MySQL

Authorized user to modify their own password:

Mysql> set Password=password ("New password");

The database administrator resets the password for the other user:

Mysql> Set password for user name @ ' client address ' =password ("New password");

Method 3: Edit the user table directly with the SQL command update

mysql> use MySQL; #系统自带的数据库

mysql> Update user set authentication_string= password (' new password ') where user = ' root ';

mysql> flush Privileges;

1.2 Password recovery and settings

Modifying a configuration file/etc/my.cnf

[Mysqld]

Skip_grant_tables #跳过授权表

#validate_password_policy =0 #刚进入mysql设置的, delete or comment

#validate_password_length =6 #删除或者注释

[Email protected] ~]# systemctl restart mysqld

[email protected] ~]# MySQL #不用密码可以进入数据库

Mysql> select * from Mysql.user\g; #密码存放的表

mysql> Update Mysql.user

Set Authentication_string=password (' 234567 ')

, where user= "root" and host= "localhost";

After the update succeeds, return to the configuration file, modify it, comment out the authorization form, and restart the service.

2 user authorization and revocation of rights

2.1 Licensing

Format:

mysql> grant permission list on library name to User name @ "client Address" identified by "password" with GRANT option;

Permission list: all; Select, insert, update; Usage

Usage: This permission can only be used for database logging, cannot perform any action, and usage permissions cannot be reclaimed, and using revoke cannot delete users.

Library Name: * * stands for all; the name of the library. Table name

Client Address:% represents all hosts that can ping a server

With GRANT option this option is not writable, and authorization to the database is also available to authorized clients

mysql> SELECT @ @hostname; #查看数据库服务器

mysql> Select User (); #查看当前登录的用户名

Mysql> show grants; #查看权限

Mysql> show processlist; #显示当前运行的程序访问用户的信息

2.2 Revocation of authorization

(1) View existing authorized users:select User,host from Mysql.user;

(2) View existing authorized user access rights:show grants for user name @ "%";

(3) Revoke User access rights:

Format:

Revoke permissions list on the library name. Table name from user name @ client address;

Mysql> revoke GRANT OPTION on * * FROM [email protected] '% ';

Mysql> revoke all on * * from [email protected] Client address

When a user is granted a non-existent library, the user can only operate on the authorized library (such as a library that is only authorized) after logging in.

(4) or make changes to the user rights of the table:

mysql> Select *from mysql.user\g;

mysql> Update mysql.db set delete_priv= "Y" where user= "user name" and host= "client address";

Flush privileges; #更新数据库

Revoking only the user's permissions is not to delete the user.

When the permission is revoked, the permissions become usage and the user can still log on.

(5) You can delete a user at this time:

Two methods

The first type:delete from mysql.user where user= "" and host= "";

The second type: Drop user username@ "client address";

Flush privileges;

(6) The database comes with the meaning of the table in the MySQL library:

The MySQL authorization library stores authorization information and uses different tables to store different authorization permissions.

User stores authorized users already owned by authorized users

DB Storage authorized user access to the library

TABLES_PRIV Store authorized user access to a table

COLUMN_PRIV Store authorized user access to a field

3 Installing the Graphics management tool

3.1 PhpMyAdmin

[[email protected] ~]# yum-y install httpd php php-mysql

[Email protected] ~]# MV Phpmyadmin-4.1.2-all-languages

/var/www/html/pma

[Email protected] ~]# CD/VAR/WWW/HTML/PMA

[email protected] ~]# CP config.sample.inc.php config.inc.php

[Email protected] ~]# vim config.inc.php

17 lines: $cfg [' Blowfish_secret ']= ' test '; #在单引号里随意添加字符, if you do not modify this, you will get an error.

31 lines: $cfg [' Servers '] [$i] [' host '] = ' database IP ';

Testing: accessing from the browser

Firefox HTTP://192.168.0.4/PMA

MySQL Password management and authorization