Data Dictionary

1
Data Dictionary

• Nature of a Data Dictionary (DD)
• Rationale for a DD
• Structure of a DD
• Typical DD Contents
• Using SQL to View DD in Oracle
• grant and revoke Statements

2
Nature of a DD

• A database contains information about entities of
  interest to users in an organization
• When created, the database itself becomes an
  entity about which information must be kept for
  various data administration purposes
• Data dictionary (or system catalog) is a database
  about the database
• Contents of a DD are commonly referred to as
  metadata
• DD can be updated, queried much as a regular
  database
• DBMS often maintains the DD

3
Rationale for a DD

• Provides a summary of the structure of the
  database
• helps DBA manage the database
• informs users of database scope
• Facilitates communication between users and
  database administrators
• Allows the DBMS to manage the creation, access,
  and modification of tables and their components

4
Structure of a DD

• DD can be integrated with the DBMS or stand-alone
• Relational systems all have some form of
  integrated DD (e.g., Oracle)
• Integrated DD is usually active, meaning it is
  automatically updated to reflect changes in the
  database
• A stand-alone DD is not directly linked to the
  database and must be updated manually

5
Typical DD Contents

• Data elements
• names, data type
• Tables
• owner, creation date, access specifications, size
• Indexes
• name, attributes involved, creation date
• Authorized users
• names, type(s) of access
• Integrity Constraints

6
Using SQL to View DD in OracleSome Oracle DD
(Catalog) Tables

• SYS.OBJ
• contains, for each database object, such data as
  object name, owner (number), creation time
• Can view details with
• describe sys.obj
• Sample query
• select name, ctime
• from sys.obj
• where ctime gt 01-feb-02

7
Using SQL to View DD in OracleSome Oracle DD
(Catalog) Tables

• SYS.COL
• Contains, for each column, such data as data
  type, table, whether nulls are allowed, and
  position in table
• View with
• describe sys.col
• Sample query
• select obj
• from sys.col
• where upper(name) city

8
Using SQL to View DD in OracleSome Oracle DD
(Catalog) Tables

• SYS.IND
• Contains the indexes defined for every table
• describe sys.ind
• select i.ind, o.name
• from sys.ind i, sys.obj o
• where i.obj o.obj
• select count() from sys.ind
• SYS.USER
• Contains name, password, and other information
  about each user
• select password from sys.user
• where name jparsons

9
Using SQL to View DD in OracleSome Oracle DD
(Catalog) Tables

• SYS.VIEW
• Contains information about defined views
• describe sys.view
• select from sys.view
• SYS.TAB
• Contains administrative information about tables
• There are many additional system tables in the
  Oracle data dictionary

10
Using Comments to DescribeDatabase Objects

• COMMENT ON statement
• Can put comments on each database object as a
  general description of that object
• comment on
• table orders
• is Contains all unfilled orders
• comment on
• column orders.dollars
• is Total value qtyproducts.price

11
Using Comments to DescribeDatabase Objects

• select comment from sys.com
• where col is null
• and obj in
• (select obj from sys.obj
• where name orders)
• The DD contains a lot of information we may not
  want to be generally available
• Possible to create views such that users can see
  data related only to tables created by themselves
• Tables owned by sys (the DBA) are not generally
  available to ordinary users

12
Sample Queries on the DD

• Can a particular user create a table with a
  particular name, or has s/he already used that
  name?
• Which views are based on more than one base
  table?
• Finally, one to show you we have not covered
  everything there is to know ...
• How much storage space is still free in the
  entire database?
• select sum(f.lengthts.blocksize)
• from sys.ts ts, sys.fet f
• where ts.ts f.ts

13
grant statementconnect, dba, resource

• Several versions of grant statement
• connect allows new users to have access to
  Oracle
• grant connect to jeff
• identified by pwxx4r
• resource allows users to create their own tables
  with all privileges (see later) on those tables
• grant resource to veda, greenthing
• / Assumes veda and greenthing exist
• dba execute all commands on all database objects
• grant dba to mom, dick, mary
• identified by dad, ehsg, plymouth

14
grant StatementTable Privileges

• Allows owner of a table to control access by
  other database users to data in the tables
• Types of access include
• select allows users to select from specified
  table
• insert, delete, update, index, alter
• references allows user to define foreign keys on
  specified table
• all allows user all privileges specified above
• with grant option clause allows recipient to pass
  the privilege on

15
grant StatementTable Privileges

• grant insert on orders to paul
• with grant option
• grant insert, delete, update
• on customers to marilyn
• grant update (quantity)
• on products
• to anil
• grant select on agents to public
• / Provides every user the privilege /

16
grant and revoke Statements

• All table privileges are stored in a table called
  TABAUTH with primary key SEQUENCE
• Update privileges on columns are stored in
  COLAUTH
• revoke statement withdraws privileges previously
  granted (connect, dba, resource, table
  privileges)
• revoke dba from dick
• revoke resource from greenthing
• revoke delete on customers from marilyn
• revoke select on agents from public