CREATE OR REPLACE PACKAGE WBSecurityServiceImpl AS FUNCTION isSecurityServiceCustomized RETURN NUMBER; /* Function: isSecurityServiceCustomized() is used to differentiate the implementation of security service ,whether the security PL/SQL implementation is customized by you or the implementation is provided by Warehouse builder itself. Return value: this function should return 1 if user wants to implement the pl/sql package of this specification, otherwise: 0; */ PROCEDURE securityCheckForCreation(outcome OUT NUMBER, userId IN VARCHAR2, objectUOIDOperationInvokedOn IN VARCHAR2, status IN VARCHAR2, parentModuleUOID IN VARCHAR2, parentProjUOID IN VARCHAR2, repos_Schema IN VARCHAR2, objectType IN NUMBER); /* Procedure: securityCheckForCreation is used for create operation security check. Whenever user attempts to create an object, warehouse builder will call this procedure to ask the implmentation whether the creation operation is acceptable or not. Argument explaination: outcome: 1: the creation operation is acceptible 0: the creation operation is not acceptible userId: the login user's database user name objectUOIDOperationInvokedOn: the folder parent's UOID, where user try to create a new object. If user try to create a new object like Project, Snapshot which do not have folder parent, then this argument will be NULL. status: An attribute of a module. Which is one of WB_DEV_STATUS, WB_QA_STATUS, WB_PROD_STATUS defined in this specification. This attribute is used to describe the process status of the installed module. If user attempts to create project, module or snapshot which are not child of any module in the parentage hierarchy, this argument will be NULL. parentModuleUOID: the UOID of the module under which(may not directly) user is trying to create a new object. If user try to create a module or project or snapshot which are not children of any module in the parentage hierarchy, this argument will be NULL. parentProjUOID: the UOID of the project under which(may not directly) user try to create an new object. if user try to create a project or snapshot which are not child of any project in the parentage hierarchy, this argument will be NULL. repos_schema: the central repository schema name the user is working on objectType: the type of object user is trying to create. It is one of the object type constants defined in this specification. */ PROCEDURE securityCheck(outcome OUT NUMBER, userId IN VARCHAR2, operation IN NUMBER, objectUOIDOperationInvokedOn IN VARCHAR2, objectTypeOperationInvokedOn IN NUMBER, status IN VARCHAR2, parentModuleUOID IN VARCHAR2, parentProjUOID IN VARCHAR2, repos_Schema IN VARCHAR2); /* PROCEDURE: securityCheck is used for the security check purpose for the following operations (constants defined in this specification): WB_EDIT WB_DELETE WB_VALIDATE WB_GENERATION WB_VERSION Whenever user try to invoke one of the above listed operations, warehouse builder will call this procedure to ask it whether the operation is acceptable or not. Argument explaination: outcome: 1: the creation operation is acceptible 0: the creation operation is not acceptible userId: the login user's database user name operation: One of constants listed above. objectUOIDOperationInvokedOn: the target object's UOID objectTypeOperationInvokedOn: The type of the object the operation is invoked on. It is one of the object type constants defined in this specification. status: An attribute of a module. Which is one of(defined in this specification): WB_DEV_STATUS WB_QA_STATUS, WB_PROD_STATUS This attribute is used to describe what progress status of the installed module. If user try to operate on project, module or snapshot which are not child of any module in the parentage hierarchy , this argument will be NULL. parentModuleUOID: the UOID of the module . The uesr is trying to invoke the operation on an object under the module.If user is invoking the operation on a module, then objectUOIDOperationInvokedOn and parentModuleUOID are the same. If user attempts to invoke an operation on a project or snapshot which are not child of any module in the parentage hierarchy, this argument will be NULL. parentProjUOID: the UOID of the project. User is trying to invoke the operation on an object under the project.If user is invoking the operation on a project,then objectUOIDOperationInvokedOn and parentProjUOID are the same. If user try to invoke the operation on a snapshot which are not child of any project in the parentage hierarchy, this argument will be NULL. repos_schema: the central repository schema name the user is working on */ PROCEDURE securityCheckForService(outcome OUT NUMBER, userId IN VARCHAR2, serviceOp IN NUMBER, moduleUOID IN VARCHAR2, projUOID IN VARCHAR2, repos_Schema IN VARCHAR2); /* PROCEDURE securityCheckForService is used for the security check purpose for the following service operations (constants defined in this specification): WB_DEPLOY WB_MDL_IMPORT WB_MDL_EXPORT WB_BRIDGE_IMPORT WB_BRIDGE_EXPORT WB_SOURCE_IMPORT WB_RUNTIME_EXECUTE WB_SNAPSHOT_RESTORE Argument explaination: outcome: 1: the creation operation is acceptible 0: the creation operation is not acceptible userId: the login user's database user name serviceOp: One of constants list above. moduleUOID: the UOID of a module and user is trying to invoke the operation:serviceOp on it. It will be NULL for the following serviceOp:WB_DEPLOY,WB_BRIDGE_EXPORT, WB_RUNTIME_EXECUTE, and only make sense for: WB_MDL_IMPORT, WB_MDL_EXPORT,WB_SOURCE_IMPORT,WB_SNAPSHOT_RESTORE. projUOID: the UOID of a project and user is trying to invoke the operation: serviceOp on it. It will be NULL for the following serviceOp:WB_DEPLOY, WB_BRIDGE_EXPORT, WB_RUNTIME_EXECUTE, and only make sense for: WB_MDL_IMPORT, WB_MDL_EXPORT,WB_SOURCE_IMPORT,WB_SNAPSHOT_RESTORE. repos_schema: the central repository schema name the user is working on */ ------BEGIN CONSTANT DEFINITION---------------------------------- CUSTOM_SHARED_LIBRARY CONSTANT VARCHAR2(100):='9E012195D16211D48D7100B0D02A59E8'; /*CUSTOM_SHARED_LIBRARY: is UOID constant for a Warehouse builder predefined folder :Custom shared library(from Warehouse builder user interface, it is the folder named "Custom" under node "Public Transformation". ) This folder contains all global shared libary transformation created by user. You can check the argument objectUOIDOperationInvokedOn in procedure securityCheckForCreation equals to this constant or not if you want to control which users can create transformations under folder "Custom". And you can check the argument parentModuleUOID in procudure securityCheck equals to this constant or not to control user can invoke the operation like WB_EDIT, WB_DELETE etc on a shared function or a procedure. Since this module is predefined, user can not change the status (one of WB_DEV_STATUS ,WB_QA_STATUS ,WB_PROD_STATUS) ,hence you can not use the status of this module to do the access control. */ ---definition of the constants for all the basic operations WB_EDIT CONSTANT INTEGER := 0; WB_DELETE CONSTANT INTEGER := 1; WB_REFERENCE CONSTANT INTEGER := 2; WB_CREATE CONSTANT INTEGER := 3; WB_VALIDATE CONSTANT INTEGER := 4; WB_GENERATION CONSTANT INTEGER := 5; WB_VERSION CONSTANT INTEGER := 6; /* *This group of operation constants should be used in procedure *SecurityCheck. The argument "operation" will be one of the above *constants. You can use it to control which user can invoke this *operation. */ ---definition of constants for all service type operations WB_DEPLOY CONSTANT INTEGER := 100; WB_MDL_IMPORT CONSTANT INTEGER := 101; WB_MDL_EXPORT CONSTANT INTEGER := 102; WB_BRIDGE_IMPORT CONSTANT INTEGER := 103; WB_BRIDGE_EXPORT CONSTANT INTEGER := 104; WB_SOURCE_IMPORT CONSTANT INTEGER := 105; WB_RUNTIME_EXECUTE CONSTANT INTEGER :=106; WB_SNAPSHOT_RESTORE CONSTANT INTEGER := 107; /* *This group of service operation constants should be used in procedure *SecurityCheckForService. The argument "serviceOp" will be one of the above *constants. You can use it to control which user can invoke this *service operation. */ ---definition of 3 status in module WB_DEV_STATUS CONSTANT VARCHAR2(100) := 'DEV_STATUS'; WB_QA_STATUS CONSTANT VARCHAR2(100) := 'QA_STATUS'; WB_PROD_STATUS CONSTANT VARCHAR2(100) := 'PROD_STATUS'; /* *This group of module status constants should be used in procedures *securityCheckForCreation and SecurityCheck, The argument *"status" will be one of the above constants if the object user want to *create is a child of a module(for securityCheckForCreation) or if the *operation user invoked is on a module or the child of a module(for *SecurityCheck),otherwise, the argument "status" will be null. You can *use it to implement your access control based on the status of a module. */ ---definition of object type---- WB_PROJECT CONSTANT INTEGER := 1; WB_ORACLE_MODULE CONSTANT INTEGER := 2; WB_GATEWAY_MODULE CONSTANT INTEGER := 3; WB_SAP_MODULE CONSTANT INTEGER := 4; WB_FLAT_FILE_MODULE CONSTANT INTEGER := 5; WB_SHARED_MODULE CONSTANT INTEGER := 6; WB_REPOS_MODULE CONSTANT INTEGER := 7; WB_COLLECTION CONSTANT INTEGER := 8; /*from paris release on, the WB_WAREHOUSE is not used any more, owb only use WB_ORACLE_MODULE which refers to both the ever-been called source module and eve been called target module. But we leave the WB_WAREHOUSEdefinition here for backward compatibility*/ WB_WAREHOUSE CONSTANT INTEGER := 2; WB_TABLE CONSTANT INTEGER := 10; WB_VIEW CONSTANT INTEGER := 11; WB_MATERIALIZED_VIEW CONSTANT INTEGER := 12; WB_SEQUENCE CONSTANT INTEGER := 13; WB_DIMENSION_TABLE CONSTANT INTEGER := 14; WB_CUBE_TABLE CONSTANT INTEGER := 15; WB_FLAT_FILE CONSTANT INTEGER := 16; WB_PACKAGE CONSTANT INTEGER := 17; WB_TRANSFORMATION CONSTANT INTEGER := 18; /*from paris, mapping re-defined */ --WB_MAPPING CONSTANT INTEGER := 57; WB_MIV_MODULE CONSTANT INTEGER := 20; WB_CONNECTOR CONSTANT INTEGER := 21; WB_LOCATION CONSTANT INTEGER := 22; WB_RUNTIME_REPOSITORY CONSTANT INTEGER := 23; WB_BUSINESS_AREA CONSTANT INTEGER := 24; WB_INTELLIGENCE_MODULE CONSTANT INTEGER := 25; WB_PROCESS_FLOW CONSTANT INTEGER := 26; WB_PROCESS_FLOW_MODULE CONSTANT INTEGER := 27; WB_PROCESS_FLOW_PACKAGE CONSTANT INTEGER:= 28; WB_QUERY_OBJECT CONSTANT INTEGER:= 29; WB_ADVANCED_QUEUE CONSTANT INTEGER:= 30; WB_EXTERNAL_TABLE CONSTANT INTEGER:= 31; WB_REPORT CONSTANT INTEGER:= 32; WB_REPORT_GROUP CONSTANT INTEGER:= 33; WB_REPORT_MODULE CONSTANT INTEGER:= 34; WB_OBJECT_TYPE CONSTANT INTEGER:= 35; WB_SNAPSHOT CONSTANT INTEGER:= 36; WB_DATA_RULE CONSTANT INTEGER:= 39; WB_DATA_RULE_FOLDER CONSTANT INTEGER:= 40; WB_CALENDAR CONSTANT INTEGER:= 41; WB_CALENDAR_MODULE CONSTANT INTEGER:= 42; WB_MINING_MODEL CONSTANT INTEGER:=45; WB_DATA_AUDITOR CONSTANT INTEGER:= 46; WB_DATE_HIERARCHY_TEMPLATE CONSTANT INTEGER:= 47; WB_DERIVATION_SCHEMA CONSTANT INTEGER:= 48; WB_DERIVATION_SET CONSTANT INTEGER:=49; WB_DRILL_PATH CONSTANT INTEGER:= 51; WB_ICON_SET CONSTANT INTEGER:= 55; WB_ITEM_CLASS CONSTANT INTEGER:= 56; WB_MAPPING CONSTANT INTEGER:= 57; WB_CONFIGURATION CONSTANT INTEGER:= 59; --WB_PLS_COLLECTION CONSTANT INTEGER:= 61; --WB_PLS_RECORD CONSTANT INTEGER:= 62; WB_DATA_PROFILE CONSTANT INTEGER:= 66; WB_PROFILE_REFERENCE CONSTANT INTEGER:= 67; WB_ACTIVITY_TEMPLATE CONSTANT INTEGER:= 68; WB_ACTIVITY_TEMPLATE_FOLDER CONSTANT INTEGER:=69; WB_PLUGGABLE_MAPPING CONSTANT INTEGER:= 70; WB_QUEUE_PROPAGATION CONSTANT INTEGER:= 71; WB_QUEUE_TABLE CONSTANT INTEGER:= 72; WB_STREAMS_CAPTURE_PROCESS CONSTANT INTEGER:=78; WB_PLUGGABLE_MAPPING_FOLDER CONSTANT INTEGER:=79; WB_EXPERT CONSTANT INTEGER:= 80; WB_EXPERT_MODULE CONSTANT INTEGER:= 81; WB_METADATA_INTERFACE_DEFN CONSTANT INTEGER :=85; WB_TRANSPORTABLE_TABLESPACE CONSTANT INTEGER:=86; WB_TRANSPORTABLE_MODULE CONSTANT INTEGER:=87; WB_PEOPLESOFT_MODULE CONSTANT INTEGER:= 88; WB_PEOPLESOFT_TREE_STRUCTURE CONSTANT INTEGER:=89; WB_GENERIC_COMPONENT CONSTANT INTEGER:= 92; WB_GENERIC_FOLDER CONSTANT INTEGER:=93; WB_GENERIC_MODULE CONSTANT INTEGER:=94; /* *This group of object type contants should be used in procedures *securityCheckForCreation and securityCheck. The argument "objectType" in *securityCheckForCreation and argument "objectTypeOperationInvokedOn" *in securityCheck will be one of the above contants. You can use this *contant to control which user can create which kind of object or which *user can invoke operations such as WB_EDIT,WB_DELETE etc. on which *kind of objects. */ ---Since there are serveral arguements in the procedures will depend ---on whether the object (on which a operation is invoked) is the ---child of the project or a module. Here is the documentation. ---------children of Project------------------ /* *WB_SNAPSHOT ,WB_METADATA_INTERFACE_DEFN and WB_PROJECT are not children of a project, but other objects in the list of type constants are. */ ---------children of Module------------------- /* * The following are not children of any module: *WB_PROJECT,WB_ORACLE_MODULE,WB_GATEWAY_MODULE,WB_SAP_MODULE, *WB_FLAT_FILE_MODULE,WB_SHARED_MODULE,WB_REPOS_MODULE,WB_COLLECTION, *WB_WAREHOUSE,WB_MIV_MODULE,WB_LOCATION,WB_CONNECTOR,WB_RUNTIME_REPOSITORY, *WB_BUSINESS_AREA,WB_INTELLIGENCE_MODULE,WB_PROCESS_FLOW_MODULE, *WB_REPORT_MODULE,WB_SNAPSHOT,WB_DATA_RULE_FOLDER,WB_CALENDAR_MODULE,WB_CONFIGURATION,WB_DATA_PROFILE, *WB_ACTIVITY_TEMPLATE_FOLDER,WB_EXPERT_MODULE, WB_TRANSPORTABLE_TABLESPACE,WB_TRANSPORTABLE_MODULE, *WB_PEOPLESOFT_MODULE,WB_GENERIC_MODULE *Other object types can be the children of module */ END WBSecurityServiceImpl;