The Crystal Repository

The Crystal object repository is a powerful new feature in Crystal Reports 9. The object repository is aptly named: it is a storage place for Crystal objects that can be shared among many developers and reports, including semiautomatic updating of changed objects. The object types that can be stored in the repository include text, graphics, custom formulas, and custom database queries or commands.

In this chapter, you will create the repository in an Oracle database, set up privileges, practice using the repository, and create a repository report. Oracle 9i will be used in the illustrations, with any differences for Oracle 8i noted. Oracle Virtual Private Database features will also be used to create a security scheme for the repository.

Creating the Repository

Crystal documentation states that you must create an ODBC DSN (Data Source Name) for the target database, set the configuration file to point to the DSN, and then Crystal Reports will create the database tables for you. Although this process does work for Oracle databases, you should be aware that only two tables are created initially, with the others created as they are required. In addition, no tables are populated with sample data. Therefore, the tables initially created will not be equivalent to the sample Access repository, in either the number of tables created or their content.

Creating the Oracle Schema

The following instructions assume that the user is familiar with Oracle database administration topics, that all configuration necessary to connect to the Oracle instance and issue commands has been completed, and that the user has DBA privileges. If not, these instructions should be passed on to the DBA for execution.

The first step is to create an Oracle user/schema to contain the repository objects. The sample code will use a schema named COR (Crystal Object Repository), a password of COR, and default tablespace assignments. Any desired schema name can be substituted and, of course, in a real-world situation the password should be something other than the user ID. Create the COR user with whatever tablespace defaults and other settings which are appropriate to your database. Log in to your database as an administrator and execute the following statement from your Oracle tool of choice, such as SQL*Plus:

CREATE USER COR PROFILE DEFAULT IDENTIFIED BY COR DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

Then grant COR the CONNECT and RESOURCE roles.

GRANT CONNECT, RESOURCE TO COR;

Creating the Repository Tables

The repository consists of six tables. The sample tables can be observed in the Microsoft Access sample repository, which is created during a standard Crystal Reports 9 installation at C:Program FilesCommon FilesCrystal Decisions2.0BinRepository_en.mdb. The tables are OR_OBJECTS, which is a master table holding the object IDs of every repository object; OR_TYPE_SCHEMA, which is a lookup table containing one record for each of the object types that can be stored in the repository, plus a “FOLDER” record; and four type-specific tables, one for each object type. The type-specific tables are OR_OBJECTDETAILS_30, which contains the detail about graphic objects; OR_OBJECTDETAILS_31, which contains the detail about text objects; OR_OBJECTDETAILS_32, which contains the detail about custom function objects; and OR_OBJECTDETAILS_33, which contains the detail about query definitions. Presumably, if new objects are added in the future, each new type will have a corresponding table.

After the creation of the repository user ID, you could create the ODBC DSN, modify the configuration file, run Crystal Reports, log in to the repository, and the repository tables would be created. However, because not all of the tables are created initially and you will be making some changes to their structure, instructions for creating the tables via Oracle commands will be given. The repository structure that would be created by default by Crystal Reports is shown in Figure 11-1.

Figure 11-1: Crystal Reports, Oracle object repository

You will make some noninvasive changes to the original structure. First, you will add a primary key on the fields OBJECT_ID and VERSION to the OR_OBJECTDETAILS_nn tables. This is the logical primary key and will facilitate backend processing. You will also change the data type for OR_OBJECTS.OWNER_ID. The reasons for this change will be explained later.

To create the repository tables, execute the following Oracle commands logged in as COR. Add any specific storage options appropriate to your database. The scripts for creating the repository objects are available as Repository.sql and Repository 8i.sql in the download files for this chapter. VARCHAR2 instead of NVARCHAR2 is used for the Oracle 8i tables.

OR_OBJECTS

CREATE TABLE OR_OBJECTS (OBJECT_ID NVARCHAR2(50) NOT NULL, OBJECT_NAME NVARCHAR2(255), PARENT_ID NVARCHAR2(50), OWNER_ID NVARCHAR2(50) DEFAULT USER, TYPE NUMBER, CONSTRAINT OR_OBJECTS_PK PRIMARY KEY(OBJECT_ID));

The type for the OWNER_ID field is Number in the original Access database. It has been changed to a character type for reasons that will be explained later.

OR_TYPE_SCHEMA

CREATE TABLE OR_TYPE_SCHEMA (TYPE NUMBER NOT NULL, TYPE_NAME NVARCHAR2(255), TABLE_NAME NVARCHAR2(255), META_INFO NVARCHAR2(255), CONSTRAINT OR_TYPE_SCHEMA_PK PRIMARY KEY(TYPE));

OR_OBJECTDETAILS_30

CREATE TABLE OR_OBJECTDETAILS_30 (OBJECT_ID NVARCHAR2(50) NOT NULL, VERSION NUMBER NOT NULL, CREATING_TIME NVARCHAR2(50) NOT NULL, MODIFIED_TIME NVARCHAR2(50), MODIFIER_NAME NVARCHAR2(50), CONTENTS BLOB DEFAULT EMPTY_BLOB(), DELETED NUMBER, SIGNATURE NVARCHAR2(32), ATTRIBUTES BLOB DEFAULT EMPTY_BLOB(), LOCALE NVARCHAR2(255), CONSTRAINT OR_OBDETAILS_30_PK PRIMARY KEY(OBJECT_ID, VERSION));

OR_OBJECTDETAILS_31

CREATE TABLE OR_OBJECTDETAILS_31 (OBJECT_ID NVARCHAR2(50) NOT NULL, VERSION NUMBER NOT NULL, CREATING_TIME NVARCHAR2(50) NOT NULL, MODIFIED_TIME NVARCHAR2(50), MODIFIER_NAME NVARCHAR2(50), CONTENTS BLOB DEFAULT EMPTY_BLOB(), DELETED NUMBER, SIGNATURE NVARCHAR2(32), ATTRIBUTES BLOB DEFAULT EMPTY_BLOB(), LOCALE NVARCHAR2(255), CONSTRAINT OR_OBDETAILS_31_PK PRIMARY KEY(OBJECT_ID, VERSION));

