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

Privileges and Views

When you create a view, the database server tests your privileges on the underlying tables and views. When you use a view, only your privileges with regard to the view are tested.

Privileges When You Create a View

When you create a view, the database server tests to make sure that you have all the privileges that you need to execute the SELECT statement in the view definition. If you do not, the view is not created.

This test ensures that users cannot gain unauthorized access to a table by creating a view on the table and querying the view.

After you create the view, the database server grants you, the creator and owner of the view, at least the Select privilege on it. No automatic grant is made to public, as is the case with a newly created table.

The database server tests the view definition to see if the view is modifiable. If it is, the database server grants you the Insert, Delete, and Update privileges on the view, provided that you also have those privileges on the underlying table or view. In other words, if the new view is modifiable, the database server copies your Insert, Delete, and Update privileges from the underlying table or view, and grants them on the new view. If you have only the Insert privilege on the underlying table, you receive only the Insert privilege on the view.

This test ensures that users cannot use a view to gain access to any privileges that they did not already have.

Because you cannot alter or index a view, the Alter and Index privileges are never granted on a view.

Privileges When You Use a View

When you attempt to use a view, the database server tests only the privileges that you are granted on the view. It does not test your right to access the underlying tables.

If you created the view, your privileges are the ones noted in the preceding paragraph. If you are not the creator, you have the privileges that were granted to you by the creator or someone who had the WITH GRANT OPTION privilege.

Therefore you can create a table and revoke public access to it; then you can grant limited access privileges to the table through views. The process of creating such a table can be demonstrated through the previous examples using the hr_data table. The following table shows its definition:

CREATE TABLE hr_data 

	(

	emp_key INTEGER,

	emp_name CHAR(40),

	hire_date DATE,

	dept_num SMALLINT,

	user-id CHAR(18),

	salary DECIMAL(8,2), 

	performance_level CHAR(1), 

	performance_notes TEXT

	)

The previous example centers on granting privileges directly on this table. The following examples take a different approach. Assume that when the table was created, the following statement was executed:

REVOKE ALL ON hr_data FROM PUBLIC

(Such a statement is not necessary in an ANSI-compliant database.) Now you create a series of views for different classes of users. For those who should have read-only access to the nonsensitive columns, you create the following view:

CREATE VIEW hr_public AS

	SELECT emp_key, emp_name, hire_date, dept_num, user_id

		FROM hr_data

Users who are given the Select privilege for this view can see nonsensitive data and update nothing. For Human Resources personnel who must enter new rows, you create a different view, as the following example shows:

CREATE VIEW hr_enter AS

	SELECT emp_key, emp_name, hire_date, dept_num

		FROM hr_data

You grant these users both Select and Insert privileges on this view. Because you, the creator of both the table and the view, have the Insert privilege on the table and the view, you can grant the Insert privilege on the view to others who have no privileges on the table.

On behalf of the person in the MIS department who enters or updates new
user IDs, you create still another view, as the following example shows:

CREATE VIEW hr_MIS AS

	SELECT emp_key, emp_name, user_id

		FROM hr_data

This view differs from the previous view in that it does not expose the department number and date of hire.

Finally, the managers need access to all columns and they need the ability to update the performance-review data for their own employees only. These requirements can be met by creating a table, hr_data, that contains a department number and a computer user IDs for each employee. Let it be a rule that the managers are members of the departments that they manage. Then the following view restricts managers to rows that reflect only their employees:

CREATE VIEW hr_mgr_data AS

	SELECT * FROM hr_data

		WHERE dept_num = 

			(SELECT dept_num FROM hr_data

				WHERE user_id = USER)

		AND NOT user_id = USER

The final condition is required so that the managers do not have update access to their own row of the table. Therefore, you can safely grant the Update privilege to managers for this view, but only on selected columns, as the following statement shows:

GRANT SELECT, UPDATE (performance_level, performance_notes)

	ON hr_mgr_data TO peter_m

Informix Guide to SQL: TutorialChapter 11: Granting and Limiting Access to Your Database Home Contents Index Master Index New Book

Privileges and Views

Privileges When You Create a View

Privileges When You Use a View

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

Home Contents Index Master Index New Book