User Profiles  «Prev  Next»

Lesson 7 Reporting on the product profile
Objective Report on existing restrictions in the product profile.

SQL*Plus Product Profile Restrictions

What are the existing restrictions in the product profile of SQL *Plus?
SQL*Plus is a command-line interface for managing Oracle databases. It allows administrators to set certain restrictions on the product profile to control user access and privileges. By applying restrictions to the product profile, administrators can enhance security and maintain the overall integrity of the database environment.
The product profile restrictions in SQLPlus are managed through the PRODUCT_PROFILE table. The table contains information about the restrictions applied to various Oracle tools, including SQLPlus.

Common SQL*Plus Product Profile Restrictions

Below are some common restrictions that can be applied to the SQL*Plus product profile:
  1. Restricting SQL*Plus access: Prevent users from accessing SQL*Plus by disabling the tool for specific users or roles.
  2. Command restrictions: Limit the execution of specific SQL*Plus commands for certain users or roles, such as ALTER SYSTEM, DROP, or CREATE.
  3. Resource restrictions: Control the amount of resources, such as CPU time, memory, or parallel processes, that users or roles can utilize when executing SQL*Plus commands.
  4. DDL and DML restrictions: Limit the Data Definition Language (DDL) and Data Manipulation Language (DML) operations that users or roles can perform within SQL*Plus.
  5. Object-level restrictions: Control access to specific objects, such as tables, views, or stored procedures, for users or roles within SQL*Plus.

Managing SQL*Plus Product Profile Restrictions

To manage SQL*Plus product profile restrictions, you can use Oracle's DBMS_RESOURCE_MANAGER package or manipulate the PRODUCT_PROFILE table directly. The following example demonstrates how to add a command restriction for a specific user:
Example: 
INSERT INTO PRODUCT_PROFILE
  (PRODUCT, USERID, ATTRIBUTE, SCOPE, NUMERIC_VALUE, CHAR_VALUE)
VALUES
  ('SQL*Plus', 'USERNAME', 'DISABLE_CMD', 'ALTER SYSTEM', NULL, 'Y');

Replace 'USERNAME' with the actual username for which you want to apply the restriction. This command adds a restriction that prevents the specified user from executing the ALTER SYSTEM command in SQL*Plus.
To remove a restriction, you can use the DELETE statement:
Example: 
DELETE FROM PRODUCT_PROFILE
WHERE PRODUCT = 'SQL*Plus'
  AND USERID = 'USERNAME'
  AND ATTRIBUTE = 'DISABLE_CMD'
  AND SCOPE = 'ALTER SYSTEM';

Replace 'USERNAME' with the actual username. This command removes the restriction that prevents the specified user from executing the ALTER SYSTEM command in SQL*Plus.
Always exercise caution when managing product profile restrictions, as misconfigurations can lead to unintended consequences and potentially compromise the security and integrity of your Oracle database environment.
As with any feature in your Oracle environment, you will want to be able to report on the existing restrictions in the PRODUCT_PROFILE table. As you might have guessed, you can do this by issuing a simple SQL statement against the PRODUCT_PROFILE table.

WHERE Clause Syntax

To report on the contents of the PRODUCT_PROFILE table, you select against the table, using the appropriate WHERE clause. To get a list of the command restrictions for user BRIAN, issue the following statement: 
SELECT attribute 
  FROM system.product_privs   
WHERE (UPPER(‘SQL*Plus’) LIKE UPPER(product)) 
  AND (UPPER(userid) LIKE ‘BRIAN’);

There are a couple of things to notice about this statement. First, use the UPPER function to make sure that you are selecting all the restrictions, regardless of any mixed case for the data. Second, because you are using the LIKE relational operator, you will pick up not only the restrictions for BRIAN, but also those for any wildcard combinations that also apply to BRIAN, such as B% and %.
Ad Indexing using Oracle Database

Restricted Roles for Same User

To get a list of the restricted roles for the same user, use the following statement: 
SELECT char_value 
  FROM system.product_privs 
 WHERE (UPPER(‘SQL*Plus') LIKE UPPER(product)) 
   AND ( (UPPER(userid) LIKE ‘BRIAN’) 
         OR (UPPER(userid) = ‘PUBLIC’)) 
   AND (UPPER(attribute) = ‘ROLES’);

Notice that you are including the username PUBLIC in the query. Any restrictions against this role apply to all users, so any restrictions against the role are also restrictions against BRIAN.
The next lesson is the module wrap-up.