mysql权限用户设置

Drop user 'employA'@'localhost';删除一个用户
新建三个用户
CREATE USER 'employeeA'@'localhost' IDENTIFIED BY '1234';
CREATE USER 'employeeB'@'localhost' IDENTIFIED BY '1234';
CREATE USER 'employerM'@'localhost' IDENTIFIED BY '1234';

新建四张表
CREATE TABLE IF NOT EXISTS t1(
  id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(150) NOT NULL,
  cate  VARCHAR(40)  NOT NULL,
  price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0
  );

CREATE TABLE IF NOT EXISTS t2(
  id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(150) NOT NULL,
  cate  VARCHAR(40)  NOT NULL,
  price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0
  );

CREATE TABLE IF NOT EXISTS t3(
  id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(150) NOT NULL,
  cate  VARCHAR(40)  NOT NULL,
  price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0
  );

CREATE TABLE IF NOT EXISTS t4(
  id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(150) NOT NULL,
  cate  VARCHAR(40)  NOT NULL,
  price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0
  );

use mysql;
由数据库管理员将表t1,t2所有权限给雇员A
grant all privileges on mysql.t1 to employeeA@localhost identified by '1234';
grant all privileges on mysql.t2 to employeeA@localhost identified by '1234';
由数据库管理员将表t3,t4所有权限给雇员B
grant all privileges on mysql.t3 to employeeB@localhost identified by '1234';
grant all privileges on mysql.t4 to employeeB@localhost identified by '1234';

回收权限
REVOKE all privileges on mysql.t2 FROM 'employeeA'@'localhost';(也可以回收WITH GRANT OPTION，只是在查看时还是会显示）

查看用户所拥有的权限
show grants for 'employeeA'@'localhost';
show grants for 'employeeB'@'localhost';
show grants for 'employerM'@'localhost';

审计功能
create database audit;
CREATE TABLE audit.check (id int(11) primary key auto_increment, time timestamp, localname varchar(30), matchname varchar(30));
set global init_connect='insert into audit.check values(connection_id(),now(),user(),current_user())';
flush privileges;

给予数据库管理员读审计内容
grant select on audit.*  to root;
给予角色insert
grant insert on audit.* to 'employeeA'@'localhost';

grant insert on audit.* to 'employeeB'@'localhost';

grant insert on audit.* to 'employerM'@'localhost';