How to Grant SQL Privileges across Levels

By Allen G. Taylor

A situation may arise where you will need to grant SQL privileges across levels. Much of the architecture of structured types is derived from the ideas of object-oriented programming. One of the ideas that comes out of that is the idea of a hierarchy, in which a type can have subtypes that derive some of their attributes from the type they come from (their supertype).

In addition to those inherited attributes, they can also have attributes that are exclusively their own. There can be multiple levels of such a hierarchy, with the type at the bottom being called a leaf type.

A typed table is a table in which each row stored in the table is an instance of the associated structured type. A typed table has one column for each attribute of its associated structured type. The name and data type of the column are the same as the name and data type of the attribute.

As an example, suppose you are a creator of paintings that you sell through galleries. In addition to original works of art, you also sell signed, numbered, limited editions, unsigned unnumbered open editions, and posters. You can create a structured type for your artwork as follows:

CREATE TYPE artwork (
artist   CHARACTER VARYING (30),
title   CHARACTER VARYING (50),
description  CHARACTER VARYING (256),
medium   CHARACTER VARYING (20),
creationDate DATE )
  NOT FINAL

Here’s another case of a feature that is not present on all DBMS products. However, PostgreSQL has the CREATE TYPE statement, as do Oracle 11g and SQL Server 2012.

As an artist trying to keep track of your inventory, you want to distinguish between originals and reproductions. You might further want to distinguish between different kinds of reproductions. The artwork type can have subtypes, which in turn can have subtypes of their own.

image0.jpg

There is a one-to-one correspondence between the types in the type hierarchy and the tables in the typed table hierarchy. Standard tables cannot be placed into a hierarchy similar to the one discussed here for typed tables.

Instead of a primary key, a typed table has a self-referencing column that guarantees uniqueness, not only for the maximal supertable of a hierarchy, but also for all its subtables. The self-referencing column is specified by a REF IS clause in the maximal supertable’s CREATE statement. When the reference is system generated, uniqueness across the board is guaranteed.