RelationalDBDesignRelationalDBDesign


User Profiles  «Prev  Next»
Lesson 5Using wildcards
ObjectiveRestrict many users at once by applying wildcards.

Using SQL Wild Cards

Poduct profiles give you a fairly granular degree of control over commands and roles that individual users have access to in SQL*Plus. But, as you may be thinking now, there are frequently hundreds or thousands of users, and managing product profiles for all these users could require a lot of time and effort. To make the use of product profiles as easy as possible, Oracle allows you to use wildcards to specify many restrictions with a single command.

Using wildcards

You can use one of two wildcard characters when specifying a product profile restriction. The wildcard character _ can be replaced with any other single character. The wildcard character % can be replaced with any number of characters. You can use the wildcard character when you specify either the username or the role that is to be restricted. The wildcard is interpreted as meaning “any number of characters can follow,” so you can use it to represent any number of trailing characters or to represent any value at all when it is used by itself.
Note: You can use the % wildcard to restrict all users from a particular role, but remember that disabling a role that has not been granted to a user will result in no role restrictions being imposed.

Examples

If you want to restrict all users whose name begins with BID followed by a single character from using the INSERT command, use the following SQL statement:
INSERT INTO PRODUCT_PROFILE(product, userid, attribute, char_value) 
VALUES(‘SQL*Plus’, ‘BID_’, ‘INSERT’, ‘DISABLED’);

If you want to restrict all users from using the HOST command, use the following SQL statement:
INSERT INTO PRODUCT_PROFILE(product, userid, attribute, char_value) 
VALUES(‘SQL*Plus’, ‘%’, ‘HOST’, ‘DISABLED’);

If you want to limit the user BRIAN from initially being granted any roles, use the following SQL statement:
INSERT INTO PRODUCT_PROFILE(product, userid, attribute, char_value) 
VALUES(‘SQL*Plus’, ‘BRIAN’, ‘ROLES’, ‘%’);

The following Slideshow illustrates these SQL statements graphically:
Text 1
1) You begin with a list of database users and the commands
Text 2
2) The INSERT statement restricts access to the SQL command INSERT
Text 3
3) The result of this restriction is the that BOB, BRIAN and BRUCE cannot use the INSERT command
Text 4
4) If you were to issue this INSERT statement, you would prevent all users from accessing
Text 5
5) This action has the effect of removing the HOST command
Text 6
6) This action has the effect of removing the HOST command

SQL Wildcard Characters
In the next lesson, you will learn how to remove a restriction imposed by a product profile.

Using Wildcards - Exercise

Click the Exercise link to try your hand at imposing user restrictions on COIN database users.
Using Wildcards - Exercise