使用python快速梳理oracle用户的权限,生成Excel表

需求：梳理Oracle数据库的用户权限,并生成Excel表格。
脚本说明:

1. 数据库类型:oracle,查询视图: dba_role_privs,dba_sys_privs,dba_tab_privs
2. python模块: cx_Oracle,xlwt
3. 当前脚本配置为单台数据库:

db = cx_Oracle.connect('username','passwd','192.168.xx.xx:1521/orcl')

具体案例：

################################################################################
#Coding     : utf-8
#FileName   : dba_privileges.py
#Desc       : [Oracle] 获取数据库权限信息:
#             dba_role_privs,dba_sys_privs,dba_tab_privs
#call       : python dba_privileges.py
#example    :
#             version history
#----------------------------------|
#version    | 1.0                  |
#----------------------------------|
#Coder      | Tangwen              |
#----------------------------------|
#Code date  | 2019/xx/xx           |
#----------------------------------|
#Modify note| initial              |
#----------------------------------|
################################################################################

import os,sys,csv
import cx_Oracle
import xlwt

# 获取用户角色授权信息
SQL_dba_role_privs = '''
select grantee, granted_role
  from dba_role_privs
 where grantee in
       (select username
          from dba_users
         where ACCOUNT_STATUS = 'OPEN'
           and username not in
               ('SYS', 'SYSTEM', 'OUTLN', 'MGMT_VIEW', 'FLOWS_FILES', 'MDSYS',
                'ORDSYS', 'EXFSYS', 'DBSNMP', 'WMSYS', 'APPQOSSYS',
                'APEX_030200', 'OWBSYS_AUDIT', 'ORDDATA', 'CTXSYS',
                'ANONYMOUS', 'SYSMAN', 'XDB', 'ORDPLUGINS', 'OWBSYS',
                'SI_INFORMTN_SCHEMA', 'OLAPSYS', 'SCOTT', 'ORACLE_OCM',
                'XS$NULL', 'MDDATA', 'DIP', 'APEX_PUBLIC_USER',
                'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR'))
 order by grantee
'''

# 获取用户系统权限信息
SQL_dba_sys_privs = '''
select grantee, privilege
  from dba_sys_privs
 where grantee in
       (select username
          from dba_users
         where ACCOUNT_STATUS = 'OPEN'
           and username not in
               ('SYS', 'SYSTEM', 'OUTLN', 'MGMT_VIEW', 'FLOWS_FILES', 'MDSYS',
                'ORDSYS', 'EXFSYS', 'DBSNMP', 'WMSYS', 'APPQOSSYS',
                'APEX_030200', 'OWBSYS_AUDIT', 'ORDDATA', 'CTXSYS',
                'ANONYMOUS', 'SYSMAN', 'XDB', 'ORDPLUGINS', 'OWBSYS',
                'SI_INFORMTN_SCHEMA', 'OLAPSYS', 'SCOTT', 'ORACLE_OCM',
                'XS$NULL', 'MDDATA', 'DIP', 'APEX_PUBLIC_USER',
                'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR'))
 order by grantee
'''

# 获取用户授权表信息
SQL_dba_tab_privs = '''
select grantee,owner,table_name,privilege,grantor
  from dba_tab_privs
 where grantee in
       (select username
          from dba_users
         where ACCOUNT_STATUS = 'OPEN'
           and username not in
               ('SYS', 'SYSTEM', 'OUTLN', 'MGMT_VIEW', 'FLOWS_FILES', 'MDSYS',
                'ORDSYS', 'EXFSYS', 'DBSNMP', 'WMSYS', 'APPQOSSYS',
                'APEX_030200', 'OWBSYS_AUDIT', 'ORDDATA', 'CTXSYS',
                'ANONYMOUS', 'SYSMAN', 'XDB', 'ORDPLUGINS', 'OWBSYS',
                'SI_INFORMTN_SCHEMA', 'OLAPSYS', 'SCOTT', 'ORACLE_OCM',
                'XS$NULL', 'MDDATA', 'DIP', 'APEX_PUBLIC_USER',
                'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR'))
 order by grantee
'''

#设置表格样式
def set_style(name,height,bold=False):
    style = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = name
    font.bold = bold
    font.color_index = 4
    font.height = height
    style.font = font
    return style

def DB_getData(execSQL):
    db = cx_Oracle.connect('username','passwd','192.168.xx.xx:1521/orcl')
    cur = db.cursor()
    cur.execute(execSQL)
    results = cur.fetchall()

    # 获取列名,将列名保存到row0列表
    Titles = []
    for col in cur.description:
        Titles.append(col[0])

    # 获取数据
    Results = []
    for result in results:
        Results.append(result)

    cur.close()
    db.close()

    return Titles,Results

def writeExcel():
    wb = xlwt.Workbook(encoding='utf-8')

    for k,v in dba_privilegesSQL.items():
        # print(dba_privilegesSQL[k])
        Title,Results = DB_getData(v)

        # 创建一个worksheet
        ws = wb.add_sheet(k,cell_overwrite_ok=False)

        # 1.excel：写第一行,标题
        # Example: Title = ['GRANTEE', 'GRANTED_ROLE']
        # print(Title,Results)
        for idex, val in enumerate(Title):
            ws.write(0, idex, val, set_style('Times New Roman', 220, True))

        # 2.写入数据到对应的sheet
        for index,value in enumerate(Results):
            for j,v in enumerate(value):
                ws.write(index+1, j, v)

    wb.save('dba_privileges.xls')

if __name__ == '__main__':
    # 字典配置[config]
    dba_privilegesSQL = {
        'dba_role_privs': SQL_dba_role_privs,
        'dba_sys_privs': SQL_dba_sys_privs,
        'dba_tab_privs': SQL_dba_tab_privs
    }
    writeExcel()