OceanBase 数据库 Oracle 模式租户在 V3.x 版本上就支持了原生 Oracle 11G 中的列级访问控制权限，而 OceanBase 数据库 V4.2.3、V4.3.1 版本新增 MySQL 列级权限功能，可以用于控制用户是否有权限对某张表的某几列进行 SELECT、INSERT 或 UPDATE。本文针对这两种模式租户，对该特性进行举例介绍说明。
详细说明
功能语法
GRANT priv_type [(col_list)] ON [schema_name.]table_name TO user [with grant option];
REVOKE priv_type [(col_list)] ON [schema_name.]table_name FROM user [with grant option];
其中 priv_type 指定授予的权限类型。同时将多个权限授予用户时，权限类型之间使用英文逗号（,）分隔，也支持同时指定多列，列名之间使用英文逗号（,）间隔。例如，授予 c1 列和 c2 列的 SELECT 权限，则表示为 SELECT(c1, c2)。
如果需要对某些列执行
UPDATE语句，则需要授予用户这些列的UPDATE权限，以及其它要访问的列的SELECT权限。 例如，对于以下语句：obclient> UPDATE tb1 SET c1=c3, c2=1+3 WHERE c4=1;由于需要更新的列是
c1和c2，则需要对用户授予c1和c2列的UPDATE权限；同时又需要访问c3和c4列，故还需要对用户授予c3和c4列的SELECT权限。如果需要对某些列执行
INSERT语句或REPLACE语句，则需要授予用户插入数据的这些列的INSERT权限，以及其它要访问的列的SELECT权限。对于其它语句，需要访问哪些列就授予用户哪些列的
SELECT权限即可。
OceanBase 数据库 Oracle 模式租户
Oracle 模式租户从 OceanBase 数据库 V3.x 版本就支持了该功能，不过跟原生 Oracle 一样，只支持列级的 INSERT/UPDATE 权限。 示例如下：
[root@ob62 ~]# obclient -h127.0.0.1 -utest@oracle -P2881 -pxxx -A -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221700453
Server version: OceanBase 4.3.5.3 (r103000112025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:20:49)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(TEST@oracle)[TEST]> create table t1 (c1 int, c2 int);
Query OK, 0 rows affected (0.102 sec)
obclient(TEST@oracle)[TEST]> insert into t1 values (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.021 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient(TEST@oracle)[TEST]> create user user1 identified by "xxx";
Query OK, 0 rows affected (0.052 sec)
obclient(TEST@oracle)[TEST]> grant connect,resource to user1;
Query OK, 0 rows affected (0.042 sec)
obclient(TEST@oracle)[TEST]> grant select (c1) on test.t1 to user1;
OBE-00969: missing ON keyword
obclient(TEST@oracle)[TEST]> grant select on test.t1 to user1;
Query OK, 0 rows affected (0.051 sec)
obclient(TEST@oracle)[TEST]> grant insert (c1) on test.t1 to user1;
Query OK, 0 rows affected (0.059 sec)
obclient(TEST@oracle)[TEST]> grant update (c1) on test.t1 to user1;
Query OK, 0 rows affected (0.047 sec)
obclient(TEST@oracle)[TEST]> grant delete (c1) on test.t1 to user1;
OBE-00969: missing ON keyword
obclient(TEST@oracle)[TEST]>
[root@ob62 ~]# obclient -h127.0.0.1 -uuser1@oracle -P2881 -pxxx -A -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221748143
Server version: OceanBase 4.3.5.3 (r103000112025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:20:49)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(USER1@oracle)[USER1]> select * from test.t1;
+------+------+
| C1 | C2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in set (0.007 sec)
obclient(USER1@oracle)[USER1]> insert into test.t1(c1) values (4);
Query OK, 1 row affected (0.002 sec)
obclient(USER1@oracle)[USER1]> insert into test.t1 values (5,5);
OBE-01031: insufficient privileges
obclient(USER1@oracle)[USER1]> update test.t1 set c1=c1+1;
Query OK, 3 rows affected (0.003 sec)
Rows matched: 3 Changed: 3 Warnings: 0
obclient(USER1@oracle)[USER1]> update test.t1 set c2=c2+1;
OBE-01031: insufficient privileges
obclient(USER1@oracle)[USER1]> select * from test.t1;
+------+------+
| C1 | C2 |
+------+------+
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | NULL |
+------+------+
4 rows in set (0.002 sec)
OceanBase 数据库 MySQL 模式租户
OceanBase 数据库 MySQL 模式从 V4.2.3、V4.3.1 版本开始新增 MySQL 列级权限功能，可以用于控制用户是否有权限对某张表的某几列进行 SELECT、INSERT 或 UPDATE操作：
| 权限 | 描述 |
|---|---|
| INSERT | 允许在列上插入数据 |
| REFERENCES | 创建外键约束权限 |
| SELECT | 允许查询列的数据 |
| UPDATE | 允许更新列的数据 |
示例如下：
[root@ob62 ~]# obclient -h127.0.0.1 -uroot@mysql -P2881 -pxxx -A -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221743574
Server version: OceanBase 4.3.5.3 (r103000112025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:20:49)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(root@mysql)[(none)]> use test;
Database changed
obclient(root@mysql)[test]> create table t1 (c1 int, c2 int);
Query OK, 0 rows affected (0.115 sec)
obclient(root@mysql)[test]> insert into t1 values (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.009 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient(root@mysql)[test]> create user user1 identified by "xxx";
Query OK, 0 rows affected (0.042 sec)
obclient(root@mysql)[test]> grant select (c1), insert (c1, c2) on test.t1 to user1;
Query OK, 0 rows affected (0.050 sec)
obclient(root@mysql)[test]> Bye
[root@ob62 ~]# obclient -h127.0.0.1 -uuser1@mysql -P2881 -pxxx -A -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221505318
Server version: OceanBase 4.3.5.3 (r103000112025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:20:49)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(user1@mysql)[(none)]> use test;
Database changed
obclient(user1@mysql)[test]> select * from t1;
ERROR 1143 (42000): SELECT command denied to user 'user1'@'%' for column 'c2' in table 't1'
obclient(user1@mysql)[test]> select c1 from t1;
+------+
| c1 |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.004 sec)
obclient(user1@mysql)[test]> insert into t1 values (4,4);
Query OK, 1 row affected (0.002 sec)
obclient(user1@mysql)[test]> select * from t1;
ERROR 1143 (42000): SELECT command denied to user 'user1'@'%' for column 'c2' in table 't1'
obclient(user1@mysql)[test]> select c1, c2 from t1;
ERROR 1143 (42000): SELECT command denied to user 'user1'@'%' for column 'c2' in table 't1'
obclient(user1@mysql)[test]> select c1 from t1;
+------+
| c1 |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.000 sec)
适用版本
OceanBase 数据库 Oracle 模式租户：OceanBase 数据库 V3.x、V4.x版本。
OceanBase 数据库 MySQL 模式租户：
OceanBase 数据库 V4.2.x 系列：V4.2.3 及更高版本。
其它系列：V4.3.1 及更高版本。
参考文档
详情请参见：直接授予权限。