OR_OBJECTDETAILS_32

CREATE TABLE OR_OBJECTDETAILS_32 (OBJECT_ID NVARCHAR2(50) NOT NULL, VERSION NUMBER NOT NULL, CREATING_TIME NVARCHAR2(50) NOT NULL, MODIFIED_TIME NVARCHAR2(50), MODIFIER_NAME NVARCHAR2(50), CONTENTS BLOB DEFAULT EMPTY_BLOB(), DELETED NUMBER, SIGNATURE NVARCHAR2(32), ATTRIBUTES BLOB DEFAULT EMPTY_BLOB(), LOCALE NVARCHAR2(255), CONSTRAINT OR_OBDETAILS_32_PK PRIMARY KEY(OBJECT_ID, VERSION));

OR_OBJECTDETAILS_33

CREATE TABLE OR_OBJECTDETAILS_33 (OBJECT_ID NVARCHAR2(50) NOT NULL, VERSION NUMBER NOT NULL, CREATING_TIME NVARCHAR2(50) NOT NULL, MODIFIED_TIME NVARCHAR2(50), MODIFIER_NAME NVARCHAR2(50), CONTENTS BLOB DEFAULT EMPTY_BLOB(), DELETED NUMBER, SIGNATURE NVARCHAR2(32), ATTRIBUTES BLOB DEFAULT EMPTY_BLOB(), LOCALE NVARCHAR2(255), CONSTRAINT OR_OBDETAILS_33_PK PRIMARY KEY(OBJECT_ID, VERSION));

After you complete these commands, the new, empty repository tables will exist in the Oracle database.

Creating an ODBC DSN

Use of the repository requires an ODBC DSN because “native” connectivity to the repository is not possible. Although an Oracle ODBC or Microsoft Oracle ODBC driver may have been installed during the Oracle client or Microsoft Data Access Components (MDAC) installations to your system, Crystal Decisions recommends using the CR Oracle ODBC Driver 4.10 for repository access. The DataDirect Technologies driver must be installed with Crystal Reports, and you will need to know your Oracle TNS service name to complete the configuration.

Open the ODBC Data Source Administrator. The ODBC Data Source Administrator can usually be found in the Control Panel or Control Panel | Administrative Tools, depending on your version of Windows. Choose Add. Select the CR Oracle ODBC Driver 4.10 and click Finish. Populate the dialog as shown in Figure 11-2, using any Data Source Name and Description you want. The example uses CR_REPOS as the DSN. Enter your TNS Service Name in the Server Name field.

Figure 11-2: ODBC DSN setup for the Crystal repository

All other parameters can be left at their default values. Click Test Connect; use COR as the User ID for now. Do not continue until the connection is successful.

Populating Repository Sample Data

If you wish to populate the repository tables with sample objects, you can use any method available to move the data from the sample Access Crystal Repository database at C:Program FilesCommon FilesCrystal Decisions2.0BinRepository_en.mdb (for a standard installation) into the corresponding Oracle tables. Data loading insert statements are not supplied with this book because of the large size of the CONTENTS fields.

One simple method to populate the tables with sample data is to create database links in the sample Access database to the Oracle tables and then create append queries to move the data. To use this method, you must have an ODBC DSN for your Oracle database where the repository resides that does not use the Crystal-supplied ODBC driver. The Crystal-supplied drivers are not licensed to function outside of Crystal Reports. For this example, you will use the Oracle-ODBC-ORA DSN that was set up in Chapter 1, which uses the Oracle-supplied ODBC driver. Open the sample Crystal Repository at C:Program FilesCommon FilesCrystal Decisions2.0BinRepository_en.mdb. Choose File | Get External Data | Link Tables. In the drop-down box for Files of Type, choose ODBC Databases and pick the Oracle-ODBC-ORA data source name. Log in to the database as COR. A list of tables will be displayed. Select the six repository tables and click OK. Links to the six Oracle tables will be created.

Now go to the Queries tab in Access, double-click Create Query in Design View, and close the Show Tables dialog box. Choose View | SQL View and enter the following statement, replacing any existing text. A text file containing the load statements can be found at Chapter 11Load sample data.sql.

INSERT INTO COR_OR_OBJECTS (OBJECT_ID, OBJECT_NAME, PARENT_ID, OWNER_ID, TYPE) SELECT OR_OBJECTS.Object_id, OR_OBJECTS.Object_name, OR_OBJECTS.Parent_id, OR_OBJECTS.Owner_id, OR_OBJECTS.Type FROM OR_OBJECTS;

Run the query to populate the Oracle OR_OBJECTS table.

Repeat the preceding process for each remaining table using the following five queries. Name and save the queries, if desired.

Note that OR_OBJECTDETAILS_33 has duplicate records and some records will fail to load.

OR_TYPE_SCHEMA

INSERT INTO COR_OR_TYPE_SCHEMA (TYPE, TYPE_NAME, TABLE_NAME, META_INFO) SELECT OR_TYPE_SCHEMA.Type, OR_TYPE_SCHEMA.Type_name, OR_TYPE_SCHEMA.Table_name, OR_TYPE_SCHEMA.Meta_info FROM OR_TYPE_SCHEMA;

OR_OBJECTDETAILS_30

INSERT INTO COR_OR_OBJECTDETAILS_30 (OBJECT_ID, VERSION, CREATING_TIME, MODIFIED_TIME, MODIFIER_NAME, CONTENTS, DELETED, SIGNATURE, ATTRIBUTES, LOCALE ) SELECT OR_OBJECTDETAILS_30.Object_id, OR_OBJECTDETAILS_30.Version, OR_OBJECTDETAILS_30.Creating_time, OR_OBJECTDETAILS_30.Modified_time, OR_OBJECTDETAILS_30.Modifier_name, OR_OBJECTDETAILS_30.Contents, OR_OBJECTDETAILS_30.Deleted, OR_OBJECTDETAILS_30.Signature, OR_OBJECTDETAILS_30.Attributes, OR_OBJECTDETAILS_30.Locale FROM OR_OBJECTDETAILS_30;

OR_OBJECTDETAILS_31

