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

This article mainly explains "how to realize database user management". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. let's study and learn "how to realize database user management".

1. User defined

User defined:

Mysql > select user,host,password from mysql.user

+-- +

| | user | host | password |

= =

User host range

From which host addresses can a user access my database?

Functions of the user:

1. Used to log in to the mysql database

2. Used to manage database objects (libraries, tables)

Permissions:

Function: set different object management capabilities for different users.

Select updata delete insert creat...

Scope of permissions:

*. *: global scope

Oldboy.*: single library level

Oldboy.t1: single table level

Create a user and authorize:

Grant all on wordpress.* to workpress@'10.0.0.%' identified by 'oldboy123'

Authorization command permission scope is used for host scope password

Modify Super Admin user: root

Change the password: mysqladmin-uroot-p password oldboy123

Root@localhost

Ordinary user: select,updata,delete,insert,create,drop (add, delete, change and search)

Action commands for users only:

Mysql > create user zabbix@'10.0.0.%' identified by 'oldboy123'

Query OK, 0 rows affected (0.01 sec)

Mysql > drop user root@'127.0.0.1'

Query OK, 0 rows affected (0.00 sec)

Mysql > select user,host,password from mysql.user

Special deletion method:

Mysql > delete from mysql.user where user='oldboy' and host='localhost'

Query OK, 1 row affected (0.00 sec)

Mysql > flush privileges

2. User authorization

Grant all on wordpress.* to workpress@'10.0.0.%' identified by 'oldboy123'

Authorization command permission scope is used for host scope

All permissions:

SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES

INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE

REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE

CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

Development user permissions: (authorized by root users)

Grant SELECT,INSERT, UPDATE, DELETE, CREATE, DROP on testdb.* to zabbix@'10.0.0.%'

Use zabbix to check:

Mysql > create database testdb

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

Create a user and authorize:

Mysql > grant all on *. * to root@'10.0.0.%' identified by 'oldboy123'

Query OK, 0 rows affected (0.00 sec)

Mysql > select user,host,password from mysql.user

Query the permissions of the user:

Mysql > show grants for zabbix@'10.0.0.%'

Create a similar administrator:

Mysql > show grants for root@'10.0.0.%'

Local Super Admin: with grants permission

Mysql > show grants for root@'localhost'

Revoke the authority:

Mysql > revoke create,drop on testdb.* from zabbix@'10.0.0.%'

Mysql > show grants for zabbix@'10.0.0.%'

Think about:

Grant select on *. * to zabbix@'10.0.0.%'

Grant INSERT, UPDATE, DELETE, CREATE, DROP on testdb.* to zabbix@'10.0.0.%'

Grant update on testdb.t1 to zabbix@'10.0.0.%'

# # #

Mysql > use testdb

Mysql > create table T1 (id int)

Mysql > show tables

Mysql > insert into T1 values (1)

Q: what permissions does zabbix@'10.0.0.%' have on the T1 table?

If permissions are set for a user at different database levels, the final permissions are superimposed, and the total maximum permissions prevail.

It is recommended that there should be no multi-scope authorization.

Thank you for your reading. the above is the content of "how to achieve database user management". After the study of this article, I believe you have a deeper understanding of how to realize database user management. Specific use also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.