Restricting Object Creation

To put restraints on what objects are built and how they are built, use SPL routines within the following setting:

• You are the DBA of the database.
• All the other users have the Connect privilege to the database. They do not have the Resource privilege.
• You use the DBA keyword to create an SPL routine (or set of SPL routines).
• Your SPL routine (or set of SPL routines) creates tables, indexes, and views in the way you define them. You might use such a routine to set up a training database environment.

Your SPL routine might include the creation of one or more tables and associated indexes, as the following example shows:

CREATE DBA PROCEDURE all_objects()

CREATE TABLE learn1 (intone SERIAL, inttwo INT NOT NULL,
   charcol CHAR(10) );
CREATE INDEX learn_ix ON learn1 (inttwo);
CREATE TABLE toys (name CHAR(15) NOT NULL UNIQUE,
   description CHAR(30), on_hand INT);
END PROCEDURE;

To use the all_objects procedure to control additions of columns to tables, revoke the Resource privilege on the database from all users. When users try to create a table, index, or view with an SQL statement outside your procedure, they cannot do so. When users execute the procedure, they have a temporary DBA privilege so the CREATE TABLE statement, for example, succeeds, and you are guaranteed that every column that is added has a constraint placed on it. In addition, objects that users create are owned by those users. For the all_objects procedure, whoever executes the procedure owns the two tables and the index.