Oracle SQL Standards

The following list matches SQL terms with their functionality.

1. product_user_profile: Limits access to SQL*Plus
2. stored procedures: Enforces uniform SQL
3. GRANT EXECUTE: Alternative to roles
4. Host Variable: Improves re-usability Host variables are the key to communication between your host program and Oracle.
   Typically, a precompiler program inputs data from a host variable to Oracle, and Oracle outputs data to a host variable in the program. Oracle stores input data in database columns, and stores output data in program host variables.
   A host variable can be any arbitrary C expression that resolves to a scalar type. But, a host variable must also be an lvalue. Host arrays of most host variables are also supported.

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 and not the 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 an Oracle Database indicating that the PUP table information is not loaded.