数据库表与权限之间关系统计脚本

来源:互联网 发布:2016淘宝最热销的产品 编辑:程序博客网 时间:2021/01/19 07:49
DBObject TypeSchemaSynonymsGrant(i/d/u)Grant(s)Grant(EXEC)TableSpaceIndexSpaceRemarkDBNAMETable                View        Sequence        Procedure        Function        Trigger        Role        

 

--所有object 
select owner, object_type, count(*)
  from dba_objects
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
 group by owner, object_type
 order by 2, 1;

 

--role查询
select * from dba_roles;

 

 

--表对应的同义词
select owner, table_owner, count(*)
  from dba_synonyms
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in (select table_name from dba_tables)
 group by owner, table_owner
 order by 2, 3;

--视图对应的同义词
select owner, table_owner, count(*)
  from dba_synonyms
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in (select view_name from dba_views)
 group by owner, table_owner
 order by 2, 3;
--序列值对应同义词 
select owner, table_owner, count(*)
  from dba_synonyms
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in (select sequence_name from dba_sequences)
 group by owner, table_owner
 order by 2, 3;

--存储过程对应同义词
select owner, table_owner, count(*)
  from dba_synonyms
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in (select object_name from dba_procedures)
 group by owner, table_owner
 order by 2, 3;

--函数 
select owner, table_owner, count(*)
  from dba_synonyms
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in
       (select object_name from dba_objects where object_type = 'FUNCTION')
 group by owner, table_owner
 order by 2, 3;

--触发器
select owner, table_owner, count(*)
  from dba_synonyms
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in
       (select object_name from dba_objects where object_type = 'TRIGGER')
 group by owner, table_owner
 order by 2, 3;

 

 

--表对应的权限 
select grantee, owner, privilege, count(*)
  from dba_tab_privs t
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in (select table_name from dba_tables)
 group by grantee, owner, privilege
 order by 2, 3, 4;

--视图对应的
select grantee, owner, privilege, count(*)
  from dba_tab_privs t
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in (select view_name from dba_views)
 group by grantee, owner, privilege
 order by 2, 3, 4;

--序列值对应
select grantee, owner, privilege, count(*)
  from dba_tab_privs t
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in (select sequence_name from dba_sequences)
 group by grantee, owner, privilege
 order by 2, 3, 4;

--存储过程对应
select grantee, owner, privilege, count(*)
  from dba_tab_privs t
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in (select object_name from dba_procedures)
 group by grantee, owner, privilege
 order by 2, 3, 4;

--函数 
select grantee, owner, privilege, count(*)
  from dba_tab_privs t
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in
       (select object_name from dba_objects where object_type = 'FUNCTION')
 group by grantee, owner, privilege
 order by 2, 3, 4;

--触发器
select grantee, owner, privilege, count(*)
  from dba_tab_privs t
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   and table_name in
       (select object_name from dba_objects where object_type = 'TRIGGER')
 group by grantee, owner, privilege
 order by 2, 3, 4;

 

--role权限查询 
select *
  from dba_role_privs
 where grantee not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
   --and granted_role like 'MIS%'
 order by 2;

 

 

--索引状况 
select owner, tablespace_name, count(*)
  from dba_indexes
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
 group by owner, tablespace_name
 order by 1, 3;

--表状况状况
select owner, tablespace_name, count(*)
  from dba_tables
 where owner not in
       ('SYS', 'SYSMAN', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'DMSYS', 'EXFSYS',
        'MDSYS', 'OLAPSYS', 'ORDSYS', 'TSMSYS', 'WMSYS', 'XDB', 'OUTLN')
 group by owner, tablespace_name
 order by 1, 3;

原创粉丝点击