Rev 5512 | Blame | Compare with Previous | Last modification | View Log | RSS feed
---------------------------------------------------------- File created - Thursday-July-14-2016------------------------------------------------------------------------------------------------------------------ DDL for Type ACCMGR_NUMBER_TAB_T--------------------------------------------------------CREATE OR REPLACE TYPE "ACCESS_MANAGER"."ACCMGR_NUMBER_TAB_T" as TABLE of NUMBER/---------------------------------------------------------- DDL for Sequence SEQ_APP_ID--------------------------------------------------------CREATE SEQUENCE "ACCESS_MANAGER"."SEQ_APP_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 101 CACHE 20 ORDER NOCYCLE ;---------------------------------------------------------- DDL for Sequence SEQ_DT_ID--------------------------------------------------------CREATE SEQUENCE "ACCESS_MANAGER"."SEQ_DT_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 261 CACHE 20 ORDER NOCYCLE ;---------------------------------------------------------- DDL for Sequence SEQ_OBJ_ID--------------------------------------------------------CREATE SEQUENCE "ACCESS_MANAGER"."SEQ_OBJ_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1441 CACHE 20 ORDER NOCYCLE ;---------------------------------------------------------- DDL for Sequence SEQ_ROLE_ID--------------------------------------------------------CREATE SEQUENCE "ACCESS_MANAGER"."SEQ_ROLE_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1681 CACHE 20 ORDER NOCYCLE ;---------------------------------------------------------- DDL for Sequence SEQ_USER_ID--------------------------------------------------------CREATE SEQUENCE "ACCESS_MANAGER"."SEQ_USER_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 13362 CACHE 20 ORDER NOCYCLE ;---------------------------------------------------------- DDL for Table APPLICATIONS--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."APPLICATIONS"( "APP_ID" NUMBER,"APPLICATION_NAME" VARCHAR2(1000 BYTE),"DB_SCHEMA" VARCHAR2(1000 BYTE),"TNS_NAME" VARCHAR2(1000 BYTE),"SCHEMA_USERNAME" VARCHAR2(255 BYTE),"SCHEMA_PASSWORD" VARCHAR2(255 BYTE),"ACRONYM" VARCHAR2(3 BYTE),"IS_RUNNING" CHAR(1 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table APPLICATION_PAGES--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."APPLICATION_PAGES"( "PAGE_ID" NUMBER,"APP_ID" NUMBER,"PAGE_NAME" VARCHAR2(1000 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table CONTROL_OBJECTS--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."CONTROL_OBJECTS"( "OBJ_ID" NUMBER,"APP_ID" NUMBER,"OBJ_NAME" VARCHAR2(1000 BYTE),"PARENT_OBJ_ID" NUMBER,"OBJ_DESCRIPTION" VARCHAR2(1000 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table DATA_PERMISSIONS--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS"( "DT_ID" NUMBER,"ROLE_ID" NUMBER,"REF_COLUMN_VAL" NUMBER,"PERM_ID" NUMBER,"PERM_VALUE" CHAR(1 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table DATA_TABLES--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."DATA_TABLES"( "DT_ID" NUMBER,"OBJ_ID" NUMBER,"TABLE_NAME" VARCHAR2(1000 BYTE),"REF_COLUMN_NAME" VARCHAR2(1000 BYTE),"DISPLAY_COLUMN_NAME" VARCHAR2(1000 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table LOGIN_TRAIL--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."LOGIN_TRAIL"( "ENUM_EVENT" NUMBER,"USER_NAME" VARCHAR2(1000 BYTE),"CLIENT_IP" VARCHAR2(255 BYTE),"APP_ID" NUMBER,"STAMP" DATE,"COMMENTS" VARCHAR2(4000 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 7340032 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table PAGE_CONTROL_OBJECTS--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."PAGE_CONTROL_OBJECTS"( "PAGE_ID" NUMBER,"OBJ_ID" NUMBER) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table PERMISSION_TYPES--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."PERMISSION_TYPES"( "PERM_ID" NUMBER,"PERM_NAME" VARCHAR2(255 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table ROLES--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."ROLES"( "ROLE_ID" NUMBER,"APP_ID" NUMBER,"ROLE_NAME" VARCHAR2(1000 BYTE),"IS_ROLE_VARIATION" CHAR(1 BYTE),"COMMENTS" VARCHAR2(4000 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table ROLE_PRIVILEGES--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES"( "ROLE_ID" NUMBER,"OBJ_ID" NUMBER,"PERM_ID" NUMBER,"PERM_VALUE" CHAR(1 BYTE)) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table USERS--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."USERS"( "USER_ID" NUMBER,"FULL_NAME" VARCHAR2(255 BYTE),"USER_NAME" VARCHAR2(1000 BYTE),"USER_PASSWORD" VARCHAR2(4000 BYTE),"USER_EMAIL" VARCHAR2(1000 BYTE),"DOMAIN" VARCHAR2(1000 BYTE),"IS_DISABLED" CHAR(1 BYTE),"IS_ONLINE" CHAR(1 BYTE),"ONLINE_AT" VARCHAR2(50 BYTE),"LAST_VISIT" DATE,"LAST_REQUEST" NUMBER) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table USER_APPLICATIONS--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."USER_APPLICATIONS"( "USER_ID" NUMBER,"APP_ID" NUMBER,"LAST_VISIT_STAMP" DATE) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for Table USER_ROLES--------------------------------------------------------CREATE TABLE "ACCESS_MANAGER"."USER_ROLES"( "USER_ID" NUMBER,"ROLE_ID" NUMBER) SEGMENT CREATION IMMEDIATEPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_DATA" ;---------------------------------------------------------- DDL for View CONTROLOBJECTS_VS_ROLES--------------------------------------------------------CREATE OR REPLACE FORCE VIEW "ACCESS_MANAGER"."CONTROLOBJECTS_VS_ROLES" ("APPLICATION ID", "APPLICATION NAME", "OBJECT_ID", "OBJECT NAME", "OBJECT DESCRIPTION", "ROLE ID", "ROLE NAME") ASselect distinct app.app_id, app.application_name, co.obj_id, co.obj_name, co.obj_description, ro.role_id, ro.role_namefrom applications app, control_objects co, roles ro, role_privileges rpwhere app.app_id=co.app_idand app.app_id=ro.app_idand ro.role_id=rp.role_idand rp.obj_id=co.obj_idorder by co.obj_name;---------------------------------------------------------- DDL for Index UNQ_APPLICATIONS--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_APPLICATIONS" ON "ACCESS_MANAGER"."APPLICATIONS" ("APPLICATION_NAME")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_PERMISSION_TYPES--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_PERMISSION_TYPES" ON "ACCESS_MANAGER"."PERMISSION_TYPES" ("PERM_NAME")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index PK_APPLICATION_PAGES--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."PK_APPLICATION_PAGES" ON "ACCESS_MANAGER"."APPLICATION_PAGES" ("PAGE_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_PAGE_COBJ--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_PAGE_COBJ" ON "ACCESS_MANAGER"."PAGE_CONTROL_OBJECTS" ("PAGE_ID", "OBJ_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index PK_ROLES--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."PK_ROLES" ON "ACCESS_MANAGER"."ROLES" ("ROLE_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index INX_ENUM_EVENT--------------------------------------------------------CREATE INDEX "ACCESS_MANAGER"."INX_ENUM_EVENT" ON "ACCESS_MANAGER"."LOGIN_TRAIL" ("ENUM_EVENT")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_ROLES--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_ROLES" ON "ACCESS_MANAGER"."ROLES" ("APP_ID", "ROLE_NAME")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index PK_PERMISSION_TYPES--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."PK_PERMISSION_TYPES" ON "ACCESS_MANAGER"."PERMISSION_TYPES" ("PERM_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_USERS--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_USERS" ON "ACCESS_MANAGER"."USERS" ("USER_NAME")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index PK_USERS--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."PK_USERS" ON "ACCESS_MANAGER"."USERS" ("USER_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index INX_CLIENT_IP--------------------------------------------------------CREATE INDEX "ACCESS_MANAGER"."INX_CLIENT_IP" ON "ACCESS_MANAGER"."LOGIN_TRAIL" ("CLIENT_IP")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 6291456 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index PK_APPLICATIONS--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."PK_APPLICATIONS" ON "ACCESS_MANAGER"."APPLICATIONS" ("APP_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index PK_CONTROL_OBJECTS--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."PK_CONTROL_OBJECTS" ON "ACCESS_MANAGER"."CONTROL_OBJECTS" ("OBJ_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index INX_USER_NAME--------------------------------------------------------CREATE INDEX "ACCESS_MANAGER"."INX_USER_NAME" ON "ACCESS_MANAGER"."LOGIN_TRAIL" ("USER_NAME")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 5242880 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_USER_ROLES--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_USER_ROLES" ON "ACCESS_MANAGER"."USER_ROLES" ("USER_ID", "ROLE_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_APP_PAGES--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_APP_PAGES" ON "ACCESS_MANAGER"."APPLICATION_PAGES" ("APP_ID", "PAGE_NAME")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_DATA_TABLE--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_DATA_TABLE" ON "ACCESS_MANAGER"."DATA_TABLES" ("TABLE_NAME", "OBJ_ID", "REF_COLUMN_NAME")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index PK_DATA_TABLES--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."PK_DATA_TABLES" ON "ACCESS_MANAGER"."DATA_TABLES" ("DT_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_USER_APPS--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_USER_APPS" ON "ACCESS_MANAGER"."USER_APPLICATIONS" ("USER_ID", "APP_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_DATA_PERM--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_DATA_PERM" ON "ACCESS_MANAGER"."DATA_PERMISSIONS" ("DT_ID", "ROLE_ID", "REF_COLUMN_VAL", "PERM_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_ROLE_OBJ_PERM--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_ROLE_OBJ_PERM" ON "ACCESS_MANAGER"."ROLE_PRIVILEGES" ("ROLE_ID", "OBJ_ID", "PERM_ID")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- DDL for Index UNQ_CONTROL_OBJ--------------------------------------------------------CREATE UNIQUE INDEX "ACCESS_MANAGER"."UNQ_CONTROL_OBJ" ON "ACCESS_MANAGER"."CONTROL_OBJECTS" ("APP_ID", "OBJ_NAME")PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ;---------------------------------------------------------- Constraints for Table DATA_TABLES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."DATA_TABLES" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;ALTER TABLE "ACCESS_MANAGER"."DATA_TABLES" ADD CONSTRAINT "PK_DATA_TABLES" PRIMARY KEY ("DT_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ENABLE;ALTER TABLE "ACCESS_MANAGER"."DATA_TABLES" MODIFY ("DISPLAY_COLUMN_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."DATA_TABLES" MODIFY ("REF_COLUMN_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."DATA_TABLES" MODIFY ("TABLE_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."DATA_TABLES" MODIFY ("OBJ_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."DATA_TABLES" MODIFY ("DT_ID" NOT NULL ENABLE);---------------------------------------------------------- Constraints for Table APPLICATIONS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."APPLICATIONS" ADD CONSTRAINT "PK_APPLICATIONS" PRIMARY KEY ("APP_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ENABLE;ALTER TABLE "ACCESS_MANAGER"."APPLICATIONS" MODIFY ("IS_RUNNING" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."APPLICATIONS" MODIFY ("ACRONYM" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."APPLICATIONS" MODIFY ("APPLICATION_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."APPLICATIONS" MODIFY ("APP_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."APPLICATIONS" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Constraints for Table PERMISSION_TYPES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."PERMISSION_TYPES" ADD CONSTRAINT "PK_PERMISSION_TYPES" PRIMARY KEY ("PERM_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ENABLE;ALTER TABLE "ACCESS_MANAGER"."PERMISSION_TYPES" MODIFY ("PERM_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."PERMISSION_TYPES" MODIFY ("PERM_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."PERMISSION_TYPES" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Constraints for Table USER_APPLICATIONS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."USER_APPLICATIONS" MODIFY ("APP_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."USER_APPLICATIONS" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."USER_APPLICATIONS" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Constraints for Table USER_ROLES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."USER_ROLES" MODIFY ("ROLE_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."USER_ROLES" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."USER_ROLES" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Constraints for Table APPLICATION_PAGES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."APPLICATION_PAGES" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;ALTER TABLE "ACCESS_MANAGER"."APPLICATION_PAGES" ADD CONSTRAINT "PK_APPLICATION_PAGES" PRIMARY KEY ("PAGE_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ENABLE;ALTER TABLE "ACCESS_MANAGER"."APPLICATION_PAGES" MODIFY ("PAGE_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."APPLICATION_PAGES" MODIFY ("APP_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."APPLICATION_PAGES" MODIFY ("PAGE_ID" NOT NULL ENABLE);---------------------------------------------------------- Constraints for Table USERS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."USERS" ADD CONSTRAINT "PK_USERS" PRIMARY KEY ("USER_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ENABLE;ALTER TABLE "ACCESS_MANAGER"."USERS" MODIFY ("USER_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."USERS" MODIFY ("FULL_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."USERS" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."USERS" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Constraints for Table CONTROL_OBJECTS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."CONTROL_OBJECTS" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;ALTER TABLE "ACCESS_MANAGER"."CONTROL_OBJECTS" ADD CONSTRAINT "PK_CONTROL_OBJECTS" PRIMARY KEY ("OBJ_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ENABLE;ALTER TABLE "ACCESS_MANAGER"."CONTROL_OBJECTS" MODIFY ("OBJ_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."CONTROL_OBJECTS" MODIFY ("APP_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."CONTROL_OBJECTS" MODIFY ("OBJ_ID" NOT NULL ENABLE);---------------------------------------------------------- Constraints for Table LOGIN_TRAIL--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."LOGIN_TRAIL" MODIFY ("STAMP" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."LOGIN_TRAIL" MODIFY ("USER_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."LOGIN_TRAIL" MODIFY ("ENUM_EVENT" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."LOGIN_TRAIL" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Constraints for Table ROLE_PRIVILEGES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES" MODIFY ("PERM_VALUE" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES" MODIFY ("PERM_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES" MODIFY ("OBJ_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES" MODIFY ("ROLE_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Constraints for Table PAGE_CONTROL_OBJECTS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."PAGE_CONTROL_OBJECTS" MODIFY ("OBJ_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."PAGE_CONTROL_OBJECTS" MODIFY ("PAGE_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."PAGE_CONTROL_OBJECTS" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Constraints for Table DATA_PERMISSIONS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" MODIFY ("PERM_VALUE" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" MODIFY ("PERM_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" MODIFY ("REF_COLUMN_VAL" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" MODIFY ("ROLE_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" MODIFY ("DT_ID" NOT NULL ENABLE);---------------------------------------------------------- Constraints for Table ROLES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."ROLES" ADD CONSTRAINT "PK_ROLES" PRIMARY KEY ("ROLE_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "RELEASEM_INDX" ENABLE;ALTER TABLE "ACCESS_MANAGER"."ROLES" MODIFY ("ROLE_NAME" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."ROLES" MODIFY ("APP_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."ROLES" MODIFY ("ROLE_ID" NOT NULL ENABLE);ALTER TABLE "ACCESS_MANAGER"."ROLES" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;---------------------------------------------------------- Ref Constraints for Table APPLICATION_PAGES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."APPLICATION_PAGES" ADD CONSTRAINT "FK_APP_PAGE_REF_APP" FOREIGN KEY ("APP_ID")REFERENCES "ACCESS_MANAGER"."APPLICATIONS" ("APP_ID") ENABLE;---------------------------------------------------------- Ref Constraints for Table CONTROL_OBJECTS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."CONTROL_OBJECTS" ADD CONSTRAINT "FK_CONTROL_OBJ_REF_APPLICS" FOREIGN KEY ("APP_ID")REFERENCES "ACCESS_MANAGER"."APPLICATIONS" ("APP_ID") ENABLE;---------------------------------------------------------- Ref Constraints for Table DATA_PERMISSIONS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" ADD CONSTRAINT "FK_DATA_PERM_REF_DATA_TABLE" FOREIGN KEY ("DT_ID")REFERENCES "ACCESS_MANAGER"."DATA_TABLES" ("DT_ID") ENABLE;ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" ADD CONSTRAINT "FK_DATA_PERM_REF_PERM_TYPES" FOREIGN KEY ("PERM_ID")REFERENCES "ACCESS_MANAGER"."PERMISSION_TYPES" ("PERM_ID") ENABLE;ALTER TABLE "ACCESS_MANAGER"."DATA_PERMISSIONS" ADD CONSTRAINT "FK_DATA_PER_REF_ROLES" FOREIGN KEY ("ROLE_ID")REFERENCES "ACCESS_MANAGER"."ROLES" ("ROLE_ID") ENABLE;---------------------------------------------------------- Ref Constraints for Table DATA_TABLES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."DATA_TABLES" ADD CONSTRAINT "FK_DATA_TABLE_REF_OBJ" FOREIGN KEY ("OBJ_ID")REFERENCES "ACCESS_MANAGER"."CONTROL_OBJECTS" ("OBJ_ID") ENABLE;---------------------------------------------------------- Ref Constraints for Table PAGE_CONTROL_OBJECTS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."PAGE_CONTROL_OBJECTS" ADD CONSTRAINT "FK_PAGE_CON_REF_APP_PAGES" FOREIGN KEY ("PAGE_ID")REFERENCES "ACCESS_MANAGER"."APPLICATION_PAGES" ("PAGE_ID") ENABLE;ALTER TABLE "ACCESS_MANAGER"."PAGE_CONTROL_OBJECTS" ADD CONSTRAINT "FK_PAGE_CON_REF_CONTROL_OBJS" FOREIGN KEY ("OBJ_ID")REFERENCES "ACCESS_MANAGER"."CONTROL_OBJECTS" ("OBJ_ID") ENABLE;---------------------------------------------------------- Ref Constraints for Table ROLES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."ROLES" ADD CONSTRAINT "FK_ROLES_REF_APPLICS" FOREIGN KEY ("APP_ID")REFERENCES "ACCESS_MANAGER"."APPLICATIONS" ("APP_ID") ENABLE;---------------------------------------------------------- Ref Constraints for Table ROLE_PRIVILEGES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES" ADD CONSTRAINT "FK_ROLE_OBJ_REF_OBJS" FOREIGN KEY ("OBJ_ID")REFERENCES "ACCESS_MANAGER"."CONTROL_OBJECTS" ("OBJ_ID") ENABLE;ALTER TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES" ADD CONSTRAINT "FK_ROLE_OBJ_REF_PERMISS" FOREIGN KEY ("PERM_ID")REFERENCES "ACCESS_MANAGER"."PERMISSION_TYPES" ("PERM_ID") ENABLE;ALTER TABLE "ACCESS_MANAGER"."ROLE_PRIVILEGES" ADD CONSTRAINT "FK_ROLE_OBJ_REF_ROLES" FOREIGN KEY ("ROLE_ID")REFERENCES "ACCESS_MANAGER"."ROLES" ("ROLE_ID") ON DELETE CASCADE ENABLE;---------------------------------------------------------- Ref Constraints for Table USER_APPLICATIONS--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."USER_APPLICATIONS" ADD CONSTRAINT "FK_USER_APPS_REF_APPS" FOREIGN KEY ("APP_ID")REFERENCES "ACCESS_MANAGER"."APPLICATIONS" ("APP_ID") ENABLE;ALTER TABLE "ACCESS_MANAGER"."USER_APPLICATIONS" ADD CONSTRAINT "FK_USER_APP_REF_USERS" FOREIGN KEY ("USER_ID")REFERENCES "ACCESS_MANAGER"."USERS" ("USER_ID") ENABLE;---------------------------------------------------------- Ref Constraints for Table USER_ROLES--------------------------------------------------------ALTER TABLE "ACCESS_MANAGER"."USER_ROLES" ADD CONSTRAINT "FK_USER_ROLES_REF_ROLES" FOREIGN KEY ("ROLE_ID")REFERENCES "ACCESS_MANAGER"."ROLES" ("ROLE_ID") ON DELETE CASCADE ENABLE;ALTER TABLE "ACCESS_MANAGER"."USER_ROLES" ADD CONSTRAINT "FK_USER_ROLES_REF_USERS" FOREIGN KEY ("USER_ID")REFERENCES "ACCESS_MANAGER"."USERS" ("USER_ID") ON DELETE CASCADE ENABLE;---------------------------------------------------------- DDL for Function IN_LIST_NUMBER--------------------------------------------------------CREATE OR REPLACE FUNCTION "ACCESS_MANAGER"."IN_LIST_NUMBER" ( sInList IN VARCHAR2 ) RETURN ACCMGR_NUMBER_TAB_t IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */sync_rtags ACCMGR_NUMBER_TAB_t := ACCMGR_NUMBER_TAB_t();pos NUMBER;in_list VARCHAR2(4000) := sInList || ',';BEGINIF NOT sInList IS NULLTHENLOOPEXIT WHEN in_list IS NULL;pos := INSTR ( in_list, ',' );sync_rtags.extend;sync_rtags(sync_rtags.count) := LTRIM ( RTRIM ( SUBSTR ( in_list, 1, pos-1 ) ) );in_list := SUBSTR ( in_list, pos+1 );END LOOP;END IF;RETURN sync_rtags;END IN_LIST_NUMBER;/---------------------------------------------------------- DDL for Function IS_SAME_STRING--------------------------------------------------------CREATE OR REPLACE FUNCTION "ACCESS_MANAGER"."IS_SAME_STRING" ( sStringOne IN VARCHAR2,sStringTwo IN VARCHAR2 ) RETURN BOOLEAN IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */ReturnValue BOOLEAN DEFAULT FALSE;BEGIN--- Compare ---IF ( NVL( sStringOne, '' ) || 'APPEND' = NVL( sStringTwo, '' ) || 'APPEND' ) THEN-- Strings are the sameReturnValue := TRUE;END IF;RETURN ReturnValue;END IS_SAME_STRING;/---------------------------------------------------------- DDL for Package PK_AMUTILS--------------------------------------------------------CREATE OR REPLACE PACKAGE "ACCESS_MANAGER"."PK_AMUTILS" ISPROCEDURE Log_Access ( nEvent IN LOGIN_TRAIL.ENUM_EVENT%TYPE,sUserName IN LOGIN_TRAIL.USER_NAME%TYPE,sClientIp IN LOGIN_TRAIL.CLIENT_IP%TYPE,nAppId IN LOGIN_TRAIL.APP_ID%TYPE,sComments IN LOGIN_TRAIL.COMMENTS%TYPE,nUserId IN NUMBER DEFAULT NULL );FUNCTION Get_Hash ( sText IN VARCHAR2 ) RETURN VARCHAR2;END pk_AMUtils;/---------------------------------------------------------- DDL for Package PK_APPLICATION--------------------------------------------------------CREATE OR REPLACE PACKAGE "ACCESS_MANAGER"."PK_APPLICATION" ISPROCEDURE Add_Application ( sAppName IN APPLICATIONS.APPLICATION_NAME%TYPE,sAppAcronym IN APPLICATIONS.ACRONYM%TYPE );PROCEDURE Remove_Application ( nAppId IN APPLICATIONS.APP_ID%TYPE );END pk_Application;/---------------------------------------------------------- DDL for Package PK_CONTROL--------------------------------------------------------CREATE OR REPLACE PACKAGE "ACCESS_MANAGER"."PK_CONTROL" ISPROCEDURE Add_Control ( sObjName IN CONTROL_OBJECTS.OBJ_NAME%TYPE,sObjDescription IN CONTROL_OBJECTS.OBJ_DESCRIPTION%TYPE,nAppId IN CONTROL_OBJECTS.APP_ID%TYPE);PROCEDURE Update_Control ( sObjId IN VARCHAR2,sObjName IN CONTROL_OBJECTS.OBJ_NAME%TYPE,sObjDescription IN CONTROL_OBJECTS.OBJ_DESCRIPTION%TYPE);PROCEDURE Remove_Control ( sObjIdList IN VARCHAR2 );PROCEDURE Set_Row_Permissions ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE,cIsVisible IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL,cIsActive IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL );PROCEDURE Delete_Data_Permission ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE );PROCEDURE Add_DataTable ( nObjId IN DATA_TABLES.OBJ_ID%TYPE,sTableName IN DATA_TABLES.TABLE_NAME%TYPE,sRefColumn IN DATA_TABLES.REF_COLUMN_NAME%TYPE,sDisplayColumn IN DATA_TABLES.DISPLAY_COLUMN_NAME%TYPE);PROCEDURE Remove_DataTable ( DtId IN NUMBER );END pk_Control;/---------------------------------------------------------- DDL for Package PK_ROLE--------------------------------------------------------CREATE OR REPLACE PACKAGE "ACCESS_MANAGER"."PK_ROLE" ISPROCEDURE Add_Role ( sRoleName IN ROLES.ROLE_NAME%TYPE,sRoleComments IN ROLES.COMMENTS%TYPE,nAppId IN ROLES.APP_ID%TYPE );PROCEDURE Remove_Role ( sRoleIdList IN VARCHAR2 );PROCEDURE Set_Role_Permissions ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL );PROCEDURE Set_Role_Variation_Permissions ( nUserId IN USER_ROLES.USER_ID%TYPE,nAppId IN ROLES.APP_ID%TYPE,nRoleId IN ROLES.ROLE_ID%TYPE,nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL );FUNCTION Is_Permissions_Changed ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE,cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE ) RETURN BOOLEAN;FUNCTION Is_Role_Variation ( nRoleId IN ROLES.ROLE_ID%TYPE ) RETURN BOOLEAN;PROCEDURE Delete_Role_Permission ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,nPermId IN ROLE_PRIVILEGES.PERM_ID%TYPE );PROCEDURE Grant_Role ( sRoleIdList IN VARCHAR2,nUserId IN USER_ROLES.USER_ID%TYPE );PROCEDURE Revoke_Role ( sRoleIdList IN VARCHAR2,nUserId IN USER_ROLES.USER_ID%TYPE );PROCEDURE Update_Role ( nRoleId IN ROLES.ROLE_ID%TYPE,sRoleName IN ROLES.ROLE_NAME%TYPE,sRoleComments IN ROLES.COMMENTS%TYPE);END pk_Role;/---------------------------------------------------------- DDL for Package PK_SECURITY--------------------------------------------------------CREATE OR REPLACE PACKAGE "ACCESS_MANAGER"."PK_SECURITY" IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 28/Apr/2005|| Spec Version: 1.0------------------------------*/TYPE typeCur IS REF CURSOR;/*================================================================================================*/--FUNCTION GET_USER_BY_ID ( ID IN NUMBER, records OUT typeCur ) RETURN NUMBER;--FUNCTION GET_USER_BY_USERNAME ( UserName IN VARCHAR2, records OUT typeCur ) RETURN NUMBER;--FUNCTION GET_USER_PERMISSIONS ( UserSK IN NUMBER, ActionNameFilter IN VARCHAR2, records OUT typeCur ) RETURN NUMBER;--FUNCTION AUTHENTICATE ( UserName IN VARCHAR2,-- UserPassword IN VARCHAR2,-- records OUT typeCur ) RETURN NUMBER;/*================================================================================================*/PROCEDURE GET_USER_STATIC_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur);PROCEDURE GET_USER_DATA_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur);FUNCTION IS_LOCAL_PASSWORD_CORRECT ( UserName IN VARCHAR2, UserPassword IN VARCHAR2 ) RETURN NUMBER;FUNCTION Set_Password ( sUserName IN VARCHAR2, sPasswordA IN VARCHAR2, sPasswordB IN VARCHAR2 ) RETURN NUMBER;/*================================================================================================*/END PK_SECURITY;/---------------------------------------------------------- DDL for Package PK_USER--------------------------------------------------------CREATE OR REPLACE PACKAGE "ACCESS_MANAGER"."PK_USER" IS/*------------------------------|| Last Modified: J.Tweddle|| Modified Date: 21/Jan/2008|| Spec Version: 2.1------------------------------*/PROCEDURE Add_User_Account ( sFullName IN USERS.FULL_NAME%TYPE,sUserName IN USERS.USER_NAME%TYPE,sUserEmail IN USERS.USER_EMAIL%TYPE,sDomain IN USERS.DOMAIN%TYPE );PROCEDURE Update_User_Account ( sUserId IN VARCHAR2, sFullName IN USERS.FULL_NAME%TYPE,sUserName IN USERS.USER_NAME%TYPE,sUserEmail IN USERS.USER_EMAIL%TYPE,sDomain IN USERS.DOMAIN%TYPE );PROCEDURE Add_Application_User ( sUserIdList IN VARCHAR2,nAppId IN USER_APPLICATIONS.APP_ID%TYPE,cIncludeEveryone IN CHAR );PROCEDURE Remove_Application_User ( sUserIdList IN VARCHAR2,nAppId IN USER_APPLICATIONS.APP_ID%TYPE,cIncludeEveryone IN CHAR DEFAULT NULL );PROCEDURE Add_Role_Member ( sUserIdList IN VARCHAR2,nRoleId IN USER_ROLES.ROLE_ID%TYPE,cIncludeEveryone IN CHAR );PROCEDURE Remove_Role_Member ( sUserIdList IN VARCHAR2,nRoleId IN USER_ROLES.ROLE_ID%TYPE,cIncludeEveryone IN CHAR DEFAULT NULL);PROCEDURE Disable_Users ( sUserIdList IN VARCHAR2,nAppId IN USER_APPLICATIONS.APP_ID%TYPE );PROCEDURE Remove_User_Account ( sUserIdList IN VARCHAR2 );END pk_user;/---------------------------------------------------------- DDL for Package Body PK_AMUTILS--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "ACCESS_MANAGER"."PK_AMUTILS"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Access ( nEvent IN LOGIN_TRAIL.ENUM_EVENT%TYPE,sUserName IN LOGIN_TRAIL.USER_NAME%TYPE,sClientIp IN LOGIN_TRAIL.CLIENT_IP%TYPE,nAppId IN LOGIN_TRAIL.APP_ID%TYPE,sComments IN LOGIN_TRAIL.COMMENTS%TYPE,nUserId IN NUMBER DEFAULT NULL ) ISUserName USERS.USER_NAME%TYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF nUserId IS NOT NULL THENSELECT USER_NAME INTO UserName FROM USERS WHERE USER_ID = nUserId;ELSEUserName := sUserName;END IF;/*-------------------------------------------------------*/-- Insert Login TrailINSERT INTO LOGIN_TRAIL ( ENUM_EVENT, USER_NAME, CLIENT_IP, APP_ID, STAMP, COMMENTS )VALUES ( nEvent,UserName,sClientIp,nAppId,TO_DATE( TO_CHAR( SYSDATE,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' ),sComments);END Log_Access;/*--------------------------------------------------------------------------------------------------*/FUNCTION Get_Hash ( sText IN VARCHAR2 ) RETURN VARCHAR2 ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/RETURN DBMS_OBFUSCATION_TOOLKIT.MD5( input_string => sText );END Get_Hash;/*--------------------------------------------------------------------------------------------------*/END pk_AMUtils;/---------------------------------------------------------- DDL for Package Body PK_APPLICATION--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "ACCESS_MANAGER"."PK_APPLICATION"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Application ( sAppName IN APPLICATIONS.APPLICATION_NAME%TYPE,sAppAcronym IN APPLICATIONS.ACRONYM%TYPE ) ISAppId NUMBER;CURSOR curAppAcronym ISSELECT app.ACRONYMFROM APPLICATIONS appWHERE app.ACRONYM = sAppAcronym;recAppAcronym curAppAcronym%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for duplicate acronymsOPEN curAppAcronym;FETCH curAppAcronym INTO recAppAcronym;IF curAppAcronym%FOUNDTHENRAISE_APPLICATION_ERROR (-20000, 'Application Acronym <b>'|| sAppAcronym ||'</b> is already used.' );END IF;CLOSE curAppAcronym;/*-------------------------------------------------------*//*+++++ INSERT APPLICATION ++++++++*/BEGIN-- Get app_idSELECT SEQ_APP_ID.NEXTVAL INTO AppId FROM DUAL;-- Insert new ApplicationINSERT INTO APPLICATIONS ( APP_ID, APPLICATION_NAME, ACRONYM )VALUES ( AppId, sAppName, sAppAcronym );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Application Name <b>'|| sAppName ||'</b> is already used.');END;/*+++++ END INSERT APPLICATION +++++*/END Add_Application;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Application ( nAppId IN APPLICATIONS.APP_ID%TYPE ) ISrowCount NUMBER DEFAULT 0;BEGIN/*--------------- Business Rules Here -------------------*/-- Check if any Users are using this ApplicationSELECT Count(*) INTO rowCountFROM USER_APPLICATIONS uaWHERE ua.APP_ID = nAppId;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some Users are still assigned to this Application ( Counted '|| rowCount ||' ).' );END IF;-- Check if any Roles are using this ApplicationSELECT Count(*) INTO rowCountFROM ROLES roWHERE ro.APP_ID = nAppId;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some Roles are still assigned to this Application ( Counted '|| rowCount ||' ).' );END IF;/*-------------------------------------------------------*/-- Remove Application --DELETEFROM APPLICATIONSWHERE APP_ID = nAppId;END Remove_Application;/*--------------------------------------------------------------------------------------------------*/END pk_Application;/---------------------------------------------------------- DDL for Package Body PK_CONTROL--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "ACCESS_MANAGER"."PK_CONTROL"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Control ( sObjName IN CONTROL_OBJECTS.OBJ_NAME%TYPE,sObjDescription IN CONTROL_OBJECTS.OBJ_DESCRIPTION%TYPE,nAppId IN CONTROL_OBJECTS.APP_ID%TYPE) ISObjID NUMBER;CURSOR curPermissionTypes ISSELECT pt.PERM_IDFROM PERMISSION_TYPES pt;recPermissionTypes curPermissionTypes%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get obj_idSELECT SEQ_OBJ_ID.NEXTVAL INTO ObjID FROM DUAL;-- Insert new Control ObjectINSERT INTO CONTROL_OBJECTS ( OBJ_ID, APP_ID, OBJ_NAME, PARENT_OBJ_ID, OBJ_DESCRIPTION )VALUES ( ObjID, nAppId, sObjName, NULL, sObjDescription );/* Set default permissions to all roles *//*OPEN curPermissionTypes;FETCH curPermissionTypes INTO recPermissionTypes;WHILE curPermissionTypes%FOUNDLOOPINSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )SELECT ROLE_ID,ObjID AS OBJ_ID,recPermissionTypes.PERM_ID AS PERM_ID,'Y' AS PERM_VALUEFROM ROLESWHERE IS_ROLE_VARIATION != 'Y';FETCH curPermissionTypes INTO recPermissionTypes;END LOOP;CLOSE curPermissionTypes;*/EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Control Name '|| sObjName ||' is already used in this Application.');END Add_Control;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Control ( sObjId IN VARCHAR2,sObjName IN CONTROL_OBJECTS.OBJ_NAME%TYPE,sObjDescription IN CONTROL_OBJECTS.OBJ_DESCRIPTION%TYPE) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (sObjId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select a Control.' );END IF;/*-------------------------------------------------------*/-- Update CONTROLUPDATE CONTROL_OBJECTS SETOBJ_NAME = sObjName, OBJ_DESCRIPTION = sObjDescriptionWHERE OBJ_ID = sObjId;END Update_Control;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Control ( sObjIdList IN VARCHAR2 ) ISrowCount NUMBER DEFAULT 0;BEGIN/*--------------- Business Rules Here -------------------*/-- Check if any Pages use this controlSELECT Count(*) INTO rowCountFROM PAGE_CONTROL_OBJECTS pcoWHERE pco.OBJ_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some Pages are still using this Control ( Counted '|| rowCount ||' ).' );END IF;-- Check if any Roles use this controlSELECT Count(*) INTO rowCountFROM ROLE_PRIVILEGES rpWHERE rp.OBJ_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some Roles are still using this Control ( Counted '|| rowCount ||' ).' );END IF;-- Check if any Data tables use this controlSELECT Count(*) INTO rowCountFROM DATA_TABLES dt,DATA_PERMISSIONS dpWHERE dt.OBJ_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ))AND dt.DT_ID = dp.DT_ID;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some Data Filters are still in use by Roles ( Counted '|| rowCount ||' ).' );END IF;/*-------------------------------------------------------*/-- Remove Data Filter --DELETEFROM DATA_TABLES dtWHERE dt.OBJ_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));-- Remove Control --DELETEFROM CONTROL_OBJECTS coWHERE co.OBJ_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));END Remove_Control;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_DataTable ( nObjId IN DATA_TABLES.OBJ_ID%TYPE,sTableName IN DATA_TABLES.TABLE_NAME%TYPE,sRefColumn IN DATA_TABLES.REF_COLUMN_NAME%TYPE,sDisplayColumn IN DATA_TABLES.DISPLAY_COLUMN_NAME%TYPE) ISDtID NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get dt_idSELECT SEQ_DT_ID.NEXTVAL INTO DtID FROM DUAL;-- Insert new Control ObjectINSERT INTO DATA_TABLES ( DT_ID, OBJ_ID, TABLE_NAME, REF_COLUMN_NAME, DISPLAY_COLUMN_NAME )VALUES ( DtID, nObjId, sTableName, sRefColumn, sDisplayColumn );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'This Data Table Reference is already used in this Action object.');END Add_DataTable;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_DataTable ( DtId IN NUMBER ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Remove Data Permissions --DELETEFROM DATA_PERMISSIONS dpWHERE dp.DT_ID = DtId;-- Remove Data Table --DELETEFROM DATA_TABLES dtWHERE dt.DT_ID = DtId;END Remove_DataTable;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Set_Row_Permissions ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE,cIsVisible IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL,cIsActive IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL ) ISnPermId PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Delete Existing Permission --Delete_Data_Permission ( nDtId, nRoleId, nRefCol );----- Set Data Permission ------- Get PermId for "Visible"SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumVISIBLE';IF (cIsVisible IS NOT NULL) THENINSERT INTO DATA_PERMISSIONS ( DT_ID, ROLE_ID, REF_COLUMN_VAL, PERM_ID, PERM_VALUE )VALUES ( nDtId, nRoleId, nRefCol, nPermId, cIsVisible );END IF;-- Get PermId for "Active"SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumACTIVE';IF (cIsActive IS NOT NULL) THENINSERT INTO DATA_PERMISSIONS ( DT_ID, ROLE_ID, REF_COLUMN_VAL, PERM_ID, PERM_VALUE )VALUES ( nDtId, nRoleId, nRefCol, nPermId, cIsActive );END IF;END Set_Row_Permissions;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Delete_Data_Permission ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nDtId IS NULL) OR (nRoleId IS NULL) OR (nRefCol IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nDtId= '|| nDtId ||', nRoleId='|| nRoleId ||', nRefCol='|| nRefCol );END IF;/*-------------------------------------------------------*/DELETEFROM DATA_PERMISSIONS dpWHERE dp.ROLE_ID = nRoleIdAND dp.DT_ID = nDtIdAND dp.REF_COLUMN_VAL = nRefCol;END Delete_Data_Permission;/*--------------------------------------------------------------------------------------------------*/END pk_Control;/---------------------------------------------------------- DDL for Package Body PK_ROLE--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "ACCESS_MANAGER"."PK_ROLE"IS/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Role ( sRoleName IN ROLES.ROLE_NAME%TYPE,sRoleComments IN ROLES.COMMENTS%TYPE,nAppId IN ROLES.APP_ID%TYPE ) ISRoleID NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get role_idSELECT SEQ_ROLE_ID.NEXTVAL INTO RoleID FROM DUAL;-- Insert new RoleINSERT INTO ROLES ( ROLE_ID, APP_ID, ROLE_NAME, IS_ROLE_VARIATION, COMMENTS )VALUES ( RoleID, nAppId, sRoleName, NULL, sRoleComments );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Role Name '|| sRoleName ||' is already used in this Application.');END Add_Role;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Role ( nRoleId IN ROLES.ROLE_ID%TYPE,sRoleName IN ROLES.ROLE_NAME%TYPE,sRoleComments IN ROLES.COMMENTS%TYPE) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nRoleId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select a Role.' );END IF;/*-------------------------------------------------------*/-- Update ROLEUPDATE ROLES SETROLE_NAME = sRoleName, ROLES.COMMENTS = sRoleCommentsWHERE ROLE_ID = nRoleId;END Update_Role;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Role ( sRoleIdList IN VARCHAR2 ) ISrowCount NUMBER DEFAULT 0;BEGIN/*--------------- Business Rules Here -------------------*/-- Check if any Users user this roleSELECT Count(*) INTO rowCountFROM USER_ROLES urWHERE ur.ROLE_ID IN (SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some Users are still using this Role ( Counted '|| rowCount ||' ).' );END IF;/*-------------------------------------------------------*/-- Remove Role Privileges --DELETEFROM ROLE_PRIVILEGES rpWHERE rp.ROLE_ID IN ( SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );-- Remove Role Data Permissions --DELETEFROM DATA_PERMISSIONS dpWHERE dp.ROLE_ID IN ( SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );-- Remove Role --DELETEFROM ROLES roWHERE ro.ROLE_ID IN ( SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );END Remove_Role;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Set_Role_Permissions ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL ) ISnPermId PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;BEGIN/*--------------- Business Rules Here -------------------*/IF (nRoleId IS NULL) OR (nObjId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRoleId= '|| nRoleId ||', nObjId='|| nObjId );END IF;/*-------------------------------------------------------*/--- Set "Visible" state ------------------------------------ Get PermId for "Visible"SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumVISIBLE';Delete_Role_Permission ( nRoleId, nObjId, nPermId );IF cIsVisible IS NOT NULL THENINSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )VALUES ( nRoleId, nObjId, nPermId, cIsVisible );ELSEIF cIsActive IS NOT NULL THEN-- If "Active" is Set then "Visible" must be "SHOW=Y"INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )VALUES ( nRoleId, nObjId, nPermId, 'Y' );END IF;END IF;--- Set "Active" state ------------------------------------ Get PermId for "Visible"SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumACTIVE';Delete_Role_Permission ( nRoleId, nObjId, nPermId );IF cIsActive IS NOT NULL THENINSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )VALUES ( nRoleId, nObjId, nPermId, cIsActive );END IF;END Set_Role_Permissions;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Set_Role_Variation_Permissions ( nUserId IN USER_ROLES.USER_ID%TYPE,nAppId IN ROLES.APP_ID%TYPE,nRoleId IN ROLES.ROLE_ID%TYPE,nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL ) ISnPermId PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;nRoleVariationId ROLES.ROLE_ID%TYPE DEFAULT NULL;sUserName USERS.USER_NAME%TYPE;nDataPermCount NUMBER;nRolePermCount NUMBER;--- Get Role Variation Id ---CURSOR curRoleVariation ISSELECT ro.ROLE_IDFROM USER_ROLES ur,ROLES roWHERE ur.ROLE_ID = ro.ROLE_IDAND ro.IS_ROLE_VARIATION = 'Y'AND ur.USER_ID = nUserId;recRoleVariation curRoleVariation%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nUserId IS NULL) OR (nAppId IS NULL) OR (nObjId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nUserId='|| nUserId ||', nAppId='|| nAppId ||', nObjId='|| nObjId);END IF;/*-------------------------------------------------------*/IF Is_Permissions_Changed( nRoleId, nObjId, cIsVisible, cIsActive ) THENIF nRoleId is not NULL AND Is_Role_Variation ( nRoleId ) THEN--- Set this user role permissions ---Set_Role_Permissions ( nRoleId, nObjId, cIsVisible, cIsActive );--- Remove this user role for no permission settings ----- Get Role Permissions CountSELECT Count(*) INTO nRolePermCount FROM ROLE_PRIVILEGES rp WHERE rp.ROLE_ID = nRoleVariationId;-- Get Role Permissions CountSELECT Count(*) INTO nDataPermCount FROM DATA_PERMISSIONS dp WHERE dp.ROLE_ID = nRoleVariationId;IF (nRolePermCount = 0) AND (nDataPermCount = 0) THEN-- There are no permission settings, hence proceed to remove this user roleDELETEFROM ROLES roWHERE ro.ROLE_ID = nRoleVariationId;END IF;ELSE--- Get Role Variation Id ---OPEN curRoleVariation;FETCH curRoleVariation INTO recRoleVariation;IF curRoleVariation%FOUND THENnRoleVariationId := recRoleVariation.ROLE_ID;END IF;CLOSE curRoleVariation;--- Create Role Variation if does not exist ---IF nRoleVariationId IS NULL THEN-- Get role_idSELECT SEQ_ROLE_ID.NEXTVAL INTO nRoleVariationId FROM DUAL;-- Get user_nameSELECT usr.USER_NAME INTO sUserName FROM USERS usr WHERE usr.USER_ID = nUserId;-- Create Role VariationINSERT INTO ROLES ( ROLE_ID, APP_ID, ROLE_NAME, IS_ROLE_VARIATION, COMMENTS )VALUES ( nRoleVariationId, nAppId, UPPER( sUserName ) || '_SPECIFIC', 'Y', 'Auto-created role to define user specific permissions.');-- Link this role to userINSERT INTO USER_ROLES ( USER_ID, ROLE_ID )VALUES ( nUserId, nRoleVariationId );END IF;--- Set this user role permissions ---Set_Role_Permissions ( nRoleVariationId, nObjId, cIsVisible, cIsActive );END IF;END IF;END Set_Role_Variation_Permissions;/*--------------------------------------------------------------------------------------------------*/FUNCTION Is_Permissions_Changed ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE,cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE ) RETURN BOOLEAN ISReturnValue BOOLEAN DEFAULT FALSE;cCurrentIsVisible ROLE_PRIVILEGES.PERM_VALUE%TYPE;cCurrentIsActive ROLE_PRIVILEGES.PERM_VALUE%TYPE;CURSOR curCurrentIsVisible ISSELECT rp.PERM_VALUEFROM ROLE_PRIVILEGES rpWHERE rp.ROLE_ID = nRoleIdAND rp.OBJ_ID = nObjIdAND rp.PERM_ID = 1;recCurrentIsVisible curCurrentIsVisible%ROWTYPE;CURSOR curCurrentIsActive ISSELECT rp.PERM_VALUEFROM ROLE_PRIVILEGES rpWHERE rp.ROLE_ID = nRoleIdAND rp.OBJ_ID = nObjIdAND rp.PERM_ID = 2;recCurrentIsActive curCurrentIsActive%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get "Visible" PermissionOPEN curCurrentIsVisible;FETCH curCurrentIsVisible INTO recCurrentIsVisible;IF curCurrentIsVisible%FOUND THENcCurrentIsVisible := recCurrentIsVisible.PERM_VALUE;END IF;CLOSE curCurrentIsVisible;-- Get "Active" PermissionOPEN curCurrentIsActive;FETCH curCurrentIsActive INTO recCurrentIsActive;IF curCurrentIsActive%FOUND THENcCurrentIsActive := recCurrentIsActive.PERM_VALUE;END IF;CLOSE curCurrentIsActive;--- Compare ---IF NOT Is_Same_String( cCurrentIsVisible, cIsVisible ) OR NOT Is_Same_String( cCurrentIsActive, cIsActive ) THENReturnValue := TRUE;END IF;RETURN ReturnValue;END Is_Permissions_Changed;/*--------------------------------------------------------------------------------------------------*/FUNCTION Is_Role_Variation ( nRoleId IN ROLES.ROLE_ID%TYPE ) RETURN BOOLEAN ISReturnValue BOOLEAN DEFAULT FALSE;cIsRoleVariation CHAR;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get is_role_variationSELECT ro.IS_ROLE_VARIATION INTO cIsRoleVariationFROM ROLES roWHERE ro.ROLE_ID = nRoleId;IF cIsRoleVariation IS NOT NULL THENReturnValue := TRUE;END IF;RETURN ReturnValue;END Is_Role_Variation;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Delete_Role_Permission ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,nPermId IN ROLE_PRIVILEGES.PERM_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nRoleId IS NULL) OR (nObjId IS NULL) OR (nPermId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRoleId= '|| nRoleId ||', nObjId='|| nObjId ||', nPermId='|| nPermId );END IF;/*-------------------------------------------------------*/DELETEFROM ROLE_PRIVILEGES rpWHERE rp.ROLE_ID = nRoleIdAND rp.OBJ_ID = nObjIdAND rp.PERM_ID = nPermId;END Delete_Role_Permission;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Grant_Role ( sRoleIdList IN VARCHAR2,nUserId IN USER_ROLES.USER_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF sRoleIdList IS NULL THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one role.' );END IF;/*-------------------------------------------------------*/--- Grant Role(s) ---INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )SELECT nUserId, qry.ROLE_IDFROM (SELECT ro.ROLE_IDFROM ROLES roWHERE ro.ROLE_ID IN ( SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) )MINUSSELECT ur.ROLE_IDFROM USER_ROLES urWHERE ur.USER_ID = nUserId) qry;END Grant_Role;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Revoke_Role ( sRoleIdList IN VARCHAR2,nUserId IN USER_ROLES.USER_ID%TYPE ) ISnidcollector ACCMGR_NUMBER_TAB_t := ACCMGR_NUMBER_TAB_t();roleId NUMBER;roleName ROLES.ROLE_NAME%TYPE;sUserSpec USERS.USER_NAME%TYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF sRoleIdList IS NULL THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one role.' );END IF;/*-------------------------------------------------------*/--- Revoke Role(s) ---DELETE FROM USER_ROLES urWHERE ur.USER_ID = nUserIdAND ur.ROLE_ID IN (SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ) );-- Get user_nameSELECT usr.USER_NAME INTO sUserSpec FROM USERS usr WHERE usr.USER_ID = nUserId;sUserSpec := UPPER( sUserSpec ) || '_SPECIFIC';nidcollector := IN_LIST_NUMBER (sRoleIdList);FOR i IN 1 .. nidcollector.COUNTLOOProleId := nidcollector (i);-- Get the role nameselect role_name into roleName from roles where role_id = roleId;-- See if the role name contains the users nameIF (IS_SAME_STRING(roleName, sUserSpec) = TRUE) THEN-- This is this users specific role and so we should remove it to ensure-- database is kept in a good clean state for the next time a specific-- role for this user is requiredDELETE FROM ROLE_PRIVILEGES rp WHERE rp.ROLE_ID = roleId;DELETE FROM DATA_PERMISSIONS dp WHERE dp.ROLE_ID = roleId;DELETE FROM ROLES ro WHERE ro.ROLE_ID = roleId;EXIT WHEN TRUE;END IF;END LOOP;END Revoke_Role;/*--------------------------------------------------------------------------------------------------*/END pk_Role;/---------------------------------------------------------- DDL for Package Body PK_SECURITY--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "ACCESS_MANAGER"."PK_SECURITY" IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 28/Apr/2005|| Body Version: 1.0------------------------------*//*-------------------------------------------------------------------------------------------------------*/FUNCTION IS_LOCAL_PASSWORD_CORRECT ( UserName IN VARCHAR2, UserPassword IN VARCHAR2 ) RETURN NUMBER ISencryptedUserPassword VARCHAR2(4000);BEGINSELECT usr.USER_PASSWORD INTO encryptedUserPasswordFROM USERS usrWHERE usr.USER_NAME = UserName;IF encryptedUserPassword = PK_AMUTILS.GET_HASH( UserPassword ) THEN-- Password CorrectRETURN 1;ELSE-- Password IncorrectRETURN -1;END IF;END;/*--------------------------------------------------------------------------------------------------*/FUNCTION Set_Password ( sUserName IN VARCHAR2, sPasswordA IN VARCHAR2, sPasswordB IN VARCHAR2 ) RETURN NUMBER ISBEGIN-- Used to set password for the first time.-- It can only be set if previous password in null/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/IF sPasswordA = sPasswordB THEN-- Update passwordsUPDATE USERS usr SETusr.USER_PASSWORD = PK_AMUTILS.Get_Hash ( sPasswordA )WHERE usr.USER_NAME = sUserNameAND usr.USER_PASSWORD IS NULL;-- Successfull updateRETURN 1;ELSE-- Password mistmatchRETURN -1;END IF;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE GET_USER_STATIC_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur) ISBEGINOPEN RecordSet FORSELECT co.obj_name, perm.perm_id, perm.perm_valueFROM ((/* All 'Y' User Permissions *//* Get all 'Y' permissions */SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, rp.PERM_VALUEFROM USER_ROLES ur,ROLE_PRIVILEGES rp,ROLES roWHERE ur.ROLE_ID = ro.ROLE_IDAND rp.ROLE_ID = ro.ROLE_IDAND ur.USER_ID = UsedIdAND rp.PERM_VALUE = 'Y'--AND ro.APP_ID = AppIdMINUS/* Revoke permissions if they set to 'N'*/SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, 'Y' AS PERM_VALUEFROM USER_ROLES ur,ROLE_PRIVILEGES rp,ROLES roWHERE ur.ROLE_ID = ro.ROLE_IDAND rp.ROLE_ID = ro.ROLE_IDAND ur.USER_ID = UsedIdAND rp.PERM_VALUE = 'N'-- AND ro.APP_ID = AppId)MINUS/* Role Variant Revokes */SELECT DISTINCT rp.OBJ_ID, rp.perm_id, 'Y' AS perm_valueFROM role_privileges rp,ROLES ro,user_roles urWHERE ro.role_id = rp.role_idAND ro.role_id = ur.role_idAND rp.PERM_VALUE = 'N'AND ro.IS_ROLE_VARIATION = 'Y'AND ur.user_id = UsedId-- AND ro.APP_ID = AppId) perm,control_objects coWHERE perm.obj_id = co.obj_id;-- AND co.APP_ID = AppId;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE GET_USER_DATA_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur) ISBEGINOPEN RecordSet FORSELECT qry.TABLE_NAME,qry.REF_COLUMN_VAL,qry.PERM_ID,qry.PERM_VALUE,co.OBJ_NAMEFROM CONTROL_OBJECTS co,DATA_TABLES dt,(/* Access Control Data Permissions */SELECT bl.DT_ID,bl.TABLE_NAME,bl.REF_COLUMN_VAL,bl.PERM_ID,DECODE ( yc.PERM_VALUE, NULL, bl.PERM_VALUE, yc.PERM_VALUE ) AS PERM_VALUEFROM (/* Get base list for Data Permissions */SELECT rol.ROLE_ID, dt.DT_ID, dt.TABLE_NAME, 0 AS REF_COLUMN_VAL, pt.PERM_ID, 'Y' AS PERM_VALUEFROM CONTROL_OBJECTS co,DATA_TABLES dt,PERMISSION_TYPES pt,DATA_PERMISSIONS dp,(/* Get User Roles for this Application */SELECT ro.*FROM ROLES ro,USER_ROLES urWHERE ur.ROLE_ID = ro.ROLE_ID--AND ro.APP_ID = AppIdAND ur.USER_ID = UsedId) rolWHERE dt.OBJ_ID = co.OBJ_IDAND dp.DT_ID = dt.DT_IDAND dp.ROLE_ID = rol.ROLE_ID--AND co.APP_ID = AppId) bl,(/* Find only 'Y' Permissions, which will change 'All' permission to 'N' */SELECT dt.DT_ID, 0 AS REF_COLUMN_VAL, dp.PERM_ID, 'N' AS PERM_VALUE, COUNT(*) AS YES_COUNTFROM DATA_PERMISSIONS dp,DATA_TABLES dt,ROLES ro,USER_ROLES urWHERE ur.ROLE_ID = ro.ROLE_IDAND dp.ROLE_ID = ro.ROLE_IDAND dp.DT_ID = dt.DT_ID-- AND ro.APP_ID = AppIdAND ur.USER_ID = UsedIdAND ro.IS_ROLE_VARIATION IS NULLGROUP BY dt.DT_ID, dp.PERM_ID) ycWHERE yc.DT_ID (+) = bl.DT_IDAND yc.PERM_ID (+) = bl.PERM_IDUNION(/* Get Filter Settings for All Permission Types */SELECT dt.DT_ID, dt.TABLE_NAME, dp.REF_COLUMN_VAL, dp.PERM_ID, dp.PERM_VALUEFROM DATA_PERMISSIONS dp,DATA_TABLES dt,ROLES ro,USER_ROLES urWHERE ur.ROLE_ID = ro.ROLE_IDAND dp.ROLE_ID = ro.ROLE_IDAND dp.DT_ID = dt.DT_ID-- AND ro.APP_ID = AppIdAND ur.USER_ID = UsedIdMINUS/* Overwrite Data Permissions using User Specific Role */SELECT DISTINCTdt.DT_ID,dt.TABLE_NAME,dp.REF_COLUMN_VAL,dp.PERM_ID,DECODE ( dp.PERM_VALUE,'Y', 'N', 'Y' ) AS PERM_VALUEFROM DATA_PERMISSIONS dp,DATA_TABLES dt,ROLES ro,USER_ROLES urWHERE ur.ROLE_ID = ro.ROLE_IDAND dp.ROLE_ID = ro.ROLE_IDAND dp.DT_ID = dt.DT_ID-- AND ro.APP_ID = AppIdAND ur.USER_ID = UsedIdAND ro.IS_ROLE_VARIATION = 'Y')) qryWHERE dt.OBJ_ID = co.OBJ_IDAND dt.DT_ID = qry.DT_ID;--AND co.APP_ID = AppId;END;/*-------------------------------------------------------------------------------------------------------*/END PK_SECURITY;/---------------------------------------------------------- DDL for Package Body PK_USER--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "ACCESS_MANAGER"."PK_USER" IS/*------------------------------|| Last Modified: J.Tweddle|| Modified Date: 21/Jan/2008|| Body Version: 2.1------------------------------*//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_User_Account ( sFullName IN USERS.FULL_NAME%TYPE,sUserName IN USERS.USER_NAME%TYPE,sUserEmail IN USERS.USER_EMAIL%TYPE,sDomain IN USERS.DOMAIN%TYPE ) ISUserId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get user_idSELECT SEQ_USER_ID.NEXTVAL INTO UserId FROM DUAL;-- Insert new User AccountINSERT INTO USERS (USER_ID, FULL_NAME, USER_NAME, USER_EMAIL, DOMAIN )VALUES( UserId, sFullName, sUserName, sUserEmail, sDomain );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'User Name '|| sUserName ||' already exists.');END Add_User_Account;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_User_Account ( sUserId IN VARCHAR2, sFullName IN USERS.FULL_NAME%TYPE,sUserName IN USERS.USER_NAME%TYPE,sUserEmail IN USERS.USER_EMAIL%TYPE,sDomain IN USERS.DOMAIN%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (sUserId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select a User Account.' );END IF;/*-------------------------------------------------------*/-- Update User AccountUPDATE USERS usr SETusr.FULL_NAME = sFullName, usr.USER_NAME = sUserName, usr.USER_EMAIL = sUserEmail, usr.DOMAIN = sDomainWHERE usr.USER_ID = sUserId;END Update_User_Account;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Application_User ( sUserIdList IN VARCHAR2,nAppId IN USER_APPLICATIONS.APP_ID%TYPE,cIncludeEveryone IN CHAR ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );END IF;/*-------------------------------------------------------*/-- Insert Application User --IF cIncludeEveryone = 'Y' THEN-- Insert All UsersINSERT INTO USER_APPLICATIONS ( USER_ID, APP_ID )SELECT qry.USER_ID, nAppIdFROM (SELECT usr.USER_IDFROM USERS usrMINUSSELECT ua.USER_IDFROM USER_APPLICATIONS uaWHERE ua.APP_ID = nAppId) qry;ELSE-- Insert specific user listINSERT INTO USER_APPLICATIONS ( USER_ID, APP_ID )SELECT qry.USER_ID, nAppIdFROM (SELECT usr.USER_IDFROM USERS usrWHERE usr.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ))MINUSSELECT ua.USER_IDFROM USER_APPLICATIONS uaWHERE ua.APP_ID = nAppIdAND ua.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ))) qry;END IF;--- Make sure Build in User is not included ---Remove_Application_User ( '0', nAppId );END Add_Application_User;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Application_User ( sUserIdList IN VARCHAR2,nAppId IN USER_APPLICATIONS.APP_ID%TYPE,cIncludeEveryone IN CHAR DEFAULT NULL) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );END IF;/*-------------------------------------------------------*/IF cIncludeEveryone = 'Y' THEN-- Remove All Users --DELETEFROM user_applications uaWHERE ua.app_id = nAppId;ELSE-- Remove Application Users --DELETEFROM user_applications uaWHERE ua.app_id = nAppIdAND ua.user_id IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));-- Remove User(s) Application Roles --DELETEFROM user_roles urWHERE ur.user_id IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ))AND ur.role_id IN (SELECT ro.role_idFROM roles roWHERE ro.app_id = nAppId);END IF;END Remove_Application_User;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Role_Member ( sUserIdList IN VARCHAR2,nRoleId IN USER_ROLES.ROLE_ID%TYPE,cIncludeEveryone IN CHAR ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );END IF;/*-------------------------------------------------------*/-- Insert Role Member --IF cIncludeEveryone = 'Y' THEN-- Insert All UsersINSERT INTO USER_ROLES ( USER_ID, ROLE_ID )SELECT qry.USER_ID, nRoleIdFROM (SELECT usr.USER_IDFROM USERS usrMINUSSELECT ur.USER_IDFROM USER_ROLES urWHERE ur.ROLE_ID = nRoleId) qry;ELSE-- Insert specific user listINSERT INTO USER_ROLES ( USER_ID, ROLE_ID )SELECT qry.USER_ID, nRoleIdFROM (SELECT usr.USER_IDFROM USERS usrWHERE usr.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ))MINUSSELECT ur.USER_IDFROM USER_ROLES urWHERE ur.ROLE_ID = nRoleIdAND ur.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ))) qry;END IF;--- Make sure Build in User is not included ---Remove_Role_Member ( '0', nRoleId );END Add_Role_Member;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Role_Member ( sUserIdList IN VARCHAR2,nRoleId IN USER_ROLES.ROLE_ID%TYPE,cIncludeEveryone IN CHAR DEFAULT NULL) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nRoleId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'RoleId is missing.' );END IF;IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );END IF;/*-------------------------------------------------------*/IF cIncludeEveryone = 'Y' THEN-- Remove All Users --DELETEFROM USER_ROLES urWHERE ur.ROLE_ID = nRoleId;ELSE-- Remove Application Users --DELETEFROM USER_ROLES urWHERE ur.ROLE_ID = nRoleIdAND ur.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));END IF;END Remove_Role_Member;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Disable_Users ( sUserIdList IN VARCHAR2,nAppId IN USER_APPLICATIONS.APP_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (sUserIdList IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );END IF;/*-------------------------------------------------------*/-- Disable User AccountsUPDATE USERS usr SETusr.IS_DISABLED = 'Y'WHERE usr.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));END Disable_Users;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_User_Account ( sUserIdList IN VARCHAR2 ) ISrowCount NUMBER DEFAULT 0;BEGIN/*--------------- Business Rules Here -------------------*/IF (sUserIdList IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one User Account.' );END IF;-- Check if any Users have any rolesSELECT Count(*) INTO rowCountFROM USER_ROLES urWHERE ur.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some Users still have Roles assigned. ( Counted '|| rowCount ||' ).' );END IF;/*-------------------------------------------------------*/-- Remove User Applications --DELETEFROM USER_APPLICATIONS uaWHERE ua.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));-- Remove User --DELETEFROM USERS usWHERE us.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual ));END Remove_User_Account;/*--------------------------------------------------------------------------------------------------*/END pk_user;/---------------------------------------------------------- DDL for Synonymn DM_DEF_MENU_ITEMS--------------------------------------------------------CREATE OR REPLACE SYNONYM "ACCESS_MANAGER"."DM_DEF_MENU_ITEMS" FOR "DM_DEV"."DEF_MENU_ITEMS";---------------------------------------------------------- DDL for Synonymn DM_DM_PROJECTS--------------------------------------------------------CREATE OR REPLACE SYNONYM "ACCESS_MANAGER"."DM_DM_PROJECTS" FOR "DEPLOYMENT_MANAGER"."DM_PROJECTS";---------------------------------------------------------- DDL for Synonymn PM_DM_PROJECTS--------------------------------------------------------CREATE OR REPLACE SYNONYM "ACCESS_MANAGER"."PM_DM_PROJECTS" FOR "DEPLOYMENT_MANAGER"."DM_PROJECTS";---------------------------------------------------------- DDL for Synonymn PROJECTS--------------------------------------------------------CREATE OR REPLACE SYNONYM "ACCESS_MANAGER"."PROJECTS" FOR "RELEASE_MANAGER"."PROJECTS";---------------------------------------------------------- DDL for Synonymn RM_PROJECTS--------------------------------------------------------CREATE OR REPLACE SYNONYM "ACCESS_MANAGER"."RM_PROJECTS" FOR "RELEASE_MANAGER"."PROJECTS";