Shulou(Shulou.com)05/31 Report--

This article mainly introduces "introduction to MySQL users and rights management". In daily operation, I believe many people have doubts about the introduction of MySQL users and rights management. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "introduction to MySQL users and rights management". Next, please follow the editor to study!

The main function of the MySQL authority system is to confirm the user connected to a given host and to give the user the relevant DML and DQL permissions on the database. MySQL access control consists of two phases, one is that the server checks whether a connection is allowed, and the other is that the server checks every request made on the assumption that it can connect. See if you have enough authority to implement it. This article mainly describes the user creation, authorization, revocation and so on related to the MySQL permission system.

1. Get help on rights management

Root@localhost [(none)] > help Account Management

For more information, type 'help', where is one of the following

Topics:

You asked for help about help category: "Account Management"

CREATE USER

DROP USER

GRANT

RENAME USER

REVOKE

SET PASSWORD

2. Create a mysql database user

-- the syntax for creating a user

Root@localhost [(none)] > help create user

Name: 'CREATE USER'

Description:

Syntax:

CREATE USER user_specification [, user_specification]...

User_specification:

User

[

| | IDENTIFIED WITH auth_plugin [AS 'auth_string'] |

IDENTIFIED BY [PASSWORD] 'password'

]

The create user command creates a new account and can also specify a password for it. This command adds a record to the user table.

This command only grants usage permissions. Further authorization is required using the grant command. You can also use the grant command to create an account directly, as shown in the following demonstration.

The following is an explanation of usage in the mysql official manual.

The USAGE privilege specifier stands for "no privileges." It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

-- current demonstration environment

Root@localhost [(none)] > show variables like 'version'

+-+ +

| | Variable_name | Value |

+-+ +

| | version | 5.5.39-log |

+-+ +

-- create a new user (no password specified)

Root@localhost [(none)] > create user 'fred'@'localhost'

Query OK, 0 rows affected (0.00 sec)

-- specify a password to create a new user.% means arbitrary, that is, frank can access the database from any host.

Root@localhost [(none)] > create user 'frank'@'%' identified by' frank'

Query OK, 0 rows affected (0.00 sec)

-- View the account you just added

Root@localhost [(none)] > select host,user,password from mysql.user where user like 'fr%'

+-- +

| | host | user | password | |

+-- +

| |% | frank | * 63DAA25989C7E01EB96570FA4DBE154711BEB361 |

| | localhost | fred |

+-- +

3. Use grant to grant permissions

-- grant command syntax

Root@localhost [mysql] > help grant

Name: 'GRANT'

Description:

Syntax:

GRANT

Priv_type [(column_list)]

[, priv_type [(column_list)]]

ON [object_type] priv_level

TO user_specification [, user_specification]...

[REQUIRE {NONE | ssl_option [[AND] ssl_option]...}]

[WITH with_option...]

GRANT PROXY ON user_specification

TO user_specification [, user_specification]...

[WITH GRANT OPTION]

Object_type:

TABLE

| | FUNCTION |

| | PROCEDURE |

Priv_level:

*

| *. *

| | db_name.* |

| | db_name.tbl_name |

| | tbl_name |

| | db_name.routine_name |

User_specification:

User

[

| | IDENTIFIED WITH auth_plugin [AS 'auth_string'] |

IDENTIFIED BY [PASSWORD] 'password'

]

How to authorize

A. you need to specify which permissions are granted

B, permissions are applied to those objects (global, specific objects, etc.)

C, which account is granted

D, you can specify a password (optional, users are automatically created in this way)

Scope of authorized permissions:

ON *. *

ON db_name.*

ON db_name.table_name

ON db_name.table_name.column_name

ON db_name.routine_name

-- A list of permissions. We directly query all permissions of the root account, as follows

-- the permissions of mysql are relatively simple compared with Oracle, and do not involve the definition and configuration of roles.

Root@localhost [(none)] > select * from mysql.user where user='root' and host='localhost'\ G

* * 1. Row *

Host: localhost

User: root

Password:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

Plugin:

Authentication_string:

1 row in set (0.00 sec)

-- note that the mysql prompt described in this article is user@hostname [(dbname)]. Different accounts and different host logins will display differently.

Secondly, the user identity and permissions represented by different prompts.

-- View the current connected user

Root@localhost [none)] > select current_user ()

+-+

| | current_user () |

+-+

| | root@localhost |

+-+

-- View the permissions of the current account

Root@localhost [(none)] > show grants;-this account is for the highest permissions with WITH GRANT OPTION

+-- +

| | Grants for root@localhost |

+-- +

| | GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' WITH GRANT OPTION |

| | GRANT PROXY ON'@''TO 'root'@'localhost' WITH GRANT OPTION |

+-- +

SUSE11b:~ # mysql-ufred-p

