Reporting on Product Profile(Existing Restrictions)
RelationalDBDesignRelationalDBDesign


User Profiles  «Prev  Next»
Lesson 7 Reporting on the product profile
Objective Report on existing restrictions in the product profile.

Reporting on Product Profile

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.

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 %.
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.