DOC

Oracle Schema Architecture

By Warren Holmes,2014-05-14 06:20
10 views 0
Oracle Schema Architecture

    ORACLE SCHEMA ARCHITECTURE

    FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS

    Howard Hyde

    Vice President, Database Administration

    Countrywide Financial Corporation

    Howard_Hyde@countrywide.com

    Updated 2007.05.02

DATABASE SCHEMA ARCHITECTURE 1 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS

    Copyright?2007 by Howard Hyde

    TABLE OF CONTENTS

    ORACLE DATABASE APPLICATION SCHEMA ACHITECTURE ................................................ 3 PURPOSE AND GOALS ........................................................................................................................... 3 DEFINITIONS......................................................................................................................................... 3 REQUIREMENTS .................................................................................................................................... 4

    Security and Integrity ...................................................................................................................... 4

    Seamless Continuity across Releases................................................................................................ 5 SCHEMA CATEGORIES ........................................................................................................................... 5

    Naming Convention ......................................................................................................................... 5

    Table / Data owning Schema......................................................................................................... 6

    API Schemas ................................................................................................................................... 6

    Client Logon (‘Service’) Schemas .................................................................................................... 7

    Utility Schemas ............................................................................................................................... 8 RELEASE SCENARIOS: ...................................................................................................................... 8 BASIC / COMBINABLE SCENARIOS ....................................................................................................... 10

    Column Dropped ........................................................................................................................... 10

    Column Added ............................................................................................................................... 10

    Foreign Key/columns added .......................................................................................................... 11

    Foreign Key/column dropped......................................................................................................... 11

    Index Added .................................................................................................................................. 12

    Index Dropped............................................................................................................................... 12

    New Table Created ........................................................................................................................ 12

    Existing Table Dropped ................................................................................................................. 13 ADVANCED SCENARIO ........................................................................................................................ 13

    Modular Restructuring .................................................................................................................. 13 CONCLUSION ..................................................................................................................................... 15 ACKNOWLEDGEMENT .................................................................................................................... 15

DATABASE SCHEMA ARCHITECTURE 2 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS

    Copyright?2007 by Howard Hyde

Oracle Database Application Schema Achitecture

Purpose and Goals

The model of schema organization, privilege grants and synonyms described in this

    document serves the following purposes:

    ? Protect database resources from accidental or malicious corruption.

    ? Partition logical application functionality.

    ? Manage Security Profiles.

    ? Prevent unnecessary program changes.

    ? Facilitate 24/7/365 Operations.

    ? Permit application ‘rollback’ to prior release in the event of bugs in new release.

Definitions

    ? A Schema in Oracle is a logical container/owner and namespace for a set of

    database objects, such as tables, views PL/SQL packages etc. The term is for

    practical purposes synonymous with a user account that can log on to the database.

    ? A Privilege is a permission given to a schema account to perform some action

    upon the system or an object. Relevant examples are SELECT, INSERT, UPDATE and

    DELETE privileges on tables, and EXECUTE privileges on code objects. EXECUTE

    privileges are granted at the package (public specification) level, not at the level

    of individual procedures or functions within the package. (The use of stand-alone

    stored procedures or functions is NOT considered best practice; always

    encapsulate inside PL/SQL packages).

    Privileges are GRANTed and REVOKEd via SQL DCL (data control language)

    statements of the same names. Some privileges may be granted categorically, as

    in GRANT SELECT ANY TABLE, while others are granted on an individual

    object basis (as in ‘GRANT SELECT ON Tarzan.TarzansTable to

    DATABASE SCHEMA ARCHITECTURE 3 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS Copyright?2007 by Howard Hyde

    Jane’). The main reason for being concerned with privileges is how they permit

    one schema to have defined and limited access to the objects owned by another

    schema.

    ? A Synonym is an alias or pointer from one schema to an object in another schema,

    permitting the first schema to address an object in the other schema without

    qualifying it with a schema name, as if it were local. By using synonyms, we

    avoid the need to use schema names in code; even if the schema names change,

    the synonyms can remain the same, pointing to different objects.

Schemas, privileges and synonyms permit us to accomplish the goals outlined at the

    beginning of this document.

Note to MS SQL Server developers/DBAs: The Oracle model of organizing database

    objects differs from Microsoft SQL Server. In common usage, a ‘schema’ in Oracle

    roughly corresponds to a ‘database’ + database owner, or ‘dbo’, in SQL Server. Where Oracle systems consist of one database with several schemas, comparable SQL Server

    systems might consist of several ‘databases’.

