Previous | Table of Contents | Next

Page 134

When you run the file above, you get the file DELTBL.SQL as follows:

DELETE ACCTS;
DELETE ACCT_ADDRS;
DELETE BONUS;
DELETE CITIES;
DELETE COMPANY_SUMMARY;
DELETE CUSTOMER;
DELETE DEPT;
DELETE DUMMY;
DELETE EMP;
DELETE FUNDS;
DELETE FUND_CONTRIB;
DELETE FUND_XACT;
DELETE F_EMPCOMP;
DELETE F_XACT_TYPE;
DELETE INVINFO;
DELETE INVREQUEST;
DELETE ITEM;
DELETE ORD;
DELETE ORDER_HISTORY;
DELETE PRICE;
DELETE PRODUCT;
DELETE SALES_REVENUE;
DELETE SALGRADE;
DELETE STOCK_HISTORY;

Running DELTBL.SQL by issuing the command @DELTBL.SQL will delete the rows in the tables shown. Other commands are used to format the output and create the command file:

If SYSTEM wants to delete the rows from all of SCOTT's tables, all he has to do is run the DELTBL.SQL file in SQL*Plus, which can be done as follows:

SQL> @DELTBL

Page 135

Restricting a User's Privileges in SQL*Plus

Oracle provides database administrators with a tool that enables them to disable specific SQL and SQL*Plus commands in the SQL*Plus environment. This is done on a per-user basis. This tool is, in fact, a table, PRODUCT_USER_PROFILE, owned by user SYSTEM. The table description for PRODUCT_USER_PROFILE is given in Table 8.2; Table 8.3 describes the purpose of the individual columns in the PRODUCT_USER_PROFILE table.

Table 8.2 The PRODUCT_USER_PROFILE Table Definition


Name Null? Type
PRODUCT NOT NULL VARCHAR2(30)
USERID VARCHAR2(30)
ATTRIBUTE VARCHAR2(240)
SCOPE VARCHAR2(240)
NUMERIC_VALUE NUMBER(15,2)
CHAR_VALUE VARCHAR2(240)
DATE_VALUE DATE
LONG_VALUE LONG

Table 8.3 Purpose of PRODUCT_USER_PROFILE Table Columns


Column Purpose
PRODUCT Must contain the product name (in this case SQL*Plus). No wildcard (%) or NULL values are allowed in this field.
USERID Must contain the name (in uppercase) of the user for whom you wish to disable the command. To disable a command for several users, use wildcard (%) or enter multiple rows. To disable a command for all the users, insert a wildcard (%) by itself in this field.
ATTRIBUTE Must contain the name (in uppercase) of the SQL or SQL*Plus command to be disabled (for example, ALTER). No wildcard value is allowed in this column.
SCOPE SQL*Plus ignores this column. A NULL should be entered in this column. This column is reserved for use by products other than SQL*Plus.
                                    continues

Page 136

Table 8.3 Continued


Column Purpose
NUMERIC_VALUE SQL*Plus ignores this column. A NULL should be entered in this column. This column is reserved for use by products other than SQL*Plus.
CHAR_VALUE Must contain the string DISABLED.
DATE_VALUE SQL*Plus ignores this column. A NULL should be entered in this column. This column is reserved for use by products other than SQL*Plus.
LONG_VALUE SQL*Plus ignores this column. A NULL should be entered in this column. This column is reserved for use by products other than SQL*Plus.

Running the script PUPBLD.SQL as the SYSTEM user creates the PRODUCT_USER_
PROFILE table. The location of this file varies from platform to platform. For example, for Personal Oracle under Windows 95, the PUPBLD.SQL file is located in directory C:\ORAWIN95\DBS (C:\ORAWIN95 is the default ORACLE_HOME directory for Oracle products under Windows 95). On UNIX platforms, the file is located in directory $ORACLE_HOME/sqlplus/admin.

The PUPBLD.SQL command file is run during the installation of Oracle software and the creation of the starter database (in the script crdb on UNIX platforms and buildall on Windows 95/NT). If you do not create the starter database during the installation process, you have to run the PUPBLD.SQL script as user SYSTEM to create the PRODUCT_USER_PROFILE table.

If the PUPBLD.SQL file is not run, every time a user logs on to SQL*Plus, he or she will receive the following warning message:

Warning: Product user profile information not loaded!
Error in disabling roles in product user profile.

Disabling a SQL Command

To disable a SQL or SQL*Plus command for a given user, the SYSTEM user must insert a row that matches the column and values shown in Table 8.4.

Table 8.4 Values Needed to Restrict SQL*Plus Commands


Column Value
PRODUCT SQL*Plus
USERID Username of user being restricted
ATTRIBUTE Command name being restricted
CHAR_VALUE DISABLED

Page 137


Column Value
SCOPE NULL
NUMERIC_VALUE NULL
DATE_VALUE NULL
LONG_VALUE NULL

For example, to disable user SCOTT's access to the operating system from SQL*Plus, log onto SQL*Plus as SYSTEM and execute the SQL statement in Listing 8.12.

Listing 8.12 Disable HOST Command for User SCOTT

INSERT INTO
product_user_profile
(product, userid, attribute, scope, numeric_value,
  char_value, date_value, long_value)
VALUES
(`SQL*Plus', `SCOTT', `HOST', NULL, NULL, `DISABLED', NULL, NULL);

SQL*Plus reads restrictions from the PRODUCT_USER_PROFILE table when a user accesses SQL*Plus and enforces them for the duration of the user's session.

NOTE
If any changes affecting a user are made to the PRODUCT_USER_PROFILE table, they will not affect a user's current session. The user will see the effect of these changes the next time he or she logs on to SQL*Plus.

Reenabling a SQL Command

To reenable a command, delete the row containing the restriction. Listing 8.13 reenables the command that was disabled in Listing 8.12.

Listing 8.13 Reenable HOST Command for User SCOTT

DELETE [FROM] product_user_profile
WHERE userid='SCOTT' and attribute='HOST';

The following SQL*Plus commands (listed in alphabetical order) can be disabled using the PRODUCT_USER_PROFILE table:

Previous | Table of Contents | Next