# mysql.host Table ### Usage The `mysql.host` table contained information about hosts and their related privileges. When determining permissions, if a matching record in the [mysql.db table](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-db-table) had a blank host value, the mysql.host table would be examined. This table is not affected by any [GRANT](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) statements, and had to be updated manually. This table is no longer used. See [privileges](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) for a more complete view of the MariaDB privilege system. This table is no longer created. However if the table is created it will be used. ### Table fields The `mysql.host` table contains the following fields: | Field | Type | Null | Key | Default | Description | | ------------------------ | ------------- | ---- | --- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Host | char(60) | NO | PRI | | Host (together with Db makes up the unique identifier for this record. | | Db | char(64) | NO | PRI | | Database (together with Host makes up the unique identifier for this record. | | Select\_priv | enum('N','Y') | NO | | N | Can perform [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) statements. | | Insert\_priv | enum('N','Y') | NO | | N | Can perform [INSERT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert) statements. | | Update\_priv | enum('N','Y') | NO | | N | Can perform [UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update) statements. | | Delete\_priv | enum('N','Y') | NO | | N | Can perform [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete) statements. | | Create\_priv | enum('N','Y') | NO | | N | Can [CREATE TABLEs](https://mariadb.com/docs/server/server-usage/tables/create-table). | | Drop\_priv | enum('N','Y') | NO | | N | Can [DROP DATABASEs](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-database) or [DROP TABLEs](https://mariadb.com/docs/server/server-usage/tables/drop-table). | | Grant\_priv | enum('N','Y') | NO | | N | User can [grant](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) privileges they possess. | | References\_priv | enum('N','Y') | NO | | N | Unused | | Index\_priv | enum('N','Y') | NO | | N | Can create an index on a table using the [CREATE INDEX](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-index) statement. Without the INDEX privilege, user can still create indexes when creating a table using the [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) statement if the user has have the CREATE privilege, and user can create indexes using the [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table) statement if they have the ALTER privilege. | | Alter\_priv | enum('N','Y') | NO | | N | Can perform [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table) statements. | | Create\_tmp\_table\_priv | enum('N','Y') | NO | | N | Can create temporary tables with the [CREATE TEMPORARY TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) statement. | | Lock\_tables\_priv | enum('N','Y') | NO | | N | Acquire explicit locks using the [LOCK TABLES](https://mariadb.com/docs/server/reference/sql-statements/transactions/lock-tables) statement; user also needs to have the SELECT privilege on a table in order to lock it. | | Create\_view\_priv | enum('N','Y') | NO | | N | Can create a view using the [CREATE\_VIEW](https://mariadb.com/docs/server/server-usage/views/create-view) statement. | | Show\_view\_priv | enum('N','Y') | NO | | N | Can show the [CREATE VIEW](https://mariadb.com/docs/server/server-usage/views/create-view) statement to create a view using the [SHOW CREATE VIEW](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-view) statement. | | Create\_routine\_priv | enum('N','Y') | NO | | N | Can create stored programs using the [CREATE PROCEDURE](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/create-procedure) and [CREATE FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-function) statements. | | Alter\_routine\_priv | enum('N','Y') | NO | | N | Can change the characteristics of a stored function using the [ALTER FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-function) statement. | | Execute\_priv | enum('N','Y') | NO | | N | Can execute [stored procedure](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures) or functions. | | Trigger\_priv | enum('N','Y') | NO | | N | Can execute [triggers](https://mariadb.com/docs/server/server-usage/triggers-events/triggers) associated with tables the user updates, execute the [CREATE TRIGGER](https://mariadb.com/docs/server/server-usage/triggers-events/triggers/create-trigger) and [DROP TRIGGER](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-trigger) statements. | ### How to Create If you need the functionality to only allow access to your database from a given set of hosts, you can create the host table with the following command: ```sql CREATE TABLE IF NOT EXISTS mysql.host (HOST CHAR(60) BINARY DEFAULT '' NOT NULL, Db CHAR(64) BINARY DEFAULT '' NOT NULL, Select_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY /*Host*/ (HOST,Db) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin COMMENT='Host privileges; Merged with database privileges'; ``` *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}