需求：梳理Oracle數(shù)據(jù)庫(kù)的用戶權(quán)限,并生成Excel表格。
腳本說(shuō)明:

1. 數(shù)據(jù)庫(kù)類型:oracle,查詢視圖: dba_role_privs,dba_sys_privs,dba_tab_privs
2. python模塊: cx_Oracle,xlwt
3. 當(dāng)前腳本配置為單臺(tái)數(shù)據(jù)庫(kù):

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

具體案例：

################################################################################
#Coding     : utf-8
#FileName   : dba_privileges.py
#Desc       : [Oracle] 獲取數(shù)據(jù)庫(kù)權(quán)限信息:
#             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

# 獲取用戶角色授權(quán)信息
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
'''

# 獲取用戶系統(tǒng)權(quán)限信息
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
'''

# 獲取用戶授權(quán)表信息
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
'''

#設(shè)置表格樣式
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])

    # 獲取數(shù)據(jù)
    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)

        # 創(chuàng)建一個(gè)worksheet
        ws = wb.add_sheet(k,cell_overwrite_ok=False)

        # 1.excel：寫第一行,標(biāo)題
        # 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.寫入數(shù)據(jù)到對(duì)應(yīng)的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()