Home | Previous Page | Next Page   Managing Databases > Granting and Limiting Access to Your Database > Using SPL Routines to Control Access to Data >

Restricting Object Creation

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 ]