Lesson 8

The SQL*Plus utility is the standard way of offering interactive access to an Oracle database.
The product profile can be used to limit the way that your user community can use SQL*Plus.
Now that you have completed this module, you should be able to:

1. Describe how the product profile works
2. Limit access to a command
3. Limit access for a role
4. Use wildcards in defining product profiles
5. Remove a restriction imposed by a product profile
6. Report on product profiles.

With product profiles, it is much easier to give some or all of your users access to SQL*Plus without having to unleash its full power into their untrained hands. In the next module, you will learn how triggers can extend the functionality of your Oracle database.

Product profiles in Oracle databases are used to manage and control user access to various Oracle tools, including SQLPlus. By configuring product profiles, you can grant or restrict access to SQLPlus for some or all users in your database environment. This guide will demonstrate how to manage SQL*Plus access for your users using product profiles.

The PRODUCT_PROFILE table is used to store product profile restrictions for Oracle tools. This table contains records for each user or role with specific product profile settings. The columns in the PRODUCT_PROFILE table include:

1. PRODUCT: The name of the Oracle tool, such as 'SQL*Plus'.
2. USERID: The user or role for which the product profile setting is applied.
3. ATTRIBUTE: The attribute that defines the restriction or setting, such as 'ACCESS'.
4. SCOPE: The specific scope of the attribute, if applicable.
5. NUMERIC_VALUE: A numeric value for the attribute, if applicable.
6. CHAR_VALUE: A character value for the attribute, if applicable.

To grant SQL*Plus access to a specific user, you can insert a record into the PRODUCT_PROFILE table with the appropriate settings.
Example:

INSERT INTO PRODUCT_PROFILE
  (PRODUCT, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE)
VALUES
  ('SQL*Plus', 'USERNAME', 'ACCESS', NULL, NULL, 'Y');

Replace 'USERNAME' with the actual username for which you want to grant access. This command grants SQL*Plus access to the specified user.

If you want to grant SQL*Plus access to all users, you can use the special value 'GLOBAL' for the USERID column.
Example:

INSERT INTO PRODUCT_PROFILE
  (PRODUCT, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE)
VALUES
  ('SQL*Plus', 'GLOBAL', 'ACCESS', NULL, NULL, 'Y');

This command grants SQL*Plus access to all users in the database environment.

To revoke SQL*Plus access for a specific user, you can delete the corresponding record from the PRODUCT_PROFILE table.
Example:

DELETE FROM PRODUCT_PROFILE
WHERE PRODUCT = 'SQL*Plus'
  AND USERID = 'USERNAME'
  AND ATTRIBUTE = 'ACCESS';

Replace 'USERNAME' with the actual username. This command revokes SQL*Plus access for the specified user.

One of the primary ways in which the SQLPlus utility is used is for executing SQL statements and scripts. SQLPlus is a command-line interface for Oracle databases that allows users to interact with the database and perform various tasks, such as querying data, creating and modifying database objects, and managing database users and permissions.
With SQL*Plus, you can connect to the database and execute SQL statements one at a time, or you can create SQL scripts that contain multiple SQL statements and execute them as a group. This can be useful for tasks such as creating tables, loading data, and creating views, as well as for running ad-hoc queries and generating reports.
SQL*Plus also provides a number of built-in commands and functions that can be used to manage the database, such as the ability to change the database connection, display the current user and session information, and format query results.
Additionally, SQLPlus can be used to automate tasks by creating shell scripts that call SQLPlus and execute specific SQL commands or scripts. This can be useful for tasks such as scheduling regular reports, performing maintenance tasks, and monitoring the database.

Click the Quiz link below to test your knowledge of some advanced product profile issues.
Wildcards Profile - Quiz