Enter password:

Fred@localhost [(none)] > show grants

+-+

| | Grants for fred@localhost |

+-+

| | GRANT USAGE ON *. * TO 'fred'@'localhost' |

+-+

-- use the root account to grant permissions to fred, all privileges

Root@localhost [(none)] > grant all privileges on *. * to 'fred'@'localhost'

Query OK, 0 rows affected (0.01 sec)

Root@localhost [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

Fred@localhost [(none)] > show grants

+-- +

| | Grants for fred@localhost |

+-- +

| | GRANT ALL PRIVILEGES ON *. * TO 'fred'@'localhost' |

+-- +

Fred@localhost [(none)] > use tempdb

Fred@localhost [tempdb] > create table tb_isam (id int,value varchar (20)) engine=myisam

Query OK, 0 rows affected (0.10 sec)

Fred@localhost [tempdb] > insert into tb_isam values (1 dagger jack'), (2 recorder robin')

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Fred@localhost [tempdb] > commit

-- the following authorization received an error and cannot be authorized

Fred@localhost [tempdb] > grant select on tempdb.* to 'frank'@'%'

ERROR 1044 (42000): Access denied for user 'fred'@'localhost' to database' tempdb'

-- Let's authorize the previously created frank from root session.

-- Grant frank select permissions for all objects on the database tempdb

Root@localhost [(none)] > grant select on tempdb.* to 'frank'@'%'

Query OK, 0 rows affected (0.00 sec)

-- Update permissions in cache

Root@localhost [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

-- Log in with a frank account from another host

Suse11a:~ # mysql-ufrank-p-h272.16.6.89

Enter password:

-- at this time frank, you can access the table TB _ isam on tempdb.

Frank@172.16.6.89 [(none)] > select * from tempdb.tb_isam

+-+ +

| | id | value |

+-+ +

| | 1 | jack |

| | 2 | robin |

+-+ +

Frank@172.16.6.89 [(none)] > show grants

+-+

| | Grants for frank@% |

+-+

| | GRANT USAGE ON *. * TO 'frank'@'%' IDENTIFIED BY PASSWORD' * 63DAA25989C7E01EB96570FA4DBE154711BEB361' |

| | GRANT SELECT ON `tempdb`. * TO 'frank'@'%'-you can see that the select permission is extra |

+-+

The following is an example of a maximum permission granted, which automatically creates a user. Since we have not set a password, the query result of the password column is empty.

Root@localhost [(none)] > grant all privileges on *. * to 'jack'@'localhost'

Query OK, 0 rows affected (0.00 sec)-the first * represents any database, and the second * represents any object on the database

Root@localhost [(none)] > select user,host,Password from mysql.user where user='jack'

+-+

| | user | host | Password | |

+-+

| | jack | localhost |

+-+

Suse11b:~ # mysql-ujack-p-h localhost

Enter password:

Jack@localhost [(none)] > show grants for current_user;-- this method is equivalent to show grants. Check your own permissions.

+-- +

| | Grants for jack@localhost |

+-- +

| | GRANT ALL PRIVILEGES ON *. * TO 'jack'@'localhost' |

+-- +

-- check the permissions of other users under the current session. Note, users who log in to session also need to have permissions to view other user permissions.

Jack@localhost [(none)] > show grants for 'frank'@'%'

+-+

| | Grants for frank@% |

+-+

| | GRANT USAGE ON *. * TO 'frank'@'%' IDENTIFIED BY PASSWORD' * 63DAA25989C7E01EB96570FA4DBE154711BEB361' |

| | GRANT SELECT ON `tempdb`. * TO 'frank'@'%' |

+-+

-- the following demonstrates authorization based on object column level

-- first of all, the select permissions prior to revoke

Root@localhost [(none)] > revoke select on tempdb.* from 'frank'@'%'

Query OK, 0 rows affected (0.00 sec)

Fred@localhost [tempdb] > create table tb_user as select * from mysql.user

Query OK, 9 rows affected (0.15 sec)

Records: 9 Duplicates: 0 Warnings: 0

Fred@localhost [tempdb] > grant select (user,host), update (host) on tempdb.tb_user to 'frank'@'%'

ERROR 1142 (42000): GRANT command denied to user' fred'@'localhost' for table 'tb_user'-Authorization failed

-- use root to authorize

Root@localhost [(none)] > grant select (user,host), update (host) on tempdb.tb_user to 'frank'@'%'

Query OK, 0 rows affected (0.00 sec)

Root@localhost [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

-- Let's check the permissions frank has.

Root@localhost [(none)] > show grants for 'frank'

+-+

| | Grants for frank@% |

+-+

| | GRANT USAGE ON *. * TO 'frank'@'%' IDENTIFIED BY PASSWORD' * 63DAA25989C7E01EB96570FA4DBE154711BEB361' |

| | GRANT SELECT (user, host), UPDATE (host) ON `tempdb`.`tb _ user`TO 'frank'@'%' |

+-+

-- use frank identity to verify the granted permissions

Frank@172.16.6.89 [(none)] > desc tempdb.tb_user

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | Host | char (60) | NO | |

| | User | char (16) | NO | |

+-+ +

Frank@172.16.6.89 [(none)] > select * from tempdb.tb_user;-wildcards are not supported when accessing, and column names must be specified

ERROR 1142 (42000): SELECT command denied to user' frank'@'suse11a.site' for table 'tb_user'

Frank@172.16.6.89 [(none)] > select host,user from tempdb.tb_user where user='frank'

+-+ +

| | host | user |

+-+ +

| |% | frank |

+-+ +

It is important to note that if your object is created in a test-related database, permission restrictions may be invalidated.

The following query is used to view the authorization table of db

Root@localhost [(none)] > select host,db,user from mysql.db

+-+

| | host | db | user | |

+-+

| |% | test | |

| |% | test\ _% | |

+-+

Column host can be updated, but column user can not, based on the previous permission grant, as shown in the following two SQL statements

Frank@172.16.6.89 [(none)] > update tempdb.tb_user set host='localhost' where user='frank'

Query OK, 1 row affected (0.12 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Frank@172.16.6.89 [(none)] > update tempdb.tb_user set user='jason' where user='jack'

ERROR 1143 (42000): UPDATE command denied to user' frank'@'suse11a.site' for column 'user' in table' tb_user'

-- about WITH GRANT OPTION

Root@localhost [(none)] > show grants;-- notice that there is WITH GRANT OPTION under root

+-- +

| | Grants for root@localhost |

+-- +

| | GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' WITH GRANT OPTION |

| | GRANT PROXY ON'@''TO 'root'@'localhost' WITH GRANT OPTION |

+-- +

Root@localhost [(none)] > show grants for 'jack'@'localhost';-notice that there is no WITH GRANT OPTION under jack

This is why the object created by the user cannot be authorized before.

| | Grants for jack@localhost |

+-- +

| | GRANT ALL PRIVILEGES ON *. * TO 'jack'@'localhost' |

+-- +

4. Revoke the authority

Revoking permissions uses the revoke keyword, and revoking permissions is basically similar to authorization.

Secondly, which permissions can be granted and which permissions can be revoked accordingly, the original to clause becomes the from clause.

Such as the following example

Mysql > revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb _ user`from 'frank'@'%'

Mysql > revoke all privileges, grant option from 'frank'@'%'

Root@localhost [(none)] > revoke SELECT (user, host), UPDATE (host) ON `tempdb`.`tb _ user`from 'frank'@'%'

Query OK, 0 rows affected (0.00 sec)

Root@localhost [(none)] > revoke all privileges, grant option from 'frank'@'%'

Query OK, 0 rows affected (0.01 sec)

Root@localhost [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

Root@localhost [(none)] > show grants for 'frank';-- have only the most basic permissions after viewing revoke

+-+

| | Grants for frank@% |

+-+

| | GRANT USAGE ON *. * TO 'frank'@'%' IDENTIFIED BY PASSWORD' * 63DAA25989C7E01EB96570FA4DBE154711BEB361' |

+-+

5. Delete and rename accounts

Delete a user using the drop user command

-- View the users that already exist in the current system

Root@localhost [(none)] > select user,host,Password from mysql.user

+-- +

| | user | host | Password | |

+-- +

| | root | localhost |

| | root | SUSE11b |

| | root | 127.0.0.1 |

| | root |:: 1 |

| | localhost |

| | suse11b |

| | fred | localhost |

| | frank |% | * 63DAA25989C7E01EB96570FA4DBE154711BEB361 |

| | jack | localhost |

+-- +

-use the drop user command to delete a user

Root@localhost [(none)] > drop user 'frank'@'%'

Query OK, 0 rows affected (0.00 sec)

Root@localhost [(none)] > drop user 'fred'@'localhost'

Query OK, 0 rows affected (0.00 sec)

Root@localhost [(none)] > select user,host,Password from mysql.user where user like 'fr%'

Empty set (0.00 sec)

How to rename an account, use the rename user command

Root@localhost [(none)] > rename user 'jack'@'localhost' to' jason'@'localhost'

Query OK, 0 rows affected (0.00 sec)

Root@localhost [(none)] > select user,host,Password from mysql.user where user like'j%'

+-+

| | user | host | Password | |

+-+

| | jason | localhost |

+-+

-- for the deletion of users, you can also delete the corresponding records directly from mysql.user, but it is not recommended to directly manipulate MySQL system tables.

At this point, the study on "introduction to MySQL users and Rights Management" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.