Requirements

    Security and Integrity

Protecting the database from corruption through accidental or malicious data

    manipulation is of paramount concern. Application users and processes should only have

    access to those resource for which they have a clearly defined and justified need. Not

    every user or client process needs to see all the tables in the business data schema.

DATABASE SCHEMA ARCHITECTURE 4 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS

    Copyright?2007 by Howard Hyde

Seamless Continuity across Releases

Enterprise database applications in a global economy need to be available day and night,

    (or day in America, day in Europe, day in Asia, day in Antarctica…) every day of the

    year; one country’s national holiday is another’s high market day.

    The schema model described in detail later supports uninterrupted 24/7/365 availability

    of database applications, through structural table changes and API code modifications.

Schema Categories

Schemas in the model are categorized as follows:

    ? Table / Data owning Schemas

    ? API Schemas; PL/SQL package owners

    ? Client Application Logon (‘Service’) Schemas

    ? Utility Schemas

Naming Convention

    Schema names may be constructed in a standard pattern for easy categorization (unless

    the objective is to obscure the purpose of each account for security reasons). One possible

    template is __.

    For example:

    ? MyApp_OLTP_TAB: a transactional table schema for the MyApp application

    ? MyApp_OLTP_API: an API schema for the interactive MyApp application.

    ? MyApp_REF_API: an API schema exposing reference data in a read-only

    fashion.

    ? MyApp_OLTP_UTL: A schema hosting ETL and reusable database

    administration code for the MyApp transactional application.

    DATABASE SCHEMA ARCHITECTURE 5 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS Copyright?2007 by Howard Hyde

Table / Data owning Schema

    The table schema/user owns the business data. This schema has no connect privilege; no

    one may log on to this account. This user typically has no privileges on any objects it

    doesn’t directly own.

    For example:

    ? MyApp_OLTP_TAB: owns the main business application data tables.

API Schemas

    These schemas own no application tables (though they may own utility or logging tables),

    but have privileges and synonyms referencing tables in the data-owning schema

    (MyApp_OLTP_TAB), and own compiled PL/SQL packages which perform DML and

    other operations upon the tables. All manipulation of the data MUST go through one of

    the API packages; client applications are NOT permitted to access the tables directly.

    In development, these schemas may have limited privileges to log on and compile code

    objects.

    In production, these schemas are locked down; no one may log in, and only the DBA

    may compile packages in these schemas.

Logically there is only one schema of this type currently in the model:

    ? MyApp_OLTP_API: Gatekeeper to the MyApp_OLTP_TAB table data. Owns

    the packages which perform SQL DML and business logic operations on the

    application tables.

In practice, we use two instances of the API schema, designated by the suffixes ‘_A’ and

    ‘_B’:

    ? MyApp_OLTP_API_A

    ? MyApp_OLTP_API_B

Alternating use of ‘A’ and ‘B’ API schemas supports the seamless continuity across

    application releases. Structural or functional changes may be exposed in one API schema

    while the older version is still available simultaneously through the other. Client

    DATABASE SCHEMA ARCHITECTURE 6 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS

    Copyright?2007 by Howard Hyde

    connections can transition in an orderly fashion from one API to another without requiring everyone to log off at once. System downtime is reduced or eliminated. We will present the details and challenges of this mechanism in more detail later.

    Multiple A/B pairs of API schemas may be created having access to the same underlying TAB schema, in order to expose different sets of functionality against the data, representing different security profiles and/or job roles of the client user invoking the methods. One client may require full DML access to the application tables, while another may only require read-only/Select access. Another client might only need access to a select subset of the tables. A different API schema would be built for each purpose.

Client Logon (‘Service’) Schemas

    As noted earlier, client applications do not log on to the TAB or API schemas; instead, they connect to schemas designated exclusively for this purpose. We call them Service schemas, with the suffix ‘_SVC’:

    ? MyApp_OLTP_SVC The service schema is granted the connect privilege in order to permit clients fitting a defined security profile or job role access to their data functionality. This account has grants to the PL/SQL packages in the API_A and API_B schemas. Note that synonyms are NOT used, as this would result in name conflicts between the ‘A’ and ‘B’ packages (the drawback to this is that it requires the client to include the API schema name as a qualifier in all calls to package methods).

    There is one service schema for each pair of API schemas, representing different security profiles or job roles.

DATABASE SCHEMA ARCHITECTURE 7 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS

    Copyright?2007 by Howard Hyde

