Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL privilege system

2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

MySQL weight limit system

1) set password

There are several ways to set the MySQL user password:

-CREATE USER...IDENTIFIED BY

-GRANT...IDENTIFIED BY

-SET PASSWORD

-mysqladmin password

-UPDATE Authorization form (not recommended)

Assign a unique strong password to all user accounts.

? Avoid passwords that can be easily guessed.

? Use the following SELECT statement to list all accounts without a password:

SELECT Host, User FROM mysql.user

WHERE Password =''

? Determine the duplicate password:

SELECT User FROM mysql.user GROUP BY password

HAVING count (user) > 1

? Invalidate the password:

ALTER USER jim@localhost PASSWORD EXPIRE

2) permissions supported by Mysql

Privilege

Meaning and Grantable Levels

ALL [PRIVILEGES]

Enable use of ALTER TABLE. Levels: Global, database, table.

ALTER ROUTINE

Enable stored routine creation. Levels: Global, database.

CREATE TABLESPACE

Enable views to be created or altered. Levels: Global, database, table.

DELETE

Enable databases, tables, and views to be dropped. Levels: Global, database, table.

EVENT

Enable the user to cause the server to read or write files. Level: Global.

GRANT OPTION

Enable use of INSERT. Levels: Global, database, table, column.

LOCK TABLES

Enable foreign key creation. Levels: Global, database, table, column.

RELOAD

Enable the user to ask where master or slave servers are. Level: Global.

REPLICATION SLAVE

Enable use of UPDATE. Levels: Global, database, table, column.

USAGE

3) example of allowed hostname format

? Hostname: localhost

? Qualified hostname: 'hostname.example.com'

? IP number: 192.168.9.78

? IP address: 10.0.0.0amp 255.255.255.0

? Pattern or wildcard:% or _

Example of user name and hostname:

? John@10.20.30.40

? John@'10.20.30.%'

? John@'%.ourdomain.com'

? John@'10.20.30.0/255.255.255.0'

4) GRANT statement

? The GRANT statement creates a new account or modifies an existing account.

? GRANT syntax:

GRANT SELECT ON world_innodb.* TO

'kari'@'localhost' IDENTIFIED BY 'Abc123'

? The clause of the statement:

-permissions to be granted

-permission level:

-Global: *. *

-Database:. *

-Table:.

-Storage routine:.

-account to be granted permissions

-optional password

5) permission level / table content and permissions

User contains a record for each account known to the server

Db database-specific permissions

Tables_priv table-specific permissions

Columns_priv column-specific permissions

Permissions for procs_priv stored procedures and functions

6) revoke account permissions

? Use the REVOKE statement to revoke specific SQL statement permissions:

REVOKE DELETE, INSERT, UPDATE ON world_innodb.*

FROM 'Amon'@'localhost'

? Revoke permissions to grant permissions to other users:

REVOKE GRANT OPTION ON world_innodb.*

FROM 'Jan'@'localhost'

? Revoke all permissions (including authorization to others):

REVOKE ALL PRIVILEGES, GRANT OPTION

FROM 'Sasha'@'localhost'

? Use the SHOW GRANTS statement to determine whether to use the SHOW GRANTS statement before issuing the REVOKE

Revoke the permission, and then reconfirm the result.

7) disable client access control

To instruct the server not to read the authorization table and disable access control, use the

-- skip-grant-tables option.

? Each connection was successful:

-you can provide any user name and any password, and you can connect from any host.

-this option disables the entire permission system.

-the connected user has virtually all permissions.

? Block client connections:

-use the-- skip-networking option to block network access and allow access only on local sockets, named pipes, or shared memory.

-use the-- socket option to start the server on a non-standard socket to prevent local applications or users from casually accessing.

8) Resource restrictions

By setting the global variable MAX_USER_CONNECTIONS to non

A zero value that limits the use of server resources.

-this will limit the number of simultaneous connections to any one account, but will not limit customers

The operation that the household computer can perform after being connected.

? Limit the following server resources for a single account:

-MAX_QUERIES_PER_HOUR: the number of queries an account can issue per hour

-MAX_UPDATES_PER_HOUR: the number of updates an account can send per hour

-MAX_CONNECTIONS_PER_HOUR: the number of times an account can connect to the server per hour

-MAX_USER_CONNECTIONS: the number of simultaneous connections allowed

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.

Views: 253

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report