Esempio n. 1
0
def audit_trail_table_ddl(aCursor=None, schema='audit', table2audit=None):

	audit_trail_table = '%s%s' % (audit_trail_table_prefix, table2audit)

	# which columns to potentially audit
	cols2potentially_audit = gmPG2.get_col_defs(link_obj = aCursor, schema = schema, table = table2audit)

	# which to skip
	cols2skip = gmPG2.get_col_names(link_obj = aCursor, schema = audit_schema, table = audit_fields_table)

	# which ones to really audit
	cols2really_audit = []
	for col in cols2potentially_audit[0]:
		if col in cols2skip:
			continue
		cols2really_audit.append("\t%s %s" % (col, cols2potentially_audit[1][col]))

	# does the audit trail target table exist ?
	exists = gmPG2.table_exists(aCursor, 'audit', audit_trail_table)
	if exists is None:
		_log.error('cannot check existence of table [audit.%s]' % audit_trail_table)
		return None

	if exists:
		_log.info('audit trail table [audit.%s] already exists' % audit_trail_table)
		# sanity check table structure
		currently_audited_cols = gmPG2.get_col_defs(link_obj = aCursor, schema = u'audit', table = audit_trail_table)
		currently_audited_cols = [ '\t%s %s' % (c, currently_audited_cols[1][c]) for c in currently_audited_cols[0] ]
		for col in cols2really_audit:
			try:
				currently_audited_cols.index(col)
			except ValueError:
				_log.error('table structure incompatible: column ".%s" not found in audit table' % col.strip())
				_log.error('%s.%s:' % (schema, table2audit))
				_log.error('%s' % ','.join(cols2really_audit))
				_log.error('%s.%s:' % (audit_schema, audit_trail_table))
				_log.error('%s' % ','.join(currently_audited_cols))
				return None
		return []

	# must create audit trail table
	_log.info('no audit trail table found for [%s.%s]' % (schema, table2audit))
	_log.info('creating audit trail table [audit.%s]' % audit_trail_table)

	# create audit table DDL
	attributes = ',\n'.join(cols2really_audit)
	table_def = tmpl_create_audit_trail_table % (
		audit_trail_table,
		attributes,
		audit_trail_parent_table,			# FIXME: use audit_schema
		audit_trail_table,
		audit_trail_table,
		audit_trail_table,
		audit_trail_table
	)
	return [table_def, '']
Esempio n. 2
0
def create_audit_ddl(aCursor):
    # get list of all marked tables
    # we could also get the child tables for audit.audit_fields
    # but we would have to potentially parse down several levels
    # of interitance (such as with clin.clin_root_item) to find
    # the actual leaf table to audit
    cmd = u"select schema, table_name from audit.audited_tables"
    rows, idx = gmPG2.run_ro_queries(link_obj=aCursor, queries=[{'cmd': cmd}])
    if len(rows) == 0:
        _log.info('no tables to audit')
        return None
    _log.debug('the following tables will be audited:')
    _log.debug(rows)
    ddl = []
    ddl.append('\set check_function_bodies 1\n')
    ddl.append('set check_function_bodies to on;\n\n')

    # for each marked table
    for row in rows:

        if not gmPG2.table_exists(link_obj=aCursor,
                                  schema=row['schema'],
                                  table=row['table_name']):
            _log.error('table to audit (%s) does not exist', row)
            return None

        # create log table if necessary
        audit_trail_ddl = audit_trail_table_ddl(aCursor=aCursor,
                                                schema=row['schema'],
                                                table2audit=row['table_name'])
        if audit_trail_ddl is None:
            _log.error(
                'cannot generate audit trail DDL for audited table [%s]' %
                row['table_name'])
            return None
        ddl.extend(audit_trail_ddl)
        if len(audit_trail_ddl) != 0:
            ddl.append('-- ----------------------------------------------')

        # create functions and triggers on log table
        ddl.extend(
            trigger_ddl(aCursor=aCursor,
                        schema=row['schema'],
                        audited_table=row['table_name']))
        ddl.append('-- ----------------------------------------------')

    #ddl.append(SQL_TEMPLATE_DEM_STAFF_FK)

    return ddl
