December 2004 Archives

This article by DMReview gives you a simple but affective list of 10 mistakes which you shouldn't make when developing a Data Warehouse.

1. Leaving out the users


2. Testing reports - not data


3. Skipping the reconciliation of data warehouse data with source system data


4. Underestimating the testing workload


5. Failing to set proper expectations with business users


6. "Fast tracking" the testing phase


7. Underestimating the duration of the ETL process


8. Choosing the "wrong" testers


9. Skipping proper sign-offs


10. Using "made up" test data

By default, a stored procedure executes with the privileges of its definer, not its invoker. Such procedures are bound to the schema in which they reside. For example, assume that the following standalone procedure, which can drop any kind of database object, resides in schema scott:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;

Also assume that user jones has been granted the EXECUTE privilege on this procedure. When user jones calls drop_it, as follows, the dynamic DROP statement executes with the privileges of user scott:
SQL> CALL drop_it('TABLE', 'dept');

Also, the unqualified reference to table dept is resolved in schema scott. So, the procedure drops the table from schema scott, not from schema jones.
However, the AUTHID clause enables a stored procedure to execute with the privileges of its invoker (current user). Such procedures are not bound to a particular schema. For example, the following version of drop_it executes with the privileges of its invoker:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;