CP8 Managing Users and Privileges

1.Understanding Users and Privileges

Users in MySQL are special objects used for the purpose of:

• Authentication (making sure that a user can acess the MySQL sever)
• Authorization(making sure that a user can interact with objects in database)

2.The root User

A user that can do anything be default.

root@localhost called the initial user.

3.Creating and Using New Users

 4.Grant Tables

MySQL stores both user information and priviledges as records in grant tables.

• mysql.user (User accounts,static global privileges,and other nonprivilege columns)
• mysql.global_grants (dynamic global privileges)
• mysql.db (database_level prvileges)
• mysql.tables_priv (table-level privileges)
• mysql.columns_priv (column-level privileges)
• mysql.procs_priv (stored procedure and function privileges)
• mysql.default_roles (default user roles)
• mysql.role_edges (edges for role subgraphs)
• mysql.password_history(password change history)

5.User Management Commands and Logging

 

6.Modifying and Dropping Users

mysql> create user 'jessie' identified by 'jessie';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> create user 'jessie' identified by 'jessie';
Query OK, 0 rows affected (0.00 sec)

mysql> select plugin,host from mysql.user where user = 'jessie';
+-----------------------+------+
| plugin                | host |
+-----------------------+------+
| mysql_native_password | %    |
+-----------------------+------+
1 row in set (0.00 sec)

mysql> alter user 'jessie' account lock;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@DBAMAXWELL ~]# mysql -u jessie -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'jessie'@'localhost'. Account is locked.

 

mysql> alter user 'jessie' account unlock;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'jessie' account unlock;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> 

 

[root@DBAMAXWELL ~]# mysql -u jessie -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>