Mysql-用户管理

Mysql-用户管理

增加用户

//mysql5.7

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123abc!!!';
flush privileges;

//mysql8
create user 'root'@'%' IDENTIFIED BY '123abc!!!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
flush privileges;

修改密码

SET PASSWORD FOR 'mysql'@'%' = PASSWORD("ShSflow!@#123");
flush privileges;

UPDATE user SET password=PASSWORD("Sino!Sh!Dbwx!123#") WHERE user='root';

授权

GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_dev`.* TO 'dev'@'%';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_sit`.* TO 'app'@'52.82.81.179';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_sit`.* TO 'app'@'69.230.215.225';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_sit`.* TO 'app'@'69.231.162.38';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_sit`.* TO 'app'@'localhost';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_uat`.* TO 'app'@'52.82.81.179';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_uat`.* TO 'app'@'69.230.215.225';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_uat`.* TO 'app'@'69.231.162.38';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `algorithm_uat`.* TO 'app'@'localhost';

GRANT all on `hx-farm_dev`.* TO 'dev'@'%';

GRANT SELECT on `hx-farm_sit`.* TO 'dev'@'%';
FLUSH PRIVILEGES;

GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `gis_manage_sit`.* TO 'app'@'69.231.162.38';
GRANT EXECUTE, SELECT, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `gis_manage_sit`.* TO 'app'@'69.234.245.185';
FLUSH PRIVILEGES;

GRANT SELECT on `gis_manage_sit`.* TO 'dev'@'%';
FLUSH PRIVILEGES;

CREATE DATABASE `hx-schedule_dev` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE `hx-schedule_sit` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

GRANT all on `hx-schedule_dev`.* TO 'dev'@'%';
grant select on `hx-schedule_sit`.* TO 'dev'@'%';
GRANT all on `hx-schedule_sit`.* TO 'app'@'69.231.162.38';
GRANT all on `hx-schedule_sit`.* TO 'app'@'69.234.245.185';
GRANT all on `hx-schedule_sit`.* TO 'app'@'52.83.200.4';

flush privileges;