mysql使用者和許可權管理

標籤：

使用者和許可權管理

Information about account privileges is stored in the user, db, host, tables_priv, columns_priv, and procs_priv tables in the mysql database.  The MySQL server reads the contents of these tables into memory when it starts and reloads them under the circumstances. Access-control decisions are based on the in-memory copies of the grant tables.

user: Contains user accounts, global privileges, and other non-privilege columns.
user: 使用者帳號、全域許可權、其他非許可權欄位

db: Contains database-level privileges.
db: 庫層級許可權

host: Obsolete.
host: 廢棄

tables_priv: Contains table-level privileges.
表層級許可權

columns_priv: Contains column-level privileges.
列層級許可權

procs_priv: Contains stored procedure and function privileges.
預存程序和儲存函數相關的許可權

proxies_priv: Contains proxy-user privileges.
代理使用者權限



There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by your operating system:

    User names, as used by MySQL for authentication purposes, have nothing to do with user names (login names) as used by Windows or Unix.
    MySQL user names can be up to 16 characters long.
    The server uses MySQL passwords stored in the user table to authenticate client connections using MySQL native authentication (against passwords stored in the mysql.user table).
    MySQL encrypts passwords stored in the user table using its own algorithm. This encryption is the same as that implemented by the PASSWORD() SQL function but differs from that used during the Unix login process.
    It is possible to connect to the server regardless of character set settings if the user name and password contain only ASCII characters.

使用者帳號：
    使用者名稱@主機
        使用者名稱：16字元以內
        主機：
            主機名稱：www.magedu.com, mysql
            IP: 172.16.10.177
            網路地址:
                172.16.0.0/255.255.0.0

            萬用字元：%，_
                172.16.%.%
                %.magedu.com

    --skip-name-resolve   略過名稱（主機名稱）解析，提高使用者串連的速度

權限等級：
    全域層級： SUPER、
    庫
    表: DELETE, ALTER, TRIGGER
    列: SELECT, INSERT, UPDATE
    預存程序和儲存函數

欄位層級：


暫存資料表：記憶體表
    heap: 16MB

觸發器：主動資料庫
    INSERT, DELETE, UPDATE
        user: log


建立使用者：
CREATE USER [email protected] [IDENTIFIED BY ‘password‘]

GRANT 授權時使用者不存在則建立新使用者
GRANT ALL PRIVILEGES ON [object_type] db.* TO [email protected]‘%‘;

 TABLE
  | FUNCTION
  | PROCEDURE

GRANT EXECUTE ON FUNCTION db.abc TO [email protected]‘%‘;


建立使用者：不會自動讀取授權表
INSERT INTO mysql.user
mysql> FLUSH PRIVILEGES;

查看使用者相關的授權資訊：
SHOW GRANTS FOR ‘[email protected]‘;


 GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count




--skip-grant-tables  跳過授權表
--skip-name-resolve  略過名稱（主機名稱）解析，提高使用者串連的速度
--skip-networking    跳過網路,只能通過本機串連

DROP USER ‘username‘@‘host‘ 刪除使用者

RENAME USER old_name TO new_name 重新命名使用者

REVOKE 收回許可權


管理員密碼忘記找回：
啟動mysqld_safe時傳遞兩個參數：
    --skip-grant-tables
    --skip-networking 跳過網路,只能通過本機串連

    set password for ‘root‘@‘localhost‘ IDENTIFIED by ‘passed‘  --此方法不行，因為跳過了授權表
    update user set password=password(‘passed‘) where USER = ‘root‘
    通過更新授權表方式直接修改其密碼，而後移除此兩個選項重啟伺服器。

mysql使用者和許可權管理