MySQL 查看資料表權限

1
2
3
4
5
6
7
8

-- 查看目前登入使用者的權限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

-- 查看指定使用者的權限
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR 'appuser'@'%';
SHOW GRANTS FOR 'readonly'@'192.168.1.100';

1

GRANT SELECT, INSERT, UPDATE ON `mydb`.* TO 'appuser'@'%'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查看資料庫層級權限（直接查 mysql.db）
SELECT Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, 
       Create_priv, Drop_priv, Alter_priv
FROM mysql.db
WHERE User = 'appuser';

-- 查看資料表層級權限
SELECT * FROM mysql.tables_priv WHERE User = 'appuser';

-- 查看所有使用者
SELECT User, Host, plugin FROM mysql.user;

1
2
3
4

-- 查看資料表層級權限
SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, IS_GRANTABLE
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA = 'mydb';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 授予特定資料庫的所有權限
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'%';

-- 授予特定資料表的讀寫權限
GRANT SELECT, INSERT, UPDATE ON mydb.orders TO 'appuser'@'%';

-- 授予全局權限（所有資料庫）
GRANT SELECT ON *.* TO 'readonly'@'%';

-- 指定密碼並授予權限
GRANT SELECT ON mydb.* TO 'readonly'@'%' IDENTIFIED BY 'password123';

-- 授予並允許該使用者轉授給其他人
GRANT SELECT ON mydb.* TO 'admin'@'%' WITH GRANT OPTION;

-- 授予後重新載入權限
FLUSH PRIVILEGES;

1
2
3
4
5
6
7

-- 撤銷特定資料庫的特定權限
REVOKE INSERT, UPDATE ON mydb.* FROM 'appuser'@'%';

-- 撤銷所有權限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'appuser'@'%';

FLUSH PRIVILEGES;

1
2
3

CREATE USER 'readonly'@'%' IDENTIFIED BY 'SecurePassword123';
GRANT SELECT ON mydb.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

1
2
3
4
5
6
7

-- 只允許從 192.168.1.0/24 網段連線
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'192.168.1.%';

-- 只允許從特定 IP 連線
CREATE USER 'dbadmin'@'192.168.1.100' IDENTIFIED BY 'password';
GRANT ALL ON mydb.* TO 'dbadmin'@'192.168.1.100';

1

DROP USER 'olduser'@'%';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 建立角色
CREATE ROLE 'read_only_role';
CREATE ROLE 'read_write_role';

-- 授予權限給角色
GRANT SELECT ON mydb.* TO 'read_only_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'read_write_role';

-- 將角色指派給使用者
GRANT 'read_only_role' TO 'user1'@'%';
GRANT 'read_write_role' TO 'user2'@'%';

-- 設定預設角色（登入時自動啟用）
SET DEFAULT ROLE 'read_only_role' TO 'user1'@'%';

-- 查看角色
SHOW GRANTS FOR 'user1'@'%' USING 'read_only_role';