INSERT INTO COR_OR_OBJECTDETAILS_31 (OBJECT_ID, VERSION, CREATING_TIME, MODIFIED_TIME, MODIFIER_NAME, CONTENTS, DELETED, SIGNATURE, ATTRIBUTES, LOCALE ) SELECT OR_OBJECTDETAILS_31.Object_id, OR_OBJECTDETAILS_31.Version, OR_OBJECTDETAILS_31.Creating_time, OR_OBJECTDETAILS_31.Modified_time, OR_OBJECTDETAILS_31.Modifier_name, OR_OBJECTDETAILS_31.Contents, OR_OBJECTDETAILS_31.Deleted, OR_OBJECTDETAILS_31.Signature, OR_OBJECTDETAILS_31.Attributes, OR_OBJECTDETAILS_31.Locale FROM OR_OBJECTDETAILS_31;

OR_OBJECTDETAILS_32

INSERT INTO COR_OR_OBJECTDETAILS_32 (OBJECT_ID, VERSION, CREATING_TIME, MODIFIED_TIME, MODIFIER_NAME, CONTENTS, DELETED, SIGNATURE, ATTRIBUTES, LOCALE ) SELECT OR_OBJECTDETAILS_32.Object_id, OR_OBJECTDETAILS_32.Version, OR_OBJECTDETAILS_32.Creating_time, OR_OBJECTDETAILS_32.Modified_time, OR_OBJECTDETAILS_32.Modifier_name, OR_OBJECTDETAILS_32.Contents, OR_OBJECTDETAILS_32.Deleted, OR_OBJECTDETAILS_32.Signature, OR_OBJECTDETAILS_32.Attributes, OR_OBJECTDETAILS_32.Locale FROM OR_OBJECTDETAILS_32;

OR_OBJECTDETAILS_33

INSERT INTO COR_OR_OBJECTDETAILS_33 (OBJECT_ID, VERSION, CREATING_TIME, MODIFIED_TIME, MODIFIER_NAME, CONTENTS, DELETED, SIGNATURE, ATTRIBUTES, LOCALE ) SELECT OR_OBJECTDETAILS_33.Object_id, OR_OBJECTDETAILS_33.Version, OR_OBJECTDETAILS_33.Creating_time, OR_OBJECTDETAILS_33.Modified_time, OR_OBJECTDETAILS_33.Modifier_name, OR_OBJECTDETAILS_33.Contents, OR_OBJECTDETAILS_33.Deleted, OR_OBJECTDETAILS_33.Signature, OR_OBJECTDETAILS_33.Attributes, OR_OBJECTDETAILS_33.Locale FROM OR_OBJECTDETAILS_33;

Verify that the data has been moved to Oracle.

Configuring Crystal to Use the New Repository

At this point, you must modify the Crystal Reports configuration file to point to the new repository tables. Open C:Program FilesCommon FilesCrystal Decisions2.0BinorMap.ini with Notepad. Modify the following line:

Crystal Repository=Crystal Repository

to the following, where CR_REPOS is the name you gave the new Oracle ODBC data source:

Crystal Repository=CR_REPOS

Close and save the file.

Test the New Repository Tables

Open an existing report in Crystal Reports. If the Repository Explorer is not open, open it. When prompted to log on to the repository, use the COR user ID and password, and the repository explorer will open. Explore the repository and try moving objects from the repository to your report and from your report to the repository. See Crystal Documentation for detailed information on how to use the repository.

Configuring Security for the Repository

Because all of the report writers in your group will use the repository, assigning appropriate database privileges is necessary to prevent users from overwriting each other’s objects, as well as for general sharing of standard objects. The sample Access repository database has no security restrictions—all users can see and modify all objects. The default Oracle repository, because it is created in a particular schema, can be seen and used only by the schema owner and other users with broad scope privileges. It cannot be used by other report writers unless they are granted specific privileges for it.

Understanding the Repository Data Model

In order to decide on a proper security scheme for the Crystal Repository in Oracle, it is necessary to understand the data model and how the repository is maintained via Crystal Reports and to decide which levels of security suit your particular purposes.

Refer to the repository data model in Figure 11-1. Note that there are no relationships shown in the diagram. The original repository database has no built-in referential integrity and no primary keys on the OR_OBJECTDETAILS_nn tables, although there is an index on OBJECT_ID for those tables.

Understanding the way Crystal Reports uses the repository tables is vital to creating a viable security scheme.

OR_OBJECTS contains one record for each active (not deleted) object stored in the repository and one record for each active folder in the repository; it has five fields:

When an object is deleted from the repository, its OR_OBJECTS record is deleted.

The OR_TYPE_SCHEMA table is a lookup table containing one record for the FOLDER type and one record for each of the currently defined object types. Users should never have to modify the data in this table. It has three fields:

The four OR_OBJECTDETAILS_nn tables contain the actual object descriptions and each correspond to an object type. There will be one OR_OBJECTDETAILS record for each version of any object that has been created; note that no OR_OBJECTDETAILS records are ever really deleted or updated. When an object is modified, a new version is created—that is, a new record is inserted with a new version number. When an object is deleted, all versions of that object’s DELETED field are set to 1, though the corresponding OBJECT_ID is deleted from the OR_OBJECTS table.

The OR_OBJECTDETAILS has nine fields:

Manipulation of the repository follows these steps:

  1. Folder objects are read from OR_OBJECTS to create the tree.

  2. Objects belonging to each folder are read from OR_OBJECTS.

  3. For each object, the appropriate OR_OBJECTDETAILS_nn table is queried in descending order by version number for all versions of the object.

  4. Data for only the first record returned, which is the most recent version, is actually retrieved.

Folders do not have any related OR_OBJECTDETAILS_nn records, so their processing is straightforward: new folder records are inserted and folder names can be updated. Note that folder records can be deleted, but folders cannot be moved.

