To put restraints on what objects are built and how they are built, use SPL routines within the following setting:
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.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]