Shulou(Shulou.com)06/01 Report--

The following is mainly about how Mysql creates users, permissions and how to reset administrator passwords. I hope that Mysql how to create users, permissions and how to reset administrator passwords can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database cloud servers are fast, reliable and easy to use.

5. MySQL CVM works in client / CVM mode, or in embedded system

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

II. Management of MySQL user accounts and permissions

2.1. MySQL user account includes: user name @ host name

User name: within 16 characters

The mainframe can be expressed in the following ways

Hostname: mysql, www.magedu.com

IP address: 172.16.90.111

Network address: 172.16.0.0Universe 255.255.0.0

Wildcard:%, _

%: any character 172.16%.%,% .magedu.com

_: any one of 172.16%.%

2.2. Permission levels: global level, library level, table level, column level, stored procedure and stored function level

Global level: SELECT * FROM db\ G; query permissions at the global library level

Mysql > SELECT * FROM db\ G

* * 1. Row *

Host:%

Db: test

User:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: N

Execute_priv: N

Event_priv: Y

Trigger_priv: Y

*

CREATE USER username@host [IDENTIFIED BY 'password'] create users

DROP USER 'username'@'host'; "delete"

RENAME USER old_name TO new_name; renaming usage

SHOW GRANTS FOR 'username'@'host'; views the list of user permissions

FLUSH PRIVILEGES; refresh the permission list

GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; adds permissions to users

REVOKE SELECT ON db.* FROM 'username'@'host'; removes the user's SELECT permission

2.3. Details of the scope of the permission:

2.4. Create users and permissions:

2.4.1. Create a user and set a password through IDENTIFIED BY 'password',

CREATE USER username@host [IDENTIFIED BY 'password']

Usage:

Mysql > CREATE USER test@localhost IDENTIFIED BY 'test'; # create user test local database account with password test.

Query OK, 0 rows affected (0.00 sec)

Mysql > FLUSH PRIVILEGES; # Refresh permission list

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW GRANTS FOR test@localhost\ G; # View the permission list of the database account test@localhost

* * 1. Row *

Grants for test@localhost: GRANT USAGE ON *. * TO 'test'@'localhost' IDENTIFIED BY PASSWORD' * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'

1 row in set (0.00 sec)

2.4.2. Test login

Reopen another client and log in to mysql as a test account

[root@lamp ~] # mysql-utest-p # Log in to mysql database as test user, enter user password

Log in to mysql > SHOW DATABASES; # successfully. View the database under this account

+-+

| | Database |

+-+

| | information_schema |

| | test |

+-+

2 rows in set (0.00 sec)

2.4.3. Permission setting GRANT

GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'

# specify permission PRIVILEGES

ON specifies the object name db.*

Object_type specifies the object type:

TABLE table

| | FUNCTION function |

| | PROCEDURE |

TO username@host specifies the user.

With_option:

GRANT OPTION

| | MAX_QUERIES_PER_HOUR count |

| | MAX_UPDATES_PER_HOUR count |

| | MAX_CONNECTIONS_PER_HOUR count |

| | MAX_USER_CONNECTIONS count |

Object_type object types are: TABLE (table) FUNCTION (function) PROCEDURE (program, library)

Usage:GRANT EXECUTE ON FUNCTION db.abc TO 'username'@'host'; # is authorized to username@host users, for

The abc function of the db database has execute permissions.

GRANT UPDATE (Age) ON db.testtb TO 'username'@'host'; # authorizes username@host users to have UPDATE permission for the Age field of the testtb table on the db database.

2.4.3.1. Test to activate the permission to create a library

A connection:

Mysql > GRANT CREATE ON test01.* TO 'test'@'%'

Query OK, 0 rows affected (0.01 sec)

B connection: (before A connection command is executed)

Mysql > CREATE DATABASE test01

ERROR 1007 (HY000): Can't create database 'test'; database exists

B connection: (after A connection command is executed)

Mysql > CREATE DATABASE test01

Query OK, 1 row affected (0.00 sec)

Mysql > use test01

Database changed

Mysql > CREATE TABLE testdb (ID INT UNSINED AUTO_INCREMENT NOT NULL,Name CHAR (20), PRIMARY KEY (ID))

Query OK, 0 rows affected (0.02 sec)

2.4.3.2. Test activate create table permissions, which need to be reconnected after the permissions are set.

A connection:

Mysql > GRANT INSERT ON test01.* TO 'test'@'%'

Query OK, 0 rows affected (0.01 sec)

Mysql > GRANT SELECT ON test01.* TO 'test'@'%'

Query OK, 0 rows affected (0.01 sec)

Mysql > FLUSH PRIVILEGES

Query OK, 0 rows affected (0.01 sec)

Mysql > SHOW GRANTS FOR 'test'@'%'; # View permissions are normal

