mysql 用户表结构

MySQL 用户表结构

基础概念

MySQL 用户表通常用于存储数据库用户的认证信息，包括用户名、密码、权限等。这个表是 MySQL 系统权限管理的基础。

类型

MySQL 用户表主要包含以下几种类型的用户：

普通用户：具有有限的权限，只能访问特定的数据库或表。
管理员用户：具有最高权限，可以管理所有数据库和用户。
系统用户：由 MySQL 系统自动生成，用于内部操作。

应用场景

用户表广泛应用于各种需要数据库访问控制的场景，如：

Web 应用：确保只有授权用户才能访问数据库。
企业应用：保护敏感数据，防止未授权访问。
云服务：在云环境中管理多个用户的数据库访问权限。

用户表结构示例

MySQL 用户表通常存储在 mysql 数据库中的 user 表。以下是一个简化的用户表结构示例：

CREATE TABLE user (
    Host char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
    User char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
    Password char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    Select_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Insert_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Update_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Delete_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Create_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Drop_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Reload_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Shutdown_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Process_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    File_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Grant_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    References_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Index_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Alter_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Show_db_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Super_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Create_tmp_table_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Lock_tables_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Execute_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Repl_slave_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Repl_client_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Create_view_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Create_routine_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Alter_routine_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Create_user_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Event_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    Trigger_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    ssl_type enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
    ssl_cipher char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    x509_issuer char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    x509_subject char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    max_questions int(11) unsigned NOT NULL DEFAULT '0',
    max_updates int(11) unsigned NOT NULL DEFAULT '0',
    max_connections int(11) unsigned NOT NULL DEFAULT '0',
    max_user_connections int(11) unsigned NOT NULL DEFAULT '0',
    plugin char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
    authentication_string text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
    password_expired enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    password_last_changed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    password_lifetime tinyint(4) unsigned NOT NULL DEFAULT '0',
    account_locked enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
    PRIMARY KEY (Host,User)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;