Skip to content

Instantly share code, notes, and snippets.

@Sakib37
Last active April 9, 2019 09:42
Show Gist options
  • Save Sakib37/ffc78c0ee78d46ac2588b256b1459159 to your computer and use it in GitHub Desktop.
Save Sakib37/ffc78c0ee78d46ac2588b256b1459159 to your computer and use it in GitHub Desktop.
Frequently used Mysql commands
List user:
SELECT user, host FROM mysql.user;
Delete user:
DROP USER 'USER'@'host';
Create user:
CREATE USER 'USER'@'10.%' IDENTIFIED BY 'PASSWORD' ;
GRANT SELECT ON *.* TO 'USER'@'host' ;
flush privileges;
Find user:
SELECT user, host FROM mysql.user where User like 'USER%' ;
Create Database:
CREATE DATABASE testdb;
Grant wildcard database access :
GRANT SELECT ON `db_name%`.* TO 'USER'@'host' ;
List privilege for user:
SHOW GRANTS FOR 'USER' ;
List columns from table:
desc `TABLE_NAME` ;
List privilege for current user:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER()
Revoke privilege from user:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER'@'host' ;
REVOKE SELECT ON *.* FROM 'USER'@'host' ;
Permission:
User create permission:
GRANT CREATE, DROP USER ON *.* TO 'vault'@'host' WITH GRANT OPTION ;
Permission for objects(database, tables etc)
GRANT SELECT,INSERT,UPDATE,DELETE, DROP ON `db`.* TO 'user'@'host';
Permission to specific columns on database tables:
GRANT SELECT,INSERT,UPDATE,DELETE, DROP (COL-1, COL-2, COL-3) ON `DATABASE`.`TABLE` TO 'user'@'host';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment