Tip

Control users' access to Oracle objects from SQL*Plus

Oracle documention, submitted by Murali Krishna

Various Oracle products use PRODUCT_USER_PROFILE, a table in the SYSTEM account, to provide product-level security that supplements the user-level security provided by the SQL GRANT and REVOKE commands and user roles. This table can be used to control users' access to various Oracle object from SQL*Plus. This tip works on Oracle versions 8, 8i and 9i.

Overview

DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus--not Oracle--enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and SET ROLE commands to control users' ability to change their database privileges.

SQL*Plus reads restrictions from PRODUCT_USER_PROFILE when a user logs in to SQL*Plus and maintains those restrictions for the duration of the session. Changes to PRODUCT_USER_PROFILE will only take effect the next time the affected users log in to SQL*Plus.

Creating the table

You can create PRODUCT_USER_PROFILE by running the command file named PUPBLD with the extension SQL as SYSTEM. The exact format of the file extension and the location of the file are system dependent. See the Oracle installation and user's manual(s) provided for your operating system or your DBA for more information.

Note: If the table is created incorrectly, all users other than SYSTEM will see a warning when connecting to Oracle that the PRODUCT_USER_PROFILE information is not

    Requires Free Membership to View

loaded.

Table structure

The PRODUCT_USER_PROFILE table consists of the following columns:

PRODUCT  NOT NULL CHAR (30)  
USERID  CHAR(30)  
ATTRIBUTE  CHAR(240)  
SCOPE  CHAR(240)  
NUMERIC_VALUE  NUMBER(15,2)  
CHAR_VALUE  CHAR(240)  
DATE_VALUE  DATE  
LONG_VALUE  LONG  

Description and use of columns:

Refer to the following list for the descriptions and use of each column in the PRODUCT_USER_PROFILE table:

Product - Must contain the product name (in this case "SQL*Plus"). You cannot enter wildcards or NULL in this column. Also notice that the product name SQL*Plus must be specified in mixed case, as shown, in order to be recognized.

Userid - Must contain the username (in uppercase) of the user for whom you wish to disable the command. To disable the command for more than one user, use SQL wild cards (%) or make multiple entries. Thus, all of the following entries are valid:

SCOTT
CLASS1
CLASS% (all users whose names start with CLASS)
% (all users)

Attribute - Must contain the name (in uppercase) of the SQL, SQL*Plus, or PL/SQL command you wish to disable (for example, GET). If you are disabling a role, it must contain the character string "ROLES". You cannot enter a wildcard. See "Administration" below for a list of SQL and SQL*Plus commands you can disable.

Scope - SQL*Plus ignores this column. It is recommended that you enter NULL in this column. Other products may store specific file restrictions or other data in this column.

Numeric_Value - SQL*Plus ignores this column. It is recommended that you enter NULL in this column. Other products may store numeric values in this column.

Char_Value - Must contain the character string "DISABLED" to disable a SQL, SQL*Plus, or PL/SQL command. If you are disabling a role, it must contain the name of the role you wish to disable. You cannot use a wildcard.

Date_Value - SQL*Plus ignores this column. It is recommended that you enter NULL in this column. Other products may store DATE values in this column.

Long_Value - SQL*Plus ignores this column. It is recommended that you enter NULL in this column. Other products may store LONG values in this column.

Administration

The DBA username SYSTEM owns and has all privileges on PRODUCT_USER_PROFILE. (When SYSTEM logs in, SQL*Plus does not read PRODUCT_USER_PROFILE. Therefore, no restrictions apply to user SYSTEM.) Other Oracle usernames should have only SELECT access to this table, which allows a view of restrictions of that username and those restrictions assigned to PUBLIC. The command file PUPBLD, when run, grants SELECT access on PRODUCT_USER_PROFILE to PUBLIC.

Disabling SQL*Plus, SQL, and PL/SQL commands

To disable a SQL or SQL*Plus command for a given user, insert a row containing the user's username in the Userid column, the command name in the Attribute column, and DISABLED in the Char_Value column. The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:

PRODUCT   USERID ATTRIBUTE CHAR_VALUE
--------  ------ --------- ----------
SQL*Plus  SCOTT  HOST      DISABLED   
SQL*Plus  %      INSERT    DISABLED   
SQL*Plus  %      UPDATE    DISABLED   
SQL*Plus  %      DELETE    DISABLED   
To re-enable commands, delete the row containing the restriction.

You can disable the following SQL*Plus commands: COPY, EDIT, EXECUTE, EXIT, GET, HOST (or your operating system's alias for HOST, such as $ on VMS and ! on UNIX), QUIT, PASSWORD, RUN, SAVE, SET (see note below), SPOOL, and START.

Note: Disabling the SQL*Plus SET command will also disable the SQL SET ROLE and SET TRANSACTION commands. Disabling the SQL*Plus START command will also disable the SQL*Plus @ and @@ commands.

You can also disable the following SQL commands: ALTER, ANALYZE, AUDIT, CONNECT, CREATE, DELETE, DROP, GRANT, INSERT, LOCK, NOAUDIT, RENAME, REVOKE, SELECT, SET ROLE, SET TRANSACTION, TRUNCATE, and UPDATE.

You can also disable the following PL/SQL commands: BEGIN,DECLARE

Note: Disabling BEGIN and DECLARE does not prevent the use of the SQL*Plus EXECUTE command. EXECUTE must be disabled separately.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in December 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.