Release 8.1.7
Part Number A82950-01
Library |
Product |
Contents |
Index |
| SQL*Plus
User's Guide and Reference
Release 8.1.7 Part Number A82950-01 |
|
This appendix describes the available methods for controlling access to database tables and SQL*Plus commands. The available methods for security fall into three broad categories:
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.
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.
The PRODUCT_USER_PROFILE table applies only to the local
database. If accessing objects on a remote database via a database link,
the PRODUCT_USER_PROFILE for the remote database does not apply. The remote
database cannot extract the username and password from the database link
in order to determine that user's profile and privileges.
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 loaded.
|
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
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: |
|
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 the section "Administration" later in this chapter for a list of SQL and SQL*Plus commands you can disable. See the section "Roles" in this chapter for information on how to disable a role. |
|
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. See "Roles" below for information on how to disable a role. |
|
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. |
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.
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 SCOPE NUMBERIC CHAR DATE VALUE VALUE VALUE ------- ------ --------- ----- -------- ------ ----- SQL*Plus SCOTT HOST DISABLED SQL*Plus % INSERT DISABLED SQL*Plus % UPDATE DISABLED SQL*Plus % DELETE DISABLED
To reenable commands, delete the row containing the restriction.
You can disable the following SQL*Plus commands:
|
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.
|
|
Note: Disabling BEGIN and DECLARE does not prevent the use of
the SQL*Plus EXECUTE command. EXECUTE must be disabled separately.
|
From SQL*Plus, users can submit any SQL command. In certain
situations, this can cause security problems. Unless you take proper precautions,
a user could use SET ROLE to access privileges obtained via an application
role. With these privileges, they might issue SQL statements from SQL*Plus
that could wrongly change database tables.
To prevent application users from accessing application roles
in SQL*Plus, you can use PRODUCT_USER_PROFILE to disable the SET ROLE command.
This allows a SQL*Plus user only those privileges associated with the roles
enabled when they started SQL*Plus. For more information about the creation
and usage of user roles, see your Oracle8i SQL Reference and Oracle8i
Administrator's Guide.
To disable a role for a given user, insert a row in PRODUCT_USER_PROFILE
containing the user's username in the Userid column, "ROLES" in the Attribute
column, and the role name in the Char_Value column.
The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:
PRODUCT USERID ATTRIBUTE SCOPE NUMBERIC CHAR DATE VALUE VALUE VALUE ------- ------ --------- ----- -------- ------ ----- SQL*Plus SCOTT ROLES ROLE1 SQL*Plus PUBLIC ROLES ROLE2
During login, these table rows are translated into the command
SET ROLE ALL EXCEPT ROLE1, ROLE2
To ensure that the user does not use the SET ROLE command
to change their roles after login, you can disable the SET ROLE command.
See "Disabling SET ROLE" earlier in this appendix.
To reenable roles, delete the row containing the restriction.
To provide for the security of your database tables in Oracle8i
using SQL commands, you can create and control access to roles.
By creating a role and then controlling who has access to
it, you can ensure that only certain users have access to particular database
privileges.
Roles are created and used with the SQL CREATE, GRANT, and SET commands:
Like the Product User Profile table, the RESTRICT option
allows you to disable certain commands that interact with the operating
system. However, commands disabled with the -RESTRICT option are disabled
even when no connection to a server exists, and remain disabled until SQL*Plus
terminates.
The following table shows which commands are disabled in
each restriction level.
For more information about the RESTRICT option, see the SQLPLUS
-R[ESTRICT] {1|2|3} command
|
Copyright © 2000 Oracle Corporation. All Rights Reserved. |
|