方案一、便捷但权限大
为每个数据库添加metadata账号,赋予读取任何表权限
create user metadata
identified by password
default tablespace USERASPACE
temporary tablespace USERTEMP;
Grant connect,resource to metadata;
Grant create session to metadata;
Grant select any table to metadata;
Grant execute any procedure to metadata;
自然就能获取相关授权用户表的信息了,不过麻烦的是,除了看到相关赋权的用户,也可以读取所有用户表的数据了。
select * from all_objects;
select * from all_tables;
select * from all_cons_columns;
select * from all_tab_comments;
select * from all_col_comments;
select * from all_tab_columns;
select * from all_source;
select * from all_constraints;
select * from user_objects;
select * from user_source;
方案二、方便且容易控制权限
create user metadata
identified by password
default tablespace USERASPACE
temporary tablespace USERTEMP;
Grant connect,resource to metadata; Grant create session to metadata; Grant select on sys.dba_objects to metadata;
Grant select on sys.dba_tables to metadata;
Grant select on sys.dba_tab_columns to metadata;
Grant select on sys.dba_cons_columns to metadata;
Grant select on sys.dba_tab_comments to metadata;
Grant select on sys.dba_col_comments to metadata;
Grant select on sys.dba_source to metadata;
Grant select on sys.dba_constraints to metadata;
方案三、建立大量的系统表中间表、还要赋权给metadata用户,还要写存储过程和定时任务,当然权限控制没问题,不过操作上比较麻烦,在此不做详述
综上,选择方案二