mysql-grant-and-revoke-db-privilege.sql

#author          : Agung Pambudi
#email           : mail@agungpambudi.com
#version         : 0.1
#==============================================================================
#                                   _         _ _
# ___ ___ _ _ ___ ___ ___ ___ _____| |_ _ _ _| |_|  ___ ___ _____
#| .'| . | | |   | . | . | .'|     | . | | | . | |_|  _| . |     |
#|__,|_  |___|_|_|_  |  _|__,|_|_|_|___|___|___|_|_|___|___|_|_|_|
#    |___|       |___|_|


# ERROR 1130 (HY000): Host 192.162.3.4 is not allowed to connect to this MySQL server

GRANT SELECT, UPDATE, INSERT ON database.* TO 'root'@'193.169.12.139' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT SELECT, UPDATE, INSERT ON database.* TO 'root'@'193.169.12.139' IDENTIFIED BY 'password' WITH GRANT OPTION;


GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.81' IDENTIFIED BY 'passwordnya' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.222' IDENTIFIED BY 'passwordnya' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.221' IDENTIFIED BY 'passwordnya' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.103' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.103' IDENTIFIED BY 'passwordnya' WITH GRANT OPTION;


REVOKE ALL ON *.* FROM 'root'@'193.169.12.33';
DROP USER 'root'@'193.169.12.222';
FLUSH PRIVILEGES;


GRANT SELECT ON database.* TO 'user'@'193.168.312.162' IDENTIFIED BY 'password';
GRANT SELECT ON database.* TO 'user'@'193.168.312.132' IDENTIFIED BY 'password';
GRANT SELECT ON database.* TO 'user'@'193.168.312.82' IDENTIFIED BY 'password';


REVOKE ALL ON *.* FROM 'user'@'192.168.%';
DROP USER 'user'@'192.168.%';

REVOKE ALL ON *.* FROM 'root'@'193.168.312.63';
DROP USER 'root'@'193.168.312.63';
FLUSH PRIVILEGES;


GRANT SELECT PRIVILEGES ON database.* TO 'root'@'193.168.312.63' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON database.* TO 'root'@'193.168.312.63' IDENTIFIED BY 'password' WITH GRANT OPTION;


GRANT INSERT ON database.* TO 'root'@'193.168.312.63' IDENTIFIED BY 'password';
GRANT SELECT ON database.* TO 'root'@'193.168.312.63' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'root'@'193.168.312.63' IDENTIFIED BY 'password' WITH GRANT OPTION;


GRANT CREATE, DROP, INSERT ON database.* TO 'user'@'193.168.312.76';
GRANT CREATE, DROP ON database.* TO 'user'@'193.168.312.43';
GRANT CREATE, DROP ON database.* TO 'user'@'193.168.312.200';
GRANT CREATE, DROP ON database.* TO 'user'@'193.168.312.204';

SHOW GRANTS FOR 'user'@'193.168.312.76';

GRANT INSERT ON database.* TO 'user'@'193.168.312.76';
GRANT INSERT ON database.* TO 'user'@'193.168.312.43';
GRANT INSERT ON database.* TO 'user'@'193.168.312.200';
GRANT INSERT ON database.* TO 'user'@'193.168.312.204';


CREATE USER 'userdb'@'%' IDENTIFIED BY '123';
GRANT ALL PRIVILEGES ON database.tablename TO 'userdb'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON database.tablename_det TO 'userdb'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;

REVOKE ALL ON *.* FROM 'root'@'193.169.12.167';
DROP USER 'root'@'193.169.12.167';

REVOKE ALL ON *.* FROM 'root'@'193.169.12.22';
DROP USER 'root'@'193.169.12.22';

REVOKE ALL ON *.* FROM 'root'@'193.169.12.133';
DROP USER 'root'@'193.169.12.133';

REVOKE ALL ON *.* FROM 'root'@'193.169.12.224';
DROP USER 'root'@'193.169.12.224';
FLUSH PRIVILEGES;

REVOKE ALL ON *.* FROM 'userdb'@'%';
DROP USER 'userdb'@'%';

REVOKE ALL ON *.* FROM 'root'@'%';
DROP USER 'root'@'%';
FLUSH PRIVILEGES;


GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.81' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.37' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.4' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.68' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.222' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.221' IDENTIFIED BY 'password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.167' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.177' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.78' IDENTIFIED BY 'password' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.224' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.133' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.139' IDENTIFIED BY 'password' WITH GRANT OPTION;

REVOKE ALL ON *.* FROM 'root'@'193.169.12.139';
DROP USER 'root'@'193.169.12.139';

GRANT ALL PRIVILEGES ON database.* TO 'root'@'193.169.12.212' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON database.* TO 'root'@'193.169.12.27' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT SELECT PRIVILEGES ON database.* TO 'root'@'193.169.12.27' IDENTIFIED BY 'password' WITH GRANT OPTION;

