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

This article mainly introduces MySQL users and rights management, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand MySQL users and rights management bar.

MySQL user and Rights Management

The default administrator user for MySQL is root, but this user is too privileged to be used only when managing the database. For the security of MySQL, in practical applications, user rights should be minimized and only the permissions used should be assigned.

1 mysql Authorization form

MySQL CVM controls users' access to the database through the MySQL permission table, which 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 permission table: records the user account information allowed to connect to the CVM, and the permissions in it are at the global level. Db permission table: record the operation permissions of each account on each database. Table_priv permission table: record the operation permissions at the data table level. One of the permissions specified here applies to all columns of a table. Columns_priv permission table: record data column-level operation permissions; the permissions specified here apply to specific columns of a table; host permission table: cooperate with db permission table to provide more detailed control over database-level operation permissions on a given host. This permission table is not affected by GRANT and REVOKE statements. 2 user and password management 2.1 create user command: CREATE USER 'username'@'host' IDENTIFIED BY' password' After the creation of the new user, we are temporarily unable to log in, because we have not assigned the corresponding permissions to this user. Note: username: the user name you will create host: specify the hosts on which the user can log in. The local user uses localhost. If you want the user to log in from any remote host, you can use the wildcard% password: the user's login password, which can be empty. If it is empty, the user can log in to the cloud server without a password, whether to authorize or revoke authorization, to specify the host of the response (that is, the content after the @ symbol), because the above passing commands are actually operating the user table in the mysql database. For example, CREATE USER 'dog'@'localhost' IDENTIFIED BY' 123456 password set create USER 'pig'@'192.168.1.101' IDENDIFIED BY' 123456 password, create USER 'pig'@'192.168.1.%' IDENTIFIED BY' 123456 password, create USER 'pig'@'%' IDENTIFIED BY'; CREATE USER 'pig'@'%';2.2 password set root password:

Set password: password1

Mysqladmin-uroot-password password1 modify the root password:

Changed from password1 to password2

Mysqladmin-uroot-p password1 password password2 administrator sets other user passwords SET PASSWORD FOR 'username'@'host' = PASSWORD (' newpassword'); sets the current login user (self) password: SET PASSWORD = PASSWORD ("newpassword"); 2.3 views current user SELECT USER (); 2.4 deletes user DROP USER 'username'@'host';2.5 to view all mysql users

Check all users and permissions of mysql, encrypt password, and time when password was last changed.

MYSQL-5.6select host,user,password from mysql.user;MYSQL-5.7SELECT user,host,authentication_string,password_last_changed FROM mysql.user;2.6 data Table structure: DESCRIBE table1;3 permissions 3.1Authorization GRANTGRANT Syntax: GRANT privileges (columns) ON whatTO user IDENTIFIED BY "password" WITH GRANT OPTION permission list: CREATE: create databases and tables. INSERT: insert; insert a new row into the table. DELETE: deletes existing records in the table. DROP: discard (delete) databases and tables. ALTER: modify tables and indexes. INDEX: create or discard indexes. REFERENCE: not used. SELECT: retrieve the records in the table. UPDATE: modify existing table records. FILE: read or write files on the cloud server. PROCESS: view the thread information executed in the CVM or kill the thread. RELOAD: reload the authorization table or empty the log, host cache, or table cache. SHUTDOWN: shut down the CVM. 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.

After the grant, revoke user permission, the user will not take effect until the user reconnects to the MySQL database. Assign permissions only: for use with CREATE USER; GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host'

Description:

All: the operation permissions to be granted to the user, such as SELECT,INSERT,UPDATE, etc., if you want to grant the permissions, use ALL;databasename: database name; tablename: table name. If you want to grant the user the corresponding operation rights to all databases and tables, you can use it, such as. * username: user; host:IP scope; create a new account and assign permissions: grantGRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host' IDENTIFIED BY' password'

Description:

Others are same as above; password: password

Example:

GRANT ALL ON *. * TO 'pig'@'%';GRANT ALL ON maindataplus.* TO' pig'@'%';GRANT SELECT,INSERT ON test.user TO 'pig'@'%'; creates a new account and grants users permission to manage other users GRANT PRIVILEGES ON databasename.tablename TO' username'@'localhost' WITH GRANT OPTION

Description:

Others are the same as above; WITH GRANT OPTION: Grant users the right to manage other users; generally configured users in WITH GRANT OPTION are equipped with host and localhost, that is, they can only log in locally; common operations

Grant query, insert, update, delete MySQL data table structure permissions.

Grant select,insert,update,delete on databasename.tablename to 'username'@'host'

Grant creates and uninstalls permissions for MySQL data tables.

Grant create,drop on databasename.tablename to 'username'@'host'

Grant acts on the columns in the table:

Grant select (id, se, rank) on databasename.tablename to 'username'@'host'

Grant manipulates MySQL foreign key permissions.

Grant references on databasename.tablename to 'username'@'host'

Grant manipulates MySQL temporary table permissions.

Grant create temporary tables on databasename.tablename to 'username'@'host'

Grant manipulates MySQL index permissions.

Grant index on databasename.tablename to 'username'@'host'

Grant manipulates MySQL view and view view source code permissions.

Grant create view on databasename.tablename to 'username'@'host';grant show view on databasename.tablename to' username'@'host'

Grant manipulates MySQL stored procedures and function permissions.

Grant create routine on databasename.tablename to 'username'@'host';grant alter routine on databasename.tablename to' username'@'host';grant execute on databasename.tablename to 'username'@'host';grant execute on procedure testdb.pr_add to' dba'@'localhost';grant execute on function testdb.fn_add to 'dba'@'localhost'

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

Grant all privileges on databasename.* to dba@'localhost';grant Advanced DBA manages permissions for all databases in MySQL. If the grant all on *. * to dba@'localhost' keyword privileges is omitted, the permission needs to be refreshed later. 3.2 Refresh permissions

Be sure to refresh the service or restart the service after modifying the permissions:

FLUSH PRIVILEGES;3.3 View user Rights SHOW GRANTS [FOR 'username'@'host']

View your own permissions by default, and add for to view the permissions of the specified user

3.4 revoke permissions: revoke

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:

REVOKE ALL PRIVILEGES ON databasename.tablename FROM 'username'@'host'

Description:

Privilege, databasename, tablename: same authorization part

Note:

If you authorize the user 'pig'@'%'' like this (or something similar): GRANT SELECT ON test.user TO 'pig'@'%', is using REVOKE SELECT ON. The FROM 'pig'@'%'; command does not undo the user's SELECT operation on the user table in the test database.

On the contrary, if you are authorized to use GRANT SELECT ON. The TO 'pig'@'%'; REVOKE SELECT ON test.user FROM' pig'@'%'; command cannot revoke the user's Select permissions on the user table in the test database.

The specific information can be viewed with the command SHOW GRANTS FOR 'pig'@'%';.

4 Abstract 4.1 grant and revoke can control access to the entire cloud server at several levels, using grant ALL and revoke ALL entire databases, using on database.* specific tables, using on database.table specific columns specific stored procedures 4.2 the meaning of the host column in the user table%: match all hosts localhost: localhost will not be resolved to IP address, directly connect 127.0.0.1 through UNIXsocket: connect through TCP/IP protocol And can only be accessed locally :: 1: 1: 1: it is compatible with ipv6. 127.0.0.14.3 with ipv4 can also delete useless users through security policy.

It is recommended that you run the mysql_secure_installation command in shell just after installing the mysqld service.

Mysql_secure_installation

After reading the above about MySQL users and rights management, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.