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

Xiaobian to share with you mysql users and permissions of the example analysis, I believe most people do not know how to share this article for your reference, I hope you read this article after a great harvest, let us go to understand it!

Creation of users

Method 1:

create user lxm;

set password for lxm=PASSWORD('123456');

alter user lxm password expire; Set user password expiration.

select user, host, password from mysql.user; Query account information

Method 2:

Users are silently created when authorized

grant select on test.table1 to lxm@192.168.2.154 identified by '123456';

Method 3:

Modify the user table directly, remember to flush privileges after modification.

insert into mysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject) values ('192.168.2.154','lxm','123456','','','');

update mysql.user set password=password ('lxm ') where user='lxm' and host='%'; modify user password

Deletion of users:

drop user lxm@192.168.2.154, lxm2@192.168.2.154, lxm3@192.168.2.154; multiple users can be deleted at once

Drop user does not automatically terminate connected user sessions.

=========================================================================

permissions

The granularity of permissions in mysql from coarse to fine is global, database, table, column, program. It can be summed up in one sentence: assign a user the right to connect from a machine to access a certain part of the records of a certain column of a certain table under a certain database.

1. Global level

Globally relevant permission information is recorded in the mysql.user table. This global permission does not mean having all permissions, it specifically means having permissions on all database objects of the MYSQL server.

2. Database level

Permission information at the database level is recorded in the mysql.db table.

3. Table Object Level

The authorization information of the table object is recorded in the mysql.tables_priv dictionary table.

4. Column level

Column-level permissions are the most granular in the mysql permission system. Permission information is recorded in the mysql.column_priv table.

grant select (col1) on test.table1 to lxm; Grant user lxm permission to query col1 column in test.table1 table.

grant insert (col1) on test.table1 to lxm; Gives user lxm permission to add values to col1 column in test.table1 table.

5. Procedure

In mysql, procedures refer to two types of objects: procedure and function. For existing programs, you can grant EXECUTE, ALTER ROUTINE, and GRANT permissions. These permissions are recorded in the table mysql.procs_priv.

Query user permissions:

show grants for lxm@'192.168.2.154; Query the permissions user lxm has

show grants; Query all permissions of the current user

Reclaim Permission:

revoke select on test.table1 from lxm@192.168.2.154;

revoke all privileges, grant option from user; revoke all privileges of the user

When changes in permissions take effect:

If you perform changes using commands provided by mysql, such as GRANT, REVOKE, SET PASSWORD, RENAME USER, the permission change takes effect immediately.

If you modify the dictionary table manually, such as INSERT, UPDATE, Delete, you need to restart the mysql service, or manually trigger the authorization table (GRANT TABLES) to reload into memory, that is, flush privileges.

Impact of permission changes on clients:

Permission changes at the table or column granularity take effect the next time the client performs an operation.

Database level permissions take effect when clients switch databases.

Global permissions and password changes that take effect when the client next connects.

On LINUX/UNIX systems, all operations performed using the mysql command-line tool are recorded in a file called.mysql_history. This file is saved by default in the root directory of the current user. You can change the path by modifying the parameter MYSQL_HISTFILE.

Permission Type List:

create user can execute create user, drop user, rename user, revoke all privileges statements

create Create database or table objects * Users with create privileges can only create and view their newly created database or table objects, not delete them.

create view You can create/modify views

select Search

insert Executing insert statements

update allows update operations

delete can execute delete statements

drop can delete tables/views/databases

alter performs alter table operations

index Create or delete an index

create tablespace Create, modify, or delete tablespaces and log file groups

create temporary tables Create temporary tables by executing the create temporary table statement

lock tables Execute lock tables on table objects with select permissions

trigger Allows you to create or delete triggers

create routine Create stored procedures and functions

alter routine modifies or deletes stored procedures and functions

execute allows users to execute stored programs

replication client allows users to connect master/slave in a replication environment

replication slave allows the slave side of the replication environment to read data from the master side

grant option allows granted permissions to be granted by that user to other users

all privileges Grant all privileges except grant option

Usage means no privileges, but you can still log in. This permission is given by default and cannot be reclaimed.

event Allows the use of event objects

file Allows users to read and write files

proxy can be used

Note: All privileges and grant option are special, and cannot be used with other privileges simultaneously when granting or reclaiming them.

max_queries_per_hour Number of query statements executed by users per hour

max_update_per_hour Number of update statements executed by the user per hour

max_connections_per_hour The maximum number of connections a user can make per hour

max_user_connections The number of simultaneous server connections

Other:

At the table level you can grant permissions such as select, insert, update, delete, create, drop, grant option, index, and alter.

execution, file, process, reload, replication client, replication slave, show databases, shutdown, and super These permissions are administrative permissions and can only be granted globally, that is, on *.* the way.

The above is "mysql user and permission example analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.