RelationalDBDesignRelationalDBDesign


User Profiles  «Prev  Next»
Lesson 6Removing a restriction
Objective Use SQL to remove a restriction

Removing Restriction using SQL

By now, you probably have a pretty good understanding of how SQL*Plus uses the information you place into the PRODUCT_PROFILE table to limit its environment for an individual user. SQL*Plus looks at the rows in the table and imposes the restrictions defined by those rows. To remove a restriction, you have to delete the relevant row in the table.

Syntax

If you want to remove the restriction that prevents user BRIAN from accessing the INSERT command in SQL*Plus, use the following SQL statement:
DELETE * FROM PRODUCT_PROFILE WHERE product = ‘SQL*Plus’ 
AND userid = ‘BRIAN’ AND attribute = ‘INSERT’;

If you want to remove the restriction that prevents all users whose username begins with the letter B from using the INSERT statement, use the following SQL statement:
DELETE * FROM PRODUCT_PROFILE WHERE product = ‘SQL*Plus’ 
AND userid = ‘B%’ AND attribute = ‘INSERT’;

Keep in mind that these two statements are not comparable. To remove a restriction, you have to delete a specific row from the PRODUCT_PROFILE table. If you add both of these restrictions to the table, the second SQL statement will allow BRIAN to use the INSERT command. The first restriction will still exist, preventing access.
In the same way, you must delete the specific row you used to implement the restriction. Even if you used the following statement to delete all restrictions for BRIAN from the PRODUCT_PROFILE table
DELETE * FROM PRODUCT_PROFILE 
WHERE product = ‘SQL*Plus’ 
AND userid = ‘BRIAN’;

you would still have the row that restricts all users whose name begins with the character B from using the INSERT statement.
Understanding how to remove restrictions should help you understand that you should develop a well-thought-out scheme for using product profiles to make them as usable as possible. In the next lesson, you will learn how to report on the restrictions imposed through a product profile.