Oracle SQL*Plus: The Definitive Guide

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
     
 

This book is the definitive guide to SQLPlus. If you want to take best advantage of the power and flexibility of this popular Oracle tool, you need this book.SQLPlus is an interactive query tool that is ubiquitous in the Oracle world. It is present in every Oracle installation and is available to almost every Oracle developer and database administrator.

…  See more details below

Overview

This book is the definitive guide to SQLPlus. If you want to take best advantage of the power and flexibility of this popular Oracle tool, you need this book.SQLPlus is an interactive query tool that is ubiquitous in the Oracle world. It is present in every Oracle installation and is available to almost every Oracle developer and database administrator. SQLPlus has been shipped with Oracle since at least version 6. It continues to be supported and enhanced with each new version of Oracle, including Oracle8 and Oracle8i. It is still the only widely available tool for writing SQL scripts. Despite this wide availability and usage, few developers and DBAs know how powerful SQLPlus really is.This book introduces SQLPlus, includes a quick reference to all of its syntax options, and, most important, provides chapters that describe, in step-by-step fashion, how to perform all of the tasks that Oracle developers and DBAs want to perform with SQLPlus — and maybe some they didn't realize they COULD perform with SQLPlus. You will learn how to write and execute script files, generate ad hoc reports, extract data from the database, query the data dictionary tables, customize your SQLPlus environment, and use the SQLPlus administrative features (new in Oracle8i).This book is an indispensable resource for readers who are new to SQLPlus, a task-oriented learning tool for those who are already using it, and a quick reference for every user.A table of contents follows:

  • Preface
  • Introduction to SQLPlus
  • Interacting with SQLPlus
  • Generating Reports with SQLPlus
  • Writing SQLPlus Scripts
  • Extracting Data with SQLPlus
  • Exploring Your Database with SQLPlus
  • Advanced Scripting
  • Tuning and Timing
  • The Product User Profile
  • Administration with SQLPlus
  • Customizing Your SQLPlus Environment
Appendices
  • A. SQLPlus Command Reference
  • B. Connect Strings and the SQLPlus Command

Read More

Product Details

ISBN-13:
9781565925786
Publisher:
O'Reilly Media, Incorporated
Publication date:
03/17/1999
Edition description:
Older Edition
Pages:
528
Product dimensions:
7.02(w) x 9.16(h) x 1.06(d)

Read an Excerpt


Chapter 9:The Product User Profile

In addition to the standard database security Oracle provides and enforces for all database objects -- tables, views, and the like -- Oracle also provides an application security scheme for SQL*Plus. This allows you to control the specific commands a SQL*Plus user is allowed to execute. At the core of the SQL*Plus application security scheme is the product user profile.

What Is the Product User Profile?

The product user profile is an Oracle table, owned by the SYSTEM user, that contains a list of SQL*Plus command restrictions by user. The table may contain role restrictions as well. The name of this table used to be PRODUCT_USER_PROFILE. Now it is just PRODUCT_PROFILE, but a synonym named PRODUCT_USER_PROFILE exists to ensure backwards compatibility.

Why Does the Product User Profile Exist?

Primarily, the product user profile enables you to give end users access to SQL*Plus for reporting and ad-hoc query purposes, yet restrict them from using SQL*Plus commands such as INSERT, DELETE, etc., that might damage production data.

Real-world applications typically implement a large number of business rules, edit checks, and even security at the application level rather than within the database. Modifying the data using an ad-hoc tool, such as SQL*Plus, bypasses the rules and puts data integrity at risk. Because of this, it's usually important to ensure that data is modified through the application, where the rules can be enforced.

If you give people an application that requires a database username and password, and those people also have access to SQL*Plus, it won't be too long before some curious and adventurous soul will figure out that the same userid and password that works for the application will also work for SQL*Plus. Next thing you know, you will have someone running ad-hoc queries that haven't been tuned, or, worse yet, you may have someone issuing ad-hoc INSERT, UPDATE, or DELETE commands. The product user profile allows you to defend against this risk.

The PRODUCT_PROFILE Table

The PRODUCT_PROFILE table is owned by SYSTEM and has the following structure:

 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 

Most users will not have SELECT access on the table itself, so if you aren't logged in as SYSTEM, you may not be able to DESCRIBE the table. Instead, you should have access to a view on the table named PRODUCT_PRIVS. This view returns all the records from the PRODUCT_PROFILE table that apply to the currently logged-on user -- you. Figure 9-1 shows the table, the view, the synonyms that normally exist, and the relationship between them.

Table 9-1 describes the purpose of each of the element shown in Figure 9-1.

How the Product User Profile Works

