WebSVN – DevTools – Blame – /ManagerSuite/Manager_Suite_VM/trunk/src/Access Manager.sql

Rev	Author	Line No.	Line
51	mhunt	1	`CREATE TABLE CONTROL_OBJECTS`
		2	`(`
		3	`OBJ_ID NUMBER NOT NULL,`
		4	`APP_ID NUMBER NOT NULL,`
		5	`OBJ_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		6	`PARENT_OBJ_ID NUMBER,`
		7	`OBJ_DESCRIPTION VARCHAR2(1000 BYTE)`
		8	`, CONSTRAINT PK_CONTROL_OBJECTS PRIMARY KEY`
		9	`(`
		10	`OBJ_ID`
		11	`)`
		12	`ENABLE`
		13	`)`
		14	`TABLESPACE "USERS"`
		15	`LOGGING`
		16	`PCTFREE 10`
		17	`PCTUSED 40`
		18	`INITRANS 1`
		19	`MAXTRANS 255`
		20	`STORAGE`
		21	`(`
		22	`INITIAL 128K`
		23	`MINEXTENTS 1`
		24	`MAXEXTENTS 2147483645`
		25	`FREELISTS 1`
		26	`FREELIST GROUPS 1`
		27	`BUFFER_POOL DEFAULT`
		28	`)`
		29	`;`
		30
		31	`CREATE TABLE PERMISSION_TYPES`
		32	`(`
		33	`PERM_ID NUMBER NOT NULL,`
		34	`PERM_NAME VARCHAR2(255 BYTE) NOT NULL`
		35	`, CONSTRAINT PK_PERMISSION_TYPES PRIMARY KEY`
		36	`(`
		37	`PERM_ID`
		38	`)`
		39	`ENABLE`
		40	`)`
		41	`TABLESPACE "USERS"`
		42	`LOGGING`
		43	`PCTFREE 10`
		44	`PCTUSED 40`
		45	`INITRANS 1`
		46	`MAXTRANS 255`
		47	`STORAGE`
		48	`(`
		49	`INITIAL 128K`
		50	`MINEXTENTS 1`
		51	`MAXEXTENTS 2147483645`
		52	`FREELISTS 1`
		53	`FREELIST GROUPS 1`
		54	`BUFFER_POOL DEFAULT`
		55	`)`
		56	`;`
		57
		58	`CREATE TABLE APPLICATIONS`
		59	`(`
		60	`APP_ID NUMBER NOT NULL,`
		61	`APPLICATION_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		62	`DB_SCHEMA VARCHAR2(1000 BYTE),`
		63	`TNS_NAME VARCHAR2(1000 BYTE),`
		64	`SCHEMA_USERNAME VARCHAR2(255 BYTE),`
		65	`SCHEMA_PASSWORD VARCHAR2(255 BYTE),`
		66	`ACRONYM VARCHAR2(3 BYTE) NOT NULL,`
		67	`IS_RUNNING CHAR(1 BYTE) NOT NULL`
		68	`, CONSTRAINT PK_APPLICATIONS PRIMARY KEY`
		69	`(`
		70	`APP_ID`
		71	`)`
		72	`ENABLE`
		73	`)`
		74	`TABLESPACE "USERS"`
		75	`LOGGING`
		76	`PCTFREE 10`
		77	`PCTUSED 40`
		78	`INITRANS 1`
		79	`MAXTRANS 255`
		80	`STORAGE`
		81	`(`
		82	`INITIAL 128K`
		83	`MINEXTENTS 1`
		84	`MAXEXTENTS 2147483645`
		85	`FREELISTS 1`
		86	`FREELIST GROUPS 1`
		87	`BUFFER_POOL DEFAULT`
		88	`)`
		89	`;`
		90
		91	`CREATE TABLE USERS`
		92	`(`
		93	`USER_ID NUMBER NOT NULL,`
		94	`FULL_NAME VARCHAR2(255 BYTE) NOT NULL,`
		95	`USER_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		96	`USER_PASSWORD VARCHAR2(4000 BYTE),`
		97	`USER_EMAIL VARCHAR2(1000 BYTE),`
		98	`DOMAIN VARCHAR2(1000 BYTE),`
		99	`IS_DISABLED CHAR(1 BYTE),`
		100	`IS_ONLINE CHAR(1 BYTE),`
		101	`ONLINE_AT VARCHAR2(50 BYTE),`
		102	`LAST_VISIT DATE,`
		103	`LAST_REQUEST NUMBER`
		104	`, CONSTRAINT PK_USERS PRIMARY KEY`
		105	`(`
		106	`USER_ID`
		107	`)`
		108	`ENABLE`
		109	`)`
		110	`TABLESPACE "USERS"`
		111	`LOGGING`
		112	`PCTFREE 10`
		113	`PCTUSED 40`
		114	`INITRANS 1`
		115	`MAXTRANS 255`
		116	`STORAGE`
		117	`(`
		118	`INITIAL 128K`
		119	`MINEXTENTS 1`
		120	`MAXEXTENTS 2147483645`
		121	`FREELISTS 1`
		122	`FREELIST GROUPS 1`
		123	`BUFFER_POOL DEFAULT`
		124	`)`
		125	`;`
		126
		127	`CREATE TABLE LOGIN_TRAIL`
		128	`(`
		129	`ENUM_EVENT NUMBER NOT NULL,`
		130	`USER_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		131	`CLIENT_IP VARCHAR2(255 BYTE),`
		132	`APP_ID NUMBER,`
		133	`STAMP DATE NOT NULL,`
		134	`COMMENTS VARCHAR2(4000 BYTE)`
		135	`)`
		136	`TABLESPACE "USERS"`
		137	`LOGGING`
		138	`PCTFREE 10`
		139	`PCTUSED 40`
		140	`INITRANS 1`
		141	`MAXTRANS 255`
		142	`STORAGE`
		143	`(`
		144	`INITIAL 128K`
		145	`MINEXTENTS 1`
		146	`MAXEXTENTS 2147483645`
		147	`FREELISTS 1`
		148	`FREELIST GROUPS 1`
		149	`BUFFER_POOL DEFAULT`
		150	`)`
		151	`;`
		152
		153	`CREATE TABLE DATA_TABLES`
		154	`(`
		155	`DT_ID NUMBER NOT NULL,`
		156	`OBJ_ID NUMBER NOT NULL,`
		157	`TABLE_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		158	`REF_COLUMN_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		159	`DISPLAY_COLUMN_NAME VARCHAR2(1000 BYTE) NOT NULL`
		160	`, CONSTRAINT PK_DATA_TABLES PRIMARY KEY`
		161	`(`
		162	`DT_ID`
		163	`)`
		164	`ENABLE`
		165	`)`
		166	`TABLESPACE "USERS"`
		167	`LOGGING`
		168	`PCTFREE 10`
		169	`PCTUSED 40`
		170	`INITRANS 1`
		171	`MAXTRANS 255`
		172	`STORAGE`
		173	`(`
		174	`INITIAL 128K`
		175	`MINEXTENTS 1`
		176	`MAXEXTENTS 2147483645`
		177	`FREELISTS 1`
		178	`FREELIST GROUPS 1`
		179	`BUFFER_POOL DEFAULT`
		180	`)`
		181	`;`
		182
		183	`CREATE TABLE ROLE_PRIVILEGES`
		184	`(`
		185	`ROLE_ID NUMBER NOT NULL,`
		186	`OBJ_ID NUMBER NOT NULL,`
		187	`PERM_ID NUMBER NOT NULL,`
		188	`PERM_VALUE CHAR(1 BYTE) NOT NULL`
		189	`)`
		190	`TABLESPACE "USERS"`
		191	`LOGGING`
		192	`PCTFREE 10`
		193	`PCTUSED 40`
		194	`INITRANS 1`
		195	`MAXTRANS 255`
		196	`STORAGE`
		197	`(`
		198	`INITIAL 128K`
		199	`MINEXTENTS 1`
		200	`MAXEXTENTS 2147483645`
		201	`FREELISTS 1`
		202	`FREELIST GROUPS 1`
		203	`BUFFER_POOL DEFAULT`
		204	`)`
		205	`;`
		206
		207	`CREATE TABLE DATA_PERMISSIONS`
		208	`(`
		209	`DT_ID NUMBER NOT NULL,`
		210	`ROLE_ID NUMBER NOT NULL,`
		211	`REF_COLUMN_VAL NUMBER NOT NULL,`
		212	`PERM_ID NUMBER NOT NULL,`
		213	`PERM_VALUE CHAR(1 BYTE) NOT NULL`
		214	`)`
		215	`TABLESPACE "USERS"`
		216	`LOGGING`
		217	`PCTFREE 10`
		218	`PCTUSED 40`
		219	`INITRANS 1`
		220	`MAXTRANS 255`
		221	`STORAGE`
		222	`(`
		223	`INITIAL 128K`
		224	`MINEXTENTS 1`
		225	`MAXEXTENTS 2147483645`
		226	`FREELISTS 1`
		227	`FREELIST GROUPS 1`
		228	`BUFFER_POOL DEFAULT`
		229	`)`
		230	`;`
		231
		232	`CREATE TABLE USER_ROLES`
		233	`(`
		234	`USER_ID NUMBER NOT NULL,`
		235	`ROLE_ID NUMBER NOT NULL`
		236	`)`
		237	`TABLESPACE "USERS"`
		238	`LOGGING`
		239	`PCTFREE 10`
		240	`PCTUSED 40`
		241	`INITRANS 1`
		242	`MAXTRANS 255`
		243	`STORAGE`
		244	`(`
		245	`INITIAL 128K`
		246	`MINEXTENTS 1`
		247	`MAXEXTENTS 2147483645`
		248	`FREELISTS 1`
		249	`FREELIST GROUPS 1`
		250	`BUFFER_POOL DEFAULT`
		251	`)`
		252	`;`
		253
		254	`CREATE TABLE ROLES`
		255	`(`
		256	`ROLE_ID NUMBER NOT NULL,`
		257	`APP_ID NUMBER NOT NULL,`
		258	`ROLE_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		259	`IS_ROLE_VARIATION CHAR(1 BYTE),`
		260	`COMMENTS VARCHAR2(4000 BYTE)`
		261	`, CONSTRAINT PK_ROLES PRIMARY KEY`
		262	`(`
		263	`ROLE_ID`
		264	`)`
		265	`ENABLE`
		266	`)`
		267	`TABLESPACE "USERS"`
		268	`LOGGING`
		269	`PCTFREE 10`
		270	`PCTUSED 40`
		271	`INITRANS 1`
		272	`MAXTRANS 255`
		273	`STORAGE`
		274	`(`
		275	`INITIAL 128K`
		276	`MINEXTENTS 1`
		277	`MAXEXTENTS 2147483645`
		278	`FREELISTS 1`
		279	`FREELIST GROUPS 1`
		280	`BUFFER_POOL DEFAULT`
		281	`)`
		282	`;`
		283
		284	`CREATE TABLE USER_APPLICATIONS`
		285	`(`
		286	`USER_ID NUMBER NOT NULL,`
		287	`APP_ID NUMBER NOT NULL,`
		288	`LAST_VISIT_STAMP DATE`
		289	`)`
		290	`TABLESPACE "USERS"`
		291	`LOGGING`
		292	`PCTFREE 10`
		293	`PCTUSED 40`
		294	`INITRANS 1`
		295	`MAXTRANS 255`
		296	`STORAGE`
		297	`(`
		298	`INITIAL 128K`
		299	`MINEXTENTS 1`
		300	`MAXEXTENTS 2147483645`
		301	`FREELISTS 1`
		302	`FREELIST GROUPS 1`
		303	`BUFFER_POOL DEFAULT`
		304	`)`
		305	`;`
		306
		307	`CREATE TABLE PAGE_CONTROL_OBJECTS`
		308	`(`
		309	`PAGE_ID NUMBER NOT NULL,`
		310	`OBJ_ID NUMBER NOT NULL`
		311	`)`
		312	`TABLESPACE "USERS"`
		313	`LOGGING`
		314	`PCTFREE 10`
		315	`PCTUSED 40`
		316	`INITRANS 1`
		317	`MAXTRANS 255`
		318	`STORAGE`
		319	`(`
		320	`INITIAL 128K`
		321	`MINEXTENTS 1`
		322	`MAXEXTENTS 2147483645`
		323	`FREELISTS 1`
		324	`FREELIST GROUPS 1`
		325	`BUFFER_POOL DEFAULT`
		326	`)`
		327	`;`
		328
		329	`CREATE TABLE DEF_ACTION_BUTTONS`
		330	`(`
		331	`ABTN_ID NUMBER NOT NULL,`
		332	`ABTN_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		333	`TEXT VARCHAR2(50 BYTE),`
		334	`ACTION_LINK VARCHAR2(4000 BYTE),`
		335	`EVENT_HANDLER VARCHAR2(4000 BYTE),`
		336	`IMG_ENABLED VARCHAR2(1000 BYTE),`
		337	`IMG_DISABLED VARCHAR2(1000 BYTE),`
		338	`HINT VARCHAR2(50 BYTE),`
		339	`VISIBLE CHAR(1 BYTE) NOT NULL,`
		340	`ACTIVE CHAR(1 BYTE) NOT NULL,`
		341	`IS_READONLY_ACTION CHAR(1 BYTE) NOT NULL`
		342	`, CONSTRAINT PK_DEF_ACTION_BUTTONS PRIMARY KEY`
		343	`(`
		344	`ABTN_ID`
		345	`)`
		346	`ENABLE`
		347	`)`
		348	`TABLESPACE "USERS"`
		349	`LOGGING`
		350	`PCTFREE 10`
		351	`PCTUSED 40`
		352	`INITRANS 1`
		353	`MAXTRANS 255`
		354	`STORAGE`
		355	`(`
		356	`INITIAL 128K`
		357	`MINEXTENTS 1`
		358	`MAXEXTENTS 2147483645`
		359	`FREELISTS 1`
		360	`FREELIST GROUPS 1`
		361	`BUFFER_POOL DEFAULT`
		362	`)`
		363	`;`
		364
		365	`CREATE TABLE APPLICATION_PAGES`
		366	`(`
		367	`PAGE_ID NUMBER NOT NULL,`
		368	`APP_ID NUMBER NOT NULL,`
		369	`PAGE_NAME VARCHAR2(1000 BYTE) NOT NULL`
		370	`, CONSTRAINT PK_APPLICATION_PAGES PRIMARY KEY`
		371	`(`
		372	`PAGE_ID`
		373	`)`
		374	`ENABLE`
		375	`)`
		376	`TABLESPACE "USERS"`
		377	`LOGGING`
		378	`PCTFREE 10`
		379	`PCTUSED 40`
		380	`INITRANS 1`
		381	`MAXTRANS 255`
		382	`STORAGE`
		383	`(`
		384	`INITIAL 128K`
		385	`MINEXTENTS 1`
		386	`MAXEXTENTS 2147483645`
		387	`FREELISTS 1`
		388	`FREELIST GROUPS 1`
		389	`BUFFER_POOL DEFAULT`
		390	`)`
		391	`;`
		392
		393	`CREATE TABLE VALIDATION_RULES`
		394	`(`
		395	`FIELD_NAME VARCHAR2(1000 BYTE) NOT NULL,`
		396	`IS_REQUIRED CHAR(1 BYTE) NOT NULL,`
		397	`IS_NUMERIC CHAR(1 BYTE),`
		398	`MIN_NUMERIC_VALUE NUMBER,`
		399	`MAX_NUMERIC_VALUE NUMBER,`
		400	`IS_DATE CHAR(1 BYTE),`
		401	`START_DATE DATE,`
		402	`END_DATE DATE,`
		403	`MIN_STRING_LENGTH NUMBER,`
		404	`MAX_STRING_LENGTH NUMBER,`
		405	`REGEXP VARCHAR2(4000 BYTE),`
		406	`REGEXP_DESCRIPTION VARCHAR2(50 BYTE)`
		407	`)`
		408	`TABLESPACE "USERS"`
		409	`LOGGING`
		410	`PCTFREE 10`
		411	`PCTUSED 40`
		412	`INITRANS 1`
		413	`MAXTRANS 255`
		414	`STORAGE`
		415	`(`
		416	`INITIAL 128K`
		417	`MINEXTENTS 1`
		418	`MAXEXTENTS 2147483645`
		419	`FREELISTS 1`
		420	`FREELIST GROUPS 1`
		421	`BUFFER_POOL DEFAULT`
		422	`)`
		423	`;`
		424
		425	`ALTER TABLE CONTROL_OBJECTS`
		426	`ADD CONSTRAINT FK_CONTROL_OBJ_REF_APPLICS FOREIGN KEY`
		427	`(`
		428	`APP_ID`
		429	`)`
		430	`REFERENCES APPLICATIONS`
		431	`(`
		432	`APP_ID`
		433	`) ENABLE`
		434	`;`
		435
		436	`ALTER TABLE DATA_TABLES`
		437	`ADD CONSTRAINT FK_DATA_TABLE_REF_OBJ FOREIGN KEY`
		438	`(`
		439	`OBJ_ID`
		440	`)`
		441	`REFERENCES CONTROL_OBJECTS`
		442	`(`
		443	`OBJ_ID`
		444	`) ENABLE`
		445	`;`
		446
		447	`ALTER TABLE ROLE_PRIVILEGES`
		448	`ADD CONSTRAINT FK_ROLE_OBJ_REF_ROLES FOREIGN KEY`
		449	`(`
		450	`ROLE_ID`
		451	`)`
		452	`REFERENCES ROLES`
		453	`(`
		454	`ROLE_ID`
		455	`) ENABLE`
		456	`;`
		457
		458	`ALTER TABLE ROLE_PRIVILEGES`
		459	`ADD CONSTRAINT FK_ROLE_OBJ_REF_OBJS FOREIGN KEY`
		460	`(`
		461	`OBJ_ID`
		462	`)`
		463	`REFERENCES CONTROL_OBJECTS`
		464	`(`
		465	`OBJ_ID`
		466	`) ENABLE`
		467	`;`
		468
		469	`ALTER TABLE ROLE_PRIVILEGES`
		470	`ADD CONSTRAINT FK_ROLE_OBJ_REF_PERMISS FOREIGN KEY`
		471	`(`
		472	`PERM_ID`
		473	`)`
		474	`REFERENCES PERMISSION_TYPES`
		475	`(`
		476	`PERM_ID`
		477	`) ENABLE`
		478	`;`
		479
		480	`ALTER TABLE DATA_PERMISSIONS`
		481	`ADD CONSTRAINT FK_DATA_PER_REF_ROLES FOREIGN KEY`
		482	`(`
		483	`ROLE_ID`
		484	`)`
		485	`REFERENCES ROLES`
		486	`(`
		487	`ROLE_ID`
		488	`) ENABLE`
		489	`;`
		490
		491	`ALTER TABLE DATA_PERMISSIONS`
53	mhunt	492	`ADD CONSTRAINT FK_DATA_PERM_REF_DATA_TABLE FOREIGN KEY`
51	mhunt	493	`(`
53	mhunt	494	`DT_ID`
51	mhunt	495	`)`
53	mhunt	496	`REFERENCES DATA_TABLES`
51	mhunt	497	`(`
53	mhunt	498	`DT_ID`
51	mhunt	499	`) ENABLE`
		500	`;`
		501
		502	`ALTER TABLE DATA_PERMISSIONS`
53	mhunt	503	`ADD CONSTRAINT FK_DATA_PERM_REF_PERM_TYPES FOREIGN KEY`
51	mhunt	504	`(`
53	mhunt	505	`PERM_ID`
51	mhunt	506	`)`
53	mhunt	507	`REFERENCES PERMISSION_TYPES`
51	mhunt	508	`(`
53	mhunt	509	`PERM_ID`
51	mhunt	510	`) ENABLE`
		511	`;`
		512
		513	`ALTER TABLE USER_ROLES`
		514	`ADD CONSTRAINT FK_USER_ROLES_REF_ROLES FOREIGN KEY`
		515	`(`
		516	`ROLE_ID`
		517	`)`
		518	`REFERENCES ROLES`
		519	`(`
		520	`ROLE_ID`
		521	`) ENABLE`
		522	`;`
		523
		524	`ALTER TABLE USER_ROLES`
		525	`ADD CONSTRAINT FK_USER_ROLES_REF_USERS FOREIGN KEY`
		526	`(`
		527	`USER_ID`
		528	`)`
		529	`REFERENCES USERS`
		530	`(`
		531	`USER_ID`
		532	`) ENABLE`
		533	`;`
		534
		535	`ALTER TABLE ROLES`
		536	`ADD CONSTRAINT FK_ROLES_REF_APPLICS FOREIGN KEY`
		537	`(`
		538	`APP_ID`
		539	`)`
		540	`REFERENCES APPLICATIONS`
		541	`(`
		542	`APP_ID`
		543	`) ENABLE`
		544	`;`
		545
		546	`ALTER TABLE USER_APPLICATIONS`
		547	`ADD CONSTRAINT FK_USER_APP_REF_USERS FOREIGN KEY`
		548	`(`
		549	`USER_ID`
		550	`)`
		551	`REFERENCES USERS`
		552	`(`
		553	`USER_ID`
		554	`) ENABLE`
		555	`;`
		556
		557	`ALTER TABLE USER_APPLICATIONS`
		558	`ADD CONSTRAINT FK_USER_APPS_REF_APPS FOREIGN KEY`
		559	`(`
		560	`APP_ID`
		561	`)`
		562	`REFERENCES APPLICATIONS`
		563	`(`
		564	`APP_ID`
		565	`) ENABLE`
		566	`;`
		567
		568	`ALTER TABLE PAGE_CONTROL_OBJECTS`
		569	`ADD CONSTRAINT FK_PAGE_CON_REF_APP_PAGES FOREIGN KEY`
		570	`(`
		571	`PAGE_ID`
		572	`)`
		573	`REFERENCES APPLICATION_PAGES`
		574	`(`
		575	`PAGE_ID`
		576	`) ENABLE`
		577	`;`
		578
		579	`ALTER TABLE PAGE_CONTROL_OBJECTS`
		580	`ADD CONSTRAINT FK_PAGE_CON_REF_CONTROL_OBJS FOREIGN KEY`
		581	`(`
		582	`OBJ_ID`
		583	`)`
		584	`REFERENCES CONTROL_OBJECTS`
		585	`(`
		586	`OBJ_ID`
		587	`) ENABLE`
		588	`;`
		589
		590	`ALTER TABLE APPLICATION_PAGES`
		591	`ADD CONSTRAINT FK_APP_PAGE_REF_APP FOREIGN KEY`
		592	`(`
		593	`APP_ID`
		594	`)`
		595	`REFERENCES APPLICATIONS`
		596	`(`
		597	`APP_ID`
		598	`) ENABLE`
		599	`;`
		600
53	mhunt	601	`CREATE OR REPLACE VIEW CONTROLOBJECTS_VS_ROLES AS select distinct app.app_id, app.application_name, co.obj_id, co.obj_name, co.obj_description, ro.role_id, ro.role_name`
		602	`from applications app, control_objects co, roles ro, role_privileges rp`
		603	`where app.app_id=co.app_id`
		604	`and app.app_id=ro.app_id`
		605	`and ro.role_id=rp.role_id`
		606	`and rp.obj_id=co.obj_id`
		607	`order by co.obj_name;`
		608
51	mhunt	609	`CREATE UNIQUE INDEX UNQ_CONTROL_OBJ ON CONTROL_OBJECTS (APP_ID ASC, OBJ_NAME ASC) TABLESPACE "USERS"`
		610	`LOGGING`
		611	`PCTFREE 10`
		612	`INITRANS 2`
		613	`MAXTRANS 255`
		614	`STORAGE`
		615	`(`
		616	`INITIAL 128K`
		617	`MINEXTENTS 1`
		618	`MAXEXTENTS 2147483645`
		619	`FREELISTS 1`
		620	`FREELIST GROUPS 1`
		621	`BUFFER_POOL DEFAULT`
		622	`)`
		623	`;`
		624
		625	`CREATE UNIQUE INDEX UNQ_PERMISSION_TYPES ON PERMISSION_TYPES (PERM_NAME ASC) TABLESPACE "USERS"`
		626	`LOGGING`
		627	`PCTFREE 10`
		628	`INITRANS 2`
		629	`MAXTRANS 255`
		630	`STORAGE`
		631	`(`
		632	`INITIAL 128K`
		633	`MINEXTENTS 1`
		634	`MAXEXTENTS 2147483645`
		635	`FREELISTS 1`
		636	`FREELIST GROUPS 1`
		637	`BUFFER_POOL DEFAULT`
		638	`)`
		639	`;`
		640
		641	`CREATE UNIQUE INDEX UNQ_APPLICATIONS ON APPLICATIONS (APPLICATION_NAME ASC) TABLESPACE "USERS"`
		642	`LOGGING`
		643	`PCTFREE 10`
		644	`INITRANS 2`
		645	`MAXTRANS 255`
		646	`STORAGE`
		647	`(`
		648	`INITIAL 128K`
		649	`MINEXTENTS 1`
		650	`MAXEXTENTS 2147483645`
		651	`FREELISTS 1`
		652	`FREELIST GROUPS 1`
		653	`BUFFER_POOL DEFAULT`
		654	`)`
		655	`;`
		656
		657	`CREATE UNIQUE INDEX UNQ_USERS ON USERS (USER_NAME ASC) TABLESPACE "USERS"`
		658	`LOGGING`
		659	`PCTFREE 10`
		660	`INITRANS 2`
		661	`MAXTRANS 255`
		662	`STORAGE`
		663	`(`
		664	`INITIAL 128K`
		665	`MINEXTENTS 1`
		666	`MAXEXTENTS 2147483645`
		667	`FREELISTS 1`
		668	`FREELIST GROUPS 1`
		669	`BUFFER_POOL DEFAULT`
		670	`)`
		671	`;`
		672
		673	`CREATE INDEX INX_CLIENT_IP ON LOGIN_TRAIL (CLIENT_IP ASC) TABLESPACE "USERS"`
		674	`LOGGING`
		675	`PCTFREE 10`
		676	`INITRANS 2`
		677	`MAXTRANS 255`
		678	`STORAGE`
		679	`(`
		680	`INITIAL 128K`
		681	`MINEXTENTS 1`
		682	`MAXEXTENTS 2147483645`
		683	`FREELISTS 1`
		684	`FREELIST GROUPS 1`
		685	`BUFFER_POOL DEFAULT`
		686	`)`
		687	`;`
		688
		689	`CREATE INDEX INX_ENUM_EVENT ON LOGIN_TRAIL (ENUM_EVENT ASC) TABLESPACE "USERS"`
		690	`LOGGING`
		691	`PCTFREE 10`
		692	`INITRANS 2`
		693	`MAXTRANS 255`
		694	`STORAGE`
		695	`(`
		696	`INITIAL 128K`
		697	`MINEXTENTS 1`
		698	`MAXEXTENTS 2147483645`
		699	`FREELISTS 1`
		700	`FREELIST GROUPS 1`
		701	`BUFFER_POOL DEFAULT`
		702	`)`
		703	`;`
		704
		705	`CREATE INDEX INX_USER_NAME ON LOGIN_TRAIL (USER_NAME ASC) TABLESPACE "USERS"`
		706	`LOGGING`
		707	`PCTFREE 10`
		708	`INITRANS 2`
		709	`MAXTRANS 255`
		710	`STORAGE`
		711	`(`
		712	`INITIAL 128K`
		713	`MINEXTENTS 1`
		714	`MAXEXTENTS 2147483645`
		715	`FREELISTS 1`
		716	`FREELIST GROUPS 1`
		717	`BUFFER_POOL DEFAULT`
		718	`)`
		719	`;`
		720
		721	`CREATE UNIQUE INDEX UNQ_DATA_TABLE ON DATA_TABLES (TABLE_NAME ASC, OBJ_ID ASC, REF_COLUMN_NAME ASC) TABLESPACE "USERS"`
		722	`LOGGING`
		723	`PCTFREE 10`
		724	`INITRANS 2`
		725	`MAXTRANS 255`
		726	`STORAGE`
		727	`(`
		728	`INITIAL 128K`
		729	`MINEXTENTS 1`
		730	`MAXEXTENTS 2147483645`
		731	`FREELISTS 1`
		732	`FREELIST GROUPS 1`
		733	`BUFFER_POOL DEFAULT`
		734	`)`
		735	`;`
		736
		737	`CREATE UNIQUE INDEX UNQ_ROLE_OBJ_PERM ON ROLE_PRIVILEGES (ROLE_ID ASC, OBJ_ID ASC, PERM_ID ASC) TABLESPACE "USERS"`
		738	`LOGGING`
		739	`PCTFREE 10`
		740	`INITRANS 2`
		741	`MAXTRANS 255`
		742	`STORAGE`
		743	`(`
		744	`INITIAL 128K`
		745	`MINEXTENTS 1`
		746	`MAXEXTENTS 2147483645`
		747	`FREELISTS 1`
		748	`FREELIST GROUPS 1`
		749	`BUFFER_POOL DEFAULT`
		750	`)`
		751	`;`
		752
		753	`CREATE UNIQUE INDEX UNQ_DATA_PERM ON DATA_PERMISSIONS (DT_ID ASC, ROLE_ID ASC, REF_COLUMN_VAL ASC, PERM_ID ASC) TABLESPACE "USERS"`
		754	`LOGGING`
		755	`PCTFREE 10`
		756	`INITRANS 2`
		757	`MAXTRANS 255`
		758	`STORAGE`
		759	`(`
		760	`INITIAL 128K`
		761	`MINEXTENTS 1`
		762	`MAXEXTENTS 2147483645`
		763	`FREELISTS 1`
		764	`FREELIST GROUPS 1`
		765	`BUFFER_POOL DEFAULT`
		766	`)`
		767	`;`
		768
		769	`CREATE UNIQUE INDEX UNQ_USER_ROLES ON USER_ROLES (USER_ID ASC, ROLE_ID ASC) TABLESPACE "USERS"`
		770	`LOGGING`
		771	`PCTFREE 10`
		772	`INITRANS 2`
		773	`MAXTRANS 255`
		774	`STORAGE`
		775	`(`
		776	`INITIAL 128K`
		777	`MINEXTENTS 1`
		778	`MAXEXTENTS 2147483645`
		779	`FREELISTS 1`
		780	`FREELIST GROUPS 1`
		781	`BUFFER_POOL DEFAULT`
		782	`)`
		783	`;`
		784
		785	`CREATE UNIQUE INDEX UNQ_ROLES ON ROLES (APP_ID ASC, ROLE_NAME ASC) TABLESPACE "USERS"`
		786	`LOGGING`
		787	`PCTFREE 10`
		788	`INITRANS 2`
		789	`MAXTRANS 255`
		790	`STORAGE`
		791	`(`
		792	`INITIAL 128K`
		793	`MINEXTENTS 1`
		794	`MAXEXTENTS 2147483645`
		795	`FREELISTS 1`
		796	`FREELIST GROUPS 1`
		797	`BUFFER_POOL DEFAULT`
		798	`)`
		799	`;`
		800
		801	`CREATE UNIQUE INDEX UNQ_USER_APPS ON USER_APPLICATIONS (USER_ID ASC, APP_ID ASC) TABLESPACE "USERS"`
		802	`LOGGING`
		803	`PCTFREE 10`
		804	`INITRANS 2`
		805	`MAXTRANS 255`
		806	`STORAGE`
		807	`(`
		808	`INITIAL 128K`
		809	`MINEXTENTS 1`
		810	`MAXEXTENTS 2147483645`
		811	`FREELISTS 1`
		812	`FREELIST GROUPS 1`
		813	`BUFFER_POOL DEFAULT`
		814	`)`
		815	`;`
		816
		817	`CREATE UNIQUE INDEX UNQ_PAGE_COBJ ON PAGE_CONTROL_OBJECTS (PAGE_ID ASC, OBJ_ID ASC) TABLESPACE "USERS"`
		818	`LOGGING`
		819	`PCTFREE 10`
		820	`INITRANS 2`
		821	`MAXTRANS 255`
		822	`STORAGE`
		823	`(`
		824	`INITIAL 128K`
		825	`MINEXTENTS 1`
		826	`MAXEXTENTS 2147483645`
		827	`FREELISTS 1`
		828	`FREELIST GROUPS 1`
		829	`BUFFER_POOL DEFAULT`
		830	`)`
		831	`;`
		832
		833	`CREATE UNIQUE INDEX UNQ_ABTN_DEF ON DEF_ACTION_BUTTONS (ABTN_NAME ASC) TABLESPACE "USERS"`
		834	`LOGGING`
		835	`PCTFREE 10`
		836	`INITRANS 2`
		837	`MAXTRANS 255`
		838	`STORAGE`
		839	`(`
		840	`INITIAL 128K`
		841	`MINEXTENTS 1`
		842	`MAXEXTENTS 2147483645`
		843	`FREELISTS 1`
		844	`FREELIST GROUPS 1`
		845	`BUFFER_POOL DEFAULT`
		846	`)`
		847	`;`
		848
		849	`CREATE UNIQUE INDEX UNQ_APP_PAGES ON APPLICATION_PAGES (APP_ID ASC, PAGE_NAME ASC) TABLESPACE "USERS"`
		850	`LOGGING`
		851	`PCTFREE 10`
		852	`INITRANS 2`
		853	`MAXTRANS 255`
		854	`STORAGE`
		855	`(`
		856	`INITIAL 128K`
		857	`MINEXTENTS 1`
		858	`MAXEXTENTS 2147483645`
		859	`FREELISTS 1`
		860	`FREELIST GROUPS 1`
		861	`BUFFER_POOL DEFAULT`
		862	`)`
		863	`;`
		864
		865	`CREATE UNIQUE INDEX UNQ_VALIDATION_RULES ON VALIDATION_RULES (FIELD_NAME ASC) TABLESPACE "USERS"`
		866	`LOGGING`
		867	`PCTFREE 10`
		868	`INITRANS 2`
		869	`MAXTRANS 255`
		870	`STORAGE`
		871	`(`
		872	`INITIAL 128K`
		873	`MINEXTENTS 1`
		874	`MAXEXTENTS 2147483645`
		875	`FREELISTS 1`
		876	`FREELIST GROUPS 1`
		877	`BUFFER_POOL DEFAULT`
		878	`)`
		879	`;`
		880
		881	`CREATE SEQUENCE SEQ_APP_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;`
		882
		883	`CREATE SEQUENCE SEQ_ROLE_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;`
		884
		885	`CREATE SEQUENCE SEQ_USER_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;`
		886
		887	`CREATE SEQUENCE SEQ_DT_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;`
		888
		889	`CREATE SEQUENCE SEQ_OBJ_ID INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20 ORDER ;`
		890
		891	`CREATE OR REPLACE SYNONYM RM_PROJECTS FOR RELEASE_MANAGER.PROJECTS;`
		892
		893	`CREATE OR REPLACE SYNONYM DM_DEF_MENU_ITEMS FOR DEF_MENU_ITEMS;`
		894
		895	`CREATE OR REPLACE SYNONYM DM_DM_PROJECTS FOR DM_PROJECTS;`
		896
		897	`CREATE OR REPLACE SYNONYM PM_DM_PROJECTS FOR DEPLOYMENT_MANAGER.DM_PROJECTS;`
		898
		899	`CREATE OR REPLACE SYNONYM PROJECTS FOR RELEASE_MANAGER.PROJECTS;`
		900
		901	`CREATE OR REPLACE FUNCTION IN_LIST_NUMBER ( sInList IN VARCHAR2 ) RETURN ACCMGR_NUMBER_TAB_t IS`
		902
		903	`/* ---------------------------------------------------------------------------`
		904	`Version: 1.0.0`
		905	`--------------------------------------------------------------------------- */`
		906
		907	`sync_rtags ACCMGR_NUMBER_TAB_t := ACCMGR_NUMBER_TAB_t();`
		908	`pos NUMBER;`
		909	`in_list VARCHAR2(4000) := sInList \|\| ',';`
		910
		911	`BEGIN`
		912
		913	`IF NOT sInList IS NULL`
		914	`THEN`
		915	`LOOP`
		916	`EXIT WHEN in_list IS NULL;`
		917	`pos := INSTR ( in_list, ',' );`
		918	`sync_rtags.extend;`
		919	`sync_rtags(sync_rtags.count) := LTRIM ( RTRIM ( SUBSTR ( in_list, 1, pos-1 ) ) );`
		920	`in_list := SUBSTR ( in_list, pos+1 );`
		921	`END LOOP;`
		922	`END IF;`
		923
		924	`RETURN sync_rtags;`
		925	`END IN_LIST_NUMBER;`
		926	`/`
		927
		928	`CREATE OR REPLACE PACKAGE PK_SECURITY IS`
		929
		930	`/*`
		931	`------------------------------`
		932	`\|\| Last Modified: S.Vukovic`
		933	`\|\| Modified Date: 28/Apr/2005`
		934	`\|\| Spec Version: 1.0`
		935	`------------------------------`
		936	`*/`
		937
		938	`TYPE typeCur IS REF CURSOR;`
		939
		940	`/================================================================================================/`
		941	`--FUNCTION GET_USER_BY_ID ( ID IN NUMBER, records OUT typeCur ) RETURN NUMBER;`
		942	`--FUNCTION GET_USER_BY_USERNAME ( UserName IN VARCHAR2, records OUT typeCur ) RETURN NUMBER;`
		943	`--FUNCTION GET_USER_PERMISSIONS ( UserSK IN NUMBER, ActionNameFilter IN VARCHAR2, records OUT typeCur ) RETURN NUMBER;`
		944	`--FUNCTION AUTHENTICATE ( UserName IN VARCHAR2,`
		945	`-- UserPassword IN VARCHAR2,`
		946	`-- records OUT typeCur ) RETURN NUMBER;`
		947	`/================================================================================================/`
		948	`PROCEDURE GET_USER_STATIC_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur);`
		949	`PROCEDURE GET_USER_DATA_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur);`
		950	`FUNCTION IS_LOCAL_PASSWORD_CORRECT ( UserName IN VARCHAR2, UserPassword IN VARCHAR2 ) RETURN NUMBER;`
		951	`FUNCTION Set_Password ( sUserName IN VARCHAR2, sPasswordA IN VARCHAR2, sPasswordB IN VARCHAR2 ) RETURN NUMBER;`
		952	`/================================================================================================/`
		953
		954	`END PK_SECURITY;`
		955	`/`
		956
		957	`CREATE OR REPLACE FUNCTION IS_SAME_STRING ( sStringOne IN VARCHAR2,`
		958	`sStringTwo IN VARCHAR2 ) RETURN BOOLEAN IS`
		959
		960	`/* ---------------------------------------------------------------------------`
		961	`Version: 1.0.0`
		962	`--------------------------------------------------------------------------- */`
		963
		964	`ReturnValue BOOLEAN DEFAULT FALSE;`
		965
		966	`BEGIN`
		967
		968	`--- Compare ---`
		969	`IF ( NVL( sStringOne, '' ) \|\| 'APPEND' = NVL( sStringTwo, '' ) \|\| 'APPEND' ) THEN`
		970	`-- Strings are the same`
		971	`ReturnValue := TRUE;`
		972
		973	`END IF;`
		974
		975	`RETURN ReturnValue;`
		976	`END IS_SAME_STRING;`
		977	`/`
		978
		979	`CREATE OR REPLACE PACKAGE pk_Application IS`
		980
		981	`PROCEDURE Add_Application ( sAppName IN APPLICATIONS.APPLICATION_NAME%TYPE,`
		982	`sAppAcronym IN APPLICATIONS.ACRONYM%TYPE );`
		983
		984	`PROCEDURE Remove_Application ( nAppId IN APPLICATIONS.APP_ID%TYPE );`
		985
		986
		987	`END pk_Application;`
		988	`/`
		989
		990	`CREATE OR REPLACE PACKAGE pk_AMUtils IS`
		991
		992	`PROCEDURE Log_Access ( nEvent IN LOGIN_TRAIL.ENUM_EVENT%TYPE,`
		993	`sUserName IN LOGIN_TRAIL.USER_NAME%TYPE,`
		994	`sClientIp IN LOGIN_TRAIL.CLIENT_IP%TYPE,`
		995	`nAppId IN LOGIN_TRAIL.APP_ID%TYPE,`
		996	`sComments IN LOGIN_TRAIL.COMMENTS%TYPE,`
		997	`nUserId IN NUMBER DEFAULT NULL );`
		998
		999	`FUNCTION Get_Hash ( sText IN VARCHAR2 ) RETURN VARCHAR2;`
		1000
		1001	`END pk_AMUtils;`
		1002	`/`
		1003
		1004	`CREATE OR REPLACE TYPE "ACCMGR_NUMBER_TAB_T" as TABLE of NUMBER`
		1005	`/`
		1006
		1007	`CREATE OR REPLACE PACKAGE pk_Role IS`
		1008
		1009
		1010	`PROCEDURE Add_Role ( sRoleName IN ROLES.ROLE_NAME%TYPE,`
		1011	`sRoleComments IN ROLES.COMMENTS%TYPE,`
		1012	`nAppId IN ROLES.APP_ID%TYPE );`
		1013
		1014	`PROCEDURE Remove_Role ( sRoleIdList IN VARCHAR2 );`
		1015
		1016	`PROCEDURE Set_Role_Permissions ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,`
		1017	`nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,`
		1018	`cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,`
		1019	`cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL );`
		1020
		1021	`PROCEDURE Set_Role_Variation_Permissions ( nUserId IN USER_ROLES.USER_ID%TYPE,`
		1022	`nAppId IN ROLES.APP_ID%TYPE,`
		1023	`nRoleId IN ROLES.ROLE_ID%TYPE,`
		1024	`nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,`
		1025	`cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,`
		1026	`cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL );`
		1027
		1028	`FUNCTION Is_Permissions_Changed ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,`
		1029	`nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,`
		1030	`cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE,`
		1031	`cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE ) RETURN BOOLEAN;`
		1032
		1033	`FUNCTION Is_Role_Variation ( nRoleId IN ROLES.ROLE_ID%TYPE ) RETURN BOOLEAN;`
		1034
		1035	`PROCEDURE Delete_Role_Permission ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,`
		1036	`nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,`
		1037	`nPermId IN ROLE_PRIVILEGES.PERM_ID%TYPE );`
		1038
		1039	`PROCEDURE Grant_Role ( sRoleIdList IN VARCHAR2,`
		1040	`nUserId IN USER_ROLES.USER_ID%TYPE );`
		1041
		1042	`PROCEDURE Revoke_Role ( sRoleIdList IN VARCHAR2,`
		1043	`nUserId IN USER_ROLES.USER_ID%TYPE );`
		1044
		1045
		1046	`END pk_Role;`
		1047	`/`
		1048
		1049	`CREATE OR REPLACE PACKAGE pk_user IS`
		1050	`/*`
		1051	`------------------------------`
		1052	`\|\| Last Modified: J.Tweddle`
		1053	`\|\| Modified Date: 21/Jan/2008`
		1054	`\|\| Spec Version: 2.1`
		1055	`------------------------------`
		1056	`*/`
		1057
		1058	`PROCEDURE Add_User_Account ( sFullName IN USERS.FULL_NAME%TYPE,`
		1059	`sUserName IN USERS.USER_NAME%TYPE,`
		1060	`sUserEmail IN USERS.USER_EMAIL%TYPE,`
		1061	`sDomain IN USERS.DOMAIN%TYPE );`
		1062
		1063	`PROCEDURE Update_User_Account ( sUserId IN VARCHAR2, sFullName IN USERS.FULL_NAME%TYPE,`
		1064	`sUserName IN USERS.USER_NAME%TYPE,`
		1065	`sUserEmail IN USERS.USER_EMAIL%TYPE,`
		1066	`sDomain IN USERS.DOMAIN%TYPE );`
		1067
		1068	`PROCEDURE Add_Application_User ( sUserIdList IN VARCHAR2,`
		1069	`nAppId IN USER_APPLICATIONS.APP_ID%TYPE,`
		1070	`cIncludeEveryone IN CHAR );`
		1071
		1072	`PROCEDURE Remove_Application_User ( sUserIdList IN VARCHAR2,`
		1073	`nAppId IN USER_APPLICATIONS.APP_ID%TYPE,`
		1074	`cIncludeEveryone IN CHAR DEFAULT NULL );`
		1075
		1076	`PROCEDURE Add_Role_Member ( sUserIdList IN VARCHAR2,`
		1077	`nRoleId IN USER_ROLES.ROLE_ID%TYPE,`
		1078	`cIncludeEveryone IN CHAR );`
		1079
		1080	`PROCEDURE Remove_Role_Member ( sUserIdList IN VARCHAR2,`
		1081	`nRoleId IN USER_ROLES.ROLE_ID%TYPE,`
		1082	`cIncludeEveryone IN CHAR DEFAULT NULL);`
		1083
		1084	`PROCEDURE Disable_Users ( sUserIdList IN VARCHAR2,`
		1085	`nAppId IN USER_APPLICATIONS.APP_ID%TYPE );`
		1086
		1087	`PROCEDURE Remove_User_Account ( sUserIdList IN VARCHAR2 );`
		1088
		1089	`END pk_user;`
		1090	`/`
		1091
		1092	`CREATE OR REPLACE PACKAGE pk_Control IS`
		1093
		1094
		1095	`PROCEDURE Add_Control ( sObjName IN CONTROL_OBJECTS.OBJ_NAME%TYPE,`
		1096	`sObjDescription IN CONTROL_OBJECTS.OBJ_DESCRIPTION%TYPE,`
		1097	`nAppId IN CONTROL_OBJECTS.APP_ID%TYPE`
		1098	`);`
		1099
		1100	`PROCEDURE Remove_Control ( sObjIdList IN VARCHAR2 );`
		1101
		1102
		1103	`PROCEDURE Set_Row_Permissions ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,`
		1104	`nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,`
		1105	`nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE,`
		1106	`cIsVisible IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL,`
		1107	`cIsActive IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL );`
		1108
		1109
		1110	`PROCEDURE Delete_Data_Permission ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,`
		1111	`nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,`
		1112	`nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE );`
		1113
		1114	`PROCEDURE Add_DataTable ( nObjId IN DATA_TABLES.OBJ_ID%TYPE,`
		1115	`sTableName IN DATA_TABLES.TABLE_NAME%TYPE,`
		1116	`sRefColumn IN DATA_TABLES.REF_COLUMN_NAME%TYPE,`
		1117	`sDisplayColumn IN DATA_TABLES.DISPLAY_COLUMN_NAME%TYPE`
		1118	`);`
		1119
		1120	`PROCEDURE Remove_DataTable ( DtId IN NUMBER );`
		1121
		1122
		1123
		1124	`END pk_Control;`
		1125	`/`
		1126
		1127	`CREATE OR REPLACE PACKAGE BODY PK_SECURITY IS`
		1128
		1129	`/*`
		1130	`------------------------------`
		1131	`\|\| Last Modified: S.Vukovic`
		1132	`\|\| Modified Date: 28/Apr/2005`
		1133	`\|\| Body Version: 1.0`
		1134	`------------------------------`
		1135	`*/`
		1136
		1137	`/-------------------------------------------------------------------------------------------------------/`
		1138	`FUNCTION IS_LOCAL_PASSWORD_CORRECT ( UserName IN VARCHAR2, UserPassword IN VARCHAR2 ) RETURN NUMBER IS`
		1139	`encryptedUserPassword VARCHAR2(4000);`
		1140	`BEGIN`
		1141	`SELECT usr.USER_PASSWORD INTO encryptedUserPassword`
		1142	`FROM USERS usr`
		1143	`WHERE usr.USER_NAME = UserName;`
		1144
		1145	`IF encryptedUserPassword = PK_AMUTILS.GET_HASH( UserPassword ) THEN`
		1146	`-- Password Correct`
		1147	`RETURN 1;`
		1148	`ELSE`
		1149	`-- Password Incorrect`
		1150	`RETURN -1;`
		1151	`END IF;`
		1152	`END;`
		1153	`/--------------------------------------------------------------------------------------------------/`
		1154	`FUNCTION Set_Password ( sUserName IN VARCHAR2, sPasswordA IN VARCHAR2, sPasswordB IN VARCHAR2 ) RETURN NUMBER IS`
		1155
		1156
		1157	`BEGIN`
		1158	`-- Used to set password for the first time.`
		1159	`-- It can only be set if previous password in null`
		1160
		1161	`/--------------- Business Rules Here -------------------/`
		1162	`/-------------------------------------------------------/`
		1163
		1164	`IF sPasswordA = sPasswordB THEN`
		1165	`-- Update passwords`
		1166	`UPDATE USERS usr SET`
		1167	`usr.USER_PASSWORD = PK_AMUTILS.Get_Hash ( sPasswordA )`
		1168	`WHERE usr.USER_NAME = sUserName`
		1169	`AND usr.USER_PASSWORD IS NULL;`
		1170
		1171	`-- Successfull update`
		1172	`RETURN 1;`
		1173
		1174	`ELSE`
		1175	`-- Password mistmatch`
		1176	`RETURN -1;`
		1177	`END IF;`
		1178
		1179
		1180	`END;`
		1181	`/-------------------------------------------------------------------------------------------------------/`
		1182	`PROCEDURE GET_USER_STATIC_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur) IS`
		1183
		1184	`BEGIN`
		1185
		1186	`OPEN RecordSet FOR`
		1187	`SELECT co.obj_name, perm.perm_id, perm.perm_value`
		1188	`FROM (`
		1189
		1190	`(`
		1191	`/* All 'Y' User Permissions */`
		1192
		1193	`/* Get all 'Y' permissions */`
		1194	`SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, rp.PERM_VALUE`
		1195	`FROM USER_ROLES ur,`
		1196	`ROLE_PRIVILEGES rp,`
		1197	`ROLES ro`
		1198	`WHERE ur.ROLE_ID = ro.ROLE_ID`
		1199	`AND rp.ROLE_ID = ro.ROLE_ID`
		1200	`AND ur.USER_ID = UsedId`
		1201	`AND rp.PERM_VALUE = 'Y'`
		1202	`--AND ro.APP_ID = AppId`
		1203	`MINUS`
		1204	`/* Revoke permissions if they set to 'N'*/`
		1205	`SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, 'Y' AS PERM_VALUE`
		1206	`FROM USER_ROLES ur,`
		1207	`ROLE_PRIVILEGES rp,`
		1208	`ROLES ro`
		1209	`WHERE ur.ROLE_ID = ro.ROLE_ID`
		1210	`AND rp.ROLE_ID = ro.ROLE_ID`
		1211	`AND ur.USER_ID = UsedId`
		1212	`AND rp.PERM_VALUE = 'N'`
		1213	`-- AND ro.APP_ID = AppId`
		1214
		1215	`)`
		1216
		1217	`MINUS`
		1218
		1219	`/* Role Variant Revokes */`
		1220	`SELECT DISTINCT rp.OBJ_ID, rp.perm_id, 'Y' AS perm_value`
		1221	`FROM role_privileges rp,`
		1222	`ROLES ro,`
		1223	`user_roles ur`
		1224	`WHERE ro.role_id = rp.role_id`
		1225	`AND ro.role_id = ur.role_id`
		1226	`AND rp.PERM_VALUE = 'N'`
		1227	`AND ro.IS_ROLE_VARIATION = 'Y'`
		1228	`AND ur.user_id = UsedId`
		1229	`-- AND ro.APP_ID = AppId`
		1230
		1231	`) perm,`
		1232	`control_objects co`
		1233	`WHERE perm.obj_id = co.obj_id;`
		1234	`-- AND co.APP_ID = AppId;`
		1235
		1236
		1237	`END;`
		1238	`/-------------------------------------------------------------------------------------------------------/`
		1239	`PROCEDURE GET_USER_DATA_PERMISSIONS ( UsedId IN NUMBER, AppId IN NUMBER, RecordSet OUT typeCur) IS`
		1240
		1241	`BEGIN`
		1242
		1243	`OPEN RecordSet FOR`
		1244	`SELECT qry.TABLE_NAME,`
		1245	`qry.REF_COLUMN_VAL,`
		1246	`qry.PERM_ID,`
		1247	`qry.PERM_VALUE`
		1248	`FROM CONTROL_OBJECTS co,`
		1249	`DATA_TABLES dt,`
		1250	`(`
		1251
		1252	`/* Access Control Data Permissions */`
		1253	`SELECT bl.DT_ID,`
		1254	`bl.TABLE_NAME,`
		1255	`bl.REF_COLUMN_VAL,`
		1256	`bl.PERM_ID,`
		1257	`DECODE ( yc.PERM_VALUE,`
		1258	`NULL, bl.PERM_VALUE,`
		1259	`yc.PERM_VALUE ) AS PERM_VALUE`
		1260	`FROM (`
		1261	`/* Get base list for Data Permissions */`
		1262	`SELECT rol.ROLE_ID, dt.DT_ID, dt.TABLE_NAME, 0 AS REF_COLUMN_VAL, pt.PERM_ID, 'Y' AS PERM_VALUE`
		1263	`FROM CONTROL_OBJECTS co,`
		1264	`DATA_TABLES dt,`
		1265	`PERMISSION_TYPES pt,`
		1266	`(`
		1267	`/* Get User Roles for this Application */`
		1268	`SELECT ro.*`
		1269	`FROM ROLES ro,`
		1270	`USER_ROLES ur`
		1271	`WHERE ur.ROLE_ID = ro.ROLE_ID`
		1272	`--AND ro.APP_ID = AppId`
		1273	`AND ur.USER_ID = UsedId`
		1274	`) rol`
		1275	`WHERE dt.OBJ_ID = co.OBJ_ID`
		1276	`--AND co.APP_ID = AppId`
		1277	`) bl,`
		1278	`(`
		1279	`/* Find only 'Y' Permissions, which will change 'All' permission to 'N' */`
		1280	`SELECT dt.DT_ID, 0 AS REF_COLUMN_VAL, dp.PERM_ID, 'N' AS PERM_VALUE, COUNT(*) AS YES_COUNT`
		1281	`FROM DATA_PERMISSIONS dp,`
		1282	`DATA_TABLES dt,`
		1283	`ROLES ro,`
		1284	`USER_ROLES ur`
		1285	`WHERE ur.ROLE_ID = ro.ROLE_ID`
		1286	`AND dp.ROLE_ID = ro.ROLE_ID`
		1287	`AND dp.DT_ID = dt.DT_ID`
		1288	`-- AND ro.APP_ID = AppId`
		1289	`AND ur.USER_ID = UsedId`
		1290	`AND ro.IS_ROLE_VARIATION IS NULL`
		1291	`GROUP BY dt.DT_ID, dp.PERM_ID`
		1292	`) yc`
		1293	`WHERE yc.DT_ID (+) = bl.DT_ID`
		1294	`AND yc.PERM_ID (+) = bl.PERM_ID`
		1295	`UNION`
		1296	`(`
		1297	`/* Get Filter Settings for All Permission Types */`
		1298	`SELECT dt.DT_ID, dt.TABLE_NAME, dp.REF_COLUMN_VAL, dp.PERM_ID, dp.PERM_VALUE`
		1299	`FROM DATA_PERMISSIONS dp,`
		1300	`DATA_TABLES dt,`
		1301	`ROLES ro,`
		1302	`USER_ROLES ur`
		1303	`WHERE ur.ROLE_ID = ro.ROLE_ID`
		1304	`AND dp.ROLE_ID = ro.ROLE_ID`
		1305	`AND dp.DT_ID = dt.DT_ID`
		1306	`-- AND ro.APP_ID = AppId`
		1307	`AND ur.USER_ID = UsedId`
		1308	`MINUS`
		1309	`/* Overwrite Data Permissions using User Specific Role */`
		1310	`SELECT DISTINCT`
		1311	`dt.DT_ID,`
		1312	`dt.TABLE_NAME,`
		1313	`dp.REF_COLUMN_VAL,`
		1314	`dp.PERM_ID,`
		1315	`DECODE ( dp.PERM_VALUE,`
		1316	`'Y', 'N', 'Y' ) AS PERM_VALUE`
		1317	`FROM DATA_PERMISSIONS dp,`
		1318	`DATA_TABLES dt,`
		1319	`ROLES ro,`
		1320	`USER_ROLES ur`
		1321	`WHERE ur.ROLE_ID = ro.ROLE_ID`
		1322	`AND dp.ROLE_ID = ro.ROLE_ID`
		1323	`AND dp.DT_ID = dt.DT_ID`
		1324	`-- AND ro.APP_ID = AppId`
		1325	`AND ur.USER_ID = UsedId`
		1326	`AND ro.IS_ROLE_VARIATION = 'Y'`
		1327	`)`
		1328
		1329	`) qry`
		1330	`WHERE dt.OBJ_ID = co.OBJ_ID`
		1331	`AND dt.DT_ID = qry.DT_ID;`
		1332	`--AND co.APP_ID = AppId;`
		1333
		1334
		1335	`END;`
		1336	`/-------------------------------------------------------------------------------------------------------/`
		1337	`END PK_SECURITY;`
		1338	`/`
		1339
		1340	`CREATE OR REPLACE PACKAGE BODY pk_Application`
		1341	`IS`
		1342	`/* ---------------------------------------------------------------------------`
		1343	`Version: 1.0.0`
		1344	`--------------------------------------------------------------------------- */`
		1345
		1346	`/--------------------------------------------------------------------------------------------------/`
		1347	`PROCEDURE Add_Application ( sAppName IN APPLICATIONS.APPLICATION_NAME%TYPE,`
		1348	`sAppAcronym IN APPLICATIONS.ACRONYM%TYPE ) IS`
		1349
		1350	`AppId NUMBER;`
		1351
		1352	`CURSOR curAppAcronym IS`
		1353	`SELECT app.ACRONYM`
		1354	`FROM APPLICATIONS app`
		1355	`WHERE app.ACRONYM = sAppAcronym;`
		1356	`recAppAcronym curAppAcronym%ROWTYPE;`
		1357
		1358
		1359	`BEGIN`
		1360	`/--------------- Business Rules Here -------------------/`
		1361
		1362	`-- Check for duplicate acronyms`
		1363	`OPEN curAppAcronym;`
		1364	`FETCH curAppAcronym INTO recAppAcronym;`
		1365
		1366	`IF curAppAcronym%FOUND`
		1367	`THEN`
		1368	`RAISE_APPLICATION_ERROR (-20000, 'Application Acronym <b>'\|\| sAppAcronym \|\|'</b> is already used.' );`
		1369
		1370	`END IF;`
		1371
		1372	`CLOSE curAppAcronym;`
		1373
		1374	`/-------------------------------------------------------/`
		1375
		1376	`/+++++ INSERT APPLICATION ++++++++/`
		1377	`BEGIN`
		1378	`-- Get app_id`
		1379	`SELECT SEQ_APP_ID.NEXTVAL INTO AppId FROM DUAL;`
		1380
		1381	`-- Insert new Application`
		1382	`INSERT INTO APPLICATIONS ( APP_ID, APPLICATION_NAME, ACRONYM )`
		1383	`VALUES ( AppId, sAppName, sAppAcronym );`
		1384
		1385
		1386	`EXCEPTION`
		1387	`WHEN DUP_VAL_ON_INDEX`
		1388	`THEN`
		1389	`RAISE_APPLICATION_ERROR (-20000, 'Application Name <b>'\|\| sAppName \|\|'</b> is already used.');`
		1390	`END;`
		1391	`/+++++ END INSERT APPLICATION +++++/`
		1392
		1393
		1394	`END Add_Application;`
		1395	`/--------------------------------------------------------------------------------------------------/`
		1396	`PROCEDURE Remove_Application ( nAppId IN APPLICATIONS.APP_ID%TYPE ) IS`
		1397
		1398	`rowCount NUMBER DEFAULT 0;`
		1399
		1400	`BEGIN`
		1401	`/--------------- Business Rules Here -------------------/`
		1402
		1403	`-- Check if any Users are using this Application`
		1404	`SELECT Count(*) INTO rowCount`
		1405	`FROM USER_APPLICATIONS ua`
		1406	`WHERE ua.APP_ID = nAppId;`
		1407
		1408	`IF rowCount > 0 THEN`
		1409	`RAISE_APPLICATION_ERROR (-20000, 'Some Users are still assigned to this Application ( Counted '\|\| rowCount \|\|' ).' );`
		1410	`END IF;`
		1411
		1412	`-- Check if any Roles are using this Application`
		1413	`SELECT Count(*) INTO rowCount`
		1414	`FROM ROLES ro`
		1415	`WHERE ro.APP_ID = nAppId;`
		1416
		1417	`IF rowCount > 0 THEN`
		1418	`RAISE_APPLICATION_ERROR (-20000, 'Some Roles are still assigned to this Application ( Counted '\|\| rowCount \|\|' ).' );`
		1419	`END IF;`
		1420
		1421	`/-------------------------------------------------------/`
		1422
		1423
		1424	`-- Remove Application --`
		1425	`DELETE`
		1426	`FROM APPLICATIONS`
		1427	`WHERE APP_ID = nAppId;`
		1428
		1429
		1430	`END Remove_Application;`
		1431	`/--------------------------------------------------------------------------------------------------/`
		1432
		1433
		1434	`END pk_Application;`
		1435	`/`
		1436
		1437	`CREATE OR REPLACE PACKAGE BODY pk_AMUtils`
		1438	`IS`
		1439	`/* ---------------------------------------------------------------------------`
		1440	`Version: 1.0.0`
		1441	`--------------------------------------------------------------------------- */`
		1442
		1443	`/--------------------------------------------------------------------------------------------------/`
		1444	`PROCEDURE Log_Access ( nEvent IN LOGIN_TRAIL.ENUM_EVENT%TYPE,`
		1445	`sUserName IN LOGIN_TRAIL.USER_NAME%TYPE,`
		1446	`sClientIp IN LOGIN_TRAIL.CLIENT_IP%TYPE,`
		1447	`nAppId IN LOGIN_TRAIL.APP_ID%TYPE,`
		1448	`sComments IN LOGIN_TRAIL.COMMENTS%TYPE,`
		1449	`nUserId IN NUMBER DEFAULT NULL ) IS`
		1450
		1451	`UserName USERS.USER_NAME%TYPE;`
		1452
		1453	`BEGIN`
		1454	`/--------------- Business Rules Here -------------------/`
		1455	`IF nUserId IS NOT NULL THEN`
		1456	`SELECT USER_NAME INTO UserName FROM USERS WHERE USER_ID = nUserId;`
		1457	`ELSE`
		1458	`UserName := sUserName;`
		1459	`END IF;`
		1460	`/-------------------------------------------------------/`
		1461
		1462
		1463	`-- Insert Login Trail`
		1464	`INSERT INTO LOGIN_TRAIL ( ENUM_EVENT, USER_NAME, CLIENT_IP, APP_ID, STAMP, COMMENTS )`
		1465	`VALUES ( nEvent,`
		1466	`UserName,`
		1467	`sClientIp,`
		1468	`nAppId,`
		1469	`TO_DATE( TO_CHAR( SYSDATE,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' ),`
		1470	`sComments`
		1471	`);`
		1472
		1473	`END Log_Access;`
		1474	`/--------------------------------------------------------------------------------------------------/`
		1475	`FUNCTION Get_Hash ( sText IN VARCHAR2 ) RETURN VARCHAR2 IS`
		1476
		1477
		1478	`BEGIN`
		1479	`/--------------- Business Rules Here -------------------/`
		1480	`/-------------------------------------------------------/`
		1481
		1482	`RETURN DBMS_OBFUSCATION_TOOLKIT.MD5( input_string => sText );`
		1483
		1484	`END Get_Hash;`
		1485	`/--------------------------------------------------------------------------------------------------/`
		1486
		1487
		1488	`END pk_AMUtils;`
		1489	`/`
		1490
		1491	`CREATE OR REPLACE PACKAGE BODY pk_Role`
		1492	`IS`
		1493	`/* ---------------------------------------------------------------------------`
		1494	`Version: 1.0.0`
		1495	`--------------------------------------------------------------------------- */`
		1496
		1497	`/--------------------------------------------------------------------------------------------------/`
		1498	`PROCEDURE Add_Role ( sRoleName IN ROLES.ROLE_NAME%TYPE,`
		1499	`sRoleComments IN ROLES.COMMENTS%TYPE,`
		1500	`nAppId IN ROLES.APP_ID%TYPE ) IS`
		1501
		1502	`RoleID NUMBER;`
		1503
		1504
		1505	`BEGIN`
		1506	`/--------------- Business Rules Here -------------------/`
		1507	`/-------------------------------------------------------/`
		1508
		1509
		1510	`-- Get role_id`
		1511	`SELECT SEQ_ROLE_ID.NEXTVAL INTO RoleID FROM DUAL;`
		1512
		1513	`-- Insert new Role`
		1514	`INSERT INTO ROLES ( ROLE_ID, APP_ID, ROLE_NAME, IS_ROLE_VARIATION, COMMENTS )`
		1515	`VALUES ( RoleID, nAppId, sRoleName, NULL, sRoleComments );`
		1516
		1517
		1518	`EXCEPTION`
		1519	`WHEN DUP_VAL_ON_INDEX`
		1520	`THEN`
		1521	`RAISE_APPLICATION_ERROR (-20000, 'Role Name '\|\| sRoleName \|\|' is already used in this Application.');`
		1522
		1523	`END Add_Role;`
		1524	`/--------------------------------------------------------------------------------------------------/`
		1525	`PROCEDURE Remove_Role ( sRoleIdList IN VARCHAR2 ) IS`
		1526
		1527	`rowCount NUMBER DEFAULT 0;`
		1528
		1529	`BEGIN`
		1530	`/--------------- Business Rules Here -------------------/`
		1531
		1532	`-- Check if any Users user this role`
		1533	`SELECT Count(*) INTO rowCount`
		1534	`FROM USER_ROLES ur`
		1535	`WHERE ur.ROLE_ID IN (`
		1536	`SELECT *`
		1537	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		1538	`);`
		1539
		1540	`IF rowCount > 0 THEN`
		1541	`RAISE_APPLICATION_ERROR (-20000, 'Some Users are still using this Role ( Counted '\|\| rowCount \|\|' ).' );`
		1542	`END IF;`
		1543
		1544	`/-------------------------------------------------------/`
		1545
		1546	`-- Remove Role Privileges --`
		1547	`DELETE`
		1548	`FROM ROLE_PRIVILEGES rp`
		1549	`WHERE rp.ROLE_ID IN (`
		1550	`SELECT *`
		1551	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		1552	`);`
		1553
		1554	`-- Remove Role Data Permissions --`
		1555	`DELETE`
		1556	`FROM DATA_PERMISSIONS dp`
		1557	`WHERE dp.ROLE_ID IN (`
		1558	`SELECT *`
		1559	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		1560	`);`
		1561
		1562	`-- Remove Role --`
		1563	`DELETE`
		1564	`FROM ROLES ro`
		1565	`WHERE ro.ROLE_ID IN (`
		1566	`SELECT *`
		1567	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		1568	`);`
		1569
		1570
		1571	`END Remove_Role;`
		1572	`/--------------------------------------------------------------------------------------------------/`
		1573	`PROCEDURE Set_Role_Permissions ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,`
		1574	`nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,`
		1575	`cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,`
		1576	`cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL ) IS`
		1577
		1578	`nPermId PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;`
		1579
		1580	`BEGIN`
		1581	`/--------------- Business Rules Here -------------------/`
		1582	`IF (nRoleId IS NULL) OR (nObjId IS NULL)`
		1583	`THEN`
		1584	`RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRoleId= '\|\| nRoleId \|\|', nObjId='\|\| nObjId );`
		1585
		1586	`END IF;`
		1587	`/-------------------------------------------------------/`
		1588
		1589
		1590	`--- Set "Visible" state ----------------------------------`
		1591
		1592	`-- Get PermId for "Visible"`
		1593	`SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumVISIBLE';`
		1594
		1595	`Delete_Role_Permission ( nRoleId, nObjId, nPermId );`
		1596
		1597	`IF cIsVisible IS NOT NULL THEN`
		1598
		1599	`INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )`
		1600	`VALUES ( nRoleId, nObjId, nPermId, cIsVisible );`
		1601
		1602	`ELSE`
		1603	`IF cIsActive IS NOT NULL THEN`
		1604	`-- If "Active" is Set then "Visible" must be "SHOW=Y"`
		1605
		1606	`INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )`
		1607	`VALUES ( nRoleId, nObjId, nPermId, 'Y' );`
		1608
		1609	`END IF;`
		1610
		1611	`END IF;`
		1612
		1613
		1614
		1615	`--- Set "Active" state ----------------------------------`
		1616
		1617	`-- Get PermId for "Visible"`
		1618	`SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumACTIVE';`
		1619
		1620	`Delete_Role_Permission ( nRoleId, nObjId, nPermId );`
		1621
		1622	`IF ( cIsActive IS NOT NULL ) AND ( cIsVisible <> 'N') THEN`
		1623
		1624	`INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )`
		1625	`VALUES ( nRoleId, nObjId, nPermId, cIsActive );`
		1626
		1627	`END IF;`
		1628
		1629
		1630	`END Set_Role_Permissions;`
		1631	`/--------------------------------------------------------------------------------------------------/`
		1632	`PROCEDURE Set_Role_Variation_Permissions ( nUserId IN USER_ROLES.USER_ID%TYPE,`
		1633	`nAppId IN ROLES.APP_ID%TYPE,`
		1634	`nRoleId IN ROLES.ROLE_ID%TYPE,`
		1635	`nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,`
		1636	`cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL,`
		1637	`cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE DEFAULT NULL ) IS`
		1638
		1639	`nPermId PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;`
		1640	`nRoleVariationId ROLES.ROLE_ID%TYPE DEFAULT NULL;`
		1641	`sUserName USERS.USER_NAME%TYPE;`
		1642	`nDataPermCount NUMBER;`
		1643	`nRolePermCount NUMBER;`
		1644
		1645	`--- Get Role Variation Id ---`
		1646	`CURSOR curRoleVariation IS`
		1647	`SELECT ro.ROLE_ID`
		1648	`FROM USER_ROLES ur,`
		1649	`ROLES ro`
		1650	`WHERE ur.ROLE_ID = ro.ROLE_ID`
		1651	`AND ro.IS_ROLE_VARIATION = 'Y'`
		1652	`AND ur.USER_ID = nUserId;`
		1653	`recRoleVariation curRoleVariation%ROWTYPE;`
		1654
		1655
		1656	`BEGIN`
		1657	`/--------------- Business Rules Here -------------------/`
		1658	`IF (nUserId IS NULL) OR (nAppId IS NULL) OR (nObjId IS NULL)`
		1659	`THEN`
		1660	`RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nUserId='\|\| nUserId \|\|', nAppId='\|\| nAppId \|\|', nObjId='\|\| nObjId);`
		1661
		1662	`END IF;`
		1663	`/-------------------------------------------------------/`
		1664
		1665	`IF Is_Permissions_Changed( nRoleId, nObjId, cIsVisible, cIsActive ) THEN`
		1666
		1667	`IF Is_Role_Variation ( nRoleId ) THEN`
		1668	`--- Set this user role permissions ---`
		1669	`Set_Role_Permissions ( nRoleId, nObjId, cIsVisible, cIsActive );`
		1670
		1671
		1672
		1673	`--- Remove this user role for no permission settings ---`
		1674	`-- Get Role Permissions Count`
		1675	`SELECT Count(*) INTO nRolePermCount FROM ROLE_PRIVILEGES rp WHERE rp.ROLE_ID = nRoleVariationId;`
		1676
		1677	`-- Get Role Permissions Count`
		1678	`SELECT Count(*) INTO nDataPermCount FROM DATA_PERMISSIONS dp WHERE dp.ROLE_ID = nRoleVariationId;`
		1679
		1680
		1681	`IF (nRolePermCount = 0) AND (nDataPermCount = 0) THEN`
		1682	`-- There are no permission settings, hence proceed to remove this user role`
		1683	`DELETE`
		1684	`FROM ROLES ro`
		1685	`WHERE ro.ROLE_ID = nRoleVariationId;`
		1686
		1687	`END IF;`
		1688
		1689
		1690	`ELSE`
		1691	`--- Get Role Variation Id ---`
		1692	`OPEN curRoleVariation;`
		1693	`FETCH curRoleVariation INTO recRoleVariation;`
		1694
		1695	`IF curRoleVariation%FOUND THEN`
		1696	`nRoleVariationId := recRoleVariation.ROLE_ID;`
		1697	`END IF;`
		1698
		1699	`CLOSE curRoleVariation;`
		1700
		1701
		1702
		1703	`--- Create Role Variation if does not exist ---`
		1704	`IF nRoleVariationId IS NULL THEN`
		1705
		1706	`-- Get role_id`
		1707	`SELECT SEQ_ROLE_ID.NEXTVAL INTO nRoleVariationId FROM DUAL;`
		1708
		1709	`-- Get user_name`
		1710	`SELECT usr.USER_NAME INTO sUserName FROM USERS usr WHERE usr.USER_ID = nUserId;`
		1711
		1712	`-- Create Role Variation`
		1713	`INSERT INTO ROLES ( ROLE_ID, APP_ID, ROLE_NAME, IS_ROLE_VARIATION, COMMENTS )`
		1714	`VALUES ( nRoleVariationId, nAppId, UPPER( sUserName ) \|\| '_SPECIFIC', 'Y', 'Auto-created role to define user specific permissions.');`
		1715
		1716	`-- Link this role to user`
		1717	`INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )`
		1718	`VALUES ( nUserId, nRoleVariationId );`
		1719
		1720
		1721	`END IF;`
		1722
		1723
		1724	`--- Set this user role permissions ---`
		1725	`Set_Role_Permissions ( nRoleVariationId, nObjId, cIsVisible, cIsActive );`
		1726
		1727	`END IF;`
		1728
		1729	`END IF;`
		1730
		1731
		1732	`END Set_Role_Variation_Permissions;`
		1733	`/--------------------------------------------------------------------------------------------------/`
		1734	`FUNCTION Is_Permissions_Changed ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,`
		1735	`nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,`
		1736	`cIsVisible IN ROLE_PRIVILEGES.PERM_VALUE%TYPE,`
		1737	`cIsActive IN ROLE_PRIVILEGES.PERM_VALUE%TYPE ) RETURN BOOLEAN IS`
		1738
		1739	`ReturnValue BOOLEAN DEFAULT FALSE;`
		1740	`cCurrentIsVisible ROLE_PRIVILEGES.PERM_VALUE%TYPE;`
		1741	`cCurrentIsActive ROLE_PRIVILEGES.PERM_VALUE%TYPE;`
		1742
		1743	`CURSOR curCurrentIsVisible IS`
		1744	`SELECT rp.PERM_VALUE`
		1745	`FROM ROLE_PRIVILEGES rp`
		1746	`WHERE rp.ROLE_ID = nRoleId`
		1747	`AND rp.OBJ_ID = nObjId`
		1748	`AND rp.PERM_ID = 1;`
		1749	`recCurrentIsVisible curCurrentIsVisible%ROWTYPE;`
		1750
		1751	`CURSOR curCurrentIsActive IS`
		1752	`SELECT rp.PERM_VALUE`
		1753	`FROM ROLE_PRIVILEGES rp`
		1754	`WHERE rp.ROLE_ID = nRoleId`
		1755	`AND rp.OBJ_ID = nObjId`
		1756	`AND rp.PERM_ID = 2;`
		1757	`recCurrentIsActive curCurrentIsActive%ROWTYPE;`
		1758
		1759	`BEGIN`
		1760	`/--------------- Business Rules Here -------------------/`
		1761	`/-------------------------------------------------------/`
		1762
		1763
		1764	`-- Get "Visible" Permission`
		1765	`OPEN curCurrentIsVisible;`
		1766	`FETCH curCurrentIsVisible INTO recCurrentIsVisible;`
		1767
		1768	`IF curCurrentIsVisible%FOUND THEN`
		1769	`cCurrentIsVisible := recCurrentIsVisible.PERM_VALUE;`
		1770	`END IF;`
		1771
		1772	`CLOSE curCurrentIsVisible;`
		1773
		1774
		1775
		1776	`-- Get "Active" Permission`
		1777	`OPEN curCurrentIsActive;`
		1778	`FETCH curCurrentIsActive INTO recCurrentIsActive;`
		1779
		1780	`IF curCurrentIsActive%FOUND THEN`
		1781	`cCurrentIsActive := recCurrentIsActive.PERM_VALUE;`
		1782	`END IF;`
		1783
		1784	`CLOSE curCurrentIsActive;`
		1785
		1786
		1787
		1788	`--- Compare ---`
		1789	`IF NOT Is_Same_String ( cCurrentIsVisible, cIsVisible ) OR NOT Is_Same_String ( cCurrentIsActive, cIsActive ) THEN`
		1790	`ReturnValue := TRUE;`
		1791
		1792	`END IF;`
		1793
		1794
		1795
		1796	`RETURN ReturnValue;`
		1797	`END Is_Permissions_Changed;`
		1798	`/--------------------------------------------------------------------------------------------------/`
		1799	`FUNCTION Is_Role_Variation ( nRoleId IN ROLES.ROLE_ID%TYPE ) RETURN BOOLEAN IS`
		1800
		1801	`ReturnValue BOOLEAN DEFAULT FALSE;`
		1802	`cIsRoleVariation CHAR;`
		1803
		1804	`BEGIN`
		1805	`/--------------- Business Rules Here -------------------/`
		1806	`/-------------------------------------------------------/`
		1807
		1808	`-- Get is_role_variation`
		1809	`SELECT ro.IS_ROLE_VARIATION INTO cIsRoleVariation`
		1810	`FROM ROLES ro`
		1811	`WHERE ro.ROLE_ID = nRoleId;`
		1812
		1813	`IF cIsRoleVariation IS NOT NULL THEN`
		1814	`ReturnValue := TRUE;`
		1815	`END IF;`
		1816
		1817	`RETURN ReturnValue;`
		1818	`END Is_Role_Variation;`
		1819	`/--------------------------------------------------------------------------------------------------/`
		1820	`PROCEDURE Delete_Role_Permission ( nRoleId IN ROLE_PRIVILEGES.ROLE_ID%TYPE,`
		1821	`nObjId IN ROLE_PRIVILEGES.OBJ_ID%TYPE,`
		1822	`nPermId IN ROLE_PRIVILEGES.PERM_ID%TYPE ) IS`
		1823
		1824
		1825	`BEGIN`
		1826	`/--------------- Business Rules Here -------------------/`
		1827	`IF (nRoleId IS NULL) OR (nObjId IS NULL) OR (nPermId IS NULL)`
		1828	`THEN`
		1829	`RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRoleId= '\|\| nRoleId \|\|', nObjId='\|\| nObjId \|\|', nPermId='\|\| nPermId );`
		1830
		1831	`END IF;`
		1832	`/-------------------------------------------------------/`
		1833
		1834	`DELETE`
		1835	`FROM ROLE_PRIVILEGES rp`
		1836	`WHERE rp.ROLE_ID = nRoleId`
		1837	`AND rp.OBJ_ID = nObjId`
		1838	`AND rp.PERM_ID = nPermId;`
		1839
		1840
		1841	`END Delete_Role_Permission;`
		1842	`/--------------------------------------------------------------------------------------------------/`
		1843	`PROCEDURE Grant_Role ( sRoleIdList IN VARCHAR2,`
		1844	`nUserId IN USER_ROLES.USER_ID%TYPE ) IS`
		1845
		1846
		1847	`BEGIN`
		1848	`/--------------- Business Rules Here -------------------/`
		1849	`IF sRoleIdList IS NULL THEN`
		1850	`RAISE_APPLICATION_ERROR (-20000, 'Please select at least one role.' );`
		1851	`END IF;`
		1852	`/-------------------------------------------------------/`
		1853
		1854	`--- Grant Role(s) ---`
		1855	`INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )`
		1856	`SELECT nUserId, qry.ROLE_ID`
		1857	`FROM (`
		1858	`SELECT ro.ROLE_ID`
		1859	`FROM ROLES ro`
		1860	`WHERE ro.ROLE_ID IN (`
		1861	`SELECT *`
		1862	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		1863	`)`
		1864	`MINUS`
		1865	`SELECT ur.ROLE_ID`
		1866	`FROM USER_ROLES ur`
		1867	`WHERE ur.USER_ID = nUserId`
		1868	`) qry;`
		1869
		1870
		1871	`END Grant_Role;`
		1872	`/--------------------------------------------------------------------------------------------------/`
		1873	`PROCEDURE Revoke_Role ( sRoleIdList IN VARCHAR2,`
		1874	`nUserId IN USER_ROLES.USER_ID%TYPE ) IS`
		1875
		1876
		1877	`BEGIN`
		1878	`/--------------- Business Rules Here -------------------/`
		1879	`IF sRoleIdList IS NULL THEN`
		1880	`RAISE_APPLICATION_ERROR (-20000, 'Please select at least one role.' );`
		1881	`END IF;`
		1882	`/-------------------------------------------------------/`
		1883
		1884	`--- Revoke Role(s) ---`
		1885	`DELETE`
		1886	`FROM USER_ROLES ur`
		1887	`WHERE ur.USER_ID = nUserId`
		1888	`AND ur.ROLE_ID IN (`
		1889	`SELECT *`
		1890	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sRoleIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		1891	`);`
		1892
		1893
		1894	`END Revoke_Role;`
		1895	`/--------------------------------------------------------------------------------------------------/`
		1896
		1897
		1898	`END pk_Role;`
		1899	`/`
		1900
		1901	`CREATE OR REPLACE PACKAGE BODY pk_user IS`
		1902	`/*`
		1903	`------------------------------`
		1904	`\|\| Last Modified: J.Tweddle`
		1905	`\|\| Modified Date: 21/Jan/2008`
		1906	`\|\| Body Version: 2.1`
		1907	`------------------------------`
		1908	`*/`
		1909
		1910	`/--------------------------------------------------------------------------------------------------/`
		1911	`PROCEDURE Add_User_Account ( sFullName IN USERS.FULL_NAME%TYPE,`
		1912	`sUserName IN USERS.USER_NAME%TYPE,`
		1913	`sUserEmail IN USERS.USER_EMAIL%TYPE,`
		1914	`sDomain IN USERS.DOMAIN%TYPE ) IS`
		1915
		1916	`UserId NUMBER;`
		1917
		1918
		1919	`BEGIN`
		1920	`/--------------- Business Rules Here -------------------/`
		1921	`/-------------------------------------------------------/`
		1922
		1923
		1924	`-- Get user_id`
		1925	`SELECT SEQ_USER_ID.NEXTVAL INTO UserId FROM DUAL;`
		1926
		1927	`-- Insert new User Account`
		1928	`INSERT INTO USERS (USER_ID, FULL_NAME, USER_NAME, USER_EMAIL, DOMAIN )`
		1929	`VALUES( UserId, sFullName, sUserName, sUserEmail, sDomain );`
		1930
		1931
		1932	`EXCEPTION`
		1933	`WHEN DUP_VAL_ON_INDEX`
		1934	`THEN`
		1935	`RAISE_APPLICATION_ERROR (-20000, 'User Name '\|\| sUserName \|\|' already exists.');`
		1936
		1937	`END Add_User_Account;`
		1938
		1939	`/--------------------------------------------------------------------------------------------------/`
		1940	`PROCEDURE Update_User_Account ( sUserId IN VARCHAR2, sFullName IN USERS.FULL_NAME%TYPE,`
		1941	`sUserName IN USERS.USER_NAME%TYPE,`
		1942	`sUserEmail IN USERS.USER_EMAIL%TYPE,`
		1943	`sDomain IN USERS.DOMAIN%TYPE ) IS`
		1944
		1945	`BEGIN`
		1946	`/--------------- Business Rules Here -------------------/`
		1947	`IF (sUserId IS NULL) THEN`
		1948	`RAISE_APPLICATION_ERROR (-20000, 'Please select a User Account.' );`
		1949	`END IF;`
		1950	`/-------------------------------------------------------/`
		1951
		1952	`-- Update User Account`
		1953	`UPDATE USERS usr SET`
		1954	`usr.FULL_NAME = sFullName, usr.USER_NAME = sUserName, usr.USER_EMAIL = sUserEmail, usr.DOMAIN = sDomain`
		1955	`WHERE usr.USER_ID = sUserId;`
		1956
		1957	`END Update_User_Account;`
		1958
		1959	`/--------------------------------------------------------------------------------------------------/`
		1960	`PROCEDURE Add_Application_User ( sUserIdList IN VARCHAR2,`
		1961	`nAppId IN USER_APPLICATIONS.APP_ID%TYPE,`
		1962	`cIncludeEveryone IN CHAR ) IS`
		1963
		1964
		1965	`BEGIN`
		1966	`/--------------- Business Rules Here -------------------/`
		1967	`IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THEN`
		1968	`RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );`
		1969	`END IF;`
		1970	`/-------------------------------------------------------/`
		1971
		1972	`-- Insert Application User --`
		1973
		1974	`IF cIncludeEveryone = 'Y' THEN`
		1975	`-- Insert All Users`
		1976	`INSERT INTO USER_APPLICATIONS ( USER_ID, APP_ID )`
		1977	`SELECT qry.USER_ID, nAppId`
		1978	`FROM (`
		1979	`SELECT usr.USER_ID`
		1980	`FROM USERS usr`
		1981	`MINUS`
		1982	`SELECT ua.USER_ID`
		1983	`FROM USER_APPLICATIONS ua`
		1984	`WHERE ua.APP_ID = nAppId`
		1985	`) qry;`
		1986
		1987	`ELSE`
		1988	`-- Insert specific user list`
		1989	`INSERT INTO USER_APPLICATIONS ( USER_ID, APP_ID )`
		1990	`SELECT qry.USER_ID, nAppId`
		1991	`FROM (`
		1992	`SELECT usr.USER_ID`
		1993	`FROM USERS usr`
		1994	`WHERE usr.USER_ID IN (`
		1995	`SELECT *`
		1996	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		1997	`)`
		1998	`MINUS`
		1999	`SELECT ua.USER_ID`
		2000	`FROM USER_APPLICATIONS ua`
		2001	`WHERE ua.APP_ID = nAppId`
		2002	`AND ua.USER_ID IN (`
		2003	`SELECT *`
		2004	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2005	`)`
		2006	`) qry;`
		2007
		2008	`END IF;`
		2009
		2010
		2011	`--- Make sure Build in User is not included ---`
		2012	`Remove_Application_User ( '0', nAppId );`
		2013
		2014
		2015	`END Add_Application_User;`
		2016
		2017	`/--------------------------------------------------------------------------------------------------/`
		2018	`PROCEDURE Remove_Application_User ( sUserIdList IN VARCHAR2,`
		2019	`nAppId IN USER_APPLICATIONS.APP_ID%TYPE,`
		2020	`cIncludeEveryone IN CHAR DEFAULT NULL) IS`
		2021
		2022
		2023	`BEGIN`
		2024	`/--------------- Business Rules Here -------------------/`
		2025	`IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THEN`
		2026	`RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );`
		2027	`END IF;`
		2028	`/-------------------------------------------------------/`
		2029
		2030	`IF cIncludeEveryone = 'Y' THEN`
		2031	`-- Remove All Users --`
		2032	`DELETE`
		2033	`FROM user_applications ua`
		2034	`WHERE ua.app_id = nAppId;`
		2035
		2036	`ELSE`
		2037	`-- Remove Application Users --`
		2038	`DELETE`
		2039	`FROM user_applications ua`
		2040	`WHERE ua.app_id = nAppId`
		2041	`AND ua.user_id IN (`
		2042	`SELECT *`
		2043	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2044	`);`
		2045	`-- Remove User(s) Application Roles --`
		2046	`DELETE`
		2047	`FROM user_roles ur`
		2048	`WHERE ur.user_id IN (`
		2049	`SELECT *`
		2050	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2051	`)`
		2052	`AND ur.role_id IN (`
		2053	`SELECT ro.role_id`
		2054	`FROM roles ro`
		2055	`WHERE ro.app_id = nAppId`
		2056	`);`
		2057	`END IF;`
		2058
		2059
		2060	`END Remove_Application_User;`
		2061
		2062	`/--------------------------------------------------------------------------------------------------/`
		2063	`PROCEDURE Add_Role_Member ( sUserIdList IN VARCHAR2,`
		2064	`nRoleId IN USER_ROLES.ROLE_ID%TYPE,`
		2065	`cIncludeEveryone IN CHAR ) IS`
		2066
		2067
		2068	`BEGIN`
		2069	`/--------------- Business Rules Here -------------------/`
		2070	`IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THEN`
		2071	`RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );`
		2072	`END IF;`
		2073	`/-------------------------------------------------------/`
		2074
		2075	`-- Insert Role Member --`
		2076
		2077	`IF cIncludeEveryone = 'Y' THEN`
		2078	`-- Insert All Users`
		2079	`INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )`
		2080	`SELECT qry.USER_ID, nRoleId`
		2081	`FROM (`
		2082	`SELECT usr.USER_ID`
		2083	`FROM USERS usr`
		2084	`MINUS`
		2085	`SELECT ur.USER_ID`
		2086	`FROM USER_ROLES ur`
		2087	`WHERE ur.ROLE_ID = nRoleId`
		2088	`) qry;`
		2089
		2090	`ELSE`
		2091	`-- Insert specific user list`
		2092	`INSERT INTO USER_ROLES ( USER_ID, ROLE_ID )`
		2093	`SELECT qry.USER_ID, nRoleId`
		2094	`FROM (`
		2095	`SELECT usr.USER_ID`
		2096	`FROM USERS usr`
		2097	`WHERE usr.USER_ID IN (`
		2098	`SELECT *`
		2099	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2100	`)`
		2101	`MINUS`
		2102	`SELECT ur.USER_ID`
		2103	`FROM USER_ROLES ur`
		2104	`WHERE ur.ROLE_ID = nRoleId`
		2105	`AND ur.USER_ID IN (`
		2106	`SELECT *`
		2107	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2108	`)`
		2109	`) qry;`
		2110
		2111	`END IF;`
		2112
		2113
		2114	`--- Make sure Build in User is not included ---`
		2115	`Remove_Role_Member ( '0', nRoleId );`
		2116
		2117
		2118	`END Add_Role_Member;`
		2119
		2120	`/--------------------------------------------------------------------------------------------------/`
		2121	`PROCEDURE Remove_Role_Member ( sUserIdList IN VARCHAR2,`
		2122	`nRoleId IN USER_ROLES.ROLE_ID%TYPE,`
		2123	`cIncludeEveryone IN CHAR DEFAULT NULL) IS`
		2124
		2125
		2126	`BEGIN`
		2127	`/--------------- Business Rules Here -------------------/`
		2128	`IF (nRoleId IS NULL) THEN`
		2129	`RAISE_APPLICATION_ERROR (-20000, 'RoleId is missing.' );`
		2130	`END IF;`
		2131
		2132	`IF (sUserIdList IS NULL) AND (cIncludeEveryone IS NULL) THEN`
		2133	`RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );`
		2134	`END IF;`
		2135	`/-------------------------------------------------------/`
		2136
		2137	`IF cIncludeEveryone = 'Y' THEN`
		2138	`-- Remove All Users --`
		2139	`DELETE`
		2140	`FROM USER_ROLES ur`
		2141	`WHERE ur.ROLE_ID = nRoleId;`
		2142
		2143	`ELSE`
		2144	`-- Remove Application Users --`
		2145	`DELETE`
		2146	`FROM USER_ROLES ur`
		2147	`WHERE ur.ROLE_ID = nRoleId`
		2148	`AND ur.USER_ID IN (`
		2149	`SELECT *`
		2150	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2151	`);`
		2152	`END IF;`
		2153
		2154
		2155	`END Remove_Role_Member;`
		2156
		2157	`/--------------------------------------------------------------------------------------------------/`
		2158	`PROCEDURE Disable_Users ( sUserIdList IN VARCHAR2,`
		2159	`nAppId IN USER_APPLICATIONS.APP_ID%TYPE ) IS`
		2160
		2161
		2162	`BEGIN`
		2163	`/--------------- Business Rules Here -------------------/`
		2164	`IF (sUserIdList IS NULL) THEN`
		2165	`RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User.' );`
		2166	`END IF;`
		2167	`/-------------------------------------------------------/`
		2168
		2169
		2170	`-- Disable User Accounts`
		2171	`UPDATE USERS usr SET`
		2172	`usr.IS_DISABLED = 'Y'`
		2173	`WHERE usr.USER_ID IN (`
		2174	`SELECT *`
		2175	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2176	`);`
		2177
		2178	`END Disable_Users;`
		2179
		2180	`/--------------------------------------------------------------------------------------------------/`
		2181	`PROCEDURE Remove_User_Account ( sUserIdList IN VARCHAR2 ) IS`
		2182
		2183	`rowCount NUMBER DEFAULT 0;`
		2184
		2185	`BEGIN`
		2186	`/--------------- Business Rules Here -------------------/`
		2187
		2188	`IF (sUserIdList IS NULL) THEN`
		2189	`RAISE_APPLICATION_ERROR (-20000, 'Please select at least one User Account.' );`
		2190	`END IF;`
		2191
		2192
		2193	`-- Check if any Users have any roles`
		2194	`SELECT Count(*) INTO rowCount`
		2195	`FROM USER_ROLES ur`
		2196	`WHERE ur.USER_ID IN (`
		2197	`SELECT *`
		2198	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2199	`);`
		2200
		2201	`IF rowCount > 0 THEN`
		2202	`RAISE_APPLICATION_ERROR (-20000, 'Some Users still have Roles assigned. ( Counted '\|\| rowCount \|\|' ).' );`
		2203	`END IF;`
		2204
		2205	`/-------------------------------------------------------/`
		2206
		2207	`-- Remove User Applications --`
		2208	`DELETE`
		2209	`FROM USER_APPLICATIONS ua`
		2210	`WHERE ua.USER_ID IN (`
		2211	`SELECT *`
		2212	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2213	`);`
		2214
		2215	`-- Remove User --`
		2216	`DELETE`
		2217	`FROM USERS us`
		2218	`WHERE us.USER_ID IN (`
		2219	`SELECT *`
		2220	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2221	`);`
		2222
		2223
		2224	`END Remove_User_Account;`
		2225	`/--------------------------------------------------------------------------------------------------/`
		2226	`END pk_user;`
		2227	`/`
		2228
		2229	`CREATE OR REPLACE PACKAGE BODY pk_Control`
		2230	`IS`
		2231	`/* ---------------------------------------------------------------------------`
		2232	`Version: 1.0.0`
		2233	`--------------------------------------------------------------------------- */`
		2234
		2235	`/--------------------------------------------------------------------------------------------------/`
		2236	`PROCEDURE Add_Control ( sObjName IN CONTROL_OBJECTS.OBJ_NAME%TYPE,`
		2237	`sObjDescription IN CONTROL_OBJECTS.OBJ_DESCRIPTION%TYPE,`
		2238	`nAppId IN CONTROL_OBJECTS.APP_ID%TYPE`
		2239	`) IS`
		2240
		2241	`ObjID NUMBER;`
		2242
		2243	`CURSOR curPermissionTypes IS`
		2244	`SELECT pt.PERM_ID`
		2245	`FROM PERMISSION_TYPES pt;`
		2246	`recPermissionTypes curPermissionTypes%ROWTYPE;`
		2247
		2248
		2249	`BEGIN`
		2250	`/--------------- Business Rules Here -------------------/`
		2251	`/-------------------------------------------------------/`
		2252
		2253	`-- Get obj_id`
		2254	`SELECT SEQ_OBJ_ID.NEXTVAL INTO ObjID FROM DUAL;`
		2255
		2256	`-- Insert new Control Object`
		2257	`INSERT INTO CONTROL_OBJECTS ( OBJ_ID, APP_ID, OBJ_NAME, PARENT_OBJ_ID, OBJ_DESCRIPTION )`
		2258	`VALUES ( ObjID, nAppId, sObjName, NULL, sObjDescription );`
		2259
		2260
		2261
		2262
		2263	`/* Set default permissions to all roles */`
		2264
		2265	`/*`
		2266	`OPEN curPermissionTypes;`
		2267	`FETCH curPermissionTypes INTO recPermissionTypes;`
		2268
		2269	`WHILE curPermissionTypes%FOUND`
		2270	`LOOP`
		2271	`INSERT INTO ROLE_PRIVILEGES ( ROLE_ID, OBJ_ID, PERM_ID, PERM_VALUE )`
		2272	`SELECT ROLE_ID,`
		2273	`ObjID AS OBJ_ID,`
		2274	`recPermissionTypes.PERM_ID AS PERM_ID,`
		2275	`'Y' AS PERM_VALUE`
		2276	`FROM ROLES`
		2277	`WHERE IS_ROLE_VARIATION != 'Y';`
		2278
		2279
		2280	`FETCH curPermissionTypes INTO recPermissionTypes;`
		2281	`END LOOP;`
		2282	`CLOSE curPermissionTypes;`
		2283	`*/`
		2284
		2285	`EXCEPTION`
		2286	`WHEN DUP_VAL_ON_INDEX`
		2287	`THEN`
		2288	`RAISE_APPLICATION_ERROR (-20000, 'Control Name '\|\| sObjName \|\|' is already used in this Application.');`
		2289
		2290
		2291	`END Add_Control;`
		2292	`/--------------------------------------------------------------------------------------------------/`
		2293	`PROCEDURE Remove_Control ( sObjIdList IN VARCHAR2 ) IS`
		2294
		2295	`rowCount NUMBER DEFAULT 0;`
		2296
		2297	`BEGIN`
		2298	`/--------------- Business Rules Here -------------------/`
		2299
		2300	`-- Check if any Pages use this control`
		2301	`SELECT Count(*) INTO rowCount`
		2302	`FROM PAGE_CONTROL_OBJECTS pco`
		2303	`WHERE pco.OBJ_ID IN (`
		2304	`SELECT *`
		2305	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2306	`);`
		2307
		2308	`IF rowCount > 0 THEN`
		2309	`RAISE_APPLICATION_ERROR (-20000, 'Some Pages are still using this Control ( Counted '\|\| rowCount \|\|' ).' );`
		2310	`END IF;`
		2311
		2312
		2313	`-- Check if any Roles use this control`
		2314	`SELECT Count(*) INTO rowCount`
		2315	`FROM ROLE_PRIVILEGES rp`
		2316	`WHERE rp.OBJ_ID IN (`
		2317	`SELECT *`
		2318	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2319	`);`
		2320
		2321	`IF rowCount > 0 THEN`
		2322	`RAISE_APPLICATION_ERROR (-20000, 'Some Roles are still using this Control ( Counted '\|\| rowCount \|\|' ).' );`
		2323	`END IF;`
		2324
		2325
		2326	`-- Check if any Data tables use this control`
		2327	`SELECT Count(*) INTO rowCount`
		2328	`FROM DATA_TABLES dt,`
		2329	`DATA_PERMISSIONS dp`
		2330	`WHERE dt.OBJ_ID IN (`
		2331	`SELECT *`
		2332	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2333	`)`
		2334	`AND dt.DT_ID = dp.DT_ID;`
		2335
		2336	`IF rowCount > 0 THEN`
		2337	`RAISE_APPLICATION_ERROR (-20000, 'Some Data Filters are still in use by Roles ( Counted '\|\| rowCount \|\|' ).' );`
		2338	`END IF;`
		2339
		2340	`/-------------------------------------------------------/`
		2341
		2342
		2343	`-- Remove Data Filter --`
		2344	`DELETE`
		2345	`FROM DATA_TABLES dt`
		2346	`WHERE dt.OBJ_ID IN (`
		2347	`SELECT *`
		2348	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2349	`);`
		2350
		2351	`-- Remove Control --`
		2352	`DELETE`
		2353	`FROM CONTROL_OBJECTS co`
		2354	`WHERE co.OBJ_ID IN (`
		2355	`SELECT *`
		2356	`FROM THE ( SELECT CAST( IN_LIST_NUMBER( sObjIdList ) AS ACCMGR_NUMBER_TAB_t ) FROM dual )`
		2357	`);`
		2358
		2359
		2360	`END Remove_Control;`
		2361	`/--------------------------------------------------------------------------------------------------/`
		2362	`PROCEDURE Add_DataTable ( nObjId IN DATA_TABLES.OBJ_ID%TYPE,`
		2363	`sTableName IN DATA_TABLES.TABLE_NAME%TYPE,`
		2364	`sRefColumn IN DATA_TABLES.REF_COLUMN_NAME%TYPE,`
		2365	`sDisplayColumn IN DATA_TABLES.DISPLAY_COLUMN_NAME%TYPE`
		2366	`) IS`
		2367
		2368	`DtID NUMBER;`
		2369
		2370
		2371	`BEGIN`
		2372	`/--------------- Business Rules Here -------------------/`
		2373	`/-------------------------------------------------------/`
		2374
		2375	`-- Get dt_id`
		2376	`SELECT SEQ_DT_ID.NEXTVAL INTO DtID FROM DUAL;`
		2377
		2378	`-- Insert new Control Object`
		2379	`INSERT INTO DATA_TABLES ( DT_ID, OBJ_ID, TABLE_NAME, REF_COLUMN_NAME, DISPLAY_COLUMN_NAME )`
		2380	`VALUES ( DtID, nObjId, sTableName, sRefColumn, sDisplayColumn );`
		2381
		2382
		2383	`EXCEPTION`
		2384	`WHEN DUP_VAL_ON_INDEX`
		2385	`THEN`
		2386	`RAISE_APPLICATION_ERROR (-20000, 'This Data Table Reference is already used in this Action object.');`
		2387
		2388
		2389	`END Add_DataTable;`
		2390	`/--------------------------------------------------------------------------------------------------/`
		2391	`PROCEDURE Remove_DataTable ( DtId IN NUMBER ) IS`
		2392
		2393
		2394	`BEGIN`
		2395	`/--------------- Business Rules Here -------------------/`
		2396
		2397	`/-------------------------------------------------------/`
		2398
		2399
		2400	`-- Remove Data Permissions --`
		2401	`DELETE`
		2402	`FROM DATA_PERMISSIONS dp`
		2403	`WHERE dp.DT_ID = DtId;`
		2404
		2405	`-- Remove Data Table --`
		2406	`DELETE`
		2407	`FROM DATA_TABLES dt`
		2408	`WHERE dt.DT_ID = DtId;`
		2409
		2410
		2411	`END Remove_DataTable;`
		2412	`/--------------------------------------------------------------------------------------------------/`
		2413	`PROCEDURE Set_Row_Permissions ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,`
		2414	`nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,`
		2415	`nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE,`
		2416	`cIsVisible IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL,`
		2417	`cIsActive IN DATA_PERMISSIONS.PERM_VALUE%TYPE DEFAULT NULL ) IS`
		2418
		2419	`nPermId PERMISSION_TYPES.PERM_ID%TYPE DEFAULT 0;`
		2420
		2421	`BEGIN`
		2422	`/--------------- Business Rules Here -------------------/`
		2423	`/-------------------------------------------------------/`
		2424
		2425	`-- Delete Existing Permission --`
		2426	`Delete_Data_Permission ( nDtId, nRoleId, nRefCol );`
		2427
		2428
		2429	`----- Set Data Permission -----`
		2430
		2431	`-- Get PermId for "Visible"`
		2432	`SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumVISIBLE';`
		2433
		2434
		2435
		2436	`IF (cIsVisible IS NOT NULL) THEN`
		2437
		2438	`INSERT INTO DATA_PERMISSIONS ( DT_ID, ROLE_ID, REF_COLUMN_VAL, PERM_ID, PERM_VALUE )`
		2439	`VALUES ( nDtId, nRoleId, nRefCol, nPermId, cIsVisible );`
		2440
		2441	`END IF;`
		2442
		2443
		2444
		2445	`-- Get PermId for "Active"`
		2446	`SELECT pt.PERM_ID INTO nPermId FROM PERMISSION_TYPES pt WHERE pt.PERM_NAME = 'enumACTIVE';`
		2447
		2448
		2449
		2450	`IF (cIsActive IS NOT NULL) THEN`
		2451
		2452	`INSERT INTO DATA_PERMISSIONS ( DT_ID, ROLE_ID, REF_COLUMN_VAL, PERM_ID, PERM_VALUE )`
		2453	`VALUES ( nDtId, nRoleId, nRefCol, nPermId, cIsActive );`
		2454
		2455	`END IF;`
		2456
		2457
		2458
		2459	`END Set_Row_Permissions;`
		2460	`/--------------------------------------------------------------------------------------------------/`
		2461	`PROCEDURE Delete_Data_Permission ( nDtId IN DATA_PERMISSIONS.DT_ID%TYPE,`
		2462	`nRoleId IN DATA_PERMISSIONS.ROLE_ID%TYPE,`
		2463	`nRefCol IN DATA_PERMISSIONS.REF_COLUMN_VAL%TYPE ) IS`
		2464
		2465
		2466	`BEGIN`
		2467	`/--------------- Business Rules Here -------------------/`
		2468	`IF (nDtId IS NULL) OR (nRoleId IS NULL) OR (nRefCol IS NULL)`
		2469	`THEN`
		2470	`RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nDtId= '\|\| nDtId \|\|', nRoleId='\|\| nRoleId \|\|', nRefCol='\|\| nRefCol );`
		2471
		2472	`END IF;`
		2473	`/-------------------------------------------------------/`
		2474
		2475	`DELETE`
		2476	`FROM DATA_PERMISSIONS dp`
		2477	`WHERE dp.ROLE_ID = nRoleId`
		2478	`AND dp.DT_ID = nDtId`
		2479	`AND dp.REF_COLUMN_VAL = nRefCol;`
		2480
		2481
		2482	`END Delete_Data_Permission;`
		2483	`/--------------------------------------------------------------------------------------------------/`
		2484
		2485
		2486	`END pk_Control;`
		2487	`/`
		2488

Subversion Repositories DevTools

(root)/ManagerSuite/Manager_Suite_VM/trunk/src/Access Manager.sql – Rev 53