10
SQL*Plus Security

This chapter describes the available methods for controlling access to database tables and SQL*Plus commands. It covers the following topics:

• PRODUCT_USER_PROFILE Table
• Disabling SQL*Plus, SQL, and PL/SQL Commands
• Creating and Controlling Roles
• Disabling Commands with SQLPLUS -RESTRICT
• iSQL*Plus Security

PRODUCT_USER_PROFILE Table

Various Oracle products use 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--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 via 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.

Creating the PUP Table

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 the Oracle installation and user's manuals provided for your operating system or 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 that the PUP table information is not loaded.

PUP Table Structure

The PUP table has 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

Description and Use of PUP Columns

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 (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: HR CLASS1 CLASS% (all users whose names start with CLASS) % (all users)
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.

PUP Table Administration

The DBA username SYSTEM owns and has all privileges on the PUP table. Other Oracle usernames should have only SELECT access to this table, which allows 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.

Disabling SQL*Plus, SQL, and PL/SQL Commands

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   HR      HOST                            DISABLED
SQL*Plus   %       INSERT                          DISABLED
SQL*Plus   %       UPDATE                          DISABLED
SQL*Plus   %       DELETE                          DISABLED

To re-enable commands, delete the row containing the restriction.

You can disable the following SQL*Plus commands:

COPY	HOST	SET
EDIT	PASSWORD	SPOOL
EXECUTE	QUIT	START
EXIT	RUN
GET	SAVE

Notes: Disabling HOST also disables your operating system's alias for HOST, such as $ on VMS, and ! on UNIX Disabling the SQL*Plus SET command will also disable the SQL SET CONSTRAINTS, SET ROLE and SET TRANSACTION commands. Disabling the SQL*Plus START command will also disable the SQL*Plus @ and @@ commands.

You can also disable the following SQL commands:

ALTER	GRANT	SET CONSTRAINTS
ANALYZE	INSERT	SET ROLE
AUDIT	LOCK	SET TRANSACTION
CONNECT	NOAUDIT	TRUNCATE
CREATE	RENAME	UPDATE
DELETE	REVOKE
DROP	SELECT

You can also disable the following PL/SQL commands:

BEGIN

DECLARE

Note: Disabling BEGIN and DECLARE does not prevent the use of the SQL*Plus EXECUTE command. EXECUTE must be disabled separately.

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:

1. Log in as SYSTEM with the command

   
   
   SQLPLUS SYSTEM/your_password
   
   

2. 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);
   

3. 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: invalid command: SELECT 
   

4. To delete this row and remove the restriction from the user HR, CONNECT again with SYSDBA privileges as SYSTEM and enter:

   
   
   DELETE FROM PRODUCT_USER_PROFILE WHERE USERID = 'HR'; 
   

Creating and Controlling Roles

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.

For more information about roles, see your Oracle9i SQL Reference, your Oracle9i Database Administrator's Guide, and your Oracle9i Database Concepts manual.

Disabling SET 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 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 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 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 Oracle9i SQL Reference and Oracle9i Database Administrator's Guide.

Disabling User Roles

To disable a role for a given user, insert a row in the PUP table containing the user's username in the Userid column, "ROLES" in the Attribute column, and the role name in the Char_Value column.

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

To re-enable roles, delete the row containing the restriction.

Disabling Commands with SQLPLUS -RESTRICT

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.

Command	Level 1	Level 2	Level 3
EDIT	disabled	disabled	disabled
GET			disabled
HOST	disabled	disabled	disabled
SAVE		disabled	disabled
SPOOL		disabled	disabled
START			disabled
STORE		disabled	disabled

Notes: Disabling HOST also disables your operating system's alias for HOST, such as $ on VMS, and ! on UNIX Disabling the SQL*Plus START command will also disable the SQL*Plus @ and @@ commands.

For more information about the RESTRICT option, see the SQLPLUS "RESTRICT Option".

iSQL*Plus Security

There are two main areas to consider for security and user authentication when using iSQL*Plus:

• The HTTP protocol connection between the web browser and the Oracle HTTP Server.
• The Oracle Net connection between the iSQL*Plus Server (in the Oracle HTTP Server) and Oracle9i.

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 Oracle HTTP Server is provided by standard HTTPS, which is fully supported by Oracle. It enables secure listener connections with an Oracle-provided encryption mechanism via the Secure Sockets Layer (SSL). It can be implemented when installing the Oracle HTTP Server by installing the mod_ssl module. For detailed information about implementing HTTPS security in Oracle, see the Oracle Advanced Security Administrator's Guide.

The Oracle Net connection between the iSQL*Plus Server and Oracle9i provides the same security as in previous client server architectures. For more information about Oracle Net connection security, see the Oracle9i Net Services Administrator's Guide and the Oracle Advanced Security Administrator's Guide.

Administration Privileges

There are three modes of access to iSQL*Plus:

• 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 Oracle HTTP Server authentication username and password entered in a separate dialog.

• Generate the iSQL*Plus Server statistics report

  Requires an Oracle HTTP Server authentication username and password.

Enabling Server Authentication for Users

By default, Oracle HTTP Server authentication is enabled for SYSDBA and SYSOPER privileged users. You may also want to limit who can access iSQL*Plus by enabling Oracle HTTP Server authentication for normal user logins. You can enable Oracle HTTP Server authentication for normal user connections by editing the isqlplus.conf file and changing the following lines:

<Location /isqlplus>
  SetHandler fastcgi-script
  Order deny,allow
  Allow from all
</Location>

to:

<Location /isqlplus>
  SetHandler fastcgi-script
  Order deny,allow
  AuthType Basic
  AuthName 'iSQL*Plus'
