看看這篇合不合用

在下孤陋寡聞

-- login as root

create database d1129;

use d1129;

create user u1129 identified by 'u1129xyz';

show grants for u1129;
+-----------------------------------+
| Grants for u1129@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `u1129`@`%` |
+-----------------------------------+

flush privileges;

create table t1129 (
  id tinyint unsigned auto_increment primary key
, c1 varchar(10)
, n1 tinyint unsigned
);

insert into t1129(c1, n1) values
('測試', 7);

grant select (id, c1) on d1129.t1129 to u1129;

-- 使用 show 查看
show grants for u1129;

+-------------------------------------------------------------+
| Grants for u1129@%                                          |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u1129`@`%`                           |
| GRANT SELECT (`c1`, `id`) ON `d1129`.`t1129` TO `u1129`@`%` |
+-------------------------------------------------------------+

flush privileges;

-- 在系統表中查看 columns 權限
select *
  from mysql.columns_priv;

+------+-------+-------+------------+-------------+---------------------+-------------+
| Host | Db    | User  | Table_name | Column_name | Timestamp           | Column_priv |
+------+-------+-------+------------+-------------+---------------------+-------------+
| %    | d1129 | u1129 | t1129      | c1          | 0000-00-00 00:00:00 | Select      |
| %    | d1129 | u1129 | t1129      | id          | 0000-00-00 00:00:00 | Select      |
+------+-------+-------+------------+-------------+---------------------+-------------+
2 rows in set (0.00 sec)


----
-- user login without database to use (parameter -D)

mysql -u u1129 -p

select id, c1 from d1129.t1129;
+----+--------+
| id | c1     |
+----+--------+
|  1 | 測試   |
+----+--------+

select id, n1 from d1129.t1129;

ERROR 1143 (42000): SELECT command denied to user 'u1129'@'localhost' for column 'n1' in table 't1129'

這樣就做到指定查詢特定欄位的功能了.

使用者某乙使用 SELECT * FROM Person , 只能看見 ID , Name 這兩個欄位。

這樣恐怕做不到哦，執行時會出現 Error
可以控管某些欄位權限不給存取，但只能參考殺豬大的做法，然後，某乙只能下
SELECT ID,Name FROM Person 會成功，若下 select * 會失敗。