DB2权限收回SQL自动生成工具

由于有些客户直接使用管理员权限建立了相关表和对象,造成其使用的帐号权限过大。通过下面的脚本生成的sql可以收回客户日常使用中不需要的特权权限:

import ibm_db
import ibm_db_dbi
import time

DB_HOST = '10.10.10.10'
DB_PORT = '50000'
DB_NAME = 'DBXXX'
DB_USER = 'XXX\\administrator'
DB_PASSWORD = 'XXX'
SCHEMA = 'XXX'
USER = 'XXX'
GROUP = 'XXXCHG'
OWNER = 'XXXOWR'
OUTPUT_FILE_REVOKE = ('C:\Users\qinth\Desktop\TEMP\\temps\%s\\revoker.sql') % (DB_NAME)
OUTPUT_HANDLE_REVOKE = open(OUTPUT_FILE_REVOKE, 'w')
OUTPUT_FILE_GRP = ('C:\Users\qinth\Desktop\TEMP\\temps\%s\grp.sql') % (DB_NAME)
OUTPUT_HANDLE_GRP = open(OUTPUT_FILE_GRP, 'w')
OUTPUT_FILE_ROLLBACK = ('C:\Users\qinth\Desktop\TEMP\\temps\%s\\rollback.sql') % (DB_NAME)
OUTPUT_HANDLE_ROLLBACK = open(OUTPUT_FILE_ROLLBACK, 'w')
SQL_SEPARATE = '%'
OS_SEPARATE = '\r\n'

def output(content, output = OUTPUT_HANDLE_REVOKE):
    log('write to output:' + str(content))
    output.write(str(content))

def log(content):
    print (str(content))
    
def log_error(content):
    print("ERROR: " + str(content))
    
def log_debug(content):
    print (str(content))
    time.sleep(5)

def get_DB_conn():
    dsn = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=" + DB_NAME + ";HOSTNAME=" + DB_HOST + ";PORT=" + DB_PORT + \
        ";PROTOCOL=TCPIP;UID=" + DB_USER + ";PWD=" + DB_PASSWORD + ";"
    log(dsn)
    try:
        ibm_db_conn = ibm_db.connect(dsn,'','')
        conn = ibm_db_dbi.Connection(ibm_db_conn)
        log("Connect to DB " + DB_NAME + " successful.")
        return conn
    except Exception,e:
        log_error(e)
        raise Exception
    
def generate_connect_sql():
    return 'connect to ' + DB_NAME + SQL_SEPARATE + OS_SEPARATE

