Oracle Unleashed- P11

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

72
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Unleashed- P11: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle Unleashed- P11

cause all references in the child table to be deleted automatically. In this respect, using ON DELETE CASCADE is similar to creating a DELETE trigger on the referenced table. This is an extremely powerful option and should be used with caution. If it is not properly understood, unwanted automatic deletions could result. Although they are ideally suited to enforcing referential integrity, REFERENCES constraints may not be desirable in certain situations. For example, if a table has a high volume of transactions and contains several foreign keys that are simple lookups, performance can be adversely affected by using the REFERENCES constraint. Each time a row is inserted or updated, the referenced tables must be checked to ensure that each foreign key being inserted exists in the referenced tables. Depending on the nature of the data and the importance of performance, it may be preferable to enforce the foreign keys through the application itself. Table Constraints Each of the column constraints described in the previous section can also be applied as table constraints, with the exception of NOT NULL. Table constraints have the additional advantage of being able to operate on multiple columns. Refer to the CHECK constraint in Listing 20.6. This provides a sanity check on the lower bounds of date_of_birth, but it would be better to check the upper bounds as well. Listing 20.10 illustrates how you can accomplish this using a table constraint. Listing 20.10. This DDL script, containing a CHECK table constraint, is an improvement on the column constraint used in Listing 20.6. CREATE TABLE individual ( ID NUMBER(10) CONSTRAINT indiv_pk PRIMARY KEY ,last_name VARCHAR2(30) CONSTRAINT indiv_l_name NOT NULL ,first_name VARCHAR2(30) CONSTRAINT indiv_f_name NOT NULL ,notes VARCHAR2(255) ,date_of_birth DATE ,last_updt_user VARCHAR2(20) CONSTRAINT indiv_lu_id NOT NULL ,last_updt_date DATE CONSTRAINT indiv_lu_dt NOT NULL ,CONSTRAINT indiv_chk_bday CHECK (date_of_birth BETWEEN (TO_DATE('01JAN1845', 'DDMONYYYY', 'nls_date_language = American')) AND last_updt_date) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
); The syntax of table constraints is nearly identical to that of column constraints, except that the CONSTRAINT clause comes at the end of the column list. Another advantage to using a table constraint applies only to constraints that result in the creation of an index. The table constraint syntax allows the designation of a tablespace and storage specification for the corresponding index. When indexes are created automatically for column constraints, they are created in the default tablespace using the default storage specification. The script in Listing 20.11 adds a UNIQUE constraint to the ind_co_rel table, enforcing uniqueness in the combination of individual_id and company_id. It also implements the USING INDEX clause to designate a tablespace and storage specification for the index that will be created. Listing 20.11. This DDL script uses the ALTER TABLE syntax to create a UNIQUE table constraint and a storage specification for the corresponding index. ALTER TABLE ind_co_rel ADD CONSTRAINT ind_co_rel_u UNIQUE(individual_id, company_id) USING INDEX TABLESPACE oracle_unleashed STORAGE (INITIAL 10K NEXT 10K MAXEXTENTS 100 PCTINCREASE 10); The syntax for tablespace and storage specification following the USING INDEX keyword is identical to that for the CREATE INDEX statement. The USING INDEX clause can also be used in a PRIMARY KEY constraint, as illustrated in Listing 20.12. Listing 20.12. This DDL script removes the UNIQUE constraint created in Listing 20.11 and re-creates it as a PRIMARY KEY. ALTER TABLE ind_co_rel DROP CONSTRAINT ind_co_rel_u / ALTER TABLE ind_co_rel DROP CONSTRAINT indco_ind_nn / ALTER TABLE ind_co_rel DROP CONSTRAINT indco_co_nn Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
/ ALTER TABLE ind_co_rel ADD CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id) USING INDEX TABLESPACE oracle_unleashed STORAGE (INITIAL 10K NEXT 10K MAXEXTENTS 100 PCTINCREASE 10) / Notice that the NOT NULL constraints on the individual_id and company_id columns were also dropped. To keep them would be redundant, because columns included in a PRIMARY KEY constraint cannot have null values. Foreign key table constraints are implemented in much the same way as REFERENCING column constraints. If necessary, multiple columns can be included in a FOREIGN KEY table constraint. This is not the case with the column constraint implementation. Note also that the FOREIGN KEY keyword is only available for the table constraint syntax. Listing 20.13, the final script for ind_co_rel, also defines temporary tables into which exceptions are logged. Listing 20.13. The final ind_co_rel script uses the FOREIGN KEY table constraint syntax and logs exceptions into temporary tables. CREATE TABLE ind_co_rel ( individual_id NUMBER(10) ,company_id NUMBER(10) ,title VARCHAR2(80) ,last_updt_user VARCHAR2(20) CONSTRAINT indco_lu_id NOT NULL EXCEPTIONS INTO ind_co_err_1 ,last_updt_date DATE CONSTRAINT indco_lu_dt NOT NULL EXCEPTIONS INTO ind_co_err_2 ,CONSTRAINT indco_ind_fk FOREIGN KEY (individual_id) REFERENCES individual(ID) ON DELETE CASCADE Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
EXCEPTIONS INTO ind_co_err_3 ,CONSTRAINT indco_co_fk FOREIGN KEY (company_id) REFERENCES company(ID) ON DELETE CASCADE EXCEPTIONS INTO ind_co_err_4 ,CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id) USING INDEX TABLESPACE oracle_unleashed STORAGE (INITIAL 10K NEXT 10K MAXEXTENTS 100 PCTINCREASE 10) EXCEPTIONS INTO ind_co_err_5 ) TABLESPACE oracle_unleashed; Each type of exception is logged into a different temporary table in Listing 20.13, which is helpful in debugging and resolving integrity problems. The tables into which exceptions are logged should be identical to the ind_co_rel table, except that they should have no constraints, for obvious reasons. The EXCEPTIONS INTO clause can be used for any type of column or table constraint. If the table referenced by EXCEPTIONS INTO does not exist, using the clause will produce an error. The EXCEPTIONS INTO clause is particularly useful in applications where frequent and timely bulk loads are required. In order to use SQL*Loader with the direct path option to maximize its performance, constraints must be disabled. When the constraints are re-enabled, the EXCEPTIONS INTO clause can be used to categorize problem records by inserting them in separate tables based on the constraint that was violated. This minimizes the negative impact constraints have on performance, while maintaining integrity and providing a means of identifying and resolving problems with the data being loaded. As shown by these examples, Oracle provides a wide variety of options for enforcing integrity through column and table constraints. Constraints are powerful tools for enforcing integrity, but they should be used with care. Overuse of constraints can add significantly to long-term maintenance requirements, and misuse can create unwanted dependencies or unnecessary exceptions. The possible trade-offs involving constraints and performance will be discussed in greater detail later in this chapter. Using Sequences Oracle sequences are ideally suited to the task of generating unique key values. A sequence is a stored object that simply generates a sequence of numbers. Listing 20.14 illustrates the syntax for creating a sequence. Listing 20.14. This sequence is used to generate primary keys for the individual table. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CREATE SEQUENCE individual_ids START WITH 1 INCREMENT BY 1 NOMAXVALUE; The values specified for the START WITH, and INCREMENT BY parameters in Listing 20.14 are the defaults. NOMAXVALUE is the default as well. The script in Listing 20.13 will produce the same result as the following: CREATE SEQUENCE individual_ids; It is a good practice to explicitly declare these defaults for documentation purposes, if nothing else. The implementation is fairly self-explanatory. The START WITH parameter indicates the first number that will be generated, INCREMENT BY specifies a number to be added to the current value to generate the next value, and NOMAXVALUE indicates that there is no maximum to the numbers it will generate, (practically no maximum, although there is an upper limit). The MINVALUE parameter is used only by descending sequences. (Specifying a negative value for INCREMENT BY produces a descending sequence.) CYCLE, when used in conjunction with MAXVALUE or MINVALUE, indicates that the sequence should start from the beginning when the minimum or maximum value is reached. The default for this option is NOCYCLE. The CACHE parameter indicates the number of values that should be pre-generated and cached by the sequence. The default value for CACHE is 20. Raising this parameter can improve performance in high transaction volume environments. ORDER ensures that sequences are used in the order generated. Regardless of the setting of this option, the same value will never be returned twice. If an application uses a sequence in a transaction that is rolled back, the value is simply discarded. NORDER, the default for this option, is acceptable unless the sequence is being used like a timestamp to indicate the order of events over time. A sequence has two pseudocolumns, currval and nextval. Currval returns the current value of the sequence, while nextval increments the sequence and returns the new value. Listing 20.15 demonstrates how a sequence can be used in an Oracle function to generate new keys when inserting records. Listing 20.15. This function accesses a sequence to fetch a new key when inserting a record. CREATE OR REPLACE FUNCTION insert_indiv (last CHAR, first CHAR, notes CHAR, dob DATE) RETURN NUMBER IS new_id NUMBER; BEGIN SELECT individual_ids.nextval INTO new_id FROM dual; INSERT INTO individual VALUES (new_id, last, first, notes, dob, user, sysdate); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
COMMIT; RETURN(new_id); EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN(1); END insert_indiv; It is a common practice to use sequences for generating unique primary keys. One sequence can be used for many tables, or a separate sequence can be created for each table requiring generated keys. Either option is preferable to any key that requires user intervention, because typographical errors are bound to occur. It is typically preferable to generate unique keys even when one exists naturally in the data (Social Security number, for example). Using a sequence to generate primary keys can improve performance in certain situations, as well. As mentioned previously, integer joins are typically faster than character-based joins, and even when a natural integer primary key exists, a sequence is often a better choice. To use Social Security number as an example, 10 bytes must be stored for each key. If a sequence is used, starting with 1, a considerable amount of disk space can be conserved, and a much smaller index produced, which will result in less I/O. Perhaps a less important consideration is the order in which values are inserted. Depending on how inserts are handled by applications accessing the sequence, the index on a sequence- generated primary key may be created in ascending order naturally, which is somewhat of an optimization in terms of I/ O performance. If the sequence is created with the ORDER option, and inserts are handled using Oracle subprograms similar to Listing 20.15, this will always be true. Using Triggers Triggers are stored procedures associated with a specific operation on a specific table. A trigger is automatically fired when the operation with which it is associated is performed on the table with which it is associated. Triggers can perform many of the same tasks as constraints, and in most cases, they can go beyond what constraints can do. For example, a NOT NULL constraint can only ensure that a value is present in a column, but it does nothing to ensure the accuracy of the data. Listing 20.16 provides an example of how you can use triggers to enforce a NOT NULL constraint and ensure the accuracy of the data being inserted. Listing 20.16. This trigger ensures that the columns last_updt_user and last_updt_date are inserted and updated accurately. CREATE OR REPLACE TRIGGER indiv_timestamp BEFORE INSERT OR UPDATE ON individual FOR EACH ROW BEGIN :new.last_updt_user := user; :new.last_updt_date := sysdate; END indiv_timestamp; The simple trigger in Listing 20.16 ensures that the last_updt_user and last_updt_date are being inserted and updated accurately in the individual table. In effect, the trigger intercepts the actual values being inserted and replaces them with Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
user and sysdate. Using the NOT NULL constraint for these columns is no longer necessary, and the trigger goes far beyond what the constraint could do. The trigger in Listing 20.16 also relieves the application of the burden of supplying values for the last_updt_user and last_updt_date columns when inserting and updating records. INSERT and UPDATE triggers are commonly used for customized transaction logging, or to generate statistical summaries to be accessed by a different group of users than those applying the transactions. For example, a large order entry system might use an INSERT trigger to write only the date, the order amount, and the salesperson to a separate table to be used only for management reporting. The syntax for creating triggers is very similar to the creation of procedures, with a few notable exceptions. The BEFORE or AFTER keyword must follow the name of the trigger, indicating whether it should be fired before or after the operation that causes it is fired. Although it may not seem logical to do so, this trigger had to be created with the BEFORE option. Trying to implement this trigger with the AFTER option produces the following error: ORA04091: table SCOTTY.INDIVIDUAL is mutating, trigger/function may not see it Because the trigger is being executed by a process that is currently involved in a transaction on the same row, it cannot be created with the AFTER option. This would, in effect, invalidate the old correlation of the trigger. Immediately following the BEFORE or AFTER keyword is the action (or actions) with which the trigger is associated. This can be INSERT, UPDATE, or DELETE, or any combination of these separated by OR. The FOR EACH ROW keyword defines the behavior of the trigger when it is fired by statements affecting multiple rows. The default behavior is to fire the trigger only once, regardless of the number of rows affected. A trigger may also include a WHEN clause, which limits the conditions under which it will fire. The WHEN clause can be used for specialized reporting, or to draw attention to a value that may seem to be out of range. For example, an accounts payable system might use an INSERT trigger to log all payments of greater than $10,000 to a temporary table, which can then be used to generate a report for management's review and approval. This could be an alternative to a CHECK condition, which might prove to be overly restrictive. In most circumstances, it would not be acceptable to reject a valid payment simply because it is unusually high. On the other hand, management may be interested in reviewing or auditing these payments. In this respect, a trigger can be used in a way that is analogous to passively enforcing a CHECK constraint. Note that in Listing 20.16, the variable new is never declared. This is the default correlation name associated with the new row (which is valid for inserts and updates only). The name old is associated with the old row by default, and is valid for updates and deletes only. These default names can be reassigned using a REFERENCING clause. The REFERENCING clause should placed immediately before the FOR EACH ROW keyword (if it is used), as in Listing 20.17. Listing 20.17. This trigger uses a REFERENCING clause to rename new. CREATE OR REPLACE TRIGGER indiv_timestamp Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEFORE INSERT OR UPDATE ON individual REFERENCING new AS new_row FOR EACH ROW BEGIN :new_row.last_updt_user := user; :new_row.last_updt_date := sysdate; END indiv_timestamp; The REFERENCING clause is rarely used, but it is provided in order to allow the use of new and old as regular program variables. Triggers, when used with sequences, can also be used to enforce primary key constraints. Listing 20.18 creates a trigger that ensures that new rows in the individual table are assigned unique primary key values. The trigger also ensures that the sequence is used, by overriding any value specified in an insert statement with the next value in the sequence. Listing 20.18. This trigger enforces the primary key on the individual table. CREATE OR REPLACE TRIGGER indiv_get_key BEFORE INSERT ON individual FOR EACH ROW DECLARE new_id NUMBER; BEGIN SELECT individual_ids.nextval INTO new_id FROM dual; :new.id := new_id; END indiv_get_key; Unfortunately, this trigger cannot coexist with the trigger in Listing 20.17. For each table, only one trigger can exist for each operation. Listing 20.19 demonstrates how these can be implemented together, replacing the CHECK constraint on date_of_birth in the process. Listing 20.19. These triggers can be used to enforce several constraints on the individual table. CREATE OR REPLACE TRIGGER indiv_ins_trg BEFORE INSERT ON individual FOR EACH ROW DECLARE new_id NUMBER; BEGIN IF ((:new.date_of_birth < TO_DATE('01JAN1845', 'DDMONYYYY', Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
'nls_date_language = American')) OR (:new.date_of_birth > sysdate)) THEN RAISE_APPLICATION_ERROR(20001, 'Invalid birth date.'); ELSE SELECT individual_ids.nextval INTO new_id FROM dual; :new.id := new_id; :new.last_updt_user := user; :new.last_updt_date := sysdate; END IF; END indiv_ins_trig; CREATE OR REPLACE TRIGGER indiv_updt_trg BEFORE UPDATE ON individual FOR EACH ROW BEGIN IF ((:new.date_of_birth < TO_DATE('01JAN1845', 'DDMONYYYY', 'nls_date_language = American')) OR (:new.date_of_birth > sysdate)) THEN RAISE_APPLICATION_ERROR(20001, 'Invalid birth date.'); ELSE :new.id := :old.id; :new.last_updt_user := user; :new.last_updt_date := sysdate; END IF; END indiv_updt_trg; In Listing 20.19, the PRIMARY KEY constraint is enforced for both inserts and updates. The UPDATE trigger completely prevents an ID from being changed, which might not be acceptable if this were not a generated key. The triggers also enforce a CHECK constraint on date_of_birth and the NOT NULL constraints on last_updt_user and last_updt_date. Triggers can be used to enforce foreign key constraints, as well. Refer to Listing 20.9. The REFERENCES constraints in the example both contained the ON DELETE CASCADE option. If the foreign keys integrity constraints are enforced by the triggers in Listing 20.20, the REFERENCES column constraints could be removed. Listing 20.20. These triggers can be used to enforce foreign key constraints. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CREATE OR REPLACE TRIGGER indiv_del_trg BEFORE DELETE ON individual FOR EACH ROW BEGIN DELETE FROM ind_co_rel WHERE individual_id = :old.id; END indiv_del_trg; CREATE OR REPLACE TRIGGER co_del_trg BEFORE DELETE ON company FOR EACH ROW BEGIN DELETE FROM ind_co_rel WHERE company_id = :old.id; END co_del_trg; CREATE OR REPLACE TRIGGER ind_co_trg BEFORE INSERT OR UPDATE ON ind_co_rel FOR EACH ROW DECLARE co_id NUMBER; indiv_id NUMBER; BEGIN SELECT ID INTO co_id FROM company WHERE ID = :new.company_id; SELECT ID INTO indiv_id FROM individual WHERE ID = :new.individual_id; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(20002, 'Invalid id.'); END ind_co_trg; As these examples demonstrate, triggers can be used to perform the same tasks as table and column constraints. In many cases, it may be preferable to use triggers because they are likely to provide better performance. This is particularly true in distributed environments, where it may not be possible to enforce foreign key constraints at all. When designing triggers, you should pay special attention to cascading triggers. Cascading occurs when a trigger on one table causes a trigger on another table to be fired. Codependencies, in particular, can be a problem. Note that in Listing 20.20, the individual and company tables both have DELETE triggers that delete corresponding rows from ind_co_rel. For the sake of example, assume that ind_co_rel has a DELETE trigger that deletes corresponding rows in address_rel and phone_rel, and that individual and company also include these deletions in their DELETE Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
triggers. This presents numerous integrity problems. If a relationship between an individual and a company is deleted, records in address_rel and phone_rel that are related to both should be deleted. Also, if an individual or company is deleted entirely, all address_rel and phone_rel records related to the specific individual or company should be deleted. When an individual or company is deleted, the ind_co_rel record is deleted, which causes its trigger to be fired, resulting in deletions from the address_rel and phone_rel tables. If these records are also to be deleted by the trigger that was originally fired by the deletion of the individual or company, the mutating table problem described earlier will occur. In this case, the ind_co_rel should probably not have a DELETE trigger at all. Meaningless records in address_rel and phone_rel exist only until the corresponding individual or company is deleted. This is just one example of how cascading triggers can produce unexpected results. Application and Performance Considerations Regardless of how integrity is enforced in the database, the application must have knowledge of the constraints. The application must be able to submit transaction statements in the proper order, and it must know how to respond to exceptions resulting from integrity problems. This point is best illustrated through the use of an example. Assume that the application needs to perform a single transaction based on the objects created in Listing 20.21. Listing 20.21. These objects participate in a single transaction. CREATE SEQUENCE individual_ids START WITH 1 INCREMENT BY 1 NOMAXVALUE; CREATE TABLE individual ( ID NUMBER(10) CONSTRAINT indiv_pk PRIMARY KEY ,last_name VARCHAR2(30) CONSTRAINT indiv_l_name NOT NULL ,first_name VARCHAR2(30) CONSTRAINT indiv_f_name NOT NULL ,notes VARCHAR2(255) ,date_of_birth DATE ,last_updt_user VARCHAR2(20) CONSTRAINT indiv_lu_id NOT NULL ,last_updt_date DATE CONSTRAINT indiv_lu_dt NOT NULL ,CONSTRAINT indiv_chk_bday Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHECK (date_of_birth BETWEEN (TO_DATE('01JAN1845', 'DDMONYYYY', 'nls_date_language = American')) AND last_updt_date) ); CREATE SEQUENCE company_ids START WITH 1 INCREMENT BY 1 NOMAXVALUE; CREATE TABLE company ( ID NUMBER(10) CONSTRAINT company_pk PRIMARY KEY ,name VARCHAR2(30) CONSTRAINT co_name_u UNIQUE CONSTRAINT co_name_nn NOT NULL ,notes VARCHAR2(255) ,last_updt_user VARCHAR2(20) CONSTRAINT co_lu_id NOT NULL ,last_updt_date DATE CONSTRAINT co_lu_dt NOT NULL ); CREATE TABLE ind_co_rel ( individual_id NUMBER(10) ,company_id NUMBER(10) ,title VARCHAR2(80) ,last_updt_user VARCHAR2(20) CONSTRAINT indco_lu_id NOT NULL EXCEPTIONS INTO ind_co_err_1 ,last_updt_date DATE CONSTRAINT indco_lu_dt NOT NULL Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
EXCEPTIONS INTO ind_co_err_2 ,CONSTRAINT indco_ind_fk FOREIGN KEY (individual_id) REFERENCES individual(ID) ON DELETE CASCADE EXCEPTIONS INTO ind_co_err_3 ,CONSTRAINT indco_co_fk FOREIGN KEY (company_id) REFERENCES company(ID) ON DELETE CASCADE EXCEPTIONS INTO ind_co_err_4 ,CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id) USING INDEX TABLESPACE oracle_unleashed STORAGE (INITIAL 10K NEXT 10K MAXEXTENTS 100 PCTINCREASE 10) EXCEPTIONS INTO ind_co_err_5 ); It would be useful if the application could insert an individual, a company, and a record relating the two in one transaction. The foreign key constraints on indiv_co_rel dictate that this record must be inserted last. In designing a process to complete this transaction, you should also consider that the application will need to insert an individual and a company separately, as well. Listing 20.22 provides three functions to accomplish these tasks. Listing 20.22. These three functions guarantee that integrity constraints are satisfied for INSERTS into the objects in Listing 20.21. CREATE OR REPLACE FUNCTION insert_indiv (last CHAR, first CHAR, notes CHAR, dob DATE) RETURN NUMBER IS invalid_name EXCEPTION; new_id NUMBER; BEGIN Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
IF ((LENGTH(RTRIM(last)) > 0) AND (LENGTH(RTRIM(first)) > 0)) THEN SELECT individual_ids.nextval INTO new_id FROM dual; INSERT INTO individual VALUES (new_id, last, first, notes, dob, user, sysdate); RETURN(new_id); ELSE RAISE invalid_name; END IF; EXCEPTION WHEN invalid_name THEN ROLLBACK; RETURN(20001); WHEN OTHERS THEN ROLLBACK; RETURN(1); END insert_indiv; CREATE OR REPLACE FUNCTION insert_company (name CHAR, notes CHAR) RETURN NUMBER IS invalid_name EXCEPTION; new_id NUMBER; BEGIN IF (LENGTH(RTRIM(name)) > 0) THEN SELECT company_ids.nextval INTO new_id FROM dual; INSERT INTO company VALUES (new_id, name, notes, user, sysdate); RETURN(new_id); ELSE Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
RAISE invalid_name; END IF; EXCEPTION WHEN invalid_name THEN ROLLBACK; RETURN(20001); WHEN OTHERS THEN ROLLBACK; RETURN(1); END insert_company; CREATE OR REPLACE FUNCTION insert_ind_co(last CHAR, first CHAR, notes CHAR, dob DATE, co_name CHAR, co_notes CHAR, title CHAR) RETURN NUMBER IS ret NUMBER; ind_id NUMBER; co_id NUMBER; BEGIN ret:=insert_indiv(last, first, notes, dob); IF (ret > 0) THEN ind_id:=ret; ret:=insert_company(co_name, co_notes); IF (ret > 0) THEN co_id:=ret; INSERT INTO ind_co_rel VALUES (ind_id, co_id, title, user, sysdate); RETURN(ind_id); ELSE Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ROLLBACK; RETURN(ret); END IF; ELSE ROLLBACK; RETURN(ret); END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN(1); END insert_ind_co; The transaction ensures that all constraints are being met, with the exception of the check constraint on individual, which could easily be added to the insert_indiv function. Sequences are used to generate primary keys, the functions check for null values, and the foreign key constraints in ind_co_rel are enforced by the order in which inserts occur. If an error occurs in any of the transactions, a rollback is issued, but commits are left out altogether. This is to ensure that an individual or company is not committed as part of the transaction that establishes their relationship. It is up to the calling application to commit the transaction as a whole. These functions illustrate the point that in some cases, it may be a good solution to allow the client application to enforce integrity. Column and table constraints can be costly, especially foreign key constraints, which require an additional read. In an environment with a high volume of transactions and users, these constraints can have a significant negative impact on performance, possibly resulting in unnecessary contention, snapshot problems, and other bottlenecks. When using the application itself to enforce referential integrity, you can run batch processes periodically to ensure that the application is enforcing integrity. You can create temporary tables to store exceptions for further analysis and resolution, and you can use simple SQL to identify problem records. For example, you can use a statement like the one following to identify invalid references to individuals in the ind_co_rel table. SELECT * FROM ind_co_rel INTO temp_no_ind WHERE individual_id NOT IN (SELECT ID FROM individual) If the application is properly designed, batch processes should not identify exceptions and would need to be run very infrequently. Primary key constraints, on the other hand, typically improve performance, especially if they are defined as a single small column. This constraint causes an index to be built, and primary key values are often stored as foreign keys in other tables. In these cases, declaring the primary key usually results in faster joins. In general, however, constraints have a negative impact on overall performance. Depending on the nature of the data, security considerations, and standards of acceptable performance, you can rely upon the application itself to enforce many of the required integrity constraints. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Summary Column constraints, table constraints, triggers, and PL/SQL are all useful in enforcing integrity, but each approach also has limitations. The best way to combine these elements to enforce integrity depends entirely on the application. Most designs use a combination of all of these methods and attempt to strike a balance between pure performance and the strict enforcement of integrity. Previous Next TOC Home Page Page Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous Next TOC Home Page Page q 21 r Application Security s Reasons for Using Application Security s Using Application-Specific Database Objects s Table-Driven Application Security s Application and Performance Considerations s Summary 21 Application Security Application security limits database access from the client side of the system. Database security limits access to specific database objects, whereas application security limits access to specific interface objects. In a broader sense, application security also includes the use of any application-specific database objects that are created to enhance security. There are several reasons why you should use application security in addition to database security. These reasons are discussed in the following section. This chapter also covers the use of application-specific database objects and table- driven user interfaces, as well as application and performance considerations relating to the use of application security. Reasons for Using Application Security In general, application security is used to enhance and supplement database security. However, it would be dangerous to rely only on application security because there is no guarantee that users will access the database only through a single application. Application security can enhance database security by further limiting access to database objects and by providing an additional layer of abstraction (hiding the details of available database objects). If you limit access to application-specific stored procedures and functions and create application-specific roles, you can hide objects based on the specific user or the role of the user accessing the system. On the same basis, the application can limit access of specific columns or make specific columns read-only by using different views for different roles or users. In a typical database application, the interface enables the user to communicate with the database by using menus and forms related to business processes and objects rather than tables and columns. This is a convenient layer that prevents users from having to know the names of the tables and columns being accessed. In many cases, information in the database is used to control the behavior of the interface, based on the role of the user. System tables contain security information used by the application to drive what capabilities are available to the interface. Menu options and forms to which the user has privileges to access are made visible, while others are hidden. For example, if an application has an administration menu containing options specific to database administration functions, the entire menu is hidden from users who do not have the administrator role. Any forms relating to these specific functions are inaccessible as well. This method of using application security is obviously preferable to providing a homogenous interface that enables users to try to perform operations on tables to which they have no privileges. If the application allowed the user to access a database object that they did not have privileges to, the resulting errors would Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
have to be trapped and displayed to the user, which can result in misunderstanding and frustration on the users' part. Using the database to drive application security is also preferable to hard-coding rules into the client application. Table- driven application security makes it easy to update a user's privileges without having to reprogram the application. Another reason for using application security relates to databases that are accessed by multiple applications. In this case, a single user might have multiple roles, of which only one applies to the specific application. Use an Oracle table when there are different application roles that could apply. For example, an order entry system might have three different roles: one for order entry, one for management reporting, and one for system administration. A billing system and a accounts receivable system might share common tables and be used by many of the same users. These users may have slightly different role definitions for each application. A simple Oracle table containing a user ID and a corresponding role can be used to determine the correct role to be used for the application at runtime. The privileges of the appropriate role can then be used to limit the user's view of the database to the scope that was intended for the particular application. Using Application-Specific Database Objects The distinction between application security and database security is sometimes blurred, as is the case when you use application-specific database objects. As illustrated by the previous example, different applications often access a common database. When you create objects to be used only by a specific application, application-level security is common. Although the objects exist in the database and database security is used, if the object exists only to service a specific application, it is "owned" by the application. The argument can be made that rights granted to the stored object fall under the category of application security. In the definition of overall systems security, the "base" objects, (clusters, tables, indexes, and sequences), and rights granted to these objects are categorized as database security issues. Views, procedures, and functions are considered application-specific objects, and rights granted to them are categorized as application security issues. Typically, in large database environments the primary DBA will not be responsible for creating and maintaining all application-specific objects. One possible way to handle security for a large database accessed by multiple applications is to grant rights to sequences and views of the tables to lead developers or administrators who serve as DBAs for the individual applications. To do this, use the WITH GRANT OPTION clause. Listing 21.1 follows the example of the database shared by order entry and billing/accounts receivable applications. It demonstrates how the WITH GRANT OPTION might be used on a subset of the database objects. Listing 21.1. This script grants limited rights to the common database objects. /* SEQUENCE */ CREATE PUBLIC SYNONYM account_no FOR account_no; CREATE OR REPLACE VIEW accounts AS SELECT * FROM account; CREATE PUBLIC SYNONYM accounts FOR accounts; /* SEQUENCE */ CREATE PUBLIC SYNONYM order_no FOR order_no; CREATE OR REPLACE VIEW orders AS SELECT * FROM order_on_acct; CREATE PUBLIC SYNONYM orders FOR orders; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
/* 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 21.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 were created to hide the system ID of the DBA, and that limited rights were granted to each application "super-user." The bardba and oedba users will create application-specific objects, roles, and users, and grant rights based on the individual application—within the database security restrictions enforced by the DBA by the limited rights granted to them. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.