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:orantdbspupbld

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:

ACCEPT

EXECUTE

RUN

APPEND

EXIT

SAVE

ARCHIVE LOG

GET

SET [1]

ATTRIBUTE

HELP and ?

SHOW

BREAK

HOST [2]

SHUTDOWN

CHANGE

INPUT

SPOOL

CLEAR

LIST and ;

START [3]

COLUMN

PASSWORD

STARTUP

COMPUTE

PAUSE

STORE

CONNECT

PRINT

TIMING

COPY

PROMPT

TTITLE

DEFINE

QUIT

UNDEFINE

DEL

RECOVER

VARIABLE

DESCRIBE

REMARK

WHENEVER OSERROR

DISCONNECT

REPFOOTER

 

EDIT

REPHEADER

 

 

[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:

ALTER

RENAME

COMMIT

ANALYZE

REVOKE

DISASSOCIATE

AUDIT

SELECT

EXPLAIN

CREATE

SET ROLE

FLASHBACK

DELETE

SET TRANSACTION

MERGE

DROP

TRUNCATE

PURGE

GRANT

UPDATE

ROLLBACK

INSERT

ASSOCIATE

SAVEPOINT

LOCK

CALL

SET CONSTRAINTS

NOAUDIT

COMMENT

VALIDATE

 

For PL/SQL, these are:

BEGIN

DECLARE

 

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 'DisabledCommands'

COLUMN role FORMAT A30 HEADING 'DisabledRoles'

 

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

 

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.


     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net