+-- +

| | Grants for test@% |

+-- +

| | GRANT USAGE ON *. * TO 'test'@'%' |

| | GRANT CREATE ON `test`.* TO 'test'@'%' |

| | GRANT SELECT, INSERT, CREATE ON `test01`. * TO 'test'@'%' |

+-- +

3 rows in set (0.00 sec)

[root@lamp ~] # tail / mydata/data/lamp.err # View log is normal

170612 9:31:34 InnoDB: Completed initialization of buffer pool

170612 9:31:34 InnoDB: highest supported file format is Barracuda.

170612 9:31:35 InnoDB: Waiting for the background threads to start

170612 9:31:36 InnoDB: 1.1.8 started; log sequence number 5241255

170612 9:31:36 [Note] Server hostname (bind-address): '0.0.0.0; port: 3306

170612 9:31:36 [Note]-'0.0.0.0' resolves to' 0.0.0.0'

170612 9:31:36 [Note] Server socket created on IP: '0.0.0.09.

170612 9:31:36 [Note] Event Scheduler: Loaded 0 events

170612 9:31:36 [Note] / usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.5.28 socket:' / tmp/mysql.sock' port: 3306 Source distribution

B connection: (before A connection command is executed)

Mysql > USE test01

Database changed

Mysql > INSERT INTO testdb (Name) VALUES ('TOM')

ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table' testtb'

B connection: (after the A connection command is executed and the session is reconnected)

Mysql > use test01

Database changed

Mysql > INSERT INTO testdb (Name) VALUES ('TOM')

Query OK, 1 row affected (0.01sec)

2.4.3.3. Test the permissions of the fields in the newly added table. You need to reconnect after the permissions are set.

A connection:

Mysql > GRANT ALTER ON test01.* TO 'test'@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > FLUSH PRIVILEGES

Query OK, 0 rows affected (0.00 sec)

B connection: (before A connection command is executed)

Mysql > ALTER TABLE testdb ADD Age TINYINT UNSIGNED

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

B connection: (after the A connection command is executed and the session is reconnected)

Mysql > use test01

Database changed

Mysql > ALTER TABLE testdb ADD Age TINYINT UNSIGNED

