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

Authorization of MYSQL user

Why authorize it?

The default user of MYSQL is root. Because the authority of root is too large, and for security reasons, it is usually only used when managing the database. If you want to connect to a MYSQL database in the project, it is recommended to create a new user with less permissions.

Function: limit which host the specified user can log in, which database to access, and what permissions the specified user can have on a database.

Create a user (mysql is case-insensitive) command format: create user "username" @ "host" identified by "password" -- username the user name you will create-- host specifies the host on which the user can log in, and if it is a local user, you can use localhost. If you want the user to log in to any remote host, you can use the wildcard "%"-- password the user's password, which can be empty. Empty means that users can log in without a password to write the following examples: create user "china" @ "192.168.8.128" identified by "123456" Create user "china" @ "localhost" identified by "123456"; create user "china" @ "%" identified by "123456"; create user "china" @ "%" identified by "" Create user "china" @ "%"

II. Authorization

The format of the command: grant privileges on dataname.tablename to "username" @ "host";-- the permissions of privileges users, such as select insert update, etc. If you want to grant all permissions, use the all-- dataname.tablename library. The table name specifies which database the user has permission to operate on. If you want to grant the user permission to operate on all databases and tables, you can use *. * when logging in on the client: mysql-h "IP"-u "specified user"-p "password" grant select,insert on db1.table1 to "china" @ "localhost" Grant all on db1.table1 to "chian" @ "localhost"; Note: the user authorized by the above name does not have the authorization authority, so that the user has the authorization authority. You can add the with grant option example: grant all on db1.table1 to "china" @ "localhost" with grant option.

Setting and changing user passwords

Command format: set password for "username" @ "host" = password ("new password"); this is the unlogged-in user set password = password ("new password")

4. Revoke the user's authority

Command format: revoke privileges on db1.table1 from "username" @ "host"; example: revoke select on *. * from "china" @ "%". Note: after the permission is revoked, the user name will be saved, but if the user is deleted before the permission is revoked, the user's permission will also be deleted.

Delete users

Command format: drop user "username" @ "host"

Appendix:

View the user's permissions: show grants for "username"

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 represents all tables in the test database, and the latter represents all tables in all databases.

The clause "WITH GRANT OPTION" indicates that the user can assign permissions to other users

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.