· 6 years ago · Oct 19, 2019, 02:28 PM
1create or replace function "dbSys".create_userPriv(
2 in userid int
3 ,in accid int
4 ,in tblvw99 text -- Optional 'vw, see JustVerify :=position('vw' in lower(tblvw99)) = 0;
5 ,in product text = 'Cloudora Justice-Standard'
6
7 ,in prodmenu text = null
8 ,in tbl_par text = null -- parent this view is used with
9 ----------
10 ,out pvusers text
11 ,out pvmenu text
12 ,out pvusersmenu text
13 ,out pverr text
14 ,out pvreg boolean
15 )
16returns record as $$
17declare
18 cur_post refcursor; vw record;
19 par record; postdata record; tb record; rec record;
20 user_data record;
21 pcname text; tgnames text;fnname text; sql text;
22 ok boolean;
23 acc record;
24 pv record;
25 -- err text = null;
26 tg record;
27 err60 text; err61 text; err62 text; err92 text;
28 JustVerify boolean; -- JustVerify :=position('vw' in lower(tblvw99)) = 0;
29 users record;
30 sysroles text = 'null';
31 err text;
32 conf record;
33begin
34
35 select userid id into users;
36 select null::int DataLimit into conf;
37
38
39 --* UID can not be null
40 if users.id is null then
41
42 -- raise notice '2518';
43 select msg from "dbSys".sysCallErrMsg(
44 64
45 ,null -- talbename = description
46 ,null -- new.id
47 ,null -- NEW.uID
48 ,errmsg := 'UID is null'
49 )into err;
50 raise exception '%', err;
51 else
52 select id,username, sysrole from "dbUserAcc".tbuser where id = users.id into users;
53 end if;
54
55 --* this a/c is reigistered
56 select id , reg from "dbUserAcc".tbacc where id = accid into acc;
57 pvreg := acc.reg;
58
59
60 -- raise ' 2116, tblvw99 %,userid %, users.id %, accid %, acc.reg %',tblvw99, userid, users.id,accid, acc.reg;
61
62 -- tbl_par := 'trpreqvw'; -- debug
63 --* Check privilege , if only registratered
64 if acc.reg then -- user 1 = registration
65 if tblvw99 is not null then -- menu to access view
66 --* Just verify privilege?
67 JustVerify :=position('vw' in lower(tblvw99)) = 0;
68 if JustVerify then
69 tblvw99:=tblvw99||'vw'; -- make it having 'vw' to verify ,if not exists
70 end if;
71 /*
72
73 --* Get real parent , child may be used by mulitple parent , eg.trpreqitemvw is used by trpreqvw, trpdrnapvw, trpcrnapvw
74 select
75 e.nam
76 --from "dbSys".getTableSchName( '"dbFinance".trpreqitem'||'vw' ) a
77 from "dbSys".getTableSchName( tbl||'vw' ) a
78 right join "dbSys".tbviewregistration b on b.sch = a.sch and b.nam =lower(a.nam)
79 left join "dbSys".tbtblregis c on c.id = b.tblregis_id and c.child -- for child only
80 left join "dbSys".tbtblregis d on d.id = c.tblregisid_top
81 left join "dbSys".tbviewregistration e on e.tblregis_id = d.id
82 where b.sch = a.sch and b.nam = lower(a.nam) and e.tblregis_default
83 into _tbl_par; */
84
85 --* Get table info
86 execute $x$
87 select
88 aa.sch
89 ,lower(aa.nam) nam
90 ,cutstr(lower(aa.nam),'vw') nam_noac
91 ,b.sch sch_pers -- w/o quoat
92 ,b.nam nam_pers
93 ,format('"%s".%s',b.sch,b.nam ) tbl_pers
94 ,a.id viewregid
95 ,b1.id viewregid_top
96 -- from "dbSys".getTableSchName( tblvw99 ) aa -- '"dbFinance".trpreqitemvw'
97 from "dbSys".getTableSchName( $2 ) aa -- '"dbFinance".trpreqitemvw'
98 left join "dbSys".tbviewregistration a on (a.sch=aa.sch and a.nam = cutstr(lower(aa.nam),'vw') )
99 left join "dbSys".tbtblregis b on ( b.id = a.tblregis_id )
100 left join "dbSys".tbviewregistration b1 on b1.tblregis_id = b.tblregisid_top
101 where case
102 --when tbl_par is null then true
103 --else b1.nam = tbl_par -- 'trpreqvw'
104 when $1 is null then true
105 else b1.nam = $1 -- 'trpreqvw'
106
107 end
108 $x$ using tbl_par, tblvw99
109 into tb;
110 -- raise '1507, tblvw99 %, tb.nam_noac %, tb.viewregid %, tb.viewregid_top %, tbl_par %',tblvw99, tb.nam_noac, tb.viewregid, tb.viewregid_top, tbl_par;
111
112 --* How many produce this user register to
113
114 select count(*) c_product, null::int prodid
115 from
116 "dbUserAcc".tbuser a
117 left join "dbUserAcc".tbuserprod b on (b.pid = a.id )
118 where a.id = userid
119 into user_data;
120 if (user_data.c_product = 1) or (product is null ) then
121 select b.prodid
122 from
123 "dbUserAcc".tbuser a
124 left join "dbUserAcc".tbuserprod b on (b.pid = a.id )
125 where a.id = userid
126 order by b.id limit 1 -- user first , if no product name specified
127 into user_data.prodid;
128 else
129 select b.prodid
130 from
131 "dbUserAcc".tbuser a
132 left join "dbUserAcc".tbuserprod b on (b.pid = a.id )
133 left join "dbUserAcc".tbprod b1 on (b1.id = b.prodid )
134 where a.id = userid and b1.title = product
135 into user_data.prodid;
136 end if;
137 if (user_data.prodid is null) and ( acc.reg) then
138 -- raise exception '%', format('No product specifify, view "%s", user "%s", product "%s"',tblvw99, userid, product );
139 end if;
140
141 --* Get privileges
142 if tb.viewregid is not null then
143
144 sql:= format(
145 'select
146 a.id
147 ,a.username ,a.sysrole
148 ,c.title menuname
149 --* User privilege
150 ,a.privilege users
151 ,c.privilege menu
152 ,d.privilege usersmenu
153 --* Use Group privilege -------------------
154 ,aa.privilege users_grp
155 --,cc.privilege menu_grp
156 --,dd.privilege usersmenu_grp
157 --*
158 --,b1.id prodid
159 ,c.id prodtaskid
160 --,d.id userprdmenuid
161
162 ,c.edition prodEdition
163 -- ,b1.ProdEditionID usersProdEdition
164 ,string_agg( b1.ProdEditionID, %L) usersProdEdition -- 0
165 from
166 --* User ------------
167 "dbUserAcc".tbuser a
168 left join "dbUserAcc".tbuserprod b on (b.pid = a.id )
169 left join "dbUserAcc".tbprod b1 on (b1.id = b.prodid )
170 -- left join "dbUserAcc".tbProd_Edition( b1.id, b1.title )
171 left join "dbUserAcc".tbprodtask c on (c.pid = b1.idgrp and c.viewregistrationid = %s ) --1
172
173 left join "dbUserAcc".tbuserprdmenu d on (d.pid = b.id and d.prodtaskid= c.id )
174 --* User Group --------
175 -- left join "dbUserAcc".tbuser aa on (aa.id = a.idgrp )
176 left join "dbUserAcc".tbuser_group_pv(a.idgrp) aa on aa.id = a.idgrp
177
178 --left join "dbUserAcc".tbuserprod bb on (bb.pid = aa.id )
179 --left join "dbUserAcc".tbprodtask cc on (cc.pid = bb.prodid and cc.viewregistrationid = c.viewregistrationid )
180 --left join "dbUserAcc".tbuserprdmenu dd on (dd.pid = bb.id and dd.prodtaskid= cc.id )
181 where
182 a.id = %s -- 2
183 group by
184 a.id, a.username, a.privilege, c.privilege, d.privilege, c.id , c.edition, aa.privilege
185 '
186 , ',' -- 0
187 , case when tb.viewregid_top is null then tb.viewregid else tb.viewregid_top end -- 1
188
189 , userid -- 2
190 );
191 --* Which product
192 if user_data.prodid is not null then
193 sql := "dbSys".sqladdwhere(sql,format('b1.id = %s',user_data.prodid ), orderby_groupby :=true) ;
194 end if;
195-- raise '15888,tb.viewregid %, tb.viewregid_top %, sql=%', tb.viewregid ,tb.viewregid_top ,sql;
196
197 execute sql into pv;
198
199 else
200 select
201 --null::text users , null::text menu , null::text usersmenu, null::text sysrole, null::text username ,null::text menuname, null::text edition
202 null::int id
203 ,null::text username
204 ,null::text sysrole
205 ,null::text menuname
206 --* User privilege
207 ,null::text users
208 ,null::text menu
209 ,null::text usersmenu
210 --* User Group privileg
211 ,null::text users_grp
212 --*
213 --,null::int prodid
214 ,null::int prodtaskid
215 --,null::int userprdmenuid
216
217 ,null::text prodEdition
218 ,null::text usersProdEdition
219
220 into pv;
221
222 end if;
223 -- raise notice '2633, pv.users %', pv.users;
224 else -- menu w/o view
225 select null::int viewregid into tb;
226 if prodmenu is null then
227 raise exception '%', format('Menu title is required');
228 end if;
229 sql:= format(
230 'select
231 a.id
232 ,a.username, a.sysrole
233 ,c.title menuname
234 --* User privilege
235 ,a.privilege users
236 ,c.privilege menu
237 ,d.privilege usersmenu
238 --* Use Group privilege
239 ,aa.privilege users_grp
240 --,cc.privilege menu_grp
241 --,dd.privilege usersmenu_grp
242 --*
243 --,b1.id prodid
244 ,c.id prodtaskid
245 --,d.id userprdmenuid
246
247 ,c.edition prodEdition
248 -- ,b1.ProdEditionID usersProdEdition
249 ,string_agg( b1.ProdEditionID, %L) usersProdEdition -- 0
250 from
251 --* User ------------
252 "dbUserAcc".tbuser a
253 left join "dbUserAcc".tbuserprod b on (b.pid = a.id )
254 left join "dbUserAcc".tbprod b1 on (b1.id = b.prodid )
255
256 left join "dbUserAcc".tbprodtask c on (c.pid = b1.idgrp and c.title = %L ) -- 1
257 left join "dbUserAcc".tbuserprdmenu d on (d.pid = b.id and d.prodtaskid= c.id )
258 --* User Group --------
259 left join "dbUserAcc".tbuser aa on (aa.id = a.idgrp )
260 --left join "dbUserAcc".tbuserprod bb on (bb.pid = aa.id )
261 --left join "dbUserAcc".tbprodtask cc on (cc.pid = bb.prodid and cc.title = c.title )
262 --left join "dbUserAcc".tbuserprdmenu dd on (dd.pid = bb.id and dd.prodtaskid= cc.id )
263 where a.id = %s -- 2
264 group by a.id, a.username, a.privilege, c.privilege, d.privilege, c.id , c.edition, aa.privilege
265
266 '
267 ,',' -- 0
268 ,prodmenu -- 1
269
270 , userid -- 2
271 );
272 -- raise notice '1663, sql=%',sql;
273 execute sql into pv;
274 end if;
275
276 --*
277 if pv.users is null then
278 pv.users = pv.users_grp;
279 end if;
280
281 --* Prepare errore message
282 select msg from "dbSys".sysCallErrMsg(
283 61
284 ,null -- talbename = description
285 ,null -- new.id
286 ,null -- NEW.uID
287 ,errmsg := 'User "%s" is terminated'
288 )into err60;
289 select msg from "dbSys".sysCallErrMsg(
290 62
291 ,null -- talbename = description
292 ,null -- new.id
293 ,null -- NEW.uID
294 ,errmsg :='Product Menu "%s" is "%s"'
295 ) into err61;
296 select msg from "dbSys".sysCallErrMsg(
297 63
298 ,null -- talbename = description
299 ,null -- new.id
300 ,null -- NEW.uID
301 ,errmsg :='User Menu "%s" for user "%s" is terminated'
302 )into err62;
303 select msg from "dbSys".sysCallErrMsg(
304 92
305 ,null -- talbename = description
306 ,null -- new.id
307 ,null -- NEW.uID
308 --,errmsg :='User Menu "%s" for user "%s" is terminated'
309 ,errmsg :='Menu "%s" is for %s only'
310
311 )into err92;
312 --* Return values
313 select pv.users ,pv.menu ,pv.usersmenu , null::text into pvusers ,pvmenu ,pvusersmenu , pverr ;
314
315 -- raise ' 2723: pverr %', pverr;
316
317 --** Edition ------------
318 /*
319 if pverr is null then
320 pverr:= case
321 when pv.prodEdition is not null then case
322 when existstr( pv.usersProdEdition, pv.prodEdition ) = 0 then
323 -- when existstr( pv.prodEdition, pv.usersProdEdition ) = 0 then
324 format(
325 (
326 select msg from "dbSys".sysCallErrMsg(
327 69
328 ,null -- talbename = description
329 ,null -- new.id
330 ,null -- NEW.uID
331 ,errmsg := '"%s" is not for edition "%s" '
332 )
333 )
334 , (select title from "dbUserAcc".tbprodtask where id = pv.prodtaskid)
335 -- , "dbUserAcc".tbprod_EditionTitle(pv.usersProdEdition) )
336 ,(select title from "dbUserAcc".tpprodedition where id = pv.usersProdEdition )
337 )
338 else null
339 end
340 else null
341 end ;
342 end if;
343
344 */
345
346 --** Menu privilege ----------------
347 if pverr is null then
348 pverr:= case
349 when pv.menu is not null then case
350 when existstr('T', pv.menu )>0 then format( err61,pv.menuname,'Terminate' )
351 when existstr('S', pv.menu )>0 then format( err61,pv.menuname,'System' )
352
353 when (existstr('U', pv.menu )>0) then case
354 when pv.sysrole in ('Supervisor','Admin' ) then null
355 --else format( err92,pv.menuname,'Supervisor and Admin' )
356 else case
357 when (existstr('V', pv.menu )>0) then null
358 else format( err92,pv.menuname,'Admin' )
359 end
360 end
361 when (existstr('M', pv.menu )>0) then case
362 when pv.sysrole in ( 'Admin' ) then null
363 --else format( err92,pv.menuname,'Admin' )
364 else case
365 when (existstr('V', pv.menu )>0) then null
366 else format( err92,pv.menuname,'Admin' )
367 end
368 end
369 else null
370
371 end
372 else null
373 end ;
374 end if;
375 --** user privilege
376 if pverr is null then
377 pverr:= case
378 when pv.users is not null then case
379 when pv.sysrole = 'Admin' then null
380 when pv.sysrole = 'System' then null
381 when existstr('T', pv.users )>0 then format( err60,pv.username )
382
383 when (existstr('U', pv.menu )>0) then case
384 when pv.sysrole in ('Supervisor','Admin' ) then null
385 else format( err92,pv.menuname,'Supervisor and Admin' )
386 end
387 when (existstr('M', pv.menu )>0) then case
388 when pv.sysrole in ( 'Admin' ) then null
389 else format( err92,pv.menuname,'Admin' )
390 end
391
392 else null
393 end
394 end ;
395 end if;
396 --** User Menu
397 if pverr is null then
398
399 pverr := case
400 when pv.usersmenu is not null then case
401 when pv.sysrole = 'Admin' then null
402 when existstr('T', pv.usersmenu )>0 then format( err62,pv.menuname, pv.username )
403
404 when (existstr('U', pv.menu )>0) then case
405 when pv.sysrole in ('Supervisor','Admin' ) then null
406 else format( err92,pv.menuname,'Supervisor and Admin' )
407 end
408 when (existstr('M', pv.menu )>0) then case
409 when pv.sysrole in ( 'Admin' ) then null
410 else format( err92,pv.menuname,'Admin' )
411 end
412 -- when existstr('T', pv.usersmenu )>0 then format('Menu "%s" for user "%s" is terminated',pv.menuname )
413
414 else null
415 end
416 else null
417 end ;
418 end if;
419
420 --* Constraint
421 if pverr is null then
422
423
424 end if;
425 -- raise notice '2768,pv.users %,pv.menu %, pv.usersmenu % ,pverr %, justverify %, userid %, tb.viewregid %',pv.users,pv.menu, pv.usersmenu , pverr,justverify, userid, tb.viewregid ;
426
427 --* Create Privilege trigger for this a/c
428 --if (pv.id is not null) then
429 --** Create trigger, if not only verify
430 if (not JustVerify) then
431 -- raise '300 tb.viewregid %, pverr %', tb.viewregid, pverr;
432 if (pverr is null) and (tb.viewregid is not null ) then
433
434 --if false then
435 --* Insert / update priviledge using trigger "dbSys".user_priv
436 pcname := 'user_priv'; -- in "dbUserAcc".tbuserPrdMenu_PROC.sql
437
438 --* 1 Before view update ------------------
439 --tgNames := format('%s_5%s_acc%s', tb.nam, pcname ,accid );
440 --tgNames := format('%s_5%s_%s', tb.nam, pcname ,accid );
441 tgNames := format('%s_UpdData_5%s_%s', tb.nam, pcname ,accid );
442 --
443 --tgNames := format('%s_z%s_%s', tb.nam, pcname ,accid );
444
445 select a.tgrelid id from pg_trigger a where not a.tgisinternal and a.tgname =tgnames into tg;
446 -- raise notice '310 tg.id %', tg.id ;
447 --if tg.id is null then
448 if true then
449 -- select exists( 'tbpostcodevw51_5user_priv' )
450
451 -- raise '1341, tblvw99 %,pcname %, tgname % ',tblvw99,pcname, tgnames;
452 sql:= format( 'drop trigger if exists %s on %s cascade', tgnames, tblvw99 );
453 -- raise notice '1920, sql = %',sql;
454 execute sql;
455
456 if "dbSys".havecolumn( tblvw99,'uid') then
457 --if tb.ViewRegistrationId is not null then
458 if true then
459 --if "dbSys".havecolumn( tb.tbl_pers,'uid') then
460 if true then
461 select * from "dbSys".tbviewregistration where id = tb.viewregid into vw;
462 sql := format(
463 'create trigger %s -- a
464 instead of insert or update on %s -- b No check on delete
465 -- instead of insert or update or delete on %s -- c
466 for each row execute procedure "%s".%s( -- d
467 %s -- 1 accid
468 ,%s -- 2 viewid
469 ,%L -- 3
470 ,%L -- 4
471 , %L -- 5
472 ,%L -- 6
473 ,%L -- 7
474 ,%s -- 8 viewregid_top
475 ,%s -- 9 DataLimit
476 ,%s -- 10
477
478 )'
479 ,tgnames --a
480 ,tblvw99 -- b
481 ,tblvw99 -- c
482 ,'dbSys', pcname -- d
483 ,accid -- 1
484 ,tb.viewregid -- 2
485
486 ,vw.privSysRole -- 3
487 ,tblvw99 -- 4
488 --,'User "%s" have no privilege "%s"' -- 5
489 --,(select msg from "dbSys".sysCallErrMsg(
490 -- 66
491 -- ,null -- talbename = description
492 -- ,null -- new.id
493 -- ,null -- NEW.uID
494 -- ,errmsg := 'User "%s" have no privilege "%s"'
495 -- ))
496 ,null -- 5
497
498 --,'Menu "%s" have no privilege "%s"' -- 6
499 --,(select msg from "dbSys".sysCallErrMsg(
500 -- 67
501 -- ,null -- talbename = description
502 -- ,null -- new.id
503 -- ,null -- NEW.uID
504 -- ,errmsg := 'Menu "%s" have no privilege "%s"'
505 -- ))
506 ,null -- 6
507 -- ,'User menu "%s" have no privilege "%s"' -- 7
508 --,(select msg from "dbSys".sysCallErrMsg(
509 -- 68
510 -- ,null -- talbename = description
511 -- ,null -- new.id
512 -- ,null -- NEW.uID
513 -- ,errmsg := 'Menu "%s" for user "%s" have no privilege "%s"'
514 -- ))
515 ,null -- 7
516
517 ,fmval( case
518 when tb.viewregid_top is null then tb.viewregid
519 else tb.viewregid_top
520 end ) -- 8 viewregid_top
521
522 ,'null' -- 9 DataLimit
523 ,'true' -- 10 Before_view_UpdData
524
525 );
526
527 raise notice ' 1945, sql=%', sql;
528 begin
529 execute sql;
530 exception
531 when others then raise '1386:%', sqlerrm;
532 end;
533
534
535 end if;
536
537 end if;
538 end if;
539 end if;
540
541
542 --* 2 After view update ------------------
543 --tgNames := format('%s_5%s_acc%s', tb.nam, pcname ,accid );
544 tgNames := format('%s_z%s_%s', tb.nam, pcname ,accid );
545 --tgNames := format('%s_z%s_%s', tb.nam, pcname ,accid );
546
547 select a.tgrelid id from pg_trigger a where not a.tgisinternal and a.tgname =tgnames into tg;
548 -- raise notice '310 tg.id %', tg.id ;
549 --if tg.id is null then
550 if true then
551 -- select exists( 'tbpostcodevw51_5user_priv' )
552
553 -- raise notice '1918, tblvw99 %,pcname %, tgname % ',tblvw99,pcname, tgname;
554 sql:= format( 'drop trigger if exists %s on %s cascade', tgnames, tblvw99 );
555 -- raise notice '1920, sql = %',sql;
556 execute sql;
557
558 if "dbSys".havecolumn( tblvw99,'uid') then
559 --if tb.ViewRegistrationId is not null then
560 if true then
561 --if "dbSys".havecolumn( tb.tbl_pers,'uid') then
562 if true then
563 select * from "dbSys".tbviewregistration where id = tb.viewregid into vw;
564 sql := format(
565 'create trigger %s -- a
566 instead of insert or update on %s -- b No check on delete
567 -- instead of insert or update or delete on %s -- c
568 for each row execute procedure "%s".%s( -- d
569 %s -- 1 accid
570 ,%s -- 2 viewid
571 ,%L -- 3
572 ,%L -- 4
573 , %L -- 5
574 ,%L -- 6
575 ,%L -- 7
576 ,%s -- 8 viewregid_top
577 ,%s -- 9 DataLimit
578 ,%s -- 10
579
580 )'
581 ,tgnames --a
582 ,tblvw99 -- b
583 ,tblvw99 -- c
584 ,'dbSys', pcname -- d
585 ,accid -- 1
586 ,tb.viewregid -- 2
587
588 ,vw.privSysRole -- 3
589 ,tblvw99 -- 4
590 --,'User "%s" have no privilege "%s"' -- 5
591 --,(select msg from "dbSys".sysCallErrMsg(
592 -- 66
593 -- ,null -- talbename = description
594 -- ,null -- new.id
595 -- ,null -- NEW.uID
596 -- ,errmsg := 'User "%s" have no privilege "%s"'
597 -- ))
598 ,null -- 5
599
600 --,'Menu "%s" have no privilege "%s"' -- 6
601 --,(select msg from "dbSys".sysCallErrMsg(
602 -- 67
603 -- ,null -- talbename = description
604 -- ,null -- new.id
605 -- ,null -- NEW.uID
606 -- ,errmsg := 'Menu "%s" have no privilege "%s"'
607 -- ))
608 ,null -- 6
609 -- ,'User menu "%s" have no privilege "%s"' -- 7
610 --,(select msg from "dbSys".sysCallErrMsg(
611 -- 68
612 -- ,null -- talbename = description
613 -- ,null -- new.id
614 -- ,null -- NEW.uID
615 -- ,errmsg := 'Menu "%s" for user "%s" have no privilege "%s"'
616 -- ))
617 ,null -- 7
618
619 ,fmval( case when tb.viewregid_top is null then tb.viewregid else tb.viewregid_top end ) -- 8
620 ,'null' -- 9 DataLimit
621 ,'false' -- 10 Before_view_UpdData
622
623 );
624
625 -- raise '1945, sql=%', sql;
626 begin
627 execute sql;
628 exception
629 when others then raise exception '1484:%',sqlerrrm;
630 end;
631
632
633 end if;
634
635 end if;
636 end if;
637 end if;
638
639 --* DataLimit
640 if conf.DataLimit is not null then
641
642 end if;
643
644 end if;
645 end if;
646 end if;
647
648 end $$ language plpgsql ; -- create_userPriv