Lesson 5	Using wildcards
Objective	Restrict 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

Question: How are SQL Wildcards used in Oracle? SQL wildcards are powerful tools that allow Oracle users to search for patterns in their database tables. Wildcards are characters that can be used in conjunction with the LIKE operator to match a wide variety of strings or characters. In Oracle, there are two main types of SQL wildcards: the percent sign (%) and the underscore (_). The percent sign (%) is used to match any sequence of zero or more characters. For example, the query "SELECT * FROM employees WHERE last_name LIKE 'Smi%'" would return all employees whose last name starts with "Smi," such as "Smith," "Smiley," and "Smirnoff." The percent sign can be used at the beginning, middle, or end of a pattern, and it is case-insensitive, so it will match both "Smith" and "smith."
The underscore (_) is used to match any single character. For example, the query "SELECT * FROM employees WHERE last_name LIKE 'Smi_h'" would return all employees whose last name is five characters long and starts with "Smi" and ends with an "h," such as "Smith" and "Smyth." The underscore can be used in any position in a pattern. In addition to the percent sign and underscore, Oracle also supports the square bracket notation for specifying a range of characters. For example, the query "SELECT * FROM employees WHERE last_name LIKE '[BFG]rown'" would return all employees whose last name starts with either "Brown," "Frown," or "Grown."
It is important to note that wildcards can be resource-intensive and slow down database queries if not used correctly. To optimize performance, it is best to use wildcards sparingly and avoid using them at the beginning of a pattern, as this can lead to full table scans. Additionally, it is always a good practice to test queries thoroughly and use indexing where appropriate to speed up the search process. SQL wildcards are a powerful feature of Oracle that allow users to search for patterns in their database tables using the LIKE operator. The percent sign and underscore are the most commonly used wildcards, while the square bracket notation can be used to specify a range of characters. However, care must be taken when using wildcards to avoid performance issues, and queries should always be thoroughly tested and optimized.
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.

SQL WildCard 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 Slide show illustrates these SQL statements graphically:

1) You begin with a list of database users and the commands they can use in SQL *Plus

2) The INSERT statement restricts access to the SQL command INSERT to users whose username begins with the character 'B'

3) The result of this restriction is the that BOB, BRIAN and BRUCE cannot use the INSERT command when they use SQL * PLUS.

4) If you were to issue this INSERT statement, you would prevent all users from accessing the HOST command in SQL * Plus.

5) This action has the effect of removing the HOST command from the SQL *Plus environment.

You begin with a list of database users and the commands
The INSERT statement restricts access to the SQL command INSERT
The result of this restriction is the that BOB, BRIAN and BRUCE cannot use the INSERT command
If you were to issue this INSERT statement, you would prevent all users from accessing
This action has the effect of removing the HOST command
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