Apply OS: Windows, macOS, Linux

Apply Navicat Product: Navicat for MySQL, Navicat for PostgreSQL, Navicat for MariaDB, Navicat Premium

Apply Navicat Version No.: All

 

MySQL & MariaDB

Information about user privileges is stored in the user, db, host, tables_priv and columns_priv tables in the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables when it starts up.

MySQL access control would involve two stages:

1. The server checks whether your desktop (host address or IP address) is allowed to connect.
2. Assuming you can connect, the server checks each request you issue to see whether you have sufficient privileges to perform it. For example, Create table privilege, Drop table privilege or Alter table privilege.

MySQL server uses the User, Db, and Host tables in the Mysql database during both stages of access control.

The server uses the five grant tables like this:

The mysql.user table scope fields determine whether to allow or reject incoming connections. For allowed connections, any privileges granted in the mysql.user table indicate the user's global (superuser) privileges. These privileges apply to all databases on the server.

The mysql.db and mysql.host tables are used together:

The mysql.db table scope fields determine which users can access which databases from which hosts. The privilege fields determine which operations are allowed.

The mysql.host table is used as an extension of the mysql.db table when you want a given mysql.db table entry to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the mysql.host value empty in the user's mysql.db table entry, then populate the mysql.host table with an entry for each of those hosts.

The mysql.tables_priv and mysql.columns_priv tables are similar to the mysql.db table, but they apply at the table and column levels rather than at the database level.

 

PostgreSQL

Information about user privileges is stored in the pg_shadow table in a schema call pg_catalog. The PostgreSQL server reads the contents of this table when it starts up.

PostgreSQL access control would involve two stages:

1. The server checks whether your desktop (host address or IP address) is allowed to connect.
2. Assuming you can connect, the server checks each request you issue to see whether you have sufficient privileges to perform it. For example, Create table privilege, Drop table privilege or Alter table privilege.