My learning experience in MYSQL (13) _ MySQL

Source: Internet
Author: User
My MYSQL learning experience (13) my MYSQL learning experience (13)

My experiences with MYSQL (1)

My experiences with MYSQL (2)

My experiences with MYSQL (III)

My experiences with MYSQL (4)

My experiences with MYSQL (5)

My experiences with MYSQL (6)

My experiences with MYSQL (7)

My experiences with MYSQL (8)

My experiences with MYSQL (9)

My experiences with MYSQL (10)

My experiences with MYSQL (12)

This article "My MYSQL learning experience (13)" will explain MYSQL User Management

In the mysql database, the mysql_install_db script initialization permission table is available, and the storage permission table is:

1. user table

2. db table

3. host table

4. table_priv table

5. columns_priv table

6. proc_priv table

MySQLAccess control involves two phases:

  • Phase 1: The server checks whether you are allowed to connect.
  • Phase 2: If you can connect, the server checks every request you send. Check whether you have sufficient permissions to implement it. For example, if you select rows from a table in the database or discard a table from the database, the server determines that you haveSelectPermission or permission on the databaseDropPermission.

The two phases of server access control are as follows:mysqlInuser,dbAndhostTable. the fields in these authorization tables are as follows:

Table name user db host
Range field Host Host Host
User Db Db
Password User
Permission field Select_priv Select_priv Select_priv
Insert_priv Insert_priv Insert_priv
Update_priv Update_priv Update_priv
Delete_priv Delete_priv Delete_priv
Index_priv Index_priv Index_priv
Alter_priv Alter_priv Alter_priv
Create_priv Create_priv Create_priv
Drop_priv Drop_priv Drop_priv
Grant_priv Grant_priv Grant_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv

For the second stage of access control (request validation), if the request involves a table, the server can also refertables_privAndcolumns_privTable. The fields of these tables are as follows:

Table name tables_priv columns_priv
Range field Host Host
Db Db
User User
Table_name Table_name
Column_name
Permission field Table_priv Column_priv
Column_priv
Other fields Timestamp Timestamp
Grantor

Each authorization table contains the range Field and permission field.

The user table consists of the user column, permission column, security column, and resource control column.

Host tables are mainly divided into user columns and permission columns.

What is missing here isMysql. user does not have a column that stores the creation time of the user.

Sometimes when troubleshooting user problems, for example, if a customer cannot connect to the database at a certain time, we can only check whether the user exists in the user table.

However, we do not know the creation time of this user. that is to say, we do not know whether the user has been created at the time specified by the customer.

Account Management

MYSQL provides many statements to manage user accounts. These statements can be used to log on to and exit the MYSQL server, create users, delete users, manage passwords, and manage permissions.

The security of the MYSQL database must be ensured through account management.

Log on to and exit MYSQL

Common mysql command parameters

-H: host name or ip address. the default value is localhost. it is recommended to specify the-h parameter.

-U: User name

-P: Password. note: the string and-p following this parameter cannot contain spaces.

-P: port number. the default value is 3306.

Database name: you can specify the database name at the end of the command.

-E: execute an SQL statement. If this parameter is specified, the command or SQL statement following-e will be executed and exited after logon.

After the command is executed, the structure of the book table is returned. after the query is returned, MYSQL is automatically exited.

User

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']    [, user [IDENTIFIED BY [PASSWORD] 'password']] 

Create a common user

CREATE USER 'jeffrey'@'localhost' identified BY 'mypass';

The user name is "jeffrey", and the default host name is "%" (that is, permission is granted to all hosts)

If the specified user does not need a password for logon, you can omit the identified BY section.

If you use a plug-in to authenticate the connection, the client must provide the creden。 required for the authentication method when the server calls the plug-in with the specified name.

If the server cannot find the corresponding plug-in when creating a user or connecting to the server, an error is returned.

Identified with syntax

CREATE user 'jeffrey'@'localhost' identified with my_auth_plugin;

Identified with can only be used in MySQL 5.5.7 or later versions.

