0720-0722
Page 720
Listing 29.1. continued
CREATE OR REPLACE VIEW orders AS SELECT * FROM order_on_acct; CREATE PUBLIC SYNONYM orders FOR orders; /* BillingAR System DBA & rights */ GRANT CONNECT ,RESOURCE ,CREATE USER ,CREATE ROLE ,CREATE PUBLIC SYNONYM TO bardba IDENTIFIED BY billing; GRANT SELECT ON account_no TO bardba WITH GRANT OPTION; GRANT SELECT, INSERT, UPDATE ON accounts TO bardba WITH GRANT OPTION; GRANT SELECT ON orders TO bardba WITH GRANT OPTION; /* Order Entry System DBA & rights */ GRANT CONNECT ,RESOURCE ,CREATE USER ,CREATE ROLE ,CREATE PUBLIC SYNONYM TO oedba IDENTIFIED BY entry; GRANT SELECT ON order_no TO oedba WITH GRANT OPTION; GRANT SELECT, INSERT, UPDATE ON orders TO oedba WITH GRANT OPTION; GRANT SELECT ON accounts TO oedba WITH GRANT OPTION;
According to the previously described model of security, the script in Listing 29.1 is where database security ends and application security begins; enforcement responsibilities are delegated to the administrators of the specific applications. Note that public synonyms are created to hide the system ID of the DBA, and that limited rights are granted to each application's super-user. The bardba and oedba users will create application-specific objects, roles, and users and will grant rights based on the individual applicationwithin the database security restrictions enforced by the DBA by the limited rights granted to them.
Note that the bardba user receives read-only access to the orders view, and that the oedba user receives read-only access to the accounts view, while neither user receives the DELETE privilege to either view. This will limit the objects they can create, as well as the rights that they can grant to additional roles and users. For example, the bardba user will not be able to create a procedure to insert records into orders, or grant the INSERT privilege on orders to other roles and users.
Page 721
Application-specific objects include procedures, functions, packages, and views.
Stored procedures and functions are typically used to insert, update, and delete records. In many cases, these subprograms will operate only on views, and not on the underlying tables themselves . This makes it easier for administrators to change the underlying structures as needed, without affecting the applications that access them. Views are also used to present separate tables as one logical business object or to limit access to specific columns .
You should use procedures and functions to perform all transactions for several reasons. One reason to use database subroutines is that they can be used to enforce integrity. For example, if a sequence is used to generate unique primary-key values for a particular table, encapsulating the insert into a procedure or function can ensure that the sequence is always used. When user IDs or timestamps are being stored, the values for the user IDs and timestamps can be supplied from within the stored procedure or function as well. This ensures the integrity of these values, simplifies transaction processing for the client-side application, and helps reduce the amount of data being passed between the client and the server.
Using procedures and functions can also enhance database security. By granting only EXECUTE privileges on subprograms, the views and tables on which the subprograms operate remain unavailable. This prevents users from accessing them through SQL*Plus or one of the many desktop database or reporting tools that might enable them to modify the subprograms.
There are additional advantages to using packages. An Oracle package encapsulates a group of variables , constants, cursors , and subprograms into a single logical unit. This can greatly simplify the process of granting privileges and improve overall performance. When EXECUTE is granted on a package, the user receives the EXECUTE privilege for each subprogram contained in the package specification. The entire package is loaded into memory when a packaged object is first referenced by the application, which reduces I/O and improves performance for subsequent calls to subprograms within the package.
You should always use views to present result sets to the application. As mentioned previously, using views can help insulate the application from structural changes to the underlying tables and limit access to specific columns. Views can also simplify embedded SQL in the application by completely eliminating the need for joins. If all joins are handled by the views, the application can treat the result set as if it were a single table. This can also simplify the process of granting privileges. For example, if a view is created that joins seven tables, the user need only have the SELECT privilege to the view, not to the underlying seven tables. The user will then be able to access the view. If the join were accomplished within SQL embedded in the application, the user would need the SELECT privilege for each of the seven tables. Through column aliasing, views can also present result sets in terms of business lingo rather than column names , which are often very different.
Page 722
Ideally, application-specific stored procedures and functions are used for all transactions and operate only on views, whereas all result sets are retrieved through application-specific views. Such a configuration greatly enhances overall security and can completely insulate the client application from changes to the structure of the underlying tables. The procedures, functions, and views can be used to present a consistent database interface, regardless of the underlying structures on which they operate. By granting only EXECUTE on subprograms and SELECT on views, you prevent users from performing any transactions or SELECTs from outside the application- specific objects to which these privileges are granted. Exclusive use of procedures, functions, and views places a layer of abstraction between the users and the database, hiding the actual implementations of the tables. This is becoming an increasingly important security consideration as users become more sophisticated and generic reporting tools become more powerful.
Public synonyms are a method of making a database object available to all users of the database. You can use public synonyms to hide the ID of the owner of application objects and to prevent an application from having to specify a schema. Granting privileges to the public synonyms rather than to the objects themselves also provides an additional layer of abstraction. The diagram in Figure 29.1 provides a visual representation of the model of application security that has been presented in this section.
Figure 29.1.
This diagram represents
the role of database
objects in application
security.
