Schema Standards

 

Database Schemas for New Oracle and SQL Server Applications
10/01/2004

For a new application we normally create at least four schemas. The schema names are based on the first four characters which represent the application name, whole or in part. The remaining characters will be as follows:

  •  D is the schema owner of all database tables, and triggers, and materialized views; the application should not connect using this account. Only database liaisions in development may have access to this account. Enterprise Database Administration is responsible for this account in development, test, and production; they have exclusive access to it in production.
  •  A is the schema owner of all application code stored in the database, such as procedures, packages, functions, etc. It has update acces to all tables owned by the xxxxD account. The application should not be accessing the database with this account. In development and test the developer should have access to this account. In production this account should be known only by the librarian for the group, and the Enterprise Database Administration support staff.
  •  01 is a "customer" who has the authority to connect to the database, and to execute procedures, packages, etc. owned by xxxxA; the 01 user also has select access to all tables owned by xxxxD. No customer should be explicitly accessing the database with this account. In development and test the developer should have access to this account. In production this account should be known only by the librarian for the group, and the Enterprise Database Administration suppport staff.
  •  99 is a "customer" who has the authority to connect to the database, and to directly insert, update, and delete rows from the xxxxD tables. No customer should be explicitly accessing the database with this account. In development and test the developer should have access to this account. In production this account should be known only by the librarian for the group, and the Enterprise Database Administration support staff.
  • Any other schemas/accounts associated with the xxxx account will have the same first four characters. Privileges will be determined in a manner similar to the policies for the other xxxx accounts.

The only exceptions to this policy are listed below:

  • The account is created by software purchased from a 3rd party vendor.
  • Any schemas/accounts previously created are grandfathered in as an exception.