There is a specific list of commands that may be disabled using the product user profile. These are listed in Table 9.2.

To disable a command for a user, insert a row into the PRODUCT_PROFILE table. You should normally log in as SYSTEM, and your INSERT statement should look like this:

INSERT INTO product_profile
    (product, userid, attribute, char_value)
    VALUES ('SQL*Plus','USERNAME','COMMAND_NAME','DISABLED'),

where:

Fields in the PRODUCT_PROFILE table other than the four listed above are not used by SQL*Plus. They should be left alone, and will default to NULL. The following example will disable the DELETE command for the user named SCOTT:

INSERT INTO product_profile
    (product, userid, attribute, char_value)
    VALUES ('SQL*Plus','SCOTT','DELETE','DISABLED'),

You can wildcard the USERID field to disable a command for a number of users at once. You can even disable a command across the board for all users. The following statement inserts a row into the PRODUCT_PROFILE table that will disable the SQL*Plus HOST command for everyone:

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

Be careful when using wildcards other than %. You have to be sure you know which users you are affecting when you create the restriction, and you have to worry about the possibility that you might create a new username in the future that inadvertently matches some existing restriction. Wildcards also make it difficult to remove a restriction for just one of the users who meet the criteria. For example, you might use “J%” to disable DELETE for all usernames starting with “J”. If you later decide that “JONES” needs DELETE access, but “JASON” and “JENNIFER” don’t, you have to rethink everything.

To remove a restriction you have created, simply delete that row from the PRODUCT_PROFILE table. For example, to once again allow all users to issue the HOST command, issue the following command:

DELETE 
  FROM product_profile
 WHERE product='SQL*Plus' 
   AND userid='%'
   AND char_value='HOST'

To disable a role for a user, log in as SYSTEM and insert a row into the PRODUCT_PROFILE table, as follows:

INSERT INTO product_profile
    (product, userid, attribute, char_value)
    VALUES ('SQL*Plus','USERNAME','ROLES','ROLE_NAME'),

where:

Fields in the PRODUCT_PROFILE table that are not listed above should be left alone, and will default to NULL. The following example will disable the PAYROLL_ADMINISTRATOR role for the user named SCOTT:

INSERT INTO product_profile
    (product, userid, attribute, char_value)
    VALUES ('SQL*Plus','SCOTT','ROLES','PAYROLL_ADMINISTRATOR'),

You can wildcard the username when disabling a role, but you must be very careful when doing this. SQL*Plus translates all the role restrictions for a user into a single SET ROLE command like this:

SET ROLE ALL EXCEPT role, role, role...

If any one of those roles is not valid for the user in question, the command will fail and none of the roles will disabled. If you wildcard the username when disabling a role, you must be absolutely certain either that each user has been granted the role in question, or that the role has been granted to PUBLIC.

The method for removing a role restriction is the same as that used to remove a command restriction — delete the row from the PRODUCT_PROFILE table. For example, to allow SCOTT to be a PAYROLL_ADMINISTRATOR when logged in using SQL*Plus, issue the following DELETE command:

DELETE 
  FROM product_profile
 WHERE product='SQL*Plus' 
   AND userid='SCOTT'
   AND command='ROLES'
   AND char_value='PAYROLL_ADMINISTRATOR'

You normally need to be logged in as SYSTEM to delete from the PRODUCT_PROFILE table.

The following script will generate a report showing all the command and role restrictions defined in the PRODUCT_PROFILE table:

SET ECHO OFF
SET PAGESIZE 50
SET LINESIZE 60
SET NEWPAGE 0
SET FEEDBACK OFF
SET TRIMSPOOL ON

TTITLE LEFT 'Product User Profile Report' -
       RIGHT 'Page ' FORMAT 9999 SQL.PNO SKIP 6
BTITLE OFF
 
COLUMN userid FORMAT A12 HEADING 'User'
COLUMN sort_by NOPRINT 
COLUMN command FORMAT A15 HEADING 'Disabled|Commands'
COLUMN role FORMAT A30 HEADING 'Disabled|Roles'

BREAK ON userid SKIP 1

PROMPT You are about to generate a product user profile report.
ACCEPT PUP_REPORT_FILE -
       PROMPT 'Enter the filename for the report output: ' -
       DEFAULT 'PUP_REPORT.LIS'

SPOOL &&PUP_REPORT_FILE
SET TERMOUT OFF