# The following line for UNIX, comment out the Windows line.
# AuthUserFile $ORACLE_HOME/sqlplus/admin/iplus.pw 
# The following line for Windows, comment out the UNIX line.
  AuthUserFile %ORACLE_HOME%\sqlplus\admin\iplus.pw
  Require valid-user
</Location>

For information about editing the isqlplus.conf file, see "Session Integrity".

iplus.pw is suggested as the file to contain the Oracle HTTP Server authentication usernames and passwords for user connections. Now, whenever a user connection is requested, users are not only required to enter their Oracle9i username and password, but they are also prompted to enter an Oracle HTTP Server authentication username and password.

Enabling or Disabling Restricted Database Access

You may want to limit the databases that users can access in iSQL*Plus to a restricted list. When restricted database access has been enabled, a dropdown list of available databases is displayed in place of the Connection Identifier text field on the Login screen. This allows greater security for iSQL*Plus Servers in hosted environments. Connection identifiers are listed in the order defined in iSQLPlusConnectIdList.

You can edit the isqlplus.conf file to enforce restricted database access by changing the following line:

FastCgiServer ... -initial-env "iSQLPlusConnectIdList=SID1, SID2,..."

where SID1, SID2,, ... is a comma separated list of Oracle Net connection identifiers specifying permitted databases. For example:

FastCgiServer ... -initial-env "iSQLPlusConnectIdList=ABC1, PROD2, DEV3"

For information about editing the isqlplus.conf file, see "Session Integrity".

While no quotes or embedded whitespace is allowed in a connection identifier, quotes are required around the entire iSQLPlusConnectIdList= argument as shown. Connection identifiers are case insensitive, and each connection identifier listed in the argument should be identical to an alias in the tnsnames.ora file.

There are several initialization parameters that are set in one statement in the isqlplus.conf file. You should leave existing elements intact.

Once set, all connections made through the Login screen, all Dynamic Reports and any connections attempted with the CONNECT command are refused unless the connection is to one of the databases in the restricted list.

Similarly, if SET INSTANCE is used, the connection identifier defined must match an entry in iSQLPlusConnectIdList or the connection is refused.

If no connection identifier is given, or if the one given does not match an entry in iSQLPlusConnectIdList, the database connection is refused and the following error occurs:

SP2-0884: Connection to database database_name is not allowed

Enabling DBA Access

SYSDBA and SYSOPER privileged connections are not available to iSQL*Plus users automatically, but must be set up the system administrator. To connect with SYSDBA or SYSOPER privileges, or to generate the iSQL*Plus Server Statistics report, your username and password must be added to the iSQL*Plus authentication file for the Oracle HTTP Server. For example, on Windows, the authentication file is installed, with no user entries, at %ORACLE_HOME%\sqlplus\admin\iplusdba.pw. The username and password used in the authentication file are independent of the Oracle9i username and password.

Warning: If the Oracle HTTP Server is started by a user who is a member of the "dba" or "oper" groups on UNIX, or who is a member of the ORA_DBA, ORA_OPER, ORA_SID_DBA, or ORA_SID_OPER groups on Windows, the iSQL*Plus DBA URL is automatically authenticated for Oracle9i by the operating system. To avoid this, start the Oracle HTTP Server as a user who is not a member of these operating system groups. Users of the iSQL*Plus DBA URL can authenticate for Oracle9i using '/ as sysdba' or '/ as sysoper'.

Adding Entries to an Oracle HTTP Server Authentication File

If you have enabled Oracle HTTP Server authentication for user connections, you need to create a separate authentication file to contain username/password entries for user level connections. See "Enabling Server Authentication for Users" for information about enabling user level Oracle HTTP Server authentication.

To create a new entry in an Oracle HTTP Server authentication file on Windows.

1. Log in to the machine running the Oracle HTTP Server as the Oracle HTTP Server administrator.
2. Open a terminal.
3. Run the htpasswd utility to add users to the authentication file. htpasswd is usually located in %ORACLE_HOME%\Apache\Apache\bin. For SYSDBA or SYSOPER users, use the form:

   htpasswd %ORACLE_HOME%\sqlplus\admin\iplusdba.pw username
   
   

   For user connections, where iplus.pw has been created as an empty authentication file, use the form:

   htpasswd %ORACLE_HOME%\sqlplus\admin\iplus.pw username
   
   

   In both cases you are prompted for the associated password. For further information about htpasswd, see the Oracle HTTP Server documentation.

To create a new entry in an Oracle HTTP Server authentication file on UNIX.

1. Log in to the machine running the Oracle HTTP Server as the Oracle HTTP Server administrator.
2. Open a terminal.
3. Run the htpasswd utility to add users to the authentication file. htpasswd is usually located in $ORACLE_HOME/Apache/Apache/bin. For SYSDBA or SYSOPER users, use the form:

   htpasswd $ORACLE_HOME/sqlplus/admin/iplusdba.pw username
   
   

   For user connections, where iplus.pw has been created as an empty authentication file, use the form:

   htpasswd $ORACLE_HOME/sqlplus/admin/iplus.pw username
   
   

   In both cases you are prompted for the associated password. For further information about htpasswd, see the Oracle HTTP Server documentation.

Security Usage Notes

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 Oracle HTTP Server authentication username and password, you may not be required to re-authenticate to the Oracle HTTP Server until you restart your browser. However, you are still required to log in with your Oracle9i username and password.
• The Product User Profile (PUP) tables are per user, per database as for SQL*Plus client server installations. See "PRODUCT_USER_PROFILE Table" for more information about PUP tables.
• The global configuration file glogin.sql is read from the middle tier machine as for a client server installation. No user login.sql file is read.