Establishing Management Standards

As we can see, the DBA has limited control over the origin of SQL, especially in a client/server environment. Given this reality, the DBA must encourage procedures for SQL standardization as well as disseminate SQL techniques to the development staff.

SELECT CHIPS, POP FROM FRIDGE 
WHERE CHIPS = 'FRITOS' AND POP = 'PEPSI'

Of course, it is one thing to impose standards, but quite another for DBAs to force the developers to adhere to the DBA rules. Here are some common methods that are used by the DBA staff to prohibit unauthorized SQL:

1. Cut off SQL*Plus access by changing the product_user_profile table. Oracle allows the DBA to restrict access to the UNIX SQL*Plus utility by making entries in SYS.PRODUCT_USER_PROFILE.
2. Grant only the GRANT EXECUTE privilege with no other access rights. In this fashion, the only way to allow the developers access to the data is by executing your stored procedure. With GRANT EXECUTE, the invoker will temporarily have the same access privileges as the creator of the stored procedure, but only while they are executing the stored procedure.

Read the following section which discusses how to enforce 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.

Here's a breakdown of how precompiler programs interact with host variables in Oracle, along with the essential concepts:
What is a Precompiler (specifically Pro*C/C++)?

• Pro*C/C++ is an Oracle precompiler that allows you to embed SQL statements directly within your C or C++ code.
• Its primary job is to translate this embedded SQL into calls to the Oracle runtime library (SQLLIB) before your code is compiled by a regular C/C++ compiler.

What are Host Variables?

• Host variables are variables declared in your C/C++ code.
• They act as bridges between your program and the Oracle database:
  • Input: Host variables pass data from your program to be used in SQL statements (e.g., for WHERE clauses, values to insert).
  • Output: Host variables receive data fetched from the database by your SQL queries.

How Precompilers Work with Host Variables

1. Declaration: You declare host variables in your code with a special syntax:
   

   EXEC SQL BEGIN DECLARE SECTION;
      char employee_name[50];
      int salary;
   EXEC SQL END DECLARE SECTION;
   

2. Precompiler Magic:
   • The precompiler identifies embedded SQL and host variables.
   • It replaces SQL code with calls to Oracle runtime libraries, ensuring correct interaction between your code and the database.
   • It generates mappings between your host variables and database columns for seamless data transfer.
3. Usage in SQL: Prefix host variables with a colon (:) within your SQL to distinguish them:

   EXEC SQL SELECT ename, sal INTO :employee_name, :salary
   		FROM emp
   		WHERE empno = 1234;
   

Indicator Variables (Optional):

• Indicator variables are special short integer variables associated with host variables.
• They provide information about the status of input/output data:
  • NULL Values: Indicate whether a database value was NULL.
  • Truncation: Indicate if an output value was too large to fully fit into the host variable.

Example with Indicator Variables:

EXEC SQL BEGIN DECLARE SECTION;
    char job[10];
    short job_ind; 
EXEC SQL END DECLARE SECTION;

...

EXEC SQL SELECT job INTO :job INDICATOR :job_ind FROM emp WHERE empno = 4321;

if (job_ind == -1) {
   // The 'job' column was NULL
} else if (job_ind == 0) {
   // The 'job' value fit into the host variable 
} else {
   // The 'job' value was truncated
}

Why Precompilers?

• Integration: Smoothly blend the power of SQL with your C/C++ code.
• Performance: The precompiler can optimize SQL interactions and data mapping for efficiency.
• Type Safety: Helps ensure data passed between the database and your program is compatible.

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.

In the next lesson, you will learn techniques to assist with managing developers.