Mysql user management

From wikinotes
Revision as of 19:59, 24 September 2022 by Will (talk | contribs) (→‎Permissions)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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 mysqld

setting 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