Query OK, 1 row affected (0.03 sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM testdb

+-- +

| | ID | Name | Age | |

+-- +

| | 1 | TOM | NULL |

+-- +

1 row in set (0.00 sec)

2.4.3.4. Test the permissions of the fields in the newly added table. You need to reconnect after the permissions are set.

A connection:

Mysql > GRANT UPDATE (Age) ON test01.testdb TO 'test'@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > FLUSH PRIVILEGES

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW GRANTS FOR 'test'@'%'

+-+

| | Grants for test@% |

+-+

| | GRANT USAGE ON *. * TO 'test'@'%' |

| | GRANT CREATE ON `test`.* TO 'test'@'%' |

| | GRANT SELECT, INSERT, CREATE, ALTER ON `test01`. * TO 'test'@'%' |

| | GRANT UPDATE (Age) ON `test01`.`testdb`TO 'test'@'%' |

+-+

4 rows in set (0.00 sec)

B connection: (after the A connection command is executed and the session is reconnected)

Mysql > use test01

Mysql > UPDATE testdb SET Age=30 WHERE ID=1

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > SELECT * FROM testdb

+-- +

| | ID | Name | Age | |

+-- +

| | 1 | TOM | 30 | |

+-- +

1 row in set (0.00 sec)

Mysql > UPDATE testdb SET Name='Jack' WHERE ID=1; # only has the right to update Age, so

ERROR 1143 (42000): UPDATE command denied to user 'test'@'localhost' for column' Name' in table 'testdb'

2.4.3.5. The test enables global permissions. After the permissions are set, you need to reconnect the XXX test and fail.

A connection:

Mysql > SET GLOBAL tx_isolation='READ-UNCOMMITTED'

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege (s) for this operation

Mysql > show grants for 'test'@'%'

+-+

| | Grants for test@% |

+-+

| | GRANT SUPER ON *. * TO 'test'@'%' |

| | GRANT CREATE ON `test`.* TO 'test'@'%' |

| | GRANT SELECT, INSERT, CREATE, ALTER ON `test01`. * TO 'test'@'%' |

| | GRANT UPDATE (Age) ON `test01`.`testdb`TO 'test'@'%' |

+-+

4 rows in set (0.00 sec)

B connection: (after the A connection command is executed and the session is reconnected)

Mysql > use test01

Mysql > set GLOBAL tx_isolation = 'READ-UNCOMMITTED'

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege (s) for this operation

Mysql >

2.4.3.6. Test revoke license

Mysql > help revoke

Name: 'REVOKE'

Syntax:

REVOKE

Priv_type [(column_list)]

[, priv_type [(column_list)]]

ON [object_type] priv_level

FROM user [, user]...

REVOKE ALL PRIVILEGES, GRANT OPTION

FROM user [, user]...

REVOKE PROXY ON user

FROM user [, user]...

Mysql > show grants for 'test'@'%'

+-+

| | Grants for test@% |

+-+

| | GRANT SUPER ON *. * TO 'test'@'%' |

| | GRANT CREATE ON `test`.* TO 'test'@'%' |

| | GRANT SELECT, INSERT, CREATE, ALTER ON `test01`. * TO 'test'@'%' |

| | GRANT UPDATE (Age) ON `test01`.`testdb`TO 'test'@'%' |

+-+

4 rows in set (0.00 sec)

Mysql > revoke SELECT ON test.* FROM 'test'@'%'

Query OK, 0 rows affected (0.00 sec)

Mysql > show grants for 'test'@'%'

+-+

| | Grants for test@% |

+-+

| | GRANT SUPER ON *. * TO 'test'@'%' |

| | GRANT CREATE ON `test`.* TO 'test'@'%' |

| | GRANT SELECT, INSERT, CREATE, ALTER ON `test01`. * TO 'test'@'%' |

| | GRANT UPDATE (Age) ON `test01`.`testdb`TO 'test'@'%' |

+-+

4 rows in set (0.00 sec)

2.4.3.7. Test the permission to activate SSL connection. You need to reconnect after setting the permission.

2.4.3.8. Other permissions are enabled in the test, which need to be reconnected after the permission is set.

Mysql > help drop user # "deactivate"

Name: 'DROP USER'

Syntax:

DROP USER user [, user]...

Mysql > help rename user # rename with passport

Name: 'RENAME USER'

Syntax:

RENAME USER old_user TO new_user

[, old_user TO new_user]...

2.5. MySQL database ROOT user password forgetting solution steps:

2.3.1. First close the mysqld process and modify the configuration file / etc/my.cnf

[root@lamp ~] # service mysqld stop # stop the mysqld process first

Shutting down MySQL.. [OK]

[root@lamp ~] # vim / etc/init.d/mysqld # modify the startup script of mysqld, which is as follows in the red box

Find start: / start

Add skip permission table and prohibit network login:-- skip-grant-tables-- skip-networking

2.3.2. Start the mysqld service and log in to change the password in mysql.user

[root@lamp ~] # service mysqld start # start the mysqld process

Starting MySQL.. [OK]

[root@lamp ~] # mysql # login to mysql at this time does not require a user name and password

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.5.28-log Source distribution

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

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

Affiliates. Other names may be trademarks of their respective

Owners.

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

Mysql > USE mysql

Database changed

Mysql > SELECT User,Host,Password FROM user; # query the three fields of the user table, which requires a password to log in

+-- +

| | User | Host | Password | |

+-- +

| | root | localhost | * A198E6EEE923DA319BBF86C99624479A198E6EEE9 |

| | root | lamp | * A198E6EEE9823DA319BBF86C99624479A198E6EEE9 |

| | root | 127.0.0.1 | * A198E6EEE9DA319BBF86C99624479A198E6EEE9 |

| | root |:: 1 | * A198E6EEE93DA319BBF86C99624479A198E6EEE9 |

| | test | localhost | * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |

+-- +

5 rows in set (0.00 sec)

Mysql > UPDATE user SET Password=PASSWORD ('redhat') WHERE User='root'

# because the grant permission list is skipped, the user password can only be changed by changing the value of the Password field in the user table.

Query OK, 0 rows affected (0.00 sec)

Rows matched: 4 Changed: 0 Warnings: 0

Mysql > SELECT User,Host,Password FROM user; # query the three fields of the user table, which requires a password to log in

+-- +

| | User | Host | Password | |

+-- +

| | root | localhost | * 84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| | root | lamp | * 84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| | root | 127.0.0.1 | * 84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| | root |:: 1 | * 84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| | test | localhost | * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |

+-- +

5 rows in set (0.00 sec)

Mysql >\ Q

2.3.3. Shut down the mysqld service and log in to delete the previously added security startup parameters

[root@lamp ~] # service mysqld stop # stop the mysqld process

Shutting down MySQL. [OK]

[root@lamp ~] # vim / etc/init.d/mysqld # modify the startup script, remove the previously modified content and save and exit

2.3.4. Start the mysqld service and log in with the modified password

[root@lamp ~] # service mysqld start # start the mysqld process

Starting MySQL.. [OK]

[root@lamp ~] # mysql # directly log in to mysql and prompt for user and password at this time

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@lamp ~] # mysql-uroot-p # specify login through root account-p specify login password required

Enter password:

Do you find it very helpful about how Mysql creates users, permissions, and how to reset administrator passwords above? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.