Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

13.1 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
product_profile, but a
synonym named product_user_profile exists to
ensure backward compatibility.

13.1.1 Why Does the Product User Profile Exist?

Primarily, the aproduct
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
statements such as INSERT and DELETE that might damage production
data.

Real-world applications typically implement many business rules, edit
checks, and 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 risks data integrity. Because of this,
it's usually important to ensure that data are
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 have access to SQL*Plus, it
won't be too long before some curious and
adventurous soul will figure out that the same username and password
that works for the application will 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 statements. The
product user profile allows you to defend against this risk.

13.1.2 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 won't 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 useryou. Figure 13-1 shows the table, the view, the synonyms that
normally exist, and the relationships among them.

Table 13-1 summarizes the purpose of each of the
elements shown in Figure 13-1.

13.1.3 How the Product User Profile Works

When you log into an Oracle database using SQL*Plus, SQL*Plus issues
two SELECT statements against the product user profile. The first
SELECT 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 logged-on user. For example, the following two rows are returned
for a user who has been denied access to the DELETE and HOST
statements:

ATTRIBUTE CHAR_VALUE
---------- ---------------------
DELETE DISABLED
HOST DISABLED A second SELECT is issued against the product user profile 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 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 were returned:

CHAR_VALUE
---------------------
PAYROLL_ADMINISTRATOR
HR_ADMINISTRATOR There are two roles to be disabled. SQL*Plus turns 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
doesn't 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 this error message, see the section
Section 13.2.1
later in this chapter, or notify your DBA.

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.

13.1.4 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 Open DataBase Connectivity (ODBC) on every desktop, and every
application under the sun capable of connecting to an Oracle
database, securing SQL*Plus should be a small part of your overall
security plan. Someone with Microsoft Access, for example, can easily
connect to an Oracle database. Once that's done,
users will be able to edit, insert, and delete any data to which they
have access. Guard against this by implementing as much of your
security as possible at the database level.
You should be aware of some potential security
"holes" when using the product user
profile to secure SQL*Plus. Oracle is a complex product, and there is
often more than one way to accomplish any given task. You have to be
vigilant about the possible use of PL/SQL, for example. The next two
sections describe some issues to be aware of when setting limits with
the product user profile.

13.1.4.1 Issues related to PL/SQL

Any SQL statement you can issue
from the SQL*Plus prompt can be issued from a PL/SQL block. Using the
profile, you can restrict a user's access to an SQL
statement, 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 statement, for example. Using
the profile, you can restrict a SQL*Plus user from issuing the UPDATE
statement. 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 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 statements, you should restrict the user
from using PL/SQL.

Data definition language (DDL)
statements, such as GRANT or CREATE TABLE, are 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.

A new method of issuing dynamic SQL was implemented in the Oracle8 i Database release. This method allows users to code dynamic SQL by simply embedding the desired statements in a 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 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:

DECLARE BEGIN EXECUTE
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 doesn't have the system
privileges required to do these things. An alternative would be to
restrict access to the CREATE statement from SQL*Plus.

13.1.4.2 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 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 gennick.employee;
FROM gennick.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 gennick.employee;
EMPLOYEE_NAME EMPLOYEE_BILLING_RATE
---------------------------------------- ---------------------
Marusia Churai 169
Mykhailo Hrushevsky 135
Pavlo Virsky 99
. . .

In this example, the first SELECT failed because the
PAYROLL_ADMINISTRATOR role had been disabled by
SQL*Plus, and the user couldn't see the
employee table. All the user has to do is issue a
SET ROLE ALL command to enable the role, allowing him to see the
data. It is not even necessary for the user to know the name of the
specific role that needs to be enabled. For this reason, disabling
the SET ROLE command should usually go hand in hand with disabling
roles.

If you have disabled a role for a user and disabled the SET ROLE
command, you should give some thought to disabling PL/SQL as well.
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
knowledgeable and determined user, but it can be done. Here is an
example:

SQL> SELECT employee_name, employee_billing_rate
2 FROM gennick.employee;
FROM gennick.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 gennick.employee;
EMPLOYEE_NAME EMPLOYEE_BILLING_RATE
---------------------------------------- ---------------------
Marusia Churai 169
Mykhailo Hrushevsky 135
Pavlo Virsky 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 that PL/SQL is sent to the
database for execution; SQL*Plus does not look inside a block.