SELECT userid, 1 sort_by, attribute command, '' role
  FROM product_profile
 WHERE product = 'SQL*Plus'
   AND attribute <> 'ROLES'
   AND char_value = 'DISABLED'
UNION
SELECT userid, 2 sort_by, '' command, char_value role 
  FROM product_profile
 WHERE product = 'SQL*Plus'
   AND attribute = 'ROLES'
ORDER BY userid, sort_by, command, role
;

SPOOL OFF
SET TERMOUT ON

--Restore these settings to their defaults
TTITLE OFF
CLEAR COLUMNS
SET PAGESIZE 14
SET LINESIZE 80
SET NEWPAGE 1
SET FEEDBACK ON
SET TRIMSPOOL OFF

When you run the script, you will be prompted for a filename, and the report output will be sent to that file. Here’s an example showing how to run the script:

SQL> @report_product_profile
You are about to generate a product user profile report.
Enter the filename for the report output: c:aprofile.lis
SQL>

When you look in the file, you will see that the report looks like this:

Product User Profile Report                       Page     1





             Disabled        Disabled
User         Commands        Roles
------------ --------------- ------------------------------
GEORGE       BEGIN
             DECLARE
             EXECUTE
                             HR_ADMINISTRATOR
                             PAYROLL_ADMINISTRATOR

JONATHAN     BEGIN
             DECLARE
             DELETE
             EXECUTE
             HOST
             SET ROLE

JEFF         HOST

To find out what restrictions apply to any one user, you must keep in mind that the USERID field in the PRODUCT_PROFILE table may contain wildcards. The following script will prompt you for a username, then display a list of all the disabled commands and roles for that user. The queries involved use the LIKE operator to account for any possible wildcards.

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF

BTITLE OFF
SET HEADING OFF
SET PAGESIZE 9999
SET NEWPAGE 1

ACCEPT user_to_show -
       PROMPT 'Show the product profile for which user? '

TTITLE LEFT restriction_heading SKIP 2
COLUMN restriction_type_heading NOPRINT NEW_VALUE restriction_heading
COLUMN sort_by NOPRINT
COLUMN restriction FORMAT A30
BREAK ON restriction_type_heading SKIP PAGE

SELECT 'User ' || UPPER('&&user_to_show')
       || ' is restricted from executing the following commands:'
       restriction_type_heading,
       1 sort_by, '    ', attribute restriction
  FROM product_profile
 WHERE product = 'SQL*Plus'
   AND attribute <> 'ROLES'
   AND char_value = 'DISABLED'
   AND UPPER('&&user_to_show') LIKE userid
UNION
SELECT 'User ' || UPPER('&&user_to_show') 
       || ' has the following roles disabled:'
       restriction_type_heading,
       2 sort_by, '    ', char_value restriction
  FROM product_profile
 WHERE product = 'SQL*Plus'
   AND attribute = 'ROLES'
   AND ( UPPER('&&user_to_show') LIKE userid
         OR userid = 'PUBLIC')
UNION
SELECT 'User ' || UPPER('&&user_to_show')
       || ' does not exist.'
       restriction_type_heading,
       3 sort_by, '    ', ' ' restriction
  FROM dual
 WHERE NOT EXISTS (
          SELECT username 
            FROM all_users
           WHERE username = UPPER('&&user_to_show'))
ORDER BY sort_by, restriction
;

--Restore these settings to their defaults.
SET HEADING ON
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON
TTITLE OFF
CLEAR BREAKS
CLEAR COLUMNS

The following example shows how to run the script and what the output looks like:

SQL> @show_product_profile
Show the product profile for which user? george

User GEORGE is restricted from executing the following commands:

     BEGIN
     DECLARE
     EXECUTE

User GEORGE has the following roles disabled:

     HR_ADMINISTRATOR
     PAYROLL_ADMINISTRATOR

The script will even tell you whether or not the user really exists. It is possible to create entries in the PRODUCT_PROFILE table for users who do not exist. It is also possible to drop a user, leaving orphan entries in the profile. The following example demonstrates this:

SQL> @show_product_profile
Show the product profile for which user? Jonathan

User JONATHAN is restricted from executing the following commands:

     BEGIN
     DECLARE
     DELETE
     EXECUTE
     HOST
     SET ROLE

User JONATHAN does not exist.