Identified with and identified by are mutually exclusive, so only one authentication method can be used for one account.

Operations of the create user statement are recorded in the server log file or operation history file.

Example ~ /. Mysql_history. This means that anyone with read permission on these files can read the plaintext password of the newly added user.

One way is to use the password keyword when creating a user.

CREATE user 'tom'@'localhost' identified BY password'*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';SELECT password('mypass');SELECT * FROM `mysql`.`user` WHERE `User` ='tom';

First, find out the hash value of your password, and then enter the hash value when creating a user.

You can only view the hash value in the log.

Use the GRANT statement to create a new user

The grant user statement can be used to create an account. you can use this statement to add a new record to the user table.

Compared with the new USER created by the create user statement, the GRANT statement must be used to GRANT the USER permission.

You must have the GRANT permission when using the GRANT statement to create a new user.

Syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...    ON [object_type] {tbl_name | * | *.* | db_name.*}    TO user [IDENTIFIED BY [PASSWORD] 'password']        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...    [REQUIRE        NONE |        [{SSL| X509}]        [CIPHER 'cipher' [AND]]        [ISSUER 'issuer' [AND]]        [SUBJECT 'subject']]    [WITH with_option [with_option] ...]

Create a new user testUser with the password testpwd using the GRANT statement, and GRANT the user the SELECT and UPDATE permissions for all data tables.

GRANT SELECT ,UPDATE ON *.* TO 'testUser'@'localhost' identified BY 'testpwd'
SELECT `Host` ,`User` ,`Select_priv` ,`Update_priv`  FROM mysql.user WHERE `User` ='testUser';

The execution result shows that the execution is successful. you can use the SELECT statement to query the permissions of the user testUser.

The query results show that the SELECT and UPDATE permission fields are Y

Note: the User and host fields in the user table are case sensitive. you must specify the correct User name or host name during query.

Directly operate the MYSQL User table

Whether it is create user or grant user, a new record is actually added to the user table during USER creation.

Use the INSERT statement to INSERT a record to the mysql. user table to create a new user.

You must have the INSERT permission when inserting data.

INSERT INTO mysql.user(host,user,password,[privilegelist])VALUES ('host','username',password('password'),privilegevaluelist)

Create a new user using INSERT. the user name is customer1, the host name is localhost, and the password is customer1.

INSERT INTO mysql.user(host,user,password)VALUES ('localhost','customer1',password('customer1'))

Statement execution failed. view the warning information as follows:

show WARNINGS ;

Because the ssl_cipher field does not have a default value defined in the user table, an error message is prompted here.

The execution of the insert statement is affected. you can use the SELECT statement to view records in the user table.

As you can see, insertion failed.

Delete common users

Use the drop user statement to DELETE a user. you can also use DELETE to DELETE the corresponding records from the mysql. USER table.

The drop user statement is used to delete one or more MYSQL accounts. To use drop user, you must have the global

Create user or DELETE permission.

Delete the user testUser

DROP user 'testUser'@'localhost';

The user testUser has been deleted.

Delete a user using the delete statement

DELETE FROM mysql.user WHERE `Host`='localhost' and `User`='testUser'

The root user changes his/her password.

You can change the root password in multiple ways.

1. use the mysqladmin command to specify the new password on the command line.

mysqladmin -u root -p password"rootpwd"

2. modify the user table of the mysql database

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

The password ('') function is used to encrypt the user password. After the update is executed, run the flush privileges statement to reload the user permission.

3. use the SET statement to change the password of the root user.

The set password statement can be used to reset the logon PASSWORD of another user or the account PASSWORD used by the user.

Syntax

SET PASSWORD=PASSWORD("ROOTPWD")

The new PASSWORD must be encrypted using the PASSWORD function.

Use the root user to log on to mysql and execute the following statement:

SET password=password('123456')

Run the flush privileges statement or restart MYSQL to reload user permissions.

Change the password of a common user as the root user

1. use the SET statement to modify the password of a common user.

SET PASSWORD FOR 'USER'@'HOST' =PASSWORD("ROOTPWD")