Objects other than folders are more complex. When a new object is created, a record is inserted into OR_OBJECTS and the appropriate OR_OBJECTDETAILS_nn table simultaneously. If an object is moved, its PARENT_ID in OR_OBJECTS is modified. If an object is updated, a new version record is inserted into OR_OBJECTDETAILS_nn. If an object is deleted, it is deleted from OR_OBJECTS, and the DELETED property in OR_OBJECTDETAILS_nn is set to 1 for all versions, leaving a situation where an OBJECT_ID exists in an OR_OBJECTDETAILS_nn table, but there is no corresponding OBJECT_ID in OR_OBJECTS.

Determining Security Levels

Crystal Decisions recommends using the underlying database’s security features to add security to the repository. However, the actual security scheme is up to the individual user and should reflect their unique situation. As an example, let’s say you want each developer to be able to store and modify their own objects, but there are also certain objects that are considered standard and need to be shared across the development team. You also do not want any developer to be able to modify another developer’s objects. Moreover, you need an administrator who can manipulate the standard objects and upgrade regular developer objects to standard status. This is the example security scheme that will be demonstrated. In no way should it be considered the only possible or desirable scheme. A much simpler scheme might be sufficient for some users, or a more complex scheme might be required for others.

The example security scheme requires users to have different rights, depending on which objects they own in the repository. It would also be desirable to restrict users so that they see only the folders that they own, plus the standard folders. However, because of the processes that Crystal Reports uses to create the repository tree and populate it with objects, that restriction is difficult to implement. Therefore, in this example, all folders will be accessible to all users.

You will create one database role, COR_User, for basic repository users. In the example, the user ID COR will be the administrator and objects owned by COR will be the standard objects. You want to grant privileges to the COR_User role as shown in Table 11-1: you want members of the COR_User role to be able to insert new objects, see and use their own objects, see and use COR’s objects, modify or delete their own objects, and be restricted from modifying or deleting anyone else’s objects.

Table 11-1: COR_User Role Desired Privileges

 

The UPDATE privilege on the OR_OBJECTS table allows the PARENT_ID to be updated when an object is moved between folders. Selection and update of the OR_OBJECTDETAILS_nn records is restricted to the most recent version by native Crystal Reports processing, but you need to keep that constraint in mind when developing your restrictions by user because the constraint to the most current version must be the first level of filtering, independent of the owner of the versions. Older versions might be kept for auditing purposes only. For deleting objects, the UPDATE privilege is required on OR_OBJECTDETAILS_nn records to allow the DELETED field to be set to 1. Because new versions are added rather than existing versions updated, it’s possible for a user to disconnect a standard object (an object owned by COR) from the repository and then store an updated version, in effect replacing the standard version. The distinction in Table 11-1 between inserting new records and inserting updates is made to account for this possibility. No deletions are ever made from the OR_OBJECTDETAILS_nn tables.

Desired privileges for the COR user (the administrator) are shown in Table 11-2.

Table 11-2: COR User Desired Privileges

 

COR is allowed to insert next versions even when the current version is owned by another user; this is how COR can take ownership of another user’s object and turn it into a standard object. COR must also have update privileges for records that it does not own so that the triggers that maintain the deleted field and the current owner field will function when COR takes ownership of another user’s object.

Implementing Repository Security

Implementing your security scheme will require two steps. First, you will add triggers to the repository tables that will simplify the addition of the security policies, and then you will add the security policies.

Configuring Tables

The somewhat awkward repository data model, along with the versioning process, makes creating the desired security model difficult. It would simplify the security implementation if you could tell, via a simple query, which OR_OBJECTDETAILS_nn record was the current version for each object. Because you know that only the most recent version of an object is used by the repository (and therefore ever read by Crystal Reports), you can safely tag the older version records in some way. The DELETED field is set to 1 for all versions when an object is deleted. It seems reasonable to use the DELETED field and set it to 1 immediately for the old version when a new version is created. If all old versions are set to 1, you know that any record whose version is 0 is a current version record. You can implement this via triggers on the OR_OBJECTDETAILS_nn tables.

You also need to take steps to simplify security constraints for the OR_OBJECTS table. To do that, you need to track the current owner of each object and use the already existing OWNER_ID column and triggers to keep it updated. It would be more desirable to create a new column for this purpose since you do not know what future use Crystal Decisions might have in mind for the OWNER_ID column. However, if you add a column, the insert commands, which are of the form “Insert into OR_OBJECTS Values ( )” (with no list of column names), will fail. Because there is no list of column names, Oracle expects a value for every column in the appropriate order, and if you add a column, Crystal Reports will not know to supply a value for it and the insert would fail. You could still add a column and then create a view named OR_OBJECTS containing only the original columns, but then you would have to write INSTEAD OF triggers for the view, and all processing would be more complex.

Instead, you should set the previous version of the OR_OBJECTDETAILS_nn deleted field to 1 when the new version is added. This requires you to update a record in the same table that contains the record that is being inserted, so you cannot use a straightforward trigger because mutating table problems would result. Instead, save the OBJECT_ID and VERSION of the old record into a temporary table in a row-level trigger and have a second statement level trigger apply the updates.

For OR_OBJECTS, update the OWNER_ID when changes are made to the detail tables so that the OR_OBJECTS.OWNER_ID is always equal to the modifier name of the current detail record.

Because you will be using COR as the administrator, set COR as the owner of all existing objects using the following commands. These commands are available in Chapter 11Set COR as owner.sql.

UPDATE OR_OBJECTS SET owner_id='COR' WHERE owner_id IS NULL OR owner_id='0'; UPDATE OR_OBJECTDETAILS_30 SET Modifier_Name='COR' WHERE Modifier_Name IS NULL; UPDATE OR_OBJECTDETAILS_31 SET Modifier_Name='COR' WHERE Modifier_Name IS NULL; UPDATE OR_OBJECTDETAILS_32 SET Modifier_Name='COR' WHERE Modifier_Name IS NULL; UPDATE OR_OBJECTDETAILS_33 SET Modifier_Name='COR' WHERE Modifier_Name IS NULL;

Create the temporary table and triggers as follows, logged in as COR. These commands are available in Chapter 11Repository triggers.sql and Repository triggers 8i.sql.