def generate_view_grant(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on views to user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and (type = 'V' or type = 'W')"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " TO USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_view_grant_grp(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on views to group' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and (type = 'V' or type = 'W')"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " TO GROUP "
        output += GROUP
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_table_grant(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on tables to user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and type <> 'V' and type <> 'N' and type <> 'W'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " TO USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_table_grant_grp(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on tables to group' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and type <> 'V' and type <> 'N' and type <> 'W'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " TO GROUP "
        output += GROUP
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_nickname_grant(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on nickname to user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and type = 'N'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " TO USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_nickname_grant_grp(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on nickname to group' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and type = 'N'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " TO GROUP "
        output += GROUP
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_package_grant(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on packages to user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select PKGSCHEMA,PKGNAME from syscat.PACKAGES where PKGSCHEMA='" + SCHEMA + "'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " ON PACKAGE "
        output += schema
        output += "."
        output += tabname
        output += " TO USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_package_grant_grp(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on packages to group' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select PKGSCHEMA,PKGNAME from syscat.PACKAGES where PKGSCHEMA='" + SCHEMA + "'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " ON PACKAGE "
        output += schema
        output += "."
        output += tabname
        output += " TO GROUP "
        output += GROUP
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_package_revoke(conn, privilege):
    output = '--Revoke ' + privilege + ' privilege on packages from user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select PKGSCHEMA,PKGNAME from syscat.PACKAGES where PKGSCHEMA='" + SCHEMA + "'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "REVOKE "
        output += privilege
        output += " ON PACKAGE "
        output += schema
        output += "."
        output += tabname
        output += " FROM USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_schema_grant(conn):
    return "GRANT CREATEIN ON SCHEMA " + SCHEMA + " TO USER " + USER + SQL_SEPARATE + OS_SEPARATE

def generate_schema_revoke(conn):
    output = "REVOKE DROPIN ON SCHEMA " + SCHEMA + " FROM USER " + USER  + SQL_SEPARATE + OS_SEPARATE
    output += "REVOKE ALTERIN ON SCHEMA " + SCHEMA + " FROM USER " + USER + SQL_SEPARATE + OS_SEPARATE
    return output

def generate_schema_rollback(conn):
    output = "GRANT DROPIN ON SCHEMA " + SCHEMA + " TO USER " + USER  + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT ALTERIN ON SCHEMA " + SCHEMA + " TO USER " + USER + SQL_SEPARATE + OS_SEPARATE
    return output

def generate_schema_grp(conn):
    output = "GRANT DROPIN ON SCHEMA " + SCHEMA + " TO GROUP " + GROUP  + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT ALTERIN ON SCHEMA " + SCHEMA + " TO GROUP " + GROUP + SQL_SEPARATE + OS_SEPARATE
    return output

def generate_tbs_grant(conn, privilege):
    output = '--Grant ' + privilege + ' privilege on tablespaces to user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tbspace from syscat.tablespaces"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        tbspace = str(row[0]).strip(' ')
        output += "GRANT "
        output += privilege
        output += " OF TABLESPACE "
        output += tbspace
        output += " TO USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_table_revoke(conn, privilege):
    output = '--Revoke ' + privilege + ' privilege on tables from user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and type <> 'V' and type <> 'N' and type <> 'W'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "REVOKE "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " FROM USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_view_revoke(conn, privilege):
    output = '--Revoke ' + privilege + ' privilege on views from user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "'  and (type = 'V' or type = 'W')"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "REVOKE "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " FROM USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_db_revoke(conn):
    #output = "REVOKE BINDADD ON DATABASE FROM USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output = ""
    #output += "REVOKE CREATE_NOT_FENCED_ROUTINE ON DATABASE FROM USER " + USER + SQL_SEPARATE + OS_SEPARATE
    #output += "REVOKE CREATE_EXTERNAL_ROUTINE ON DATABASE FROM USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output += "REVOKE IMPLICIT_SCHEMA ON DATABASE FROM USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output += "REVOKE LOAD ON DATABASE FROM USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output += "REVOKE QUIESCE_CONNECT ON DATABASE FROM USER " + USER + SQL_SEPARATE + OS_SEPARATE
    return output

def generate_db_rollback(conn):
    output = "GRANT BINDADD ON DATABASE TO USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT IMPLICIT_SCHEMA ON DATABASE TO USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT LOAD ON DATABASE TO USER " + USER + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT QUIESCE_CONNECT ON DATABASE TO USER " + USER + SQL_SEPARATE + OS_SEPARATE
    return output

def generate_db_grp(conn):
    output = "GRANT BINDADD ON DATABASE TO GROUP " + GROUP + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO GROUP " + GROUP + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO GROUP " + GROUP + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT IMPLICIT_SCHEMA ON DATABASE TO GROUP " + GROUP + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT LOAD ON DATABASE TO GROUP " + GROUP + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT QUIESCE_CONNECT ON DATABASE TO GROUP " + GROUP + SQL_SEPARATE + OS_SEPARATE
    output += "GRANT CONNECT ON DATABASE TO GROUP " + GROUP + SQL_SEPARATE + OS_SEPARATE
    return output

def generate_disconnect_sql():
    return 'TERMINATE'+ SQL_SEPARATE + OS_SEPARATE

def generate_nickname_revoke(conn, privilege):
    output = '--Revoke ' + privilege + ' privilege on nicknames from user' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "'  and type = 'N'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "REVOKE "
        output += privilege
        output += " ON TABLE "
        output += schema
        output += "."
        output += tabname
        output += " FROM USER "
        output += USER
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_table_owner_transfer(conn,owner = OWNER):
    output = '--Transfer ownerships ' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and type <> 'V' and type <> 'N' and type <> 'W'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "TRANSFER OWNERSHIP OF TABLE "
        output += schema
        output += "."
        output += tabname
        output += " TO USER "
        output += owner
        output += " PRESERVE PRIVILEGES"
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_view_owner_transfer(conn,owner = OWNER):
    output = '--Transfer ownerships ' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and (type = 'V' or type = 'W')"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "TRANSFER OWNERSHIP OF VIEW "
        output += schema
        output += "."
        output += tabname
        output += " TO USER "
        output += owner
        output += " PRESERVE PRIVILEGES"
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output

def generate_nickname_owner_transfer(conn,owner = OWNER):
    output = '--Transfer ownerships ' + OS_SEPARATE
    curs = conn.cursor()
    sql = "select tabschema,tabname from syscat.tables where tabschema='" + SCHEMA + "' and type = 'N'"
    curs.execute(sql,(DB_NAME,))
    rows = curs.fetchall()
    for row in rows:
        schema = str(row[0]).strip(' ')
        tabname = str(row[1]).strip(' ')
        output += "TRANSFER OWNERSHIP OF NICKNAME "
        output += schema
        output += "."
        output += tabname
        output += " TO USER "
        output += owner
        output += " PRESERVE PRIVILEGES"
        output += SQL_SEPARATE
        output += OS_SEPARATE
    return output
    
    
if __name__ == '__main__':
    conn = get_DB_conn()
    
    #Grant
    output(generate_connect_sql())
    output(generate_table_grant(conn,'UPDATE'))
    output(generate_table_grant(conn,'SELECT'))
    output(generate_table_grant(conn,'DELETE'))
    output(generate_table_grant(conn,'INSERT'))
    output(generate_table_grant(conn,'REFERENCES'))
    output(generate_table_grant(conn,'INDEX'))
    output(generate_view_grant(conn,'UPDATE'))
    output(generate_view_grant(conn,'INSERT'))
    output(generate_view_grant(conn,'DELETE'))
    output(generate_view_grant(conn,'SELECT'))
    output(generate_package_grant(conn,'BIND'))
    output(generate_package_grant(conn,'EXECUTE'))
    output(generate_schema_grant(conn))
    output(generate_tbs_grant(conn,'USE'))
    output(generate_nickname_grant(conn,'SELECT'))
    output(generate_nickname_grant(conn,'DELETE'))
    output(generate_nickname_grant(conn,'UPDATE'))
    output(generate_nickname_grant(conn,'INSERT'))
    output(generate_nickname_grant(conn,'INDEX'))
    output(generate_nickname_grant(conn,'REFERENCES'))
    
    #Change Owner
    #output(generate_nickname_owner_transfer(conn))
    output(generate_table_owner_transfer(conn))
    output(generate_view_owner_transfer(conn))

    
    #Revoke
    #output(generate_package_revoke(conn, 'CONTROL'))
    output(generate_table_revoke(conn, 'CONTROL'))
    output(generate_table_revoke(conn, 'ALTER'))
    output(generate_view_revoke(conn, 'CONTROL'))
    output(generate_schema_revoke(conn))
    #output(generate_nickname_revoke(conn, 'CONTROL'))
    #output(generate_nickname_revoke(conn, 'ALTER'))
    output(generate_db_revoke(conn))
    output(generate_disconnect_sql())
    
    #
    #Rollback
    output(generate_connect_sql(),OUTPUT_HANDLE_ROLLBACK)
    output(generate_package_grant(conn,'CONTROL'),OUTPUT_HANDLE_ROLLBACK)
    output(generate_table_grant(conn,'CONTROL'),OUTPUT_HANDLE_ROLLBACK)
    output(generate_table_grant(conn,'ALTER'),OUTPUT_HANDLE_ROLLBACK)
    output(generate_view_grant(conn,'CONTROL'),OUTPUT_HANDLE_ROLLBACK)
    output(generate_schema_rollback(conn),OUTPUT_HANDLE_ROLLBACK)
    output(generate_nickname_grant(conn,'ALTER'),OUTPUT_HANDLE_ROLLBACK)
    output(generate_nickname_grant(conn,'CONTROL'),OUTPUT_HANDLE_ROLLBACK)
    output(generate_db_rollback(conn),OUTPUT_HANDLE_ROLLBACK)
    output(generate_nickname_owner_transfer(conn,owner=USER),OUTPUT_HANDLE_ROLLBACK)
    output(generate_table_owner_transfer(conn,owner=USER),OUTPUT_HANDLE_ROLLBACK)
    output(generate_view_owner_transfer(conn,owner=USER),OUTPUT_HANDLE_ROLLBACK)
    output(generate_disconnect_sql(),OUTPUT_HANDLE_ROLLBACK)
    
    #
    #GRP
    output(generate_connect_sql(),OUTPUT_HANDLE_GRP)
    output(generate_package_grant_grp(conn,'CONTROL'),OUTPUT_HANDLE_GRP)
    output(generate_table_grant_grp(conn,'CONTROL'),OUTPUT_HANDLE_GRP)
    output(generate_table_grant_grp(conn,'ALTER'),OUTPUT_HANDLE_GRP)
    output(generate_view_grant_grp(conn,'CONTROL'),OUTPUT_HANDLE_GRP)
    output(generate_schema_grp(conn),OUTPUT_HANDLE_GRP)
    output(generate_nickname_grant_grp(conn,'ALTER'),OUTPUT_HANDLE_GRP)
    output(generate_nickname_grant_grp(conn,'CONTROL'),OUTPUT_HANDLE_GRP)
    output(generate_db_grp(conn),OUTPUT_HANDLE_GRP)
    output(generate_disconnect_sql(),OUTPUT_HANDLE_GRP)
    
    OUTPUT_HANDLE_REVOKE.close()
    OUTPUT_HANDLE_GRP.close()
    OUTPUT_HANDLE_ROLLBACK.close()
    conn.close()
    

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*