Notice: This application will enforce Multi-factor authentication (MFA) for NIH users beginning the evening of Wed Aug 3rd.
NIH | National Cancer Institute | NCI Wiki  

Contents of this Page

Views

  1. In general part of original database and code; intent was to control user access and privileges; users could only access via views and not tables directly.
  2. Want to continue using views, e.g. CSI table/view work to make it an AC, also views should filter out "deleted" content.
  3. Check that DELETED_IND defaults to "No", should be set to "Yes" when record is "logically" deleted.
  4. Workflow Status should be used for "business retired" rules and not filtering by views.
  5. Want to move to a binary value and bitmap index for DELETED_IND. (Verified the data_elements table does this, need to check all others.)
  6. Generally need an index review for all views and tables, e.g. use most selective/unique column first.

Conditions for creating views

Views may be created under the following conditions:

  1. If a query is needed by many applications , it should be saved as a view.
  2. To avoid creation of redundant data.

Naming

  1. Review naming standards around "SBR" and "SBREXT" schema.
  2. Recognize some views need to have "old" table names for coding transitions, e.g. CSI to AC.

Synonyms

  1. Places load on database during Import and Export, negligible on transaction performance.
  2. All views and packages would have public synonyms. SBR and SBREXT would use private synonyms for tables for "each other".
  3. Research best practices, e.g. when to use public vs. private, make it "easy" to maintain, etc.
  4. Generally avoid private if possible because of the number created per account.
  5. ODI adopters and turning up private copies of caDSR?
  6. Think about making public synonym names identifiable by a prefix, e.g. "NCI", "CADSR", etc. (Probably an MDR related effort.)

Security Design

Access to the caDSR repository is controlled through a combination of accounts, synonyms, business roles, and database roles. These components are defined and managed as follows:

Repository Accounts

caDSR User Accounts

caDSR user accounts are created by means of the Administration Tool (link to work procedure). Users may have accounts on any tier so they can assist in development, quality assurance, and acceptance testing as well as perform their usual work. A user may have more than one account in a given database instance.

Tool Accounts

Tool accounts are used behind the scenes to enable database connections from tools, such as via a JBoss connection pool.

caDSR Developer Accounts

caDSR Developers will have two accounts in each development tier: Development, QA, and Stage. One account, named according to the customary format, will have "user" characteristics and will be used for testing. Another account, named like the other except with an 'X' as the final character in the account id, will be granted extended privileges and used for development and debugging.

Schema Accounts

The two caDSR schema accounts are SBR (original ISO 11179 information model objects) and SBREXT (caDSR extensions to that model). The development team will have access to these schema accounts in the Development tier only. The SBR and SBREXT accounts are used only to execute DDL.

Role Based Security Design

  1. Tool accounts, used by JBoss connection pools, need one role; User accounts, used by individuals need another.
  2. Use "reserved" account for data load and data migration.
  3. Define role for reading database content, e.g. "DER_READER"
  4. Define role for general user, e.g. "DER_USER".
  5. Define role for developers, e.g "DER_DEV"
  6. Define role for applications, e.g. "DER_APPL" (used for CURATION, SENTINEL, CDEBROWSER, etc.)
  7. Define role for administrators, e.g. "DER_ADMIN"

Role Hierarchy

Each Role inherits the privileges of the role under it and adds additional privileges.
diagram showing role hierarchy for roles as described.

SCM

  1. Manage database via scripts
  2. Check all scripts into SCM
  3. Deployment to tiers will execute scripts from SCM only.
  4. Comment scripts like Java code.

Packages and Procedures

  1. Organization/content
  2. Use triggers for lowest level business rules, e.g. setting dependant values like date_modified, begin_date, etc.
  3. Place all trigger logic in a stored procedure and invoke from the trigger.
  4. Make packages object oriented like a Java Class and the stored procedures are the equivalent to class methods.
  5. Move toward more use of code level, e.g. Java, SQL where appropriate, simple queries, etc.
  6. Move toward placing business rules into reusable objects outside the database for evolution to XML schemas, etc.

Database Coding Standards

The following are standards specific to caDSR

  1. To prepare for any automated or command line use of SQL scripts please always include "whenever sqlerror exit sql.sqlcode rollback;" at the top of the script and "commit;" at the bottom.

LOV Tables

  1. Currently use text as primary key and has no generated id.
  2. Move to adding an id column, type integer, and assign the id sequentially starting at zero.
  3. Move code and database foreign keys to use integer id, not text. Needed to allow easy changes to display values, e.g. correct spelling errors, change case, etc.

Reviews

Planned

Date

Topic

Lead

12/15/07

---

Indices

Steve

---

---

Obsolete / unused tables

Prerna

---

---

Open source download packaging

---

---

---

Run toolkit profiler (TKPROF)

---

 

 

  • No labels