Esempio n. 3
0
def create_audit_ddl(aCursor):
	# get list of all marked tables
	# we could also get the child tables for audit.audit_fields
	# but we would have to potentially parse down several levels
	# of interitance (such as with clin.clin_root_item) to find
	# the actual leaf tables to audit
	cmd = u"select schema, table_name from audit.audited_tables"
	rows, idx = gmPG2.run_ro_queries(link_obj=aCursor, queries = [{'cmd': cmd}])
	if len(rows) == 0:
		_log.info('no tables to audit')
		return None
	_log.debug('the following tables will be audited:')
	_log.debug(rows)
	# for each marked table
	ddl = []
	ddl.append('\set check_function_bodies 1\n')
	ddl.append('set check_function_bodies to on;\n\n')
	for row in rows:

		# sanity check: does table exist ?
		if not gmPG2.table_exists(link_obj = aCursor, schema = row['schema'], table = row['table_name']):
			_log.error('table to audit (%s) does not exist', row)
			return None

		audit_trail_ddl = audit_trail_table_ddl(aCursor=aCursor, schema=row['schema'], table2audit=row['table_name'])
		if audit_trail_ddl is None:
			_log.error('cannot generate audit trail DDL for audited table [%s]' % row['table_name'])
			return None
		ddl.extend(audit_trail_ddl)
		if len(audit_trail_ddl) != 0:
			ddl.append('-- ----------------------------------------------')

		ddl.extend(trigger_ddl(aCursor = aCursor, schema = row['schema'], audited_table = row['table_name']))
		ddl.append('-- ----------------------------------------------')

	return ddl
Esempio n. 4
0
def audit_trail_table_ddl(aCursor=None, schema=None, table2audit=None):

    audit_trail_table = '%s%s' % (LOG_TABLE_PREFIX, table2audit)

    # which columns to potentially audit
    cols2potentially_audit = gmPG2.get_col_defs(link_obj=aCursor,
                                                schema=schema,
                                                table=table2audit)

    # which to skip
    cols2skip = gmPG2.get_col_names(link_obj=aCursor,
                                    schema=AUDIT_SCHEMA,
                                    table=AUDIT_FIELDS_TABLE)

    # which ones to really audit
    cols2really_audit = []
    for col in cols2potentially_audit[0]:
        if col in cols2skip:
            continue
        cols2really_audit.append("\t%s %s" %
                                 (col, cols2potentially_audit[1][col]))

    # does the audit trail target table exist ?
    exists = gmPG2.table_exists(aCursor, AUDIT_SCHEMA, audit_trail_table)
    if exists is None:
        _log.error('cannot check existence of table [audit.%s]' %
                   audit_trail_table)
        return None

    if exists:
        _log.info('audit trail table [audit.%s] already exists' %
                  audit_trail_table)
        # sanity check table structure
        currently_audited_cols = gmPG2.get_col_defs(link_obj=aCursor,
                                                    schema=AUDIT_SCHEMA,
                                                    table=audit_trail_table)
        currently_audited_cols = [
            '\t%s %s' % (c, currently_audited_cols[1][c])
            for c in currently_audited_cols[0]
        ]
        for col in cols2really_audit:
            try:
                currently_audited_cols.index(col)
            except ValueError:
                _log.error(
                    'table structure incompatible: column ".%s" not found in audit table'
                    % col.strip())
                _log.error('%s.%s:' % (schema, table2audit))
                _log.error('%s' % ','.join(cols2really_audit))
                _log.error('%s.%s:' % (AUDIT_SCHEMA, audit_trail_table))
                _log.error('%s' % ','.join(currently_audited_cols))
                return None
        return []

    # must create audit trail table
    _log.info('no audit trail table found for [%s.%s]' % (schema, table2audit))
    _log.info('creating audit trail table [audit.%s]' % audit_trail_table)

    args = {
        'log_schema': AUDIT_SCHEMA,
        'log_base_tbl': AUDIT_TRAIL_PARENT_TABLE,
        'log_tbl': audit_trail_table,
        'log_cols': u',\n	'.join(cols2really_audit)
    }
    return [SQL_TEMPLATE_CREATE_AUDIT_TRAIL_TABLE % args, '']