MySQL nineth article ~mysql users and permissions

Introduction: Let's talk about MySQL users and permissions things

A description:

The MySQL user system consists of several parts

1 Host (Access IP limit) 2 user (user) 3 password (password) 4 privileges (permission) 5 dbname (Library) 6 TableName (table)

The MySQL authorization form relates to the table, respectively user,tables_priv,columns_priv,db

1) User table

The user table lists the users and their passwords that can connect to the server, and it specifies what global (superuser) permissions they have. Any permissions that are enabled on the user table are global permissions and apply to all databases. For example, if you have delete permission enabled, the users listed here can delete records from any table, so think carefully before you do so.

2) DB table
The DB table lists the databases, and the user has permission to access them. The permissions specified here apply to all tables in a database.

3) Tables_priv table

The Tables_priv table specifies table-level permissions, and one of the permissions specified here applies to all columns of a table.

4) Columns_priv Table
The COLUMNS_PRIV table specifies column-level permissions. The permissions specified here apply to a specific column of a table.

Two related user rights:

1 copy Account Grant REPLICATION slave,replication Client

2 Development Program Account (normal) grant Select,insert,update,delete on dbname.* to + only contains additions and deletions check the permissions

Development Program Account (Advanced) Grant Select,insert,update,delete,create,create routine,execute,create view,show View,alter,tigger on Dbname.* to

1 Stored Procedure permissions issue: The user who eventually executes the stored procedure is also the stored procedure definition to have the various permissions associated with the stored procedure definition statement. The DBA must be created with a Development account when executing a stored procedure, or even if the Development Account has permission to execute the stored procedure, it cannot execute

2 View permissions issues: View definition internal user and host restrictions to be especially careful, you may not be able to access the view (even if you have show view permissions)

30 million do not grant research and development drop permission (to back up before deleting)

3 DBA Remote Operations account (in use)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, INDEX, ALTER, SHOW DATABASES, SUPER, TRIGGER on * * to ' OPDBA ‘@‘%‘ ;

The following are the permissions explained

1 SUPER Execute KILL command

2 select,insert,update,delete DML permissions

3 Create drop creation Delete permission

4 Process View Progress permissions (for troubleshooting issues)

5 ALTER performs DDL operations

6 TRIGGER Trigger permissions (for Pt-osc to perform DDL operations)

7 Index Permissions

The combination of the above permissions is fully sufficient for all my work in my day job, you can look at the choice

4 MHA Users

Recommended all privileges

5 Monitoring Users (Lepus)

Grant SELECT, PROCESS, Super,replication CLIENT

6 Inception users

We recommend the same as the development of ordinary accounts

Three related management

1 Creating a user

1 password= ' Date +%s | Sha256sum | Base64 | Head-c 12 ' Generate 12-bit password

2 grant permission to dbname.* to ' pro_user ' @ ' host ' identified is recommended for library level restrictions (special handling of cross-Library queries)

2 Viewing authorization statements

1 View All Users

SELECT DISTINCT CONCAT (' User: ', user, ' @ ', host, '; ') as query from Mysql.user;

2 View the appropriate authorization statement

Show grants for ' user ' @ ' host '

3 Two authorizations to users (Add new permissions, add new host, etc.)

1 Viewing authorization statements

2 Direct authorization (does not change the password, this is a small trick it)

Add new host for example

GRANT USAGE on *. lepus_user ' @ ' 1.1 ' identified by PASSWORD ' *2470c0c06dee42fd1618bb99005adca2ec9d1e19 '

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on ' Lepus '. * to ' lepus_user ' @ ' localhost '

Authorized

GRANT USAGE on *. lepus_user ' @ ' 1.2 ' identified by PASSWORD ' *2470c0c06dee42fd16c9d1e19 '

Can

4 Deleting a user

Drop user ' user ' @ ' host ' (be sure to write it all)

5 return permission

Revoke insert on lepus.* from ' lepus_user ' @ ' localhost ';


6 do not forget to finish the operation.

Flush privileges;

Four considerations

1 for database user management must begin to standardize, including naming, length, permissions and so on

2 Special attention to Super privilege, with Drop,alter user's collection and management

3 Direct Update Delete operation directly to the Mysql.user table is not recommended

This is my insight into MySQL user management

MySQL nineth article ~mysql users and Permissions