测试环境及版本
CPU架构：x86
集群架构：2-2-2
OB详细版本信息：
MySQL [(none)]> show variables like '%version%';
+--------------------------------------------------+---------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------------------------------+---------------------------------------------------------------------------------------------------------------+
| group_replication_allow_local_lower_version_join | OFF |
| innodb_version | 5.7.38 |
| ndbinfo_version | |
| ndb_version | |
| ndb_version_string | |
| ob_compatibility_version | 4.2.5.6 |
| ob_last_schema_version | 0 |
| ob_security_version | 4.2.5.6 |
| protocol_version | 10 |
| slave_type_conversions | ALL_LOSSY |
| tls_version | |
| version | 5.7.25-OceanBase-v4.2.5.6 |
| version_comment | OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13) |
| version_compile_machine | |
| version_compile_os | |
| version_tokens_session | |
| version_tokens_session_number | 0 |
+--------------------------------------------------+---------------------------------------------------------------------------------------------------------------+
17 rows in set (0.004 sec)
[root@ocpserver ~]# obclient -h10.31.28.74 -P2883 -uSYS@SERVICE:ob_oracle_srv -p
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3836
Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
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(SYS@SERVICE:ob_oracle_srv)[SYS]>
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> create user test_truncate identified by 123456;
Query OK, 0 rows affected (0.240 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select username from dba_users where username = 'TEST_TRUNCATE';
+---------------+
| USERNAME |
+---------------+
| TEST_TRUNCATE |
+---------------+
1 row in set (0.028 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_sys_privs where grantee = 'TEST_TRUNCATE';
Empty set (0.024 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_role_privs where grantee = 'TEST_TRUNCATE';
Empty set (0.035 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_tab_privs where grantee = 'TEST_TRUNCATE';
Empty set (0.104 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> grant create session to test_truncate;
Query OK, 0 rows affected (0.124 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_sys_privs where grantee = 'TEST_TRUNCATE';
+---------------+----------------+--------------+
| GRANTEE | PRIVILEGE | ADMIN_OPTION |
+---------------+----------------+--------------+
| TEST_TRUNCATE | CREATE SESSION | NO |
+---------------+----------------+--------------+
1 row in set (0.034 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_role_privs where grantee = 'TEST_TRUNCATE';
Empty set (0.035 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_tab_privs where grantee = 'TEST_TRUNCATE';
Empty set (0.014 sec)
[root@ocpserver ~]# obclient -h10.31.28.74 -P2883 -uTEST_TRUNCATE@ob_oracle#ob_primary_cluster -p
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 10033
Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
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_TRUNCATE@ob_oracle)[TEST_TRUNCATE]>
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> select * from user_sys_privs;
+---------------+----------------+--------------+
| USERNAME | PRIVILEGE | ADMIN_OPTION |
+---------------+----------------+--------------+
| TEST_TRUNCATE | CREATE SESSION | NO |
+---------------+----------------+--------------+
1 row in set (0.042 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> select * from user_role_privs;
Empty set (0.076 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> select * from user_tab_privs;
Empty set (0.178 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> create table test_table (
-> id number(6),
-> name varchar2(18)
-> );
ORA-01031: insufficient privileges
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]>
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> grant create table to test_truncate;
Query OK, 0 rows affected (0.119 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> create table test_table (
-> id number(6),
-> name varchar2(18)
-> );
Query OK, 0 rows affected (0.239 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> insert into test_table (id,name) values (1,'microsoft');
Query OK, 1 row affected (0.083 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> insert into test_table (id,name) values (2,'microstart');
Query OK, 1 row affected (0.001 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> insert into test_table values (3,'bilibili');
Query OK, 1 row affected (0.002 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> insert into test_table values (4,'bibilili');
Query OK, 1 row affected (0.001 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> select * from test_table;
+------+------------+
| ID | NAME |
+------+------------+
| 1 | microsoft |
| 2 | microstart |
| 3 | bilibili |
| 4 | bibilili |
+------+------------+
4 rows in set (0.004 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> revoke create table from test_truncate;
Query OK, 0 rows affected (0.090 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_sys_privs where grantee = 'TEST_TRUNCATE';
+---------------+----------------+--------------+
| GRANTEE | PRIVILEGE | ADMIN_OPTION |
+---------------+----------------+--------------+
| TEST_TRUNCATE | CREATE SESSION | NO |
+---------------+----------------+--------------+
1 row in set (0.013 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_role_privs where grantee = 'TEST_TRUNCATE';
Empty set (0.017 sec)
obclient(SYS@SERVICE:ob_oracle_srv)[SYS]> select * from dba_tab_privs where grantee = 'TEST_TRUNCATE';
Empty set (0.043 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> \q
Bye
[root@ocpserver ~]# obclient -h10.31.28.74 -P2883 -uTEST_TRUNCATE@ob_oracle#ob_primary_cluster -p
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1347137
Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
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_TRUNCATE@ob_oracle)[TEST_TRUNCATE]>
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> select * from user_sys_privs;
+---------------+----------------+--------------+
| USERNAME | PRIVILEGE | ADMIN_OPTION |
+---------------+----------------+--------------+
| TEST_TRUNCATE | CREATE SESSION | NO |
+---------------+----------------+--------------+
1 row in set (0.029 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> select * from user_role_privs;
Empty set (0.051 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> select * from user_tab_privs;
Empty set (0.076 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> drop table test_table;
Query OK, 0 rows affected (0.211 sec)
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]> desc test_table;
ORA-04043: object TEST_TRUNCATE.TEST_TABLE does not exist
obclient(TEST_TRUNCATE@ob_oracle)[TEST_TRUNCATE]>
obclient(SYS@ob_oracle)[SYS]> create user test_user identified by 123456;
Query OK, 0 rows affected (0.186 sec)
obclient(SYS@ob_oracle)[SYS]> grant create session to test_user;
Query OK, 0 rows affected (0.106 sec)
[root@ocpserver ~]# obclient -h10.31.28.74 -P2883 -uTEST_USER@ob_oracle#ob_primary_cluster -p
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1616738
Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
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_USER@ob_oracle)[TEST_USER]> drop table test_truncate.test_table;
ORA-00942: table or view 'TEST_TRUNCATE.TEST_TABLE' does not exist
obclient(SYS@ob_oracle)[SYS]> grant drop any table to test_user;
Query OK, 0 rows affected (0.197 sec)
obclient(TEST_USER@ob_oracle)[TEST_USER]> truncate table test_truncate.test_table;
Query OK, 0 rows affected (0.375 sec)
obclient(TEST_USER@ob_oracle)[TEST_USER]> drop table test_truncate.test_table;
Query OK, 0 rows affected (0.151 sec)
obclient(SYS@ob_oracle)[SYS]> revoke drop any table from test_user;
Query OK, 0 rows affected (0.116 sec)
obclient(TEST_USER@ob_oracle)[TEST_USER]> drop table test_truncate.test_table;
ORA-00942: table or view 'TEST_TRUNCATE.TEST_TABLE' does not exist
[root@ocpserver ~]# mysql -h10.31.28.74 -P2883 -uroot@ob_mysql#ob_primary_cluster -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 28586
Server version: 5.6.25 OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> create user test_truncate identified by '123456';
Query OK, 0 rows affected (0.186 sec)
MySQL [(none)]> select user_name from oceanbase.dba_ob_users where user_name = 'test_truncate';
+---------------+
| user_name |
+---------------+
| test_truncate |
+---------------+
1 row in set (0.024 sec)
MySQL [(none)]> show grants for test_truncate;
+---------------------------------------+
| Grants for test_truncate@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'test_truncate' |
+---------------------------------------+
1 row in set (0.029 sec)
[root@ocpserver ~]# mysql -h10.31.28.74 -P2883 -utest_truncate@ob_mysql#ob_primary_cluster -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 25461
Server version: 5.6.25 OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> create database test_db;
ERROR 1044 (42000): Access denied for user 'test_truncate'@'%' to database 'test_db'
MySQL [(none)]> grant create on test_db.* to test_truncate;
Query OK, 0 rows affected (0.121 sec)
MySQL [(none)]> show grants for test_truncate;
+------------------------------------------------+
| Grants for test_truncate@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_truncate' |
| GRANT CREATE ON `test_db`.* TO 'test_truncate' |
+------------------------------------------------+
2 rows in set (0.004 sec)
MySQL [(none)]> show grants for current_user();
+------------------------------------------------+
| Grants for test_truncate@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_truncate' |
| GRANT CREATE ON `test_db`.* TO 'test_truncate' |
+------------------------------------------------+
2 rows in set (0.006 sec)
create database test_db;
use test_db;
MySQL [(none)]> create database test_db;
Query OK, 1 row affected (0.144 sec)
MySQL [(none)]> use test_db;
Database changed
MySQL [test_db]> create table test_table (
-> id int(6),
-> name varchar(18)
-> );
Query OK, 0 rows affected (0.316 sec)
MySQL [test_db]> insert into test_table (id,name) values (1,'microsoft');
ERROR 1142 (42000): INSERT command denied to user 'test_truncate'@'%' for table 'test_table'
MySQL [(none)]> grant insert on test_db.* to 'test_truncate'@'%';
Query OK, 0 rows affected (0.152 sec)
MySQL [(none)]> show grants for test_truncate;
+--------------------------------------------------------+
| Grants for test_truncate@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_truncate' |
| GRANT CREATE, INSERT ON `test_db`.* TO 'test_truncate' |
+--------------------------------------------------------+
2 rows in set (0.011 sec)
MySQL [test_db]> show grants for current_user();
+--------------------------------------------------------+
| Grants for test_truncate@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_truncate' |
| GRANT CREATE, INSERT ON `test_db`.* TO 'test_truncate' |
+--------------------------------------------------------+
2 rows in set (0.004 sec)
MySQL [test_db]> insert into test_table (id,name) values (1,'microsoft');
ERROR 1142 (42000): INSERT command denied to user 'test_truncate'@'%' for table 'test_table'
MySQL [test_db]> \q
Bye
[root@ocpserver ~]# mysql -h10.31.28.74 -P2883 -utest_truncate@ob_mysql#ob_primary_cluster -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 19946
Server version: 5.6.25 OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [test_db]>
MySQL [test_db]> insert into test_table (id,name) values (1,'microsoft');
Query OK, 1 row affected (0.014 sec)
MySQL [test_db]> insert into test_table (id,name) values (2,'microstart');
Query OK, 1 row affected (0.005 sec)
MySQL [test_db]> insert into test_table values (3,'bilibili');
Query OK, 1 row affected (0.006 sec)
MySQL [test_db]> insert into test_table values (4,'bibilili');
Query OK, 1 row affected (0.012 sec)
MySQL [test_db]> select * from test_table;
ERROR 1142 (42000): SELECT command denied to user 'test_truncate'@'%' for table 'test_table'
MySQL [(none)]> grant select on test_db.* to test_truncate;
Query OK, 0 rows affected (0.198 sec)
MySQL [test_db]> \q
Bye
[root@ocpserver ~]# mysql -h10.31.28.74 -P2883 -utest_truncate@ob_mysql#ob_primary_cluster -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1616329
Server version: 5.6.25 OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [test_db]> select * from test_table;
+------+------------+
| id | name |
+------+------------+
| 1 | microsoft |
| 2 | microstart |
| 3 | bilibili |
| 4 | bibilili |
+------+------------+
4 rows in set (0.051 sec)
MySQL [test_db]> truncate table test_table;
ERROR 1142 (42000): DROP command denied to user 'test_truncate'@'%' for table 'test_table'
MySQL [(none)]> grant drop on test_db.* to test_truncate;
Query OK, 0 rows affected (0.047 sec)
MySQL [test_db]> truncate table test_table;
Query OK, 0 rows affected (0.354 sec)
MySQL [(none)]> create user test_user identified by '123456';
Query OK, 0 rows affected (0.131 sec)
[root@ocpserver ~]# mysql -h10.31.28.74 -P2883 -utest_user@ob_mysql#ob_primary_cluster -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1093115
Server version: 5.6.25 OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> drop table test_truncate.test_table;
ERROR 1142 (42000): DROP command denied to user 'test_user'@'%' for table 'test_table'
MySQL [(none)]>
MySQL [(none)]> grant drop on test_truncate.* to test_user;
Query OK, 0 rows affected (0.169 sec)
MySQL [(none)]> truncate table test_db.test_table;
Query OK, 0 rows affected (0.340 sec)
MySQL [(none)]> drop table test_db.test_table;
Query OK, 0 rows affected
在 Oracle模式中，权限模型遵循以下原则：
对象所有者自动拥有该对象的所有权限，并且这些权限是隐式拥有的，包括 DROP、TRUNCATE 等，这是 Oracle 兼容性设计的一部分。
所以Oracle模式的租户中，对象拥有者可以不单独赋予drop权限即可执行truncate命令，其他用户需要赋予drop权限方可执行truncate命令。
在MySQL模式中，仅需单独赋予对象拥有者或其他用户drop权限即可执行truncate命令。