Open Framework, Information Management Strategy & Collaborative Governance | Data & Social Methodology - MIKE2.0 Methodology
Wiki Home
Collapse Expand Close

Collapse Expand Close

To join, please contact us.

Improve MIKE 2.0
Collapse Expand Close
Need somewhere to start? How about the most wanted pages; or the pages we know need more work; or even the stub that somebody else has started, but hasn't been able to finish. Or create a ticket for any issues you have found.

Access Control Auditing and Security Design Deliverable Template

From MIKE2.0 Methodology

Jump to: navigation, search
This deliverable template is used to describe a sample of the MIKE2.0 Methodology (typically at a task level). More templates are now being added to MIKE2.0 as this has been a frequently requested aspect of the methodology. Contributors are strongly encouraged to assist in this effort.
Deliverable templates are illustrative as opposed to fully representative. Please help add examples to this template that are representative of the proposed output.


= Overview

Access control, Auditing and Security Design is of critical importance in protecting corporate assets. In addition, an information system must ensure that analysis is based on a complete set of data. Data access controls are needed to protect:

  • Loading the raw data
  • Updating the core data
  • Accessing the core data
  • Accessing other users’ summaries or extracts
  • Controlling the proliferation of data

Controls can be implemented using a combination of the following strategies:

  • Roles
  • Profiles
  • Horizontal and vertical partitioned views
  • Audit tables and database triggers
  • Public and private synonyms
  • Summary extracts sharing

In general, these controls are done mostly within the database itself, with additional controls in the integration and presentation layer. forfait sosh rio b and you portabilité du numéro calcul IMC rio orange

Example 1 - Database level Security design

Listed below are example Database Security Design for Oracle and DB2:


This document describes some strategies for implementing a security regime at the database level. The strategies outlined here should be considered baseline and are suitable for enhancing for specific client requirements.

Data base Security Aims

The aims of DB security at the bottom level are to allow access to only authorised personnel.


Often three roles are immediately obvious

  • those with only Read access
  • those with Add / Change / Delete access
  • those with administration privileges.

A distinction needs to be made also between Application Access and End User access. Some times an application implements it's own security and interacts with the database on behalf of the user. Depending on the application different modules require different roles as part of the auditing procedure.

Good Design Principles / Unwritten rules

These are principles that will make it easy to quickly implement production ready systems.

  • It is not acceptable to share user-id access to any of our systems. An application may use an administration level id to do its work but individual users should not logon using this id except to install / maintain the application. Individual users require their own user-id.
  • System users (eg. Sys or system in Oracle, or the Instance Owner in DB2) should not be used by developers or application users
  • Individual users should not be given table maintenance privileges against application objects.
  • Individual products should be kept in independent Schema’s (Oracle)
  • Access to all objects beyond the users home Schema should be via roles and not assigned to the user.
  • Public synonyms referring to the schema owner tables so the owner does not need to be explicitly specified. The allows users/applications to login as non schema owners but still reference the object if the schema owner changes or renamed. Access to public synonyms does not provide access to the object, underlying object privileges to the table is still controlled via security
Oracle Style

The number of roles allowed to be enabled at once for any user is defined by the MAX_ENABLED_ROLES initialisation parameter. Roles can be granted to other roles, therefore a standard user may include the query role.

DB2 Style

The following provides the outline for a DB2 security strategy in line with "good design principles" shown before.

  • Role-based database object privileges (such as SELECT, UPDATE)
  • Roles as outlined in section 2.1 are implemented by Groups – defined in underlying operating system (OS like AIX)
  • Database object privileges are granted by either:
  • Granting authorizations to groups
  • Granting EXECUTE privileges to groups on application packages
  • User privileges are established by roles (OS groups) membership. Thus individual users need not be defined within the DB, just to the underlying OS
  • Functional Ids help enforce "way of access"

It is worth mentioning that the only practical and fully secure way of data access independent of the access tool is by the use of application packages and not by granting table/view privileges.

Example 2 – Front end Data control requirements for Configurable Controls

Listed below are example Front end Data control requirements for Configurable Controls:

  Overall DQ Control Requirements
1. Defining and managing controls The system should provide a web or PC-based graphical-user interface (GUI).
The system should be able to connect to the source database and display summary information on the current dataset (i.e. min, max, frequencies, etc.)
The system should permit the user to classify "problem" or outlying records into classifications that they create for their management purposes.
The system should be able to execute a control conditional on the result of another control.
For Example:
Field A should = Field B 98% of the time.
2. Standard Universal Features for User Interface The system should present a user interface that contains the standard, universal features of a user interface in its class, such as navigational methods, cut-and-paste, search, etc.
3. Context Sensitive Help The system should provide context-sensitive and user defined help.
4. Connecting to the corporate metadata repository The system should be able to connect to the corporate interactive metadata repository to display current information about the data element for which a control is being defined.
5. Descriptive Metadata The system should be able to store descriptive metadata regarding the controls (as distinquished from metric metadata that results from executing controls.)
6. Verifiability The system must provide a means to verify the correctness of the control definitions, e.g., through the creation of a validation test suite.
7. Data Movement Monitor The system should be capable of reading a source data store produced by the Source Extract portion of the data movement program(s) it is monitoring.
The system should be able to persist the source data store header for each file it processes in a data movement control database. This would include:
  • The source file/data source from which the data was created
  • The program that created the source data/file/data store
  • The source file/data store creation date-time stamp
  • The number of records processed.
    The system should be able to persist the source data store header information within 1 minute of the Source Extract program reading the source data file.
    The system should be able to read and persist information regarding records processed by the Transform and Load portion of the data movement program(s).
Wiki Contributors
Collapse Expand Close

View more contributors