Utility Schemas

    Other schema / user account types may be created for specialized purposes. We have

    found it useful to create utility schemas corresponding to each table schema, to contain

    packages that perform ETL, purging, or other administrative maintenance tasks.

    ? MyApp_OLTP_UTL

Continuous Operation Release Scenarios:

The following scenarios illustrate the steps required to complete each type of structural

    database change while maintaining backward compatibility with the previous database

    release and keeping the application continuously available. Each release will have to be

    carefully planned to combine all of the operations necessary.

    To summarize, for each release:

    ? DDL (i.e. ALTER TABLE DROP COLUMN) changes may not invalidate existing

    API packages either in compilation or in runtime execution. Therefore,

    destructive DDL must be deferred until a release beyond the immediate one.

    ? API changes may not permit data manipulations that would corrupt data as

    viewed or manipulated by the prior release API.

We have experimented with different methods of cycling the A/B schemas, including

    alternating for each release, but we have settled on the method of always deploying

    initially to the ‘B’ schema, then synchronizing code to ‘A’ after the new code has been

    verified.

    We will refer to Release 1 and Release 2 in our illustration of the evolution of an application. Some DDL changes require a staggering of releases to ensure backward

    compatibility. Release 0 indicates the current production version of the database tables

    and API. When Release 1 is initially rolled out, Release 0 is still available in production,

    while Release 1 is considered to be in a status of ‘Beta’ until verified. Then, a status

    DATABASE SCHEMA ARCHITECTURE 8 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS Copyright?2007 by Howard Hyde

update occurs (with no code changes) whereby Release 1 is ‘promoted’ to Production

    status. As long as Release 0 is still available in the other API schema, it is considered

    ‘Deprecated’. If a bug is subsequently discovered in Release 1, Release 0 may be

    reinstated as ‘Production’ by a status update, with no code execution, simply by directing

    client connections to go back to the API schema hosting that release. Release 1 would

    then be demoted to ‘Closed’ and its packages dropped.

    ? WARNING: This application ‘rollback’ mechanism doesn’t work if the bug was

    caused by an error at the DDL level, that is, a structural change to tables, columns,

    keys, constraints, indexes etc. That requires careful manual/custom DBA

    intervention.

The following table illustrates a typical release cycle:

    Time API_A Release / Status API_B Release / Status T0 Release 0: Production ---

    T1 Release 0: Production Release 1: Beta

    QA testers verify integrity of

    new API T2 Release 0: Deprecated Release 1: Production

    New client connections routed to API_B; Connections to API_A

    eventually die out.

    T3 Release 0: Production Release 1: Closed BUG Status revert; No code change. Packages dropped In Release 1! Client connections routed back to Client connections dropped

    API_A

    T3 Normal Release 1: Production (sync) Release 1: Production

    API Packages are now the same in both API_A and API_B.

    New client connections routed to API_A

    T4 Release 1: Production Release 2: Beta etc.

DATABASE SCHEMA ARCHITECTURE 9 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS

    Copyright?2007 by Howard Hyde

Many DDL changes, like adding a column, can be completed in a single release without

    compromising backward database/API compatibility (provided that the column is

    nullable and/or has a default value). Others, such as dropping a column, require that the

    ALTER DDL be deferred to Release 2 in order not to invalidate the API of Release 0.

    Each release therefore may have DDL changes left over from the previous release as well

    as new changes for the current one.

Basic / Combinable Scenarios

Staggering DDL and API code

    Column Dropped

    ? Release 1:

    o DDL coded or generated transforming Release 0 schema into new image in a

    development database.

    o API coded/generated against new DDL metadata image.

    o API compiled in API_B schema in Production database; after validation and

    attrition of prior connections in API_A, the new code is compiled in API_A. ? Release 2:

    o ALTER DDL deferred from Release 1 executed in TAB schema.

    o API packages recompiled as necessary without change.

    -or-

    o API packages altered with new changes for Release 2.

Column Added

    ? Release 1:

    o DDL coded/generated transforming Release 0 schema into new image in Dev.

    Added column must be NULLable or have default value.

    o API generated from new metadata image.

    o ALTER DDL executed in TAB schema in Prod. DATABASE SCHEMA ARCHITECTURE 10 FOR SECURE AND HIGHLY AVAILABLE APPLICATIONS

    Copyright?2007 by Howard Hyde

Report this document

For any questions or suggestions please email
cust-service@docsford.com