Table 9-1: Product User Profile Elements

Element

Who Sees It?

Purpose

PRODUCT_PROFILE table

SYSTEM

This is the product user profile table itself.

PRODUCT_USER_PROFILE

private synonym

SYSTEM

Provides backwards compatibility, because the table name used to be PRODUCT_USER_PROFILE.

PRODUCT_PRIVS view

All users

A view that shows each user the restrictions that apply to him or her.

PRODUCT_USER_PROFILE

public synonym

All users

A public synonym pointing to the view.

PRODUCT_PROFILE

public synonym

All users

A public synonym pointing to the view.

When you log into an Oracle database using SQL*Plus, SQL*Plus will issue two SELECT statements against the product user profile. The first SELECT statement retrieves a list of command restrictions and looks like this:

SELECT attribute, scope,         numeric_value, char_value, date_value        FROM system.product_privs    WHERE (UPPER('SQL*Plus') LIKE UPPER(product))    AND (UPPER(user) LIKE UPPER(userid)) 

The two fields of interest to SQL*Plus are ATTRIBUTE and CHAR_VALUE. Together, these columns tell SQL*Plus which commands to disable for the currently logged on user. For example, the following two rows will be returned for a user who has been denied access to the DELETE and HOST commands:

ATTRIBUTE  CHAR_VALUE ---------- --------------------- DELETE     DISABLED HOST       DISABLED 

A second SELECT statement is issued against the product user profile in order to retrieve any role restrictions for the user. Here's what that statement looks like:

SELECT char_value    FROM system.product_privs   WHERE (UPPER('SQL*Plus') LIKE UPPER(product))     AND ( (UPPER(user) LIKE UPPER(userid))           OR (UPPER(userid) = 'PUBLIC'))     AND (UPPER(attribute) = 'ROLES') 

In this case, the CHAR_VALUE column returns a list of roles that are to be disabled whenever the user connects using SQL*Plus. SQL*Plus then disables these roles with a SET ROLE command. By way of example, assume that the following data was returned:

CHAR_VALUE --------------------- PAYROLL_ADMINISTRATOR HR_ADMINISTRATOR 

There are two roles to be disabled. SQL*Plus will turn them off by issuing the following command to Oracle:

SET ROLE ALL EXCEPT payroll_administrator, hr_administrator 

This establishes the default condition for the user of having those roles turned off. The user may be able to issue another SET ROLE command to turn them back on again, but the starting condition is that the roles are off.

If SQL*Plus's attempt to query the product user profile results in an error, perhaps because the table does not exist, you will see the following message from SQL*Plus:

Error accessing PRODUCT_USER_PROFILE Warning:  Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM Connected. 

If you do happen to get the above error message, see the section titled "Creating the Profile Table" later in this chapter, or notify your DBA.

NOTE:  

The SYSTEM user presents a special case. If you log into Oracle as SYSTEM, SQL*Plus detects this, and does not query the product user profile. Therefore, you can never restrict what the SYSTEM user is allowed to do.

Product User Profile Limitations

The product user profile is used for application security. The application is SQL*Plus. No other applications respect the limitations set in the profile. In today's world, with ODBC (Open DataBase Connectivity) on every desktop, and every application under the sun capable of connecting to an Oracle database, securing SQL*Plus should be only a small part of your overall security plan. It is relatively easy for someone with Microsoft Access, for example, to connect to an Oracle database. Once that's done, that user will be able to freely edit, insert, and delete any data to which they have access. Guard against this by implementing as much of your security at the database level as possible.

There are also some potential security "holes" you should be aware of when using the product user profile to secure SQL*Plus. Oracle is a complex product, and often there is more than one way to accomplish any given task. You have to be particularly vigilant about the possible use of PL/SQL. The next two sections describe some known issues to be aware of when setting limits with the product user profile.

Issues related to PL/SQL

Any SQL command you can issue from the SQL*Plus prompt can also be issued from a PL/SQL block. Remember this. It's important. Using the profile, you can restrict a user's access to a SQL command, but it may be possible to get around that restriction with PL/SQL. For this reason, you may want to restrict access to PL/SQL as well.

Take the UPDATE command, for example. Using the profile, you can restrict a SQL*Plus user from issuing the UPDATE command. Should the user try an update, an error will be returned, as the following example shows:

SQL> UPDATE sqlplus.employee invalid command: update 

This is all well and good, but the update can easily be coded in PL/SQL. Here's how:

SQL> BEGIN   2  UPDATE sqlplus.employee   3     SET employee_billing_rate = 300   4   WHERE employee_id = 101;   5  END;   6  /   PL/SQL procedure successfully completed. 

