什么方式能够逆向获取用户的创建和授权语句

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】
【 使用版本 】2.2.77
【问题描述】
为了方便审计，希望通过查询获取到oceanbase中所有创建的非系统用户和对用户的grant语句，用什么方式可以实现呢？最好还能生成反向revoke语句，这样很方便直观审计。

3 个赞
#3

你好，你提的这个技术问题牵涉到OceanBase企业版范围内的功能细节。建议你通过以下方式寻求帮助：
1.如你所在的企业客户已签署OceanBase企业版销售合同，请你联系客户经理；
2.如你所在的企业客户尚未签署OceanBase企业版销售合同，你可通过OceanBase官网商务咨询页面留下你的联系方式，OceanBase企业版的业务顾问会在一个工作日内与你联系。
OceanBase官网商务咨询

https://www.oceanbase.com/contactus?fromPage=https%3A%2F%2Fwww.oceanbase.com%2Fsoftwarecenter-enterprise&dataSources=softwarecenter-enterprise_footercontact_d2022

2 个赞
#4

权限可以看OCEANBASE.__ALL_VIRTUAL_DDL_OPERATION表中GRANT语句

obclient(root@sys)[oceanbase]> SELECT TENANT_ID, GMT_CREATE, LEFT(DDL_STMT_STR,50)
    ->   FROM OCEANBASE.__ALL_VIRTUAL_DDL_OPERATION
    ->  WHERE 1 = 1
    ->    AND TENANT_ID > 1
    ->    AND DDL_STMT_STR IS NOT NULL
    ->    AND DDL_STMT_STR != ''
    ->    AND DDL_STMT_STR LIKE 'GRANT%'
    ->  ORDER BY GMT_CREATE DESC;
+-----------+----------------------------+----------------------------------------------------+
| TENANT_ID | GMT_CREATE                 | LEFT(DDL_STMT_STR,50)                              |
+-----------+----------------------------+----------------------------------------------------+
|      1003 | 2026-03-25 14:18:03.454622 | GRANT ALL PRIVILEGES ON `mysql`.* TO `root`        |
|      1003 | 2026-03-25 14:18:03.454622 | GRANT ALL PRIVILEGES ON `information_schema`.* TO  |
|      1003 | 2026-03-25 14:18:03.454622 | GRANT ALL PRIVILEGES ON `test`.* TO `root`         |
|      1003 | 2026-03-25 14:18:03.453564 | GRANT ALL PRIVILEGES ON `oceanbase`.* TO `root`    |
|      1003 | 2026-03-25 14:18:03.453564 | GRANT ALL PRIVILEGES ON `__recyclebin`.* TO `root` |
|      1003 | 2026-03-25 14:18:03.453564 | GRANT ALL PRIVILEGES ON `__public`.* TO `root`     |
|      1004 | 2026-03-25 14:18:03.299233 | GRANT ALL PRIVILEGES ON `test`.* TO `root`         |
|      1004 | 2026-03-25 14:18:03.299233 | GRANT ALL PRIVILEGES ON `information_schema`.* TO  |
|      1004 | 2026-03-25 14:18:03.298162 | GRANT ALL PRIVILEGES ON `oceanbase`.* TO `root`    |
|      1004 | 2026-03-25 14:18:03.298162 | GRANT ALL PRIVILEGES ON `__recyclebin`.* TO `root` |
|      1004 | 2026-03-25 14:18:03.298162 | GRANT ALL PRIVILEGES ON `__public`.* TO `root`     |
|      1004 | 2026-03-25 14:18:03.298162 | GRANT ALL PRIVILEGES ON `mysql`.* TO `root`        |
|      1001 | 2026-03-20 11:20:32.197064 | GRANT ALL PRIVILEGES ON *.* TO `root` WITH GRANT O |
|      1001 | 2026-03-20 11:20:32.159540 | GRANT PLAINACCESS ON *.* TO `root`                 |
|      1002 | 2026-03-20 11:20:32.109142 | grant all privileges to sys                        |
|      1002 | 2026-03-20 11:20:32.089763 | grant plainaccess any sensitive rule to sys        |
|      1002 | 2026-03-20 11:20:28.107743 | GRANT SELECT ON "SYS"."GV$OB_UNITS" TO "STANDBY_RE |
|      1002 | 2026-03-20 11:20:28.095999 | GRANT SELECT ON "SYS"."GV$OB_LOG_STAT" TO "STANDBY |
|      1002 | 2026-03-20 11:20:28.084217 | GRANT SELECT ON "SYS"."GV$OB_PARAMETERS" TO "STAND |
|      1002 | 2026-03-20 11:20:28.073486 | GRANT SELECT ON "SYS"."DBA_OB_LS_HISTORY" TO "STAN |
|      1002 | 2026-03-20 11:20:28.060610 | GRANT SELECT ON "SYS"."DBA_OB_LS" TO "STANDBY_REPL |
|      1002 | 2026-03-20 11:20:28.043015 | GRANT SELECT ON "SYS"."DBA_OB_ACCESS_POINT" TO "ST |
|      1002 | 2026-03-20 11:20:28.017239 | GRANT SELECT ON "SYS"."DBA_OB_TENANTS" TO "STANDBY |
|      1002 | 2026-03-18 09:54:16.828226 | GRANT CONNECT TO AAA                               |
|      1002 | 2026-03-12 17:44:43.433519 | GRANT USAGE ON "SYS"."DBMS_LOCK" TO "AAA"          |
|      1002 | 2026-03-12 17:44:07.111431 | grant create session,connect,resource to aaa       |
|      1002 | 2026-03-02 18:04:04.642460 | GRANT ALL PRIVILEGES ON `ORAAUDITOR`.* TO `SYS`    |
|      1002 | 2026-03-02 18:04:04.641374 | GRANT ALL PRIVILEGES ON `SYS`.* TO `SYS`           |
|      1002 | 2026-03-02 18:04:04.641374 | GRANT ALL PRIVILEGES ON `oceanbase`.* TO `SYS`     |
|      1002 | 2026-03-02 18:04:04.641374 | GRANT ALL PRIVILEGES ON `__recyclebin`.* TO `SYS`  |
|      1002 | 2026-03-02 18:04:04.641374 | GRANT ALL PRIVILEGES ON `__public`.* TO `SYS`      |
|      1002 | 2026-03-02 18:04:04.641374 | GRANT ALL PRIVILEGES ON `LBACSYS`.* TO `SYS`       |
|      1001 | 2026-03-02 18:03:56.416955 | GRANT ALL PRIVILEGES ON `test`.* TO `root`         |
|      1001 | 2026-03-02 18:03:56.415935 | GRANT ALL PRIVILEGES ON `information_schema`.* TO  |
|      1001 | 2026-03-02 18:03:56.415935 | GRANT ALL PRIVILEGES ON `mysql`.* TO `root`        |
|      1001 | 2026-03-02 18:03:56.414858 | GRANT ALL PRIVILEGES ON `__public`.* TO `root`     |
|      1001 | 2026-03-02 18:03:56.414858 | GRANT ALL PRIVILEGES ON `__recyclebin`.* TO `root` |
|      1001 | 2026-03-02 18:03:56.413770 | GRANT ALL PRIVILEGES ON `oceanbase`.* TO `root`    |
+-----------+----------------------------+----------------------------------------------------+
38 rows in set (0.053 sec)

