MySQL Tutorial
MySQL Installation and Configuration
MySQL Database Operations
Database Design
MySQL Data Types
MySQL Storage Engines
MySQL Basic Operations of Tables
MySQL Constraints
MySQL Operators
MySQL Function
MySQL Manipulate Table Data
MySQL View
MySQL Indexes
MySQL Stored Procedure
MySQL Trigger
MySQL Transactions
MySQL Character Set
MySQL User Management
MySQL Database Backup and Recovery
MySQL Log
MySQL Performance Optimization
MySQL uses privilege tables in the mysql system database to manage the permissions of the users. The main privilege tables include:
user: Stores global privileges for each account, such as CREATE USER, CREATE TABLE, CREATE DATABASE, SELECT, INSERT, UPDATE, DELETE, etc.db: Stores database-level privileges for each account.tables_priv: Stores table-level privileges for each account.columns_priv: Stores column-level privileges for each account.procs_priv: Stores stored procedure and function privileges for each account.Here is a tutorial on how to view and manage user privileges using these tables:
Step 1: Connect to MySQL.
Connect to your MySQL server using the MySQL command-line client or through any other MySQL interface you prefer. Here is a basic command to connect to MySQL from the command line:
mysql -u root -p
Step 2: Select the mysql database.
Once you're logged in, select the mysql database:
USE mysql;
Step 3: Query the privilege tables.
You can then use the SELECT statement to view the contents of the privilege tables. For example, to see the global privileges for each account, you would use:
SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv FROM user;
This command will return a table that shows the SELECT, INSERT, UPDATE, and DELETE privileges for each account on any database and table.
Step 4: Modify the privileges.
If you have the necessary permissions, you can modify the privileges directly using the UPDATE statement. For example, to revoke the SELECT privilege from a user called myuser who connects from localhost, you would use:
UPDATE user SET Select_priv='N' WHERE User='myuser' AND Host='localhost';
Step 5: Flush the privileges.
Any time you make changes to the privilege tables directly, you must tell MySQL to reload the privileges:
FLUSH PRIVILEGES;
Step 6: Exit MySQL.
When you're done, you can exit the MySQL interface by typing exit at the MySQL prompt and then pressing Enter.
Note: Directly modifying the privilege tables can be risky and is generally discouraged. It's usually better to use the GRANT and REVOKE statements to manage privileges, as these commands automatically take care of all the details, such as updating the privilege tables and flushing the privileges.
Granting and revoking privileges with MySQL tables:
mysql schema, such as mysql.user, mysql.db, mysql.tables_priv, etc.GRANT SELECT ON mydatabase.* TO 'myuser'@'localhost';
REVOKE SELECT ON mydatabase.* FROM 'myuser'@'localhost';