That was certainly easy enough, wasn't it? So much for your security. If you need to restrict a user from issuing any INSERT, UPDATE, DELETE, or SELECT commands, you should also restrict the user from using PL/SQL.

Data definition language (DDL) commands, such as GRANT or CREATE TABLE, are a bit more difficult to code from PL/SQL, but they can be done. As long as a user has EXECUTE access to the DBMS_SQL package, you should consider the possibility that the user may be able to code dynamic SQL statements.

NOTE:  

Beginning with Oracle8i, a new version of dynamic SQL is being implemented. This will allow users to code dynamic SQL by simply embedding the desired commands in the PL/SQL block. Thus, the lack of EXECUTE privileges on the DBMS_SQL package won't necessarily stop the user from being able to issue dynamic SQL.

There are two obvious ways to execute PL/SQL from SQL*Plus. One way is to type in a PL/SQL block at the command prompt and execute it. The other way is to use the SQL*Plus EXECUTE command. To restrict a user's access to PL/SQL, you must disable the following three SQL*Plus commands:

Leave any one of the above commands enabled, and you might as well leave them all enabled; the user will still have full access to PL/SQL. There are even less obvious ways to execute PL/SQL, and you may want to guard against these as well. The user could create a stored function and execute that from a SELECT statement, or the user could create a trigger on a table and then fire that trigger. The easiest way to guard against either of these possibilities is to ensure that the user does not have the system privileges required to do these things. An alternative would be to also restrict access to the CREATE command from SQL*Plus.

Issues related to roles

When you disable a role, SQL*Plus turns that role off when the user first connects, but that doesn't prevent the user from turning the role on again. The user can simply issue a SET ROLE command of his own, as the following example shows, turning the desired role back on:

SQL> SELECT employee_name, employee_billing_rate   2    FROM sqlplus.employee;   FROM jeff.employee                * ERROR at line 2: ORA-00942: table or view does not exist     SQL> SET ROLE ALL;   Role set.   SQL> SELECT employee_name, employee_billing_rate   2    FROM jeff.employee;   EMPLOYEE_NAME                            EMPLOYEE_BILLING_RATE ---------------------------------------- --------------------- Jonathan Gennick                                           300 Jenny Gennick                                              135 Jeff Gennick                                                99 ... 

In this example, the first SELECT failed because the PAYROLL_ADMINISTRATOR role had been disabled by SQL*Plus, and consequently the user could not see the EMPLOYEE table. Notice, though, that all the user had to do was issue a SET ROLE ALL command in order to enable the role, allowing him to see the data. It was not even necessary for the user to know the name of the specific role that needed to be enabled. For this reason, disabling the SET ROLE command should usually go hand in hand with disabling roles.

If you've disabled a role for a user, and also disabled the SET ROLE command, you should give some thought to disabling PL/SQL as well. At the very least, you might want to revoke EXECUTE privileges on the DBMS_SQL package. The reason for this is that by using dynamic SQL, the SET ROLE command can be executed from within a PL/SQL block. Admittedly, this would take a very knowledgeable and determined user, but it can be done. Here is an example:

SQL> SELECT employee_name, employee_billing_rate   2    FROM sqlplus.employee;   FROM sqlplus.employee                * ERROR at line 2: ORA-00942: table or view does not exist     SQL> SET ROLE ALL; invalid command: set role SQL>  SQL> DECLARE   2    set_role_cursor   INTEGER;   3    rows_affected     INTEGER;   4  BEGIN   5    set_role_cursor := DBMS_SQL.OPEN_CURSOR;   6    DBMS_SQL.PARSE (set_role_cursor,   7                    'SET ROLE payroll_administrator',   8                    DBMS_SQL.NATIVE);   9    rows_affected := DBMS_SQL.EXECUTE(set_role_cursor);  10    DBMS_SQL.CLOSE_CURSOR(set_role_cursor);  11  END;  12 /   PL/SQL procedure successfully completed.   SQL> SELECT employee_name, employee_billing_rate   2    FROM sqlplus.employee;   EMPLOYEE_NAME                            EMPLOYEE_BILLING_RATE ---------------------------------------- --------------------- Jonathan Gennick                                           300 Jenny Gennick                                              135 Jeff Gennick                                                99 ... 

SQL*Plus honors the restriction against using the SET ROLE command from the SQL*Plus prompt, but it has no way of knowing what is going on inside a PL/SQL block. Remember, PL/SQL is sent to the database for execution. SQL*Plus does not look inside a block.

Using the Product User Profile

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

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

Creating the Profile Table

