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

来源：互联网发布：2016淘宝最热销的产品编辑：程序博客网时间：2024/04/20 03:29

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;