CREATE GLOBAL TEMPORARY TABLE OLD_VERSIONS (OBJECT_ID NVARCHAR2(50) NOT NULL, VERSION NUMBER NOT NULL, PRIMARY KEY(OBJECT_ID, VERSION)) ON COMMIT DELETE ROWS;

This creates a temporary table for storage of the old version’s primary key fields. The contents of the temporary table will be deleted after the actions are completed.

CREATE OR REPLACE TRIGGER TRIGGER_30_ROW BEFORE INSERT ON COR.OR_OBJECTDETAILS_30 FOR EACH ROW BEGIN :NEW.Modifier_Name:=UPPER(:NEW.Modifier_Name); UPDATE COR.OR_OBJECTS SET Owner_ID=:NEW.Modifier_Name WHERE Object_ID=:NEW.Object_ID; INSERT INTO COR.OLD_VERSIONS VALUES (:NEW.OBJECT_ID, :NEW.VERSION-1); END;

This is the row-level trigger on the detail table. It converts the new Modifier_Name to uppercase because Crystal Reports sends the user name that the user types into the logon dialog, which may be in lowercase or mixed case, then it updates the OWNER_ID in the OR_OBJECTS table to match the MODIFIER_NAME of the most recent version of the object in the detail table. It also inserts the OBJECT_ID and old version number into the temporary table for use by the statement level trigger. It is executed before the insert.

This update trigger is required to keep the Modifier_Name in uppercase because Crystal Reports applies an update after the initial insert into the OR_OBJECTDETAILS tables.

CREATE OR REPLACE TRIGGER TRIGGER_30_UPDATE BEFORE UPDATE ON COR.OR_OBJECTDETAILS_30 FOR EACH ROW BEGIN :NEW.Modifier_Name:=UPPER(:NEW.Modifier_Name); END;

This is the statement level trigger that reads the temporary table and sets the DELETED field to 1 for the old version of the object in the detail table. It is executed after the insert.

CREATE OR REPLACE TRIGGER TRIGGER_30_STATEMENT AFTER INSERT ON COR.OR_OBJECTDETAILS_30 BEGIN FOR UPD IN (SELECT OBJECT_ID, VERSION FROM COR.OLD_VERSIONS) LOOP UPDATE COR.OR_OBJECTDETAILS_30 SET Deleted=1 WHERE OBJECT_ID=UPD.OBJECT_ID AND VERSION=UPD.VERSION; END LOOP; END;

Similar triggers for the other object detail tables must be created.

CREATE OR REPLACE TRIGGER TRIGGER_31_ROW BEFORE INSERT ON COR.OR_OBJECTDETAILS_31 FOR EACH ROW BEGIN :NEW.Modifier_Name:=UPPER(:NEW.Modifier_Name); UPDATE COR.OR_OBJECTS SET Owner_ID=:NEW.Modifier_Name WHERE Object_ID=:NEW.Object_ID; INSERT INTO COR.Old_Versions VALUES (:NEW.OBJECT_ID, :NEW.VERSION-1); END; / CREATE OR REPLACE TRIGGER TRIGGER_31_UPDATE BEFORE UPDATE ON COR.OR_OBJECTDETAILS_31 FOR EACH ROW BEGIN :NEW.Modifier_Name:=UPPER(:NEW.Modifier_Name); END; / CREATE OR REPLACE TRIGGER TRIGGER_31_STATEMENT AFTER INSERT ON COR.OR_OBJECTDETAILS_31 BEGIN FOR UPD IN (SELECT OBJECT_ID, VERSION FROM COR.OLD_VERSIONS) LOOP UPDATE COR.OR_OBJECTDETAILS_31 SET Deleted=1 WHERE OBJECT_ID=UPD.OBJECT_ID AND VERSION=UPD.VERSION; END LOOP; END; / CREATE OR REPLACE TRIGGER TRIGGER_32_ROW BEFORE INSERT ON COR.OR_OBJECTDETAILS_32 FOR EACH ROW BEGIN :NEW.Modifier_Name:=UPPER(:NEW.Modifier_Name); UPDATE COR.OR_OBJECTS SET Owner_ID=:NEW.Modifier_Name WHERE Object_ID=:NEW.Object_ID; INSERT INTO COR.Old_Versions VALUES (:NEW.OBJECT_ID, :NEW.VERSION-1); END; / CREATE OR REPLACE TRIGGER TRIGGER_32_UPDATE BEFORE UPDATE ON COR.OR_OBJECTDETAILS_32 FOR EACH ROW BEGIN :NEW.Modifier_Name:=UPPER(:NEW.Modifier_Name); END; / CREATE OR REPLACE TRIGGER TRIGGER_32_STATEMENT AFTER INSERT ON COR.OR_OBJECTDETAILS_32 BEGIN FOR UPD IN (SELECT OBJECT_ID, VERSION FROM COR.OLD_VERSIONS) LOOP UPDATE COR.OR_OBJECTDETAILS_32 SET Deleted=1 WHERE OBJECT_ID=UPD.OBJECT_ID AND VERSION=UPD.VERSION; END LOOP; END; / CREATE OR REPLACE TRIGGER TRIGGER_33_ROW BEFORE INSERT ON COR.OR_OBJECTDETAILS_33 FOR EACH ROW BEGIN :NEW.Modifier_Name:=UPPER(:NEW.Modifier_Name); UPDATE COR.OR_OBJECTS SET Owner_ID=:NEW.Modifier_Name WHERE Object_ID=:NEW.Object_ID; INSERT INTO COR.Old_Versions VALUES (:NEW.OBJECT_ID, :NEW.VERSION-1); END; / CREATE OR REPLACE TRIGGER TRIGGER_33_UPDATE BEFORE UPDATE ON COR.OR_OBJECTDETAILS_33 FOR EACH ROW BEGIN :NEW.Modifier_Name:=UPPER(:NEW.Modifier_Name); END; / CREATE OR REPLACE TRIGGER TRIGGER_33_STATEMENT AFTER INSERT ON COR.OR_OBJECTDETAILS_33 BEGIN FOR UPD IN (SELECT OBJECT_ID, VERSION FROM COR.OLD_VERSIONS) LOOP UPDATE COR.OR_OBJECTDETAILS_33 SET Deleted=1 WHERE OBJECT_ID=UPD.OBJECT_ID AND VERSION=UPD.VERSION; END LOOP; END;