Oracle supplies a script named PUPBLD.SQL that creates the table, views, and synonyms shown earlier in this chapter in Figure 9-1. On Windows-based systems, the script can be found in the DBS directory, underneath the Oracle home directory. On Windows NT, the full path and filename for the script is:

C:\ORANT\DBS\PUPBLD.SQL 

On Unix systems, the script will be in the SQL*Plus product's admin directory. For example, under HP-UX, and running Oracle 7.3.3, the full path and filename for the script is:

$ORACLE_HOME/sqlplus/admin/pupbld.sql 

In some instances, the SQL*Plus directory will be named plusXX instead of sqlplus. The XX in the name represents the Oracle version number, so with Oracle 7.1, the directory would be named plus71.

PUPBLD.SQL should be executed while logged in as user SYSTEM. Executing it while logged in as some other user will result in the profile table being created in the wrong schema, and may also result in a few privilege violations as it tries to create 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 nothing to worry about and are simply the result of the way Oracle wrote the script. If you now were to run the script again, you would see a different set of errors. Any errors returned because an object already exists, or because an object does not exist, may safely be ignored.

Limiting Access to Commands

To limit access to a command, you simply need to insert a row into the PRODUCT_PROFILE table. This row tells SQL*Plus which command to disable and for what user. To reenable a command, simply delete the row with the restriction. The following sections show you how to do this.

Commands that can be disabled

There is a specific list of commands that may be disabled using the product user profile. These are listed in Table 9-2.

Table 9-2: Commands You Can Disable Using the Product User Profile (continued)

SQL*Plus

SQL

PL/SQL

CONNECT

ALTER

BEGIN

COPY

ANALYZE

DECLARE

EDIT

AUDIT

 

EXECUTE

CREATE

 

EXIT

DELETE

 

GET

DROP

 

HOST[1]

GRANT

 

QUIT

INSERT

 

PASSWORD

LOCK

 

RUN

NOAUDIT

 

SAVE

RENAME

 

SET[2]

REVOKE

 

SPOOL

SELECT

 

START[3]

SET ROLE

 

 

SET TRANSACTION

 

 

TRUNCATE

 

 

UPDATE

 

Disabling a command

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

where:

`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, exactly 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 that are used with the LIKE predicate, the percent sign and the underscore. A value of `%' would make the restriction apply to all users.
`COMMAND_NAME'
This is the name of the command you wish to disable. It should always be uppercase.
`DISABLED'
The keyword `DISABLED' must be stored in the CHAR_VALUE field.

Fields in the PRODUCT_PROFILE table other than the four listed above are not used by SQL*Plus. They should be left alone, and will default to NULL. The following example will disable the DELETE command 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 even disable a command 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 about the possibility that you might create a new username in the future that inadvertently matches some existing restriction. Wildcards also make it difficult to remove a restriction for just 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.

Re-enabling a command

To remove a restriction you have created, simply delete that row from the PRODUCT_PROFILE table. For example, to once again allow all users to issue the HOST command, issue the following command:

DELETE    FROM product_profile  WHERE product='SQL*Plus'     AND userid='%'    AND char_value='HOST' 

Limiting Access to Roles

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

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

where:

`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, exactly as shown here.
`USERNAME'
Is the username of the user you are restricting. It should always be uppercase. You can wildcard the username when restricting a role, but you must be very careful when doing so.
`ROLES'
Instead of a command, the keyword ROLES in this field tells SQL*Plus that you are restricting a role.
`ROLE_NAME'
Is the name of the role to disable.

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

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

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.

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 currently defined in the PRODUCT_PROFILE table. The second section provides a script that will show 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.

Listing all restrictions

The following script will generate a report showing all the command and role restrictions defined in the PRODUCT_PROFILE table:

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> @report_product_profile 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 

Listing restrictions for a particular user

To find out what restrictions apply to any one user, you must keep in mind that the USERID field in the PRODUCT_PROFILE table may contain wildcards. The following script will prompt you for a username, then display a list of all the disabled commands and roles for that user. The queries involved use the LIKE operator to account for any possible wildcards.

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

SQL> @show_product_profile 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. 

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

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.

Disabling the START command also disables @ and @@.

-- End of Chapter 9 --

Read More

Meet the Author

Jonathan Gennick is an O'Reilly & Associates editor specializing in database and programming titles. Prior to joining O'Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital's Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it's set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQLLoader (O'Reilly & Associates, 2001); Oracle SQLPlus Pocket Reference (O'Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly & Associates, 2002) and editing Practical PostgreSQL (O'Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews University in Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at jonathan@gennick.com, and you can learn more about him personally by visiting his website at http://gennick.com.

Read More

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >