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

Reproduced from https://www.cnblogs.com/SQL888/p/5748824.html

MySQL has a root user by default, but this user is too privileged to be used only when managing the database. If you want to connect to the MySQL database in your project, it is recommended that you create a new user with less privileges to connect.

Enter the following command in MySQL command line mode to create a new user for MySQL:

1CREATE USER username IDENTIFIED BY 'password'

The new user has been created, but an error will be reported if you log in with this user at this time, because we have not assigned the appropriate permissions to this user. The command to assign permissions is as follows:

1GRANT ALL PRIVILEGES ON *. * TO 'username' @' localhost' IDENTIFIED BY 'password'

Grant username users all permissions on all databases.

If you find that the permission just granted is too large, and if we just want to grant it permission on a certain database, you need to switch to root user to revoke the permission and re-authorize it:

1 2EVOKE ALL PRIVILEGES ON *. * FROM 'username' @' localhost'; GRANT ALL PRIVILEGES ON wordpress.* TO 'username' @' localhost' IDENTIFIED BY 'password'

You can even specify that the user can only execute select and update commands:

1GRANT SELECT, UPDATE ON wordpress.* TO 'username' @' localhost' IDENTIFIED BY 'password'

This way, log in to MySQL again as username, only the wordpress database is visible to it, and if you only grant it select permissions, then it cannot execute delete statements.

In addition, whenever you adjust the permissions, you usually need to execute the following statement to refresh the permissions:

1FLUSH PRIVILEGES

Delete the user you just created:

1DROP USER username@localhost

If you pay attention to the above commands, you can find that whether it is authorization or revocation, you need to specify the host of the response (that is, the content after the @ symbol), because the above passing commands are actually manipulating the user table in the mysql database. You can use the following command to view the corresponding users and the corresponding host:

1SELECT User, Host FROM user

MySQL Study-- MySQL user and Rights Management

The MySQL server controls the user's access to the database through the MySQL permission table. The MySQL permission table is stored in the mysql database and initialized by the mysql _ install_db script. These MySQL permission tables are user,db,table_priv,columns_priv and host, respectively. The structure and contents of these tables are described below:

User permissions table: records the user account information that is allowed to connect to the server, in which permissions are at the global level.

Db permission table: record the operation permissions of each account on each database.

Table_priv permissions table: record operation permissions at the data table level.

Columns_priv permissions table: record operation permissions at the data column level.

Host permission table: cooperate with db permission table to control database-level operation permissions on a given host in more detail. This permission table is not affected by GRANT and REVOKE statements.

Case study:

First, create and authorize users (root users)

[root@mysrv] # mysql-u root-poracle

Mysql > select version ()\ g

+-- +

| | version () |

+-- +

| | 5.6.25-enterprise-commercial-advanced-log |

+-- +