REVOKE ALL ON *.* FROM 'root'@'193.169.12.%';
DROP USER 'root'@'193.169.12.%';
FLUSH PRIVILEGES;

REVOKE ALL ON *.* FROM 'root'@'193.169.12.94';
DROP USER 'root'@'193.169.12.94';
FLUSH PRIVILEGES;

REVOKE ALL ON *.* FROM 'root'@'193.169.12.233';
DROP USER 'root'@'193.169.12.233';
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.89' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON database.* TO 'root'@'193.169.12.139' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.139' IDENTIFIED BY 'password' WITH GRANT OPTION;


REVOKE ALL ON *.* FROM 'userdb'@'%';
DROP USER 'userdb'@'%';
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON userdb.* TO 'userdb'@'193.169.12.%' IDENTIFIED BY 'userdb123' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'193.169.12.221' IDENTIFIED BY 'password123' WITH GRANT OPTION;


REVOKE ALL ON *.* FROM 'user'@'193.168.312.132';
DROP USER 'user'@'193.168.312.132';

REVOKE ALL ON *.* FROM 'user'@'193.168.312.162';
DROP USER 'user'@'193.168.312.162';
REVOKE ALL ON *.* FROM 'user'@'193.168.312.67';
DROP USER 'user'@'193.168.312.67';

REVOKE ALL ON *.* FROM 'user'@'193.168.312.82';
DROP USER 'user'@'193.168.312.82';
FLUSH PRIVILEGES;


SELECT * FROM mysql.user;
GRANT SELECT,INSERT ON database.* TO user@'193.168.312.%' IDENTIFIED BY 'password123';
FLUSH PRIVILEGES;

GRANT CREATE TEMPORARY TABLES ON database.* TO user@'193.168.312.%' IDENTIFIED BY 'password123';
GRANT INSERT IGNORE INTO ON database.* TO user@'193.168.312.%' IDENTIFIED BY 'password123';
GRANT CREATE TEMPORARY TABLES, DROP, SELECT,INSERT,UPDATE,  ON database.* TO user@'193.168.312.%' IDENTIFIED BY 'password123';

SHOW GRANTS FOR user@'193.168.312.%';
GRANT ALL PRIVILEGES ON *.* TO user@'193.168.312.76' IDENTIFIED BY 'password123';


GRANT CREATE TEMPORARY TABLES ON database.* TO user@'193.169.12.137' IDENTIFIED BY 'password123';
GRANT CREATE TEMPORARY TABLES ON database.* TO user@'193.169.12.233' IDENTIFIED BY 'password123';
GRANT CREATE TEMPORARY TABLES ON database.* TO user@'193.169.12.223' IDENTIFIED BY 'password123';



GRANT CREATE TEMPORARY TABLES ON database.* TO indomaret@'193.169.12.22' IDENTIFIED BY 'password';
GRANT CREATE TEMPORARY TABLES ON database.* TO indomaret@'193.169.12.22' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

GRANT CREATE TEMPORARY TABLES ON database.* TO user@'193.169.12.223' IDENTIFIED BY 'password123';
GRANT CREATE TEMPORARY TABLES ON database.* TO indomaret@'193.169.12.161' IDENTIFIED BY 'password';




GRANT CREATE TEMPORARY TABLES ON database.* TO user@'193.169.12.128' IDENTIFIED BY 'password123';
GRANT CREATE TEMPORARY TABLES ON database.* TO user@'193.169.12.192' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT ON *.* TO 'root'@'193.169.12.224' IDENTIFIED BY 'passwordnya' WITH GRANT OPTION;


GRANT ALL PRIVILEGES ON database.* TO user@'193.168.312.204' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON database.* TO user@'193.168.312.43' IDENTIFIED BY 'password123';


GRANT ALL PRIVILEGES ON database.* TO indomaret@'193.168.312.204' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO indomaret@'193.168.312.43' IDENTIFIED BY 'password';



# other
SELECT * FROM mysql.user;

REVOKE ALL ON *.* FROM 'agungpambudi'@'localhost';
SHOW GRANTS FOR 'agungpambudi'@'localhost';

DROP USER 'agungpambudi'@'localhost';
FLUSH PRIVILEGES;

DROP USER 'userdb'@'localhost';
CREATE USER 'userdb'@'localhost' IDENTIFIED BY '12345789';
SET PASSWORD FOR 'userdb'@'localhost' = PASSWORD('12345789');


ALTER USER 'userdb'@'localhost' IDENTIFIED BY PASSWORD('12345789');

UPDATE mysql.user SET Password=PASSWORD('12345789') WHERE User='userdb';
FLUSH PRIVILEGES;