RelationalDBDesignRelationalDBDesign


User Profiles  «Prev  Next»
Lesson 2 The product profile explained
Objective Explain how the product profile works.

Product Profile Explained

SQL*Plus is the utility that gives Oracle users the ability to interact directly with the Oracle database. There will be times when you want to give all kinds of users the ability to use SQL*Plus for ad hoc access to the database. However, many users may typically be accessing your Oracle database through an application system, which imposes its own constraints on their actions and unrestrained or uninhibited access can create problems.
For instance, you may allow a user to delete data in a table only when a certain set of application-specific conditions is met. If the same user has access to SQL*Plus, he or she can simply execute a DELETE command outside of the boundaries of the application. The product profile gives you the ability to limit access specifically to some of the capabilities available through SQL*Plus.

Implementation

Product profiles are implemented through a table in the Oracle database called PRODUCT_PROFILE in the SYSTEM schema. Note: This table used to be called PRODUCT_USER_PROFILE, and a synonym by that name is created when the table is created for compatibility.
PRODUCT_PROFILE contains a number of columns, but the only ones of real relevance to you are the following:
  1. PRODUCT-This column contains the value SQL*Plus.
  2. USER_ID-This column contains the user ID that will be affected by the limitation.
  3. ATTRIBUTE-This column can contain either the name of a particular command that will be disabled or the keyword ROLES, which indicates that a role is to be disabled.
  4. CHAR_VALUE-This column contains either the keyword DISABLED for a command or the name of a role.

You control access for SQL*Plus by using simple INSERT statements that insert data into these columns. Because the table is owned by SYSTEM, you should be logged on as SYSTEM when you execute these statements.
If the PRODUCT_PROFILE table does not exist, you will have to create it, as explained in this sidebar.
In the next lesson, you will learn how to impose restrictions on the use of a command.