Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 11: Granting and Limiting Access to Your Database

Home Contents Index Master Index New Book

Controlling Access to Data Using Routines

You can use a Stored Procedure Language (SPL) routine to control access to individual tables and columns in the database. You can accomplish various degrees of access control through a routine. (SPL Routines are fully described in Chapter 14, "Creating and Using SPL Routines.") A powerful feature of Stored Procedure Language (SPL) is the ability to designate a routine as a DBA-privileged routine. When you write a DBA-privileged routine, you can allow users who have few or no table privileges to have DBA privileges when they execute the routine. In the routine, users can carry out very specific tasks with their temporary DBA privilege. The DBA-privileged feature lets you accomplish the following tasks:

You can restrict how much information individual users can read from a table.
You can restrict all the changes that are made to the database and ensure that entire tables are not emptied or changed accidentally.
You can monitor an entire class of changes made to a table, such as deletions or insertions.
You can restrict all object creation (data definition) to occur within a stored routine so that you have complete control over how tables, indexes, and views are built.

Restricting Reads of Data

The routine in the following example hides the SQL syntax from users, but it requires that users have the Select privilege on the customer table. If you want to restrict what users can select, write your routine to work in the following environment:

You are the DBA of the database.
The users have the Connect privilege to the database. They do not have the Select privilege on the table.
Your stored routine (or set of stored routines) is created using the DBA keyword.
Your stored routine (or set of stored routines) reads from the table for users.

If you want users to read only the name, address, and telephone number of a customer, you can modify the routine (in this case, a function) as the following example shows:

CREATE DBA FUNCTION read_customer(cnum INT)

RETURNING CHAR(15), CHAR(15), CHAR(18);

 

DEFINE p_lname,p_fname CHAR(15);

DEFINE p_phone CHAR(18);



SELECT fname, lname, phone

	INTO p_fname, p_lname, p_phone

	FROM customer

	WHERE customer_num = cnum;



RETURN p_fname, p_lname, p_phone;



END FUNCTION;

Restricting Changes to Data

Using stored routines, you can restrict changes made to a table. Simply channel all changes through a stored routine. The stored routine makes the changes, rather than users making the changes directly. If you want to limit users to deleting one row at a time to ensure that they do not accidentally remove all the rows in the table, set up the database with the following privileges:

You are the DBA of the database.
All the users have the Connect privilege to the database. They might have the Resource privilege. They do not have the Delete privilege (for this example) on the table being protected.
Your stored routine is created using the DBA keyword.
Your stored routine performs the deletion.

Write a stored routine (in this case, a procedure) similar to the following one, which deletes rows from the customer table using a WHERE clause with the customer_num that the user provides:

CREATE DBA PROCEDURE delete_customer(cnum INT)



DELETE FROM customer 

	WHERE customer_num = cnum;



END PROCEDURE;

Monitoring Changes to Data

Using stored routines, you can create a record of changes made to a database. You can record changes made by a particular user, or you can make a record of each time a change is made.

You can monitor all the changes made to the database by a single user. Simply channel all changes through stored routines that keep track of changes that each user makes. If you want to record each time the user acctclrk modifies the database, set up the database with the following privileges:

You are the DBA of the database.
All other users have the Connect privilege to the database. They might have the Resource privilege. They do not have the Delete privilege (for this example) on the table being protected.
Your stored routine is created using the DBA keyword.
Your stored routine performs the deletion and records that a certain user has made a change.

Write a stored routine similar to the following one, which uses a customer number the user provides to update a table. If the user happens to be acctclrk, a record of the deletion is put in the file updates.

CREATE DBA PROCEDURE delete_customer(cnum INT)



DEFINE username CHAR(8);



DELETE FROM customer 

	WHERE customer_num = cnum;



IF username = 'acctclrk' THEN

	SYSTEM 'echo Delete from customer by acctclrk >> 
/mis/records/updates' ;

ENF IF

END PROCEDURE;

To monitor all the deletions made through the routine, remove the IF statement and make the SYSTEM statement more general. If you change the previous routine to record all deletions, it looks like the routine shown next.

CREATE DBA PROCEDURE delete_customer(cnum INT)



DEFINE username CHAR(8);

LET username = USER ; 

DELETE FROM tbname WHERE customer_num = cnum;



SYSTEM

 'echo Deletion made from customer table, by '||username 
||'>>/hr/records/deletes';



END PROCEDURE;

Restricting Object Creation

To put restraints on what objects are built and how they are built, use stored 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.
Your stored routine (or set of stored routines) is created using the DBA keyword.
Your stored routine (or set of stored routines) creates tables, indexes, and views in the way you defined them. You might use such a routine to set up a training database environment.

Your 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;