2. use the update statement to change the password of a common user.

UPDATE mysql.user SET `Password` =password('rootpwd') WHERE `User`='root' and `Host`='localhost'

After Execution, use the flush privileges statement or restart MYSQL to reload user permissions.

3. use the GRANT statement to change the password of a common user.

GRANT USAGE ON *.* TO 'someuser'@'%'  IDENTIFIED BY 'somepwd'

Use the following statement to change the password of testUser to 123456.

grant USAGE ON *testUser*TO 'localhost' identified BY '123456';

Note: If you use the GRANT statement and MYSQLADMIN to set the PASSWORD, both of them will encrypt the PASSWORD. in this case, you do not need to use the PASSWORD () function.

Change password for common users

Use the SET statement to change your password

SET password=password('newpassword');

For example, to change the password of the user testUser, you need to use the user testUser to log on to mysql and then execute

SET password=password('123456');

Root user password loss solution

Use the -- skip-grant-tables option to start the MYSQL service.

When MYSQL is started using the -- skip-grant-tables option, the server does not load the permission judgment, and any user can access the database.

LINUX

Use mysqld_safe to start the MYSQL service, or use the/etc/init. d/mysql command to start mysql

mysqld_safe --skip-grant-tables user=mysql

Or

/etc/init.d/mysql start-mysqld --skip-grant-tables

After starting the MYSQL service, you can use the root user to log on.

Windows

For more information, see this article.

Windows mysql prompt: 1045 access denied for user 'root' @ 'localhost' using password yes

Permission management

Various permissions in MYSQL

For GRANT and REVOKE statements, priv_type can be specified as any of the following:

Permission

Meaning

ALL [PRIVILEGES]

Set all simple permissions except GRANT OPTION

ALTER

Allow the use of ALTER TABLE

ALTER ROUTINE

Modify or cancel stored subroutines

CREATE

Allow the use of CREATE TABLE

CREATE ROUTINE

Create a stored subroutine

CREATE TEMPORARY TABLES

Allow the use of CREATE TEMPORARY TABLE

CREATE USER

Create user, drop user, rename user, and revoke all privileges are allowed.

CREATE VIEW

Allow the use of CREATE VIEW

DELETE

DELETE allowed

DROP

Allow DROP TABLE

EXECUTE

Allow users to run stored subroutines

FILE

Allow SELECT... into outfile and LOAD DATA INFILE

INDEX

Allow the use of create index and DROP INDEX

INSERT

INSERT allowed

LOCK TABLES

Allow the use of lock tables for TABLES with SELECT permissions

PROCESS

Show full processlist allowed

REFERENCES

Not implemented

RELOAD

Allow FLUSH

REPLICATION CLIENT

Allows the user to ask the address of the slave server or master server

REPLICATION SLAVE

Used for replication slave server (read binary log events from the master server)

SELECT

Allow SELECT

SHOW DATABASES

Show databases show all DATABASES

SHOW VIEW

Allow the use of SHOW CREATE VIEW

SHUTDOWN

AllowedMysqladmin shutdown

SUPER

Allow the use of change master, KILL, purge master logs, and set global statements,Mysqladmin debugCommand; allows you to connect (once), even if max_connections is reached.

UPDATE

Allow UPDATE

USAGE

Synonym for "no permission"

GRANT OPTION

Grant permissions

When upgrading from an earlier version of MySQL, EXECUTE, create view, show view, create user, create routine, and alter routine permissions must be used.

Authorization

Authorization is to grant permissions to a user.

The granted permissions can be divided into multiple levels:

·Global level

Global permissions apply to all databases on a given server. These permissions are stored in the mysql. user table. Grant all on *. * and revoke all on *. * only GRANT and REVOKE global permissions.

·Database level

The database permission applies to all targets in a given database. These permissions are stored in the mysql. db and mysql. host tables. GRANT ALL ONDb_name. * And REVOKE ALL ONDb_name. * Only grant and revoke Database permissions.

·Surface level