1 row in set (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | prod |

| | test |

+-+

5 rows in set (0.01 sec) 1. Establish and authorize tom users (privileged administrative users)

Mysql > grant all on prod.* to 'tom'@'%' identified by' tom' with grant option

Query OK, 0 rows affected (0.00 sec)

Check whether the user creation is successful:

Mysql > select user,host from user

12 3 4 5 6 7 8 9 10 11 12 13 +-+ | user | host | +-+-+ | tom |% | root | 127.0.0.1 | | root |:: 1 | localhost | | root | localhost | scott | localhost | mysrv | root | mysrv | +-+- -+ 8 rows in set (0.00 sec)

View the authorization of the tom user:

Mysql > show grants for tom

+-+

| | Grants for tom@% |

+-+

| | GRANT USAGE ON *. * TO 'tom'@'%' IDENTIFIED BY PASSWORD' * 71FF744436C7EA1B954F6276121DB5D2BF68FC07' | |

| | GRANT ALL PRIVILEGES ON `prod`. * TO 'tom'@'%' WITH GRANT OPTION |

+-+

GRANT syntax:

GRANT privileges (columns)

ON what

TO user IDENTIFIED BY "password"

WITH GRANT OPTION permission list:

ALTER: modify tables and indexes.

CREATE: create databases and tables.

DELETE: deletes existing records in the table.

DROP: discard (delete) databases and tables.

INDEX: create or discard indexes.

INSERT: inserts a new row into the table.

REFERENCE: not used.

SELECT: retrieve the records in the table.

UPDATE: modify existing table records.

FILE: read or write files on the server.

PROCESS: view thread information executed on the server or kill threads.

RELOAD: reload the authorization table or empty the log, host cache, or table cache.

SHUTDOWN: shut down the server.

ALL: all permissions, synonymous with ALL PRIVILEGES.

USAGE: special "No permission" permission.

The user account consists of "username" and "host", which indicates where the user is allowed to access. Tom@'%' represents any address, which can be omitted by default. It can also be "tom@192.168.1.%", "tom@%.abc.com" and so on. The database format is db@table, which can be "test.*" or "*. *", the former representing all tables in the test database and the latter representing all tables in all databases.

The clause "WITH GRANT OPTION" indicates that the user can assign permissions to other users. 2. We use root to create a few more users, and then tom, the administrator of the test database, assigns permissions to them.

Mysql > create user 'tom1' identified by' tom1', 'tom2' identified by' tom2'

Query OK, 0 rows affected (0.00 sec)

Mysql > select user,host from user

12 3 4 5 6 7 8 9 10 11 12 13 14 15 +-+-+ | user | host | +-+-+ | tom |% | tom1 |% | tom2 |% | root | 127.0.0.1 | | root |: 1 | localhost | root | localhost | | scott | localhost | mysrv | | root | mysrv | +-- -+-+ 10 rows in set (0.00 sec)

Root users log out, tom logs in, and users are authorized to access the prod library

[root@mysrv] # mysql-u tom-ptom

ERROR 1045 (28000): Access denied for user 'tom'@'localhost' (using password: YES)

Tom users can't log in!

Then authorize the tom user:

Mysql > grant all on prod.* to 'tom'@'localhost' identified by' tom' with grant option

Query OK, 0 rows affected (0.00 sec)

Mysql > show grants for tom

+-+

| | Grants for tom@% |

+-+

| | GRANT USAGE ON *. * TO 'tom'@'%' IDENTIFIED BY PASSWORD' * 71FF744436C7EA1B954F6276121DB5D2BF68FC07' | |

| | GRANT ALL PRIVILEGES ON `prod`. * TO 'tom'@'%' WITH GRANT OPTION |

+-+

2 rows in set (0.00 sec)

Mysql > use mysql

Database changed

Mysql > select user,host from user

12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 +-+-+ | user | host | +-+-+ | tom |% | tom1 |% | tom2 |% | root | 127.0.0.1 | root |: 1 | localhost | root | localhost | | scott | localhost | | tom | localhost | mysrv | | root | | mysrv | +-+-+ 11 rows in set (0.00 sec) |

Log in to tom:

[root@mysrv] # mysql-u tom-ptom prod

Mysql > select database ()

+-+

| | database () |

+-+

| | prod |

+-+

1 row in set (0.01 sec)

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | tom@localhost |

+-+

1 row in set (0.00 sec)

Create a table:

Mysql > show tables

+-+

| | Tables_in_prod |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

Mysql > create table T2 as select * from T1

Query OK, 3 rows affected (0.15 sec)

Records: 3 Duplicates: 0 Warnings: 0

View table information:

Mysql > desc T2

+-+ +

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

+-+ +

| | id | int (11) | YES | | NULL |

| | name | varchar (10) | YES | | NULL |

+-+ +

2 rows in set (0.01sec)

Mysql > show create table T2

+- -+

| | Table | Create Table |

+- -+

| | T2 | CREATE TABLE `t2` (

`id`int (11) DEFAULT NULL

`name` varchar (10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+- -+

1 row in set (0.01 sec)

Mysql > show create table T2\ G

* * 1. Row *

Table: t2

Create Table: CREATE TABLE `t2` (

`id`int (11) DEFAULT NULL

`name` varchar (10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Mysql > select * from T2

+-+ +

| | id | name |

+-+ +

| | 10 | tom |

| | 20 | jerry |

| | 30 | rose |

+-+ +

3 rows in set (0.00 sec)

3. Tom users are authorized by tom1,tom2

Mysql > grant select on prod.* to tom1

Query OK, 0 rows affected (0.00 sec)

Mysql > grant select on prod.* to tom2

Query OK, 0 rows affected (0.02 sec)

Mysql > grant insert,update on prod.* to tom2

Query OK, 0 rows affected (0.00 sec)

Tom2 login (remote login):

C:\ Users\ Administrator > mysql-h 192.168.240-utom2-ptom2

Mysql > select database ()

+-+

| | database () |

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

Mysql > use prod

Database changed

Mysql > select database ()

+-+

| | database () |

+-+

| | prod |

+-+

1 row in set (0.00 sec)

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | tom2@% |

+-+

1 row in set (0.00 sec)

Mysql > show grants for tom2

+-+

| | Grants for tom2@% |

+-+

| | GRANT USAGE ON *. * TO 'tom2'@'%' IDENTIFIED BY PASSWORD |

| | GRANT SELECT, INSERT, UPDATE ON `prod`. * TO 'tom2'@'%' |

+-+

2 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_prod |

+-+

| | T1 |

| | T2 |

+-+

2 rows in set (0.00 sec)

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 10 | tom |

| | 20 | jerry |

| | 30 | rose |

+-+ +

3 rows in set (0.00 sec)

Mysql > select * from T2

+-+ +

| | id | name |

+-+ +

| | 10 | tom |

| | 20 | jerry |

| | 30 | rose |

+-+ +

3 rows in set (0.00 sec)

Mysql > insert into T1 values (40 recorder John')

Query OK, 1 row affected (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.09 sec)

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 10 | tom |

| | 20 | jerry |

| | 30 | rose |

| | 40 | john |

+-+ +

4 rows in set (0.00 sec)

Mysql > update T1 set name='ellen' where id=40

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 10 | tom |

| | 20 | jerry |

| | 30 | rose |

| | 40 | ellen |

+-+ +

4 rows in set (0.00 sec)

Mysql > delete from T1

ERROR 1142 (42000): DELETE command denied to user 'tom2'@'192.168.8.254' for tab

Le 't1'

Mysql > commit

Query OK, 0 rows affected (0.05 sec)

Mysql > select * from T1

+-+ +

| | id | name |

+-+ +

| | 10 | tom |

| | 20 | jerry |

| | 30 | rose |

| | 40 | ellen |

+-+ +

4 rows in set (0.00 sec) 4. Reclaim the update permission of tom2:

Mysql > revoke update on prod.* from tom2

Query OK, 0 rows affected (0.00 sec)

Tom2 logs in again:

C:\ Users\ Administrator > mysql-h 192.168.240-utom2-ptom2

Mysql > use prod

Database changed

Mysql > update T1 set name='lily' where id=10

ERROR 1142 (42000): UPDATE command denied to user 'tom2'@'192.168.8.254' for tab

Le 't1'

-update failed!

Second, modify the user password:

1. Root users modify ordinary user passwords

Mysql > set password for tom1=password ('oracle')

Query OK, 0 rows affected (0.01 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Tom1 re-login:

C:\ Users\ Administrator > mysql-h 192.168.240-utom1-ptom1

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'tom1'@'192.168.8.254' (using passwor

D: YES)

-the login of the old password failed!

C:\ Users\ Administrator > mysql-h 192.168.240-utom1-poracle

Mysql >

2. Ordinary users change their passwords:

C:\ Users\ Administrator > mysql-h 192.168.240-utom1-poracle

Mysql > set password=password ('tom1')

Query OK, 0 rows affected (0.00 sec)

Re-login:

C:\ Users\ Administrator > mysql-h 192.168.240-utom1-ptom1

Mysql >

-the new password logged in successfully!

3. Delete the user:

1. Reclaim all user permissions

Mysql > revoke all on prod.* from tom2

Query OK, 0 rows affected (0.01 sec)

2. Delete a user

Mysql > drop user tom2

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > select user,host from user

12 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 +-+ | user | host | +-+-+ | jerry |% | rose |% | tom |% | tom1 |% | root | 127.0.0.1 | | root |: 1 | localhost | | jerry | localhost | | root | localhost | | rose | Localhost | | scott | localhost | | tom | localhost | mysrv | | root | mysrv | +-+-+ 14 rows in set (0.00 sec)

-Summary-

Create a user:

GRANT insert, update ON testdb.* TO user1@'%' IDENTIFIED BY 'password' WITH GRANT OPTION

CREATE USER user2 IDENTIFIED BY 'password'

Assign permissions:

GRANT select ON testdb.* TO user2

View permissions:

SHOW GRANTS FOR user1

Change the password:

SET PASSWORD FOR user1 = PASSWORD ('newpwd')

SET PASSWORD = PASSWORD ('newpwd')

Remove permissions:

REVOKE all ON *. * FROM user1

Delete a user:

DROP USER user1

Database list:

SHOW DATABASES

Datasheet list:

SHOW TABLES

Current database:

SELECT DATABASE ()

Current user:

SELECT USER ()

Data table structure:

DESCRIBE table1

Refresh permissions:

FLUSH PRIVILEGES

Grant and revoke can control access at several levels

1, the entire server, using grant ALL and revoke ALL

2, the whole database, using on database.*

3, feature table, using on database.table

4, specific column

5, specific stored procedures

The meaning of the value of host column in user Table

% match all hosts

Localhost localhost will not be resolved to an IP address, but will be connected directly through UNIXsocket

127.0.0.1 will connect through the TCP/IP protocol and can only be accessed locally

:: 1:: 1 is compatible with ipv6, representing 127.0.0.1 of the same as ipv4

Grant ordinary data users, the right to query, insert, update and delete all table data in the database.

Grant select on testdb.* to common_user@'%'

Grant insert on testdb.* to common_user@'%'

Grant update on testdb.* to common_user@'%'

Grant delete on testdb.* to common_user@'%'

Or, replace it with a MySQL command:

Grant select, insert, update, delete on testdb.* to common_user@'%'

Grant database developer, create tables, indexes, views, stored procedures, functions. Wait for permission.

Grant creates, modifies, and deletes MySQL data table structure permissions.

Grant create on testdb.* to developer@'192.168.0.%'

Grant alter on testdb.* to developer@'192.168.0.%'

Grant drop on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL foreign key permissions.

Grant references on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL temporary table permissions.

Grant create temporary tables on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL index permissions.

Grant index on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL view and view view source code permissions.

Grant create view on testdb.* to developer@'192.168.0.%'

Grant show view on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL stored procedures and function permissions.

Grant create routine on testdb.* to developer@'192.168.0.%';-- now, can show procedure status

Grant alter routine on testdb.* to developer@'192.168.0.%';-- now, you can drop a procedure

Grant execute on testdb.* to developer@'192.168.0.%'

Grant the permissions of a normal DBA to manage a MySQL database.

Grant all privileges on testdb to dba@'localhost'

The keyword "privileges" can be omitted.

Grant Advanced DBA manages permissions for all databases in MySQL.

Grant all on *. * to dba@'localhost'

MySQL grant permissions, which can be used at multiple levels.

1. Grant acts on the entire MySQL server:

Grant select on *. * to dba@localhost;-- dba can query tables in all databases in MySQL.

Grant all on *. * to dba@localhost;-- dba can manage all databases in MySQL

2. Grant acts on a single database:

Grant select on testdb.* to dba@localhost;-- dba can query tables in testdb.

3. Grant acts on a single data table:

Grant select, insert, update, delete on testdb.orders to dba@localhost

4. Grant acts on the columns in the table:

Grant select (id, se, rank) on testdb.apache_log to dba@localhost

5. Grant acts on stored procedures and functions:

Grant execute on procedure testdb.pr_add to 'dba'@'localhost'

Grant execute on function testdb.fn_add to 'dba'@'localhost'

Note: after modifying permissions, be sure to refresh the service, or restart the service, refresh the service with: FLUSH PRIVILEGES.

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.