Because Crystal Reports tries to populate the OWNER_ID with the number 0, you also need to reset it to the real user ID when an object is inserted:

CREATE OR REPLACE TRIGGER OR_OBJECTS_TRIGGER BEFORE INSERT ON COR.OR_OBJECTS FOR EACH ROW BEGIN :NEW.OWNER_ID:=UPPER(USER); END;

To initialize the existing records, run the following commands. This will set the current owner and deleted fields properly for any already existing repository objects. These commands are available in Chapter 11Initialize Repository.sql.

The UPDATE commands for the OR_OBJECTDETAILS_nn tables set the deleted field to 1 for every version of an object except the most recent version. The most recent version is the record with the highest version number as returned by the inline view called b.

UPDATE OR_OBJECTDETAILS_30 SET deleted=1 WHERE ROWID IN (SELECT a.rowid FROM OR_OBJECTDETAILS_30 a, (SELECT object_id, MAX(version) version FROM OR_OBJECTDETAILS_30 GROUP BY object_id) b WHERE a.object_id=b.object_id AND a.version<>b.version); / UPDATE OR_OBJECTDETAILS_31 SET deleted=1 WHERE ROWID IN (SELECT a.rowid FROM OR_OBJECTDETAILS_31 a, (SELECT object_id, MAX(version) version FROM OR_OBJECTDETAILS_31 GROUP BY object_id) b WHERE a.object_id=b.object_id AND a.version<>b.version); / UPDATE OR_OBJECTDETAILS_32 SET deleted=1 WHERE ROWID IN (SELECT a.rowid FROM OR_OBJECTDETAILS_32 a, (SELECT object_id, MAX(version) version FROM OR_OBJECTDETAILS_32 GROUP BY object_id) b WHERE a.object_id=b.object_id AND a.version<>b.version); / UPDATE OR_OBJECTDETAILS_33 SET deleted=1 WHERE ROWID IN (SELECT a.rowid FROM OR_OBJECTDETAILS_33 a, (SELECT object_id, MAX(version) version FROM OR_OBJECTDETAILS_33 GROUP BY object_id) b WHERE a.object_id=b.object_id AND a.version<>b.version);

The UPDATE statements that follow for the OR_OBJECTS table set the Owner_ID to equal the Modifier_Name of the most recent version of an object as found in the OR_OBJECTDETAILS_nn tables. There are four update statements, one for each object type:

UPDATE OR_OBJECTS a SET Owner_Id= (SELECT Modifier_Name FROM OR_OBJECTDETAILS_30 b WHERE b.object_id=a.object_id AND b.deleted=0) WHERE Object_ID IN (Select Object_ID from OR_OBJECTDETAILS_30); / UPDATE OR_OBJECTS a SET Owner_Id = (SELECT Modifier_Name FROM OR_OBJECTDETAILS_31 b WHERE b.object_id=a.object_id AND b.deleted=0) WHERE Object_ID IN (Select Object_ID from OR_OBJECTDETAILS_31); / UPDATE OR_OBJECTS a SET Owner_Id = (SELECT Modifier_Name FROM OR_OBJECTDETAILS_32 b WHERE b.object_id=a.object_id AND b.deleted=0) WHERE Object_ID IN (Select Object_ID from OR_OBJECTDETAILS_32); / UPDATE OR_OBJECTS a SET Owner_Id = (SELECT Modifier_Name FROM OR_OBJECTDETAILS_33 b WHERE b.object_id=a.object_id AND b.deleted=0) WHERE Object_ID IN (Select Object_ID from OR_OBJECTDETAILS_33);

Crystal Reports will check to see if tables of the appropriate names are available to the logged in user. If they are not available, it will create tables in the logged in user’s schema. To avoid this, create public synonyms for the COR tables. Chapter 11Create Synonyms.sql contains the following commands. For the COR user to be able to create these public synonyms, the COR user must be granted the CREATE PUBLIC SYNONYM privilege. Alternatively, a DBA can create the synonyms.

CREATE PUBLIC SYNONYM OR_OBJECTS FOR COR.OR_OBJECTS; CREATE PUBLIC SYNONYM OR_TYPE_SCHEMA FOR COR.OR_TYPE_SCHEMA; CREATE PUBLIC SYNONYM OR_OBJECTDETAILS_30 FOR COR.OR_OBJECTDETAILS_30; CREATE PUBLIC SYNONYM OR_OBJECTDETAILS_31 FOR COR.OR_OBJECTDETAILS_31; CREATE PUBLIC SYNONYM OR_OBJECTDETAILS_32 FOR COR.OR_OBJECTDETAILS_32; CREATE PUBLIC SYNONYM OR_OBJECTDETAILS_33 FOR COR.OR_OBJECTDETAILS_33;

Implementing Security Policies

Beginning with version 8i, Oracle contains a feature called Virtual Private Database (VPD) or fine-grained access control. Traditional access control mechanisms can restrict access at the table or column level. VPD allows access restrictions at the row level without requiring the creation of views. Before VPD, if restrictions were required at the row level, views were created that returned only the rows that a certain user or group of users had access to, and privileges were granted to the views instead of the underlying tables. With VPD, the creation of views is not necessary. VPD works by appending a WHERE clause, transparently, to every query executed against the table in question. Functions are created that return a string containing the filtering clause, and then functions are applied to tables via new security policy procedures.

You can implement your security model using Oracle’s Virtual Private Database or fine-grained access control features. You will create three functions to implement the security scheme: one for inserts, one for selects, and one for updates and deletes. However, you must first create the COR_USER role and grant privileges on the repository objects to that role, which will give members of the COR_USER role table level privileges to the COR objects. The VPD functions will then add row level restrictions to those privileges.

A user with DBA privileges must create the COR_USER role as shown:

CREATE ROLE COR_USER NOT IDENTIFIED;

Then the user COR can grant the appropriate privileges. These grant statements can be found in Chapter 11COR_USER.sql.

