Mysql user management
From wikinotes
Documentation
official docs https://dev.mysql.com/doc/refman/8.0/en/ root password docs https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html revoke docs https://dev.mysql.com/doc/refman/8.0/en/revoke.html grant docs https://dev.mysql.com/doc/refman/8.0/en/grant.html
Root Password
mysql-8.0
service mysql-server stop mysqld_safe --skip-networking mysql# mysql-8.0 ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password'; FLUSH PRIVILEGES;mysql-6.0
service mysql-server stop mysqld_safe --skip-grant-tables --skip-networking mysql# mysql-6.0 UPDATE mysql.USER SET authentication_string=PASSWORD('yourawesomepass') WHERE User='root'; FLUSH PRIVILEGES;verify password
sudo pkill mysqld service mysql-server start mysql -u root -pyourawesomepass SET PASSWORD=PASSWORD('yourawesomepass'); # your password is now updated
User Management
NOTE:
When users are created as
'USER'@'%'
:
- logins from the localhost, providing a password will be rejected
- logins from anywhere else require a password
SELECT * FROM mysql.user; # show all users CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; # create user (allowed login only on localhost) CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; # create user (allowed login anywhere) ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';" # change password FLUSH PRIVILEGES; # reload passwords, user permissions
Permissions
MySQL permissions have the following concepts:
- An account is a unique combination of a user, and hostname/ip/wildcard
- Authorization manages mysql facilities you can do (ex. shutdown)
- AccessControl manages what you can query/change
resetting permissions
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';" > resetpass.sql /usr/local/libexec/mysqld --init-file=resetpass.sql sudo pkill mysqldsetting permissions
SHOW GRANTS FOR 'will'@'localhost' # See User permissions REVOKE ALL ON *.* FROM 'user'@'localhost'; # Remove User Permissions CREATE USER will IDENTIFIED BY <password> # Create User CREATE USER will@localhost IDENTIFIED BY <password> # Create User only allowed to access from localhost UPDATE will # Update password, enccrypting password in table SET password = PASSWORD('mypassword'); FLUSH PRIVILEGES; GRANT ALL ON *.* TO 'will'@'localhost' WITH GRANT OPTION; # SuperUser privileges to will GRANT ALL ON table.* TO 'will'@'%' WITH GRANT OPTION; # grant privileges to 'will' on table