用户可以CDB_OB_USERS和DBA_OB_TENANTS关联查询

-- 获取全部用户和角色
select t1.TENANT_ID,t2.TENANT_NAME,t1.USER_NAME,t1.HOST,t1.IS_LOCKED,t1.TYPE,t1.INFO
from oceanbase.CDB_OB_USERS t1 inner join oceanbase.DBA_OB_TENANTS t2 
on t1.TENANT_ID=t2.TENANT_ID and t2.TENANT_TYPE='USER'
order by t1.TENANT_ID,t1.USER_NAME

+-----------+-------------+------------+------+-----------+------+-----------------------------+
| TENANT_ID | TENANT_NAME | USER_NAME  | HOST | IS_LOCKED | TYPE | INFO                        |
+-----------+-------------+------------+------+-----------+------+-----------------------------+
|      1002 | obmysql     | aaa        | %    | NO        | USER |                             |
|      1002 | obmysql     | ORAAUDITOR | %    | YES       | USER | system administrator        |
|      1002 | obmysql     | root       | %    | NO        | USER | system administrator        |
|      1004 | oboracle    | CONNECT    | %    | NO        | ROLE | oracle connect role         |
|      1004 | oboracle    | DBA        | %    | NO        | ROLE | oracle dba role             |
|      1004 | oboracle    | LBACSYS    | %    | YES       | USER | oracle system administrator |
|      1004 | oboracle    | ORAAUDITOR | %    | YES       | USER | oracle system administrator |
|      1004 | oboracle    | PUBLIC     | %    | NO        | ROLE | oracle public role          |
|      1004 | oboracle    | RESOURCE   | %    | NO        | ROLE | oracle resource role        |
|      1004 | oboracle    | SYS        | %    | NO        | USER | oracle system administrator |
+-----------+-------------+------------+------+-----------+------+-----------------------------+
10 rows in set (0.033 sec)
4 个赞
#5

66666

1 个赞
#6

打卡

#7

赞一个

#8

加油！！！！！

1 个赞
#9

3.0没有这个视图

#10

666

#11

学到了