GRANT SELECT, INSERT, UPDATE, DELETE ON COR.OR_OBJECTS TO COR_USER; GRANT SELECT ON COR.OR_TYPE_SCHEMA TO COR_USER: GRANT SELECT, INSERT, UPDATE ON COR.OR_OBJECTDETAILS_30 TO COR_USER; GRANT SELECT, INSERT, UPDATE ON COR.OR_OBJECTDETAILS_31 TO COR_USER; GRANT SELECT, INSERT, UPDATE ON COR.OR_OBJECTDETAILS_32 TO COR_USER; GRANT SELECT, INSERT, UPDATE ON COR.OR_OBJECTDETAILS_33 TO COR_USER; GRANT SELECT, INSERT ON COR.OLD_VERSIONS TO COR_USER;

The insert function must create a string that is usable as a WHERE clause, adds no restrictions on inserts for the user COR who is allowed to insert into OR_OBJECTS, and inserts both first versions and subsequent versions into the OR_OBJECTDETAILS. For other users, the clause must add no restrictions for the OR_OBJECTS table and, for the OR_OBJECTDETAILS tables, it must restrict the session user to inserting new versions or inserting subsequent versions only when they own the current version.

CREATE OR REPLACE FUNCTION OR_INSERT_FUNCTION (Schema_Name IN VARCHAR2, Table_Name IN VARCHAR2) RETURN VARCHAR2 AS Clause VARCHAR2(2000); BEGIN Clause:=''; IF Table_Name LIKE 'OR_OBJECTDETAILS___' THEN IF USER<>'COR' THEN Clause:='Version=1 OR UPPER(NVL(USER,''COR''))= (SELECT UPPER(NVL(Owner_ID,''COR'')) FROM COR.OR_OBJECTS WHERE COR.'||Table_name||'.Object_ID =COR.OR_OBJECTS.Object_ID)'; END IF; END IF; RETURN Clause; END;

For the OR_OBJECTS table, the select function must allow COR to select any objects (with no restrictions) and allow other users to select only folders or objects that they own or objects that are owned by COR. For the OR_OBJECTDETAILS tables, the select function must allow COR to select only the most recent version of objects and allow other users to select only the most recent version of objects that they own or that are owned by COR.

CREATE OR REPLACE FUNCTION OR_SELECT_FUNCTION (Schema_Name IN VARCHAR2, Table_Name IN VARCHAR2) RETURN VARCHAR2 AS Clause VARCHAR2(2000); BEGIN Clause:=''; IF Table_Name='OR_OBJECTS' THEN IF USER<>'COR' THEN Clause:='TYPE=0 OR (UPPER(NVL(Owner_ID,''COR''))=UPPER(USER) OR UPPER(NVL(Owner_ID,''COR''))=''COR'')'; END IF; END IF; IF Table_Name LIKE 'OR_OBJECTDETAILS___' THEN IF USER='COR' THEN Clause:='Deleted=0'; ELSE Clause:='Deleted=0 AND (UPPER(NVL(Modifier_Name,''COR''))=UPPER(USER) OR UPPER(NVL(Modifier_Name,''COR''))=''COR'')'; END IF; END IF; RETURN Clause; END;

For the OR_OBJECTS table, the function used for updates and deletes must restrict users other than COR from updating any object they do not own. For the OR_OBJECTDETAILS tables, the function must restrict updates for users other than COR to the current version only for objects owned by the session user. For COR, the function must restrict updates to the current version. COR must be allowed to update any records in both of these tables in order to be able to take ownership of objects and have those objects treated as standard objects in the future.

CREATE OR REPLACE FUNCTION OR_UPDATE_OR_DELETE_FUNCTION (Schema_Name IN VARCHAR2, Table_Name IN VARCHAR2) RETURN VARCHAR2 AS Clause VARCHAR2(2000); BEGIN Clause:=''; IF Table_Name='OR_OBJECTS' THEN IF USER<>'COR' THEN Clause:='UPPER(NVL(Owner_ID,''COR''))=UPPER(USER)'; END IF; END IF; IF Table_Name LIKE 'OR_OBJECTDETAILS___' THEN IF USER='COR' THEN Clause:='Deleted=0'; ELSE Clause:='Deleted=0 AND UPPER(NVL(Modifier_Name,''COR''))=UPPER(USER)'; END IF; END IF; RETURN Clause; END;

The security functions are implemented with the following commands. To successfully execute these commands the COR user must be granted the EXECUTE privilege on the DBMS_RLS package by a DBA.

GRANT EXECUTE ON DBMS_RLS TO COR;

The security policy creation statements follow and can also be found in Chapter 11Policy Creation.sql:

EXECUTE DBMS_RLS.ADD_POLICY('COR','OR_OBJECTS', 'OR_SELECT_POLICY','COR', 'OR_SELECT_FUNCTION','SELECT'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_30', 'OR_SELECT_POLICY', 'COR','OR_SELECT_FUNCTION','SELECT'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_31', 'OR_SELECT_POLICY', 'COR','OR_SELECT_FUNCTION','SELECT'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_32', 'OR_SELECT_POLICY', 'COR','OR_SELECT_FUNCTION','SELECT'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_33', 'OR_SELECT_POLICY', 'COR','OR_SELECT_FUNCTION','SELECT'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTS', 'OR_INSERT_POLICY','COR', 'OR_INSERT_FUNCTION','INSERT'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_30', 'OR_INSERT_POLICY', 'COR','OR_INSERT_FUNCTION','INSERT',TRUE); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_31', 'OR_INSERT_POLICY', 'COR','OR_INSERT_FUNCTION','INSERT',TRUE); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_32', 'OR_INSERT_POLICY', 'COR','OR_INSERT_FUNCTION','INSERT',TRUE); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_33', 'OR_INSERT_POLICY', 'COR','OR_INSERT_FUNCTION','INSERT',TRUE); EXECUTE DBMS_RLS.ADD_POLICY 'COR','OR_OBJECTS', 'OR_UPDATE_OR_DELETE_POLICY', 'COR', 'OR_UPDATE_OR_DELETE_FUNCTION','UPDATE,DELETE'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_30', 'OR_UPDATE_OR_DELETE_POLICY','COR', 'OR_UPDATE_OR_DELETE_FUNCTION', 'UPDATE,DELETE'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_31', 'OR_UPDATE_OR_DELETE_POLICY','COR', 'OR_UPDATE_OR_DELETE_FUNCTION', 'UPDATE,DELETE'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_32', 'OR_UPDATE_OR_DELETE_POLICY','COR', 'OR_UPDATE_OR_DELETE_FUNCTION', 'UPDATE,DELETE'); EXECUTE DBMS_RLS.ADD_POLICY ('COR','OR_OBJECTDETAILS_33', 'OR_UPDATE_OR_DELETE_POLICY','COR', 'OR_UPDATE_OR_DELETE_FUNCTION','UPDATE,DELETE');

