MySQLStudy-MySQL User and permission management MySQL server uses the MySQL permission table to control user access to the database. the MySQL permission table is stored in the mysql database and initialized by the mysql_install_db script. These MySQL permission tables are respectively user, db, table_p
MySQL Study-MySQL User and permission management
The MySQL server uses the MySQL permission table to control user access to the database. 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 following describes the structure and content of these tables:
User permission table: Record the user account information that can be connected to the server. the permissions in the record are global.
Db permission table: Records the operation permissions of each account on each database.
Table_priv permission table: Records Data Table-level operation permissions.
Columns_priv permission list: Records the operation permissions at the data column level.
Host permission table: Use the database permission table to control database-level operation permissions on a given host. This permission table is not affected by the GRANT and REVOKE statements.
Case analysis:
1. create and authorize a user (root user)
[Root @ mysrv ~] # Mysql-u root-poracle
Mysql> select version () \ g
+ ------------------------------------------- +
| Version () |
+ ------------------------------------------- +
| 5.6.25-enterprise-defined cial-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. create and authorize tom users (privileged 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 is successfully created:
Mysql> select user, host from user;
+-------+-----------+| 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;
+ Shards +
| Grants for tom @ % |
+ Shards +
| Grant usage on *. * TO 'Tom '@' % 'identified by password' * 71FF744436C7EA1B954F6276121DB5D2BF68FC07' |
| Grant all privileges on 'prod'. * TO 'Tom '@' % 'with grant option |
+ Shards +
GRANT syntax:
GRANT privileges (columns)
ON what
TO user identified by "password"
WITH GRANT OPTION
Permission list:
ALTER: modify tables and indexes.
CREATE: CREATE a database and a table.
DELETE: DELETE existing records in the table.
DROP: discard (delete) databases and tables.
INDEX: create or discard an INDEX.
INSERT: INSERT a new row into the table.
REFERENCE: unused.
SELECT: retrieves records from a table.
UPDATE: modify existing table records.
FILE: read or write files on the server.
PROCESS: view information about the thread executed on the server or kill the thread.
RELOAD: RELOAD the authorization table or clear logs, host caches, or table caches.
SHUTDOWN: shut down the server.
ALL: ALL permissions, synonym for all privileges.
USAGE: special "no permission" permission.
The user account consists of "username" and "host", which indicates the user is allowed to access from where. Tom @ '%' indicates 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 indicates all tables of the test database, and the latter indicates all tables of all databases.
Clause "with grant option" indicates that this user can assign permissions to other users.
2. we use root to create several more users, and then the test database administrator tom will assign them permissions.
Mysql> create user 'tom1' identified by 'tom1', 'tomm2' identified by 'tomm2 ';
Query OK, 0 rows affected (0.00 sec)
Mysql> select user, host from user;
+-------+-----------+| 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 user exits, tom logs on, and authorizes the user to access the prod Library
[Root @ mysrv ~] # Mysql-u tom-ptom
ERROR 1045 (28000): Access denied for user 'Tom '@ 'localhost' (using password: YES)
The tom user cannot log on !!!
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;
+ Shards +
| Grants for tom @ % |
+ Shards +
| Grant usage on *. * TO 'Tom '@' % 'identified by password' * 71FF744436C7EA1B954F6276121DB5D2BF68FC07' |
| Grant all privileges on 'prod'. * TO 'Tom '@' % 'with grant option |
+ Shards +
2 rows in set (0.00 sec)
Mysql> use mysql;
Database changed
Mysql> select user, host from user;
+-------+-----------+| 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)
Tom login:
[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.01 sec)
Mysql> show create table t2;
+ ------- + Response +
| Table | Create Table |
+ ------- + Response +
| T2 | create table 'T2 '(
'Id' int (11) default null,
'Name' varchar (10) DEFAULT NULL
) ENGINE = InnoDB default charset = latin1 |
+ ------- + Response +
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. the tom user is tom1 and tom2 authorized
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.8.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, '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.01 sec)
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. revoke the update permission of tom2:
Mysql> revoke update on prod. * from tom2;
Query OK, 0 rows affected (0.00 sec)
Tom2 re-login:
C: \ Users \ Administrator> mysql-h 192.168.8.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!
II. modify the user password:
1. the root user changes the password of a common user.
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.8.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)
--- Logon with the old password failed!
C: \ Users \ Administrator> mysql-h 192.168.8.240-utom1-poracle
Mysql>
2. change the password of a common user:
C: \ Users \ Administrator> mysql-h 192.168.8.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.8.240-utom1-ptom1
Mysql>
--- The new password is successfully logged in!
3. delete a user:
1. revoke 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;
+-------+-----------+| 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)
------- Abstract --------------------------------------
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 password:
Set password for user1 = PASSWORD ('newpwd ');
Set password = PASSWORD ('newpwd ');
Remove permission:
REVOKE all ON *. * FROM user1;
Delete a user:
Drop user user1;
Database list:
Show databases;
Data table list:
Show tables;
Current database:
Select database ();
Current User:
Select user ();
Data table structure:
DESCRIBE table1;
Refresh permission:
Flush privileges;
Grant and revoke can control access permissions at several levels
1. the entire server uses grant ALL and revoke ALL
2. the entire database uses on database .*
3. feature table: on database. table
4. specific columns
5. specific stored procedures
Significance of the value of the host column in the user table
% Match all hosts
Localhost is not resolved to an IP address and is directly connected through UNIXsocket
127.0.0.1 is connected through the TCP/IP protocol and can only be accessed on the local machine;
: 1: 1 is compatible with ipv6, indicating 127.0.0.1 of the same ipv4
Grant normal 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 @ '%'
Alternatively, replace the following with a MySQL command:
Grant select, insert, update, delete on testdb. * to common_user @ '%'
Grant database developers to create tables, indexes, views, stored procedures, and functions... .
Grant permissions to create, modify, and delete MySQL data table structures.
Grant create on testdb. * to developer @ '192. 192.% ';
Grant alter on testdb. * to developer @ '192. 192.% ';
Grant drop on testdb. * to developer @ '192. 192.% ';
Grant the MySQL foreign key operation permission.
Grant references on testdb. * to developer @ '192. 192.% ';
Grant the permission to operate MySQL temporary tables.
Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';
Grant the permission to operate MySQL indexes.
Grant index on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate the MySQL view and view the source code.
Grant create view on testdb. * to developer @ '192. 192.% ';
Grant show view on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate MySQL stored procedures and functions.
Grant create routine on testdb. * to developer @ '192. 192.% '; -- now, can show procedure status
Grant alter routine on testdb. * to developer @ '192. 192.% '; -- now, you can drop a procedure
Grant execute on testdb. * to developer @ '192. 192.% ';
Grant common DBA permission to manage a MySQL database.
Grant all privileges on testdb to dba @ 'localhost'
The keyword "privileges" can be omitted.
Grant advanced DBA permission to manage all databases in MySQL.
Grant all on *. * to dba @ 'localhost'
MySQL grant permissions can be applied to multiple levels.
1. grant applies to 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 applies to a single database:
Grant select on testdb. * to dba @ localhost; -- dba can query tables in testdb.
3. grant applies to a single data table:
Grant select, insert, update, delete on testdb. orders to dba @ localhost;
4. grant applies to columns in the table:
Grant select (id, se, rank) on testdb. apache_log to dba @ localhost;
5. grant applies to 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 the permissions, you must refresh the service or restart the service to use flush privileges.