The table permission applies to all columns in a given table. These permissions are stored in the mysql. talbes_priv table. GRANT ALL ONDb_name.tbl_nameAnd REVOKE ALL ONDb_name.tbl_nameOnly grant and revoke table permissions.

·Column level

The column permission applies to a single column in a given table. These permissions are stored in the mysql. columns_priv table. When using REVOKE, you must specify the same columns as the authorized columns.

·Subroutine Level

The create routine, alter routine, EXECUTE, and GRANT permissions apply to stored subroutines. These permissions can be granted at the global level and database level. In addition to create routine, these permissions can be granted as a subroutine level and stored in the mysql. procs_priv table.

When the target is a table, a stored function, or a stored procedure,Object_typeThe clause should be specified as TABLE, FUNCTION, or PROCEDURE. When upgrading from an earlier version of MySQL, you must upgrade your authorization table to use this sub-statement.

Use the GRANT statement to create a new user grantUser with the password "grantpwd"

The user has the query and insert permissions for all data, and grants the GRANT permission.

GRANT SELECT ,INSERT ON *.*TO 'grantUser'@'localhost' identified BY '123456' WITH GRANT OPTION ;

The query shows that grantUser is successfully created and grant select, INSERT, and GRANT permissions. the corresponding field value is Y.

A user authorized to GRANT permissions can log on to MYSQL and create other user accounts. here, it is a grantUser user.

Revoke permissions

Revoking permissions is to cancel some permissions granted to the user. Revoking unnecessary permissions of users can ensure the security of the system to a certain extent.

After the REVOKE is used to REVOKE permissions, the user account records will be deleted from the db, host, tables_priv, columns_priv tables, but the user account records are still

Save it in the user table.

Syntax

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...    ON [object_type] {tbl_name | * | *.* | db_name.*}    FROM user [, user] ...REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use the REVOKE statement, you must have the global CREATE permission or UPDATE permission for the mysql database.

Use the REVOKE statement to cancel the INSERT permission of the grantUser.

REVOKE INSERT ON *.* FROM 'grantUser'@'localhost';

We can see that the INSERT permission of the grantUser user has been revoked.

Note: when upgrading from an earlier version of MYSQL, if you want to use EXECUTE, create view, show view, create user, create routine, ALTER ROUTINE

Permission. the authorization table must be upgraded first.

View permissions

The show grant statement can display the user's permission information.

Syntax

show grants FOR 'user'@'host';

Use the show grant statement to query grantUser permissions.

show grants FOR 'grantUser'@'localhost';

The returned results show the account information in the user table. Next, assume that the account starts with the grant select on keyword, indicating that the user is granted the SELECT permission;

*. * Indicates that the SELECT permission applies to all data tables in all databases;

Password after IDENTIFIED

Here, only individual user permissions are defined. GRANT can display more detailed permission information, including global and non-global permissions.

If the permissions at the surface or column level are granted to users, they can also be displayed in the results.

View anonymous users in MYSQL

If anonymous users exist, the client can log on to the MYSQL database without a password, which poses a security risk.

How to check anonymous users

SELECT * FROM mysql.user WHERE `User`='';

If you find the record whose user field value is null, it indicates that an anonymous user exists and you need to delete this record.

If you use an anonymous user to log on to MYSQL, you can see that the user name is empty.

Delete statement

DELETE FROM mysql.user WHERE `User`='';SELECT * FROM mysql.user WHERE `User`='';

Summary

This article briefly describes the user management and permissions of MYSQL.

If you want to learn more about MYSQL access control

Refer to this article: MySQL permission architecture

The core isTwo graphs

1. client connection request authentication stage

2. client operation request authentication stage

Finally, I opened a MYSQL Microblog. I hope you can support o (primary _ primary) o.

WelcomeMYSQL official Weibo: Start MYSQL

The Weibo link is displayed on the left bar of my blog. you can also click the Weibo link on the left bar of my blog to go to MYSQL to start Weibo o

If anything is wrong, you are welcome to make a brick o

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.