Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

13.2 Using the Product User Profile

To use the product
user profile, you must create it first. Oracle provides a script for
this purpose. Once the product user profile table has been created,
you need to know how to do three things:

Restrict a user, or group of users, from using a specific command.

Set a role so it will be disabled for a given user or group of users
when SQL*Plus first connects.

Report the restrictions currently in the profile table.

The next few sections show you how to perform each of these tasks.

13.2.1 Creating the Profile Table

Oracle
supplies a script named
pupbld.sql that creates the table, views, and
synonyms shown in Figure 13-1Figure 13-1. You can generally find the script at the
following location:
$ORACLE_HOME/sqlplus/admin/pupbld.sql You should execute pupbld.sql while logged in as
user SYSTEM. Executing it while logged in as some other user will
result in the profile table's being created in the
wrong schema, and may result in a few privilege violations as the
script creates public synonyms. The following example shows the
script being executed:

SQL> @c:\orant\dbs\pupbld
drop synonym product_user_profile
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
date_value from product_user_profile
*
ERROR at line 3:
ORA-00942: table or view does not exist
drop table product_user_profile
*
ERROR at line 1:
ORA-00942: table or view does not exist
alter table product_profile add (long_value long)
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
View created.
Grant succeeded.
Synonym created.
Synonym created.
Synonym created.
Table created.
Grant succeeded.
View created.
Grant succeeded.
Synonym created.
0 rows updated.
SQL>

Do not be alarmed by the error messages. They are simply the result
of the way Oracle wrote the script. If you were to run the script
again, you would see a different set of errors. Any errors returned
because an object exists does not exist may safely be ignored.

13.2.2 Limiting Access to Commands and Statements

To limit access to
a SQL*Plus command or a SQL or PL/SQL statement, you need to insert a
row into the product_profile table. This row tells
SQL*Plus which command statement to disable and for which user. To
re-enable a command or statement, delete the row with the
restriction. The following sections show you how to
do
this.

13.2.2.1 Commands and statements that can be disabled

A specific list of commands or statements may be disabled using the
product user profile.

For SQL*Plus, these are:

^[1] Disabling the SET command takes SET ROLE and SET
TRANSACTION with it. That's because SQL*Plus simply
looks at the first word to see if it matches the entry in the profile
table.

^[2] Disabling HOST also disables $, !, or any other
operating-system-specific shortcut for executing a host
command.

^[3] Disabling the START command also disables @ and
@@.

For SQL, these are:

For PL/SQL, these are:

13.2.2.2 Disabling a command or statement

To disable a command or statement 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','DISABLED');

in which:

' SQL*Plus '
This is a constant. It identifies the product to which the
restriction applies, in this case SQL*Plus. It should always be
mixed-case as shown here.

username
The username of the user you are restricting. It should always be
uppercase. You can wildcard this using the wildcard characters used
with the LIKE predicate, the percent sign, and the underscore. A
value of '%' would make the restriction apply to
all users.

command
This is the name of the command or statement you wish to disable. It
should always be uppercase, and it should be one of those listed in
the "Commands and statements that can be
disabled" section.

'DISABLED'
The keyword 'DISABLED' must be
stored in the CHAR_VALUE field.

Fields in the product_profile table other than the
four listed above aren't used by SQL*Plus. They
should be left alone and will default to null. The following example
disables the DELETE statement 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 disable a command or
statement 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 that you might create a new username that
inadvertently matches some existing restriction. Wildcards make it
difficult to remove a restriction for 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.

13.2.2.3 Re-enabling a command or statement

To remove a
restriction
you have created, delete that row from the
product_profile table. For example, to allow all
users to issue the HOST command again, issue the following command:
DELETE
FROM product_profile
WHERE product='SQL*Plus'
AND userid='%'
AND char_value='HOST'

13.2.3 Limiting Access to Roles

You
disable roles for a user in much the same way that
you disable commands and statements. The primary reason to disable a
role is that a user might have a role for purposes of running an
application, but you don't want the user to have
that role when issuing ad hoc commands from SQL*Plus.

13.2.3.1 Disabling a role

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');

in which:

' SQL*Plus '
Is a constant. It identifies the product to which the restriction
applies, in this case SQL*Plus. It should always be mixed-case as
shown here.

username
Is the username of the user you are restricting. It must be
uppercase. You can wildcard the username when restricting a role, but
you must be careful when doing so.

' ROLES '
Instead of a command or statement, the keyword ROLES in this field
tells SQL*Plus you are restricting a role.

role_name
Is the name of the role to disable.

Fields in the product_profile table 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
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 be disabled. If you
wildcard the username when disabling a role, you must be certain
either that each user has been granted the role in question or that
the role has been granted to PUBLIC.

13.2.3.2 Re-enabling a role

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 statement:
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.

13.2.4 Reporting on the Product User Profile

The
following sections show you two different ways to
look at the product user profile. The first section provides a script
you can run to generate a report showing all the restrictions defined
in the product_profile table. The second section
provides a script that shows you the restrictions for a particular
user, which you can specify.

You should run these scripts while logged in as the SYSTEM user. If
you run them while logged in as anyone else, you will see only the
restrictions that apply to you.

13.2.4.1 Listing all restrictions

The
script
in Example 13-1 generates a report showing all the
command and role restrictions defined in the
product_profile table.

Example 13-1. A script to report on product user profile restrictions

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> @ex13-1
You are about to generate a product user profile report.
Enter the filename for the report output: c:\a\profile.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

13.2.4.2 Listing restrictions for a particular user

To find out what restrictions apply to any one user, remember that
the userid field in the
product_profile table may contain wildcards. The
script in Example 13-2 prompts you for a username,
and displays a list of all the disabled commands, statements, and
roles for that user. The queries involved use the LIKE operator to
account for any possible wildcards.

Example 13-2. A script to list product user profile restrictions for a given user

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> @ex13-2
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 tell you whether the user exists. It is possible to
create entries in the product_profile table for
users who do not exist. It is possible to drop a user, leaving orphan
entries in the profile. The following example demonstrates this:

SQL> @ex13-2
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.