Lesson 5	Using wildcards
Objective	Restrict many users at once by applying wildcards to SQL*Plus product profiles, using modern Oracle pattern-matching and security best practices.

Using SQL Wildcards with Product Profiles

Product profiles give you fine-grained control over which SQL*Plus commands and roles individual users can use. In real environments, however, you rarely manage users one at a time—there may be hundreds or thousands of accounts. Wildcards solve this problem by letting you apply a single restriction that automatically affects whole groups of users whose usernames or roles match a pattern.

1. Wildcards in Oracle pattern matching

In Oracle SQL and PL/SQL, wildcards are most commonly used with the LIKE operator to search for patterns in text:

% (percent) matches zero or more characters.
_ (underscore) matches exactly one character.

Examples in a typical query context:


-- Any last name starting with 'Smi'
SELECT *
FROM   employees
WHERE  last_name LIKE 'Smi%';

-- Exactly five characters, starting with 'Smi' and ending with 'h'
SELECT *
FROM   employees
WHERE  last_name LIKE 'Smi_h';

These same wildcard rules apply when you store patterns in a control table such as PRODUCT_PROFILE (or its view PRODUCT_USER_PROFILE). Instead of matching rows in a data table, the pattern is matched against usernames and roles at SQL*Plus runtime, which is what this lesson focuses on.

When using wildcards, remember two modern best practices:

A leading % in a LIKE predicate can prevent index usage and cause full scans; use it intentionally and test performance.
For general database access control, prefer roles and privileges. Product profiles are SQL*Plus–specific and should be used as a supplementary safety layer, not your only protection.

2. Restricting groups of users with PRODUCT_PROFILE

Product profiles allow you to disable specific SQL*Plus commands or limit role usage for users whose USERID or role matches a pattern. You can:

Disable a command for a small group of users whose names share a prefix.
Disable a command for all users at once.
Control which roles are initially enabled when users connect.

The examples in this lesson assume a table called PRODUCT_PROFILE with columns such as PRODUCT, USERID, ATTRIBUTE, and CHAR_VALUE. SQL*Plus reads this information automatically when a user logs in.

2.1 Restricting a pattern of usernames with underscore

Suppose your naming convention uses BIDx (BID plus one character) for a group of batch or integration users, and you want to prevent all of them from issuing the INSERT command in SQL*Plus. You can do that with a single row:


INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value)
VALUES ('SQL*Plus', 'BID_', 'INSERT', 'DISABLED');

The underscore wildcard _ matches any single character, so this pattern covers BID1, BID2, BIDA, and so on. Each of those users will see INSERT disabled when they connect via SQL*Plus.

2.2 Restricting a broader group with percent

If you want to disable INSERT for every user whose name starts with the letter B, you can make the pattern more general:


INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value)
VALUES ('SQL*Plus', 'B%', 'INSERT', 'DISABLED');

Here, 'B%' matches BOB, BRIAN, BRUCE, and any other username beginning with B. This is an efficient way to enforce a business rule for a naming convention such as “all B* accounts are trainees who must not insert data directly.”

Expert Oracle Indexing and Access Paths

Initial list of database users and allowed SQL*Plus commands. — You begin with a list of SQL*Plus users and the commands each one can execute. Wildcards let you change behavior for many of these users at once.

INSERT statement that disables INSERT for users whose name begins with B. — `INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value) VALUES ('SQL*Plus', 'B%', 'INSERT', 'DISABLED');`
This statement disables the `INSERT` command for every SQL*Plus user whose username begins with `'B'`.

Effect of wildcard restriction on several B* users. — As a result of the preceding restriction, users such as BOB, BRIAN, and BRUCE cannot use the `INSERT` command when they connect through SQL*Plus.

3. Disabling a command for all users

Sometimes you need to prevent every SQL*Plus user from executing a specific command. For example, you might want to disable the HOST command so that users cannot spawn operating system shells from within SQL*Plus. You can accomplish this with a single wildcard pattern:


INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value)
VALUES ('SQL*Plus', '%', 'HOST', 'DISABLED');

The '%' pattern in the USERID column matches all users, so this entry removes the HOST command from the SQL*Plus environment for every account that logs in.

INSERT statement that removes HOST for all SQL*Plus users. — If you issue this `INSERT`, you prevent all users from accessing the `HOST` command in SQL*Plus.

HOST command no longer available in SQL*Plus menus or prompts. — The result of this restriction is that the `HOST` command no longer appears as an available command in SQL*Plus.

Further illustration of HOST being unavailable to all users. — A final view confirms that the `HOST` command remains unavailable to all users who connect through SQL*Plus.

4. Controlling initial roles with exact matches

Product profiles also allow you to influence role behavior at login. For example, the following statement prevents user BRIAN from having any roles enabled initially:


INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value)
VALUES ('SQL*Plus', 'BRIAN', 'ROLES', '%');

In this case, the pattern is stored in CHAR_VALUE, and SQL*Plus treats '%' as meaning “all roles.” Combined with the ROLES attribute, this entry controls how roles are enabled when BRIAN starts a session. In modern environments you would still design your security around database roles and privileges, but product profiles can provide an additional layer of control for SQL*Plus usage.

5. Summary of wildcard-based restrictions

A single INSERT into PRODUCT_PROFILE can restrict a SQL command such as INSERT for many users at once by using patterns like 'BID_' or 'B%'.
Using patterns that start with a meaningful prefix lets you manage groups created by your naming conventions (for example, all usernames beginning with a particular letter or code).
A pattern of '%' in the USERID column disables a command such as HOST for all SQL*Plus users.
Entries that use the ROLES attribute allow you to control which roles are enabled for specific users when they connect.
Wildcards remain fully supported in Oracle SQL and PL/SQL, and when used carefully they provide a compact, maintainable way to apply restrictions across large user populations.

In the next lesson, you will learn how to remove a restriction imposed by a product profile and restore a command or role for selected users.

Using Wildcards – Exercise

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