Now your security policy is implemented. Create new user IDs, assign the COR_User role to them, and test that their privileges are as expected. You should also grant the COR_User role to Xtreme:

GRANT COR_USER TO XTREME;

Using this security scheme, it is recommended that you create separate folders for standard objects and move any user objects into those folders when COR takes ownership of those objects. Otherwise, users may see standard objects showing up in other user’s folders and be confused. Crystal Reports has no knowledge of the security policies that have been implemented. If you attempt a repository action that will not be allowed due to the security policies, you may receive a generic error message stating that the action could not be completed or no error message at all. You should also note that the user logged in to the repository is completely independent of the database connections for the report. You may be logged in to the repository as one user and to the report datasources as a different user.

Using the Repository

Storing objects in the repository allows you to share them with other report developers. It also allows you to modify an object in the repository and then update reports that contain that object so that those reports will use the new version. This is a great addition to efficiency and standards maintenance.

As mentioned previously, there are four types of objects that can be stored in the repository: SQL Commands, custom functions, graphics, and text objects. For SQL Commands against Oracle databases and custom functions written to be used only against Oracle databases, I recommend creating Oracle views and PL/SQL functions instead of depending on the repository. You will get the same benefits of sharable code, with truly automatic version updating, plus speed and security enhancements. If you need to report off non-Oracle databases, or cannot create views and functions, then the repository is the second best solution.

There is no Oracle substitute for storing graphic and text objects in the repository. These objects are used for formatting and there is no database equivalent. (You could create a table for these objects and join them into your report, but that would be awkward and nonintuitive.) You should take advantage of the repository for objects of these types.

Refer to Crystal Reports or other third-party documentation for details concerning the use of the repository. How to use the repository is not specific to the database being used to house it, so there are no specific Oracle topics to cover, other than those related to the backend security scheme described in the previous section.

A Repository Report

Because the repository has been moved into an Oracle database, you can create a Crystal Report to list the repository objects. Most repository-related information is readily available from the Repository Explorer, but a paper report might be desirable documentation, especially for determining the owner of various objects, since the owner is not displayed in the Repository Explorer.

For the report, you will want information from the OR_OBJECTS table and from the OR_OBJECTDETAILS tables. Because all of the OR_OBJECTDETAILS tables have the same columns defined and are joined to OR_OBJECTS on the same field, it will simplify the report if you union the detail tables together. Here is a view definition that implements the union and converts the datetime string fields into Oracle date fields. This statement is available as Chapter 11OR_OBJECTDETAILS_VIEW.sql. Create the view logged in as the COR user.

CREATE OR REPLACE VIEW OR_OBJECTDETAILS_VIEW AS SELECT TO_DATE(CREATING_TIME,'YYYY-MM-DD HH24:MI:SS ') CREATING_TIME, TO_DATE(MODIFIED_TIME,'YYYY-MM-DD HH24:MI:SS ') MODIFIED_TIME, OBJECT_ID, VERSION FROM COR.OR_OBJECTDETAILS_30 UNION ALL SELECT TO_DATE(CREATING_TIME,'YYYY-MM-DD HH24:MI:SS '), TO_DATE(MODIFIED_TIME,'YYYY-MM-DD HH24:MI:SS '), OBJECT_ID, VERSION FROM COR.OR_OBJECTDETAILS_31 UNION ALL SELECT TO_DATE(CREATING_TIME,'YYYY-MM-DD HH24:MI:SS '), TO_DATE(MODIFIED_TIME,'YYYY-MM-DD HH24:MI:SS '), OBJECT_ID, VERSION FROM COR.OR_OBJECTDETAILS_32 UNION ALL SELECT TO_DATE(CREATING_TIME,'YYYY-MM-DD HH24:MI:SS '), TO_DATE(MODIFIED_TIME,'YYYY-MM-DD HH24:MI:SS '), OBJECT_ID, VERSION FROM COR.OR_OBJECTDETAILS_33 WITH READ ONLY;

Note that the contents or attributes fields are not included. They are BLOB fields that contain XML-like information, and Crystal Reports cannot interpret them correctly.

Once the view is created, SELECT privileges for it should be granted to the COR_User role as shown here:

GRANT SELECT ON OR_OBJECTDETAILS_VIEW TO COR_USER;

Note that even though the OR_OBJECTDETAILS tables contain multiple versions of objects, you will never see more than one version. The security policy will restrict the SELECT to returning the current version. Likewise, the security policies that were implemented for using the repository apply equally to the report. You will only be able to report on repository objects that you have privileges to see via the security policies, so you do not need to implement separate security for the report.

Open Chapter 11Repository.rpt. You will receive an error stating that a repository object has been deleted because the report contains two objects that are linked to my repository and do not exist in your repository. The report will run anyway because local copies of the objects are kept in the report file. Run the report logging in as any user who has the COR_User role. You will see something similar to Figure 11-3.

Figure 11-3: Repository report

The report uses the OR_OBJECTS table and the OR_OBJECTDETAILS_VIEW view and will return any rows that pass the security policy conditions for the logged in user. It is grouped hierarchically to show the folder locations of the objects. You may substitute your own graphic and contact text for that which is included in the sample report.

Whether you use an Access database or an Oracle database to contain the repository objects, the repository features can be useful to you. This chapter introduced an example that can be used to implement multiuser security for a repository in an Oracle database, and many other security options are possible as well.

Related

Категории