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