9 SQL*Plus Security

PRODUCT_USER_PROFILE Table

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.

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

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    LONG
                                      VALUE       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 PL/SQL commands:

1. Log in as SYSTEM with the command

   SQLPLUS SYSTEM
   

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 
   SELECT * FROM EMP_DETAILS_VIEW; 
   

   This command causes the following error message:

   SP2-0544: Command SELECT disabled in Product User Profile

   
   

4. 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'; 
   

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 Oracle Database SQL Reference, your Oracle Database Administrator's Guide, and your Oracle Database Concepts manual.

PRODUCT    USERID  ATTRIBUTE  SCOPE   NUMERIC     CHAR      DATE     LONG
                                      VALUE       VALUE     VALUE    VALUE
-------    ------  ---------  -----   --------    ------    -----    -----
SQL*Plus   HR      ROLES                          ROLE1
SQL*Plus   PUBLIC  ROLES                          ROLE2

SET ROLE ALL EXCEPT ROLE1, ROLE2

Disabling Commands with SQLPLUS -RESTRICT

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.

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

Program Argument Security

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 connection information, particularly your password.

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

  sqlplus @myscript.sql
  

When SQL*Plus is started like this, it uses the first line of the script as the username/password@connection_identifier string.

Avoid storing your username and password in files or scripts. If you do store your username and password in a file or script, ensure that the file or script is secured from non-authorized access.

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

• The Oracle Net connection between the Application Server and Oracle Database.

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 Database Net Services Administrator's Guide and the Oracle Advanced Security Administrator's Guide.