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 db, tables_priv, columns_priv and procs_priv privilege tables

MySQL Privilege Tables Tutorial

MySQL manages user account information in a database named mysql. This mysql database contains several tables that store user information and privileges. The user table contains global privileges for each user account, and the db, tables_priv, columns_priv, and procs_priv tables contain database-level, table-level, column-level, and routine-level privileges, respectively.

1. The db table:

The db table contains database-level privileges. Each row in the db table matches a user/host combination with a database, and specifies the privileges that the user has for that database. Here is the basic structure:

SHOW COLUMNS FROM mysql.db;

The important fields are User, Host, Db (database name), and the privilege fields (Select_priv, Insert_priv, Update_priv, etc.).

2. The tables_priv table:

The tables_priv table contains table-level privileges. Each row in the tables_priv table matches a user/host combination with a specific table, and specifies the privileges that the user has for that table. Here is the basic structure:

SHOW COLUMNS FROM mysql.tables_priv;

The important fields are User, Host, Db (database name), Table_name, and Table_priv (a set of table-level privileges).

3. The columns_priv table:

The columns_priv table contains column-level privileges. Each row in the columns_priv table matches a user/host combination with a specific table column, and specifies the privileges that the user has for that column. Here is the basic structure:

SHOW COLUMNS FROM mysql.columns_priv;

The important fields are User, Host, Db (database name), Table_name, Column_name, and Column_priv (a set of column-level privileges).

4. The procs_priv table:

The procs_priv table contains routine-level (stored procedures and functions) privileges. Each row in the procs_priv table matches a user/host combination with a specific routine, and specifies the privileges that the user has for that routine. Here is the basic structure:

SHOW COLUMNS FROM mysql.procs_priv;

The important fields are User, Host, Db (database name), Routine_name, Routine_type (whether it's a procedure or a function), and Proc_priv (a set of routine-level privileges).

Important:

  • As a best practice, you should not manually modify these tables. Instead, use the GRANT, REVOKE, CREATE USER, DROP USER, RENAME USER, and SET PASSWORD statements to modify user account information and privileges.
  • These tables are protected by the MySQL server, and are not intended to be directly accessed by users. They are used internally by the server to perform authentication and privilege checking.
  • The privilege tables are stored in the mysql system database, and their structure may change in future MySQL versions. Therefore, you should always use the MySQL privilege system to manipulate user accounts and privileges.
  • The FLUSH PRIVILEGES statement tells the server to reload the grant tables. You should use this statement whenever you manually modify the grant tables or want to make sure that changes to user accounts and privileges take effect immediately.

  1. How to query MySQL privilege tables:

    • Description: You can query privilege tables to inspect the existing privileges for users.
    • Query:
      SELECT * FROM mysql.tables_priv WHERE user='username' AND host='hostname';

  2. Granting permissions using MySQL privilege tables:

    • Description: Granting permissions involves updating the privilege tables to allow specific operations for users.
    • Example:
      GRANT SELECT, INSERT ON database.* TO 'username'@'hostname';
Copyright © devanddep.com | All Rights Reserved