|SQL*Plus® User's Guide and Reference
Part Number B12170-01
This chapter describes the available methods for controlling access to database tables, SQL*Plus and iSQL*Plus commands, and iSQL*Plus access. It covers the following topics:
SQL*Plus uses the PRODUCT_USER_PROFILE (PUP) table, 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 the PUP table to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus—not Oracle Database—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 the PUP table when a user logs in to SQL*Plus and maintains those restrictions for the duration of the session. Changes to the PUP table will only take effect the next time the affected users log in to SQL*Plus.
When SYSTEM, SYS, or a user authenticating with SYSDBA or SYSOPER privileges connects or logs in, SQL*Plus does not read the PUP table. Therefore, no restrictions apply to these users.
The PUP table applies only to the local database. If accessing objects on a remote database through a database link, the PUP table 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 the PUP table by running the script 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 your DBA for more information.
Note:If the table is created incorrectly, all users other than privileged users will see a warning when connecting to Oracle Database that the PUP table information is not loaded.
The PUP table has the following columns:
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
The following list describes each column in the PUP table:
|PRODUCT||Must contain the product name (in this case "SQL*Plus"). You cannot enter wildcards or NULL in this column.|
|USERID||Must contain the username (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 to disable (for example, RUN). If you are disabling a role, it must contain the character string "ROLES". You cannot enter a wildcard. See "PUP Table Administration" for a list of SQL and SQL*Plus commands you can disable. See "Creating and Controlling Roles" for information on how to disable a role.|
|SCOPE||Not used, it is recommended that you enter NULL. Other products may store specific file restrictions or other data in this column.|
|NUMERIC_VALUE||Not used, it is recommended that you enter NULL. 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 "Disabling Commands with SQLPLUS -RESTRICT" for information on disabling a role.|
|DATE_VALUE||Not used, it is recommended that you enter NULL. Other products may store DATE values in this column.|
|LONG_VALUE||Not used, it is recommended that you enter NULL. Other products may store LONG values in this column.|
The DBA username SYSTEM owns and has all privileges on the PUP table. Other Oracle Database usernames should have only SELECT access to this table, which enables a view of restrictions for that username and those restrictions assigned to PUBLIC. The script PUPBLD.SQL, when run, grants SELECT access on the PUP table 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 LONG VALUE VALUE VALUE VALUE ------- ------ --------- ----- -------- ------ ----- ----- SQL*Plus HR HOST DISABLED SQL*Plus % INSERT DISABLED SQL*Plus % UPDATE DISABLED SQL*Plus % DELETE DISABLED
|SQL*Plus Commands That Can Be Disabled|
|ARCHIVE LOG||DESCRIBE||START(@, @@)|
|SQL Commands That Can Be Disabled|
Example 10-1 Setting Restrictions in the PUP Table
This is an example of how to insert a row into the PUP table to restrict the user HR from using the SELECT statement:
Log in as SYSTEM with the command
Insert a row into the PUP table with the command:
INSERT INTO PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'HR', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
Connect as HR and try to SELECT something:
CONNECT HR/your_password; SELECT * FROM EMP_DETAILS_VIEW;
This command causes the following error message:
SP2-0544: Command SELECT disabled in Product User Profile
To delete this row and remove the restriction from the user HR, CONNECT again as SYSTEM and enter:
DELETE FROM PRODUCT_USER_PROFILE WHERE USERID = 'HR';
You can use SQL commands to create and control access to roles to provide security for your database tables. 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:
To create a role, you use the CREATE command. You can create roles with or without passwords.
To grant access to roles, you use the GRANT command. In this way, you can control who has access to the privileges associated with the role.
To access roles, you use the SET ROLE command. If you created the role with a password, the user must know the password in order to access the role.
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 through 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 the PUP table to disable the SET ROLE command. You also need to disable the BEGIN and SQL*Plus EXECUTE commands to prevent application users setting application roles through a PL/SQL block. This gives 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 Oracle Database SQL Reference and Oracle Database Administrator's Guide.
Note:When you enter "PUBLIC" or "%" for the Userid column, you disable the role for all users. You should only use "%" or "PUBLIC" for roles which are granted to "PUBLIC". If you try to disable a role that has not been granted to a user, none of the roles for that user are disabled.
The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:
PRODUCT USERID ATTRIBUTE SCOPE NUMERIC CHAR DATE LONG VALUE VALUE VALUE VALUE ------- ------ --------- ----- -------- ------ ----- ----- SQL*Plus HR 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" for more information.
Like the Product User Profile table, the RESTRICT option enables 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.
|Command||Level 1||Level 2||Level 3|
For more information about the RESTRICT option, see the SQLPLUS RESTRICT Option.
Some operating systems allow any user to see what programs are being run. If the display also shows command-line arguments, it may be possible to view the usernames and passwords of other SQL*Plus users.
For example, on many UNIX or Linux systems the ps command shows program arguments. To stop passwords being displayed depends on how you use SQL*Plus.
To run SQL*Plus interactively, always wait for SQL*Plus to prompt for the connection information.
To run a batch SQL script from a UNIX shell script, set environment variables MYUSERNAME and MYPASSWORD to the appropriate values. Run a shell script containing:
sqlplus /nolog <<EOF connect $MYUSERNAME/$MYPASSWORD select ... EOF
To run a batch SQL script, hard code the username and password as the first line of the SQL script. Then call the script with:
When SQL*Plus is started like this, it uses the first line of the script as the username/password@connection_identifier string.
Any file on your computer storing a username and password needs to be secured from non-authorized access.
There are two main areas to consider for security and user authentication when using iSQL*Plus:
It is useful to note that in iSQL*Plus you cannot access the middle tier operating system to run commands such as HOST, EDIT and SPOOL which depend on operating system access.
In iSQL*Plus, security for the connection between the web browser and the Application Server is provided by standard HTTPS. It enables secure listener connections with an Oracle Database-provided encryption mechanism through the Secure Sockets Layer (SSL).
The Oracle Net connection between the iSQL*Plus Server and Oracle Database provides the same security as in previous client server architectures. It is recommended that you enable an Oracle Net listener password if possible. For more information about Oracle Net connection security, see the Oracle Net Services Administrator's Guide and the Oracle Advanced Security Administrator's Guide.
You can enable security for the connection between the web browser and the iSQL*Plus Application Server using SSL.
To enable SSL for the iSQL*Plus Application Server, see "Enabling SSL with iSQL*Plus".
For detailed information about SSL, see the Oracle Application Server Containers for J2EE Security Guide.
Connect as a normal user.
Requires an Oracle Database account username and password entered in the iSQL*Plus Login screen.
Connect as a SYSDBA or SYSOPER privileged user.
Requires an Oracle Database account username and password entered in the iSQL*Plus DBA Login screen, and an Application Server authentication username and password entered in a separate dialog.
To connect with SYSDBA or SYSOPER privileges, your username and password must be added to the iSQL*Plus authentication file for the iSQL*Plus Application Server. To enable DBA access, see "Enabling iSQL*Plus DBA Access".
The restricted database parameter limits the databases that users can access in iSQL*Plus. When enabled, a dropdown list of available databases is displayed in place of the Connection Identifier text field on the Login screen. This enables greater security for iSQL*Plus Servers in hosted environments.
Connection identifiers are listed in the order defined by the iSQLPlusConnectIdList in the configuration file, web.xml.
For more information about restricted database access, see "Enabling or Disabling Restricted Database Access".
The following notes may assist you in understanding and configuring iSQL*Plus:
Once you have successfully logged in with SYSDBA or SYSOPER privileges and authenticated with your Application Server authentication username and password, you may not be required to re-authenticate to the Application Server until you restart your browser. However, you are still required to log in with your Oracle Database username and password.
See "PRODUCT_USER_PROFILE Table" for more information about PUP tables.