dmSEC	; functies ivm security ;dmSEC
	;
YZ	Q
	;
GRANTREV(class,gd,type,user,scr)	; opzetten/delete van priviliges voor een klasse voor een bepaalde SQL-user
	; class = naam van de klasse
	;         indien eindigend met * : alle klassen die beginnen met class
	;               bv :    Sales.OrderHeaders      : enkel deze klasse
	;                       Sales.Order*            : alle klassen die beginnen met Sales.Order
	; gd    = "" = grant : toekennen
	;          1 = revoke : verwijderen
	; user  = naam(en) van de role of SQL-user : naam1,naam2
	;       niet ingevuld = "user"
	; type  = kan een waarde hebben uit "siudr"
	;       s=select, i=insert, u=update, d=delete, r=reference 
	;       niet ingevuld = "s"
	;       * = alles
	N (class,gd,user,type,scr)
	D CVER^cQ13
	s $zt="TRAP^cAN000"
	S %msql=$$MSQL(),D="\"
	I '$L($g(class)) S OK="0\No class defined" G GRANTREVZ
	S gd=$g(gd)
	I '$L($G(user)) S user="UserReadOnly"     ; role 
	I $E(user,$L(user))'="," S user=user_","
	I '$L($G(type)) S type="s"                ; select
	S (R,I1)=$TR(class,"*"),OK=""
	I $L(R),class'["*" S I1=$O(^oddCOM(I1),-1)
	I '$L(I1) S I1="%zzzz"  ; nooit systeem classen
	F  S I1=$O(^oddCOM(I1)) Q:$S(class["*":$E(I1,1,$L(R))'=R,1:I1'=class)!'$L(I1)  d  
	. ; ophalen sqlname
	. S sqlname=$$SQLN(I1)
	. I $g(scr) W !,I1,?40,sqlname
	. I $P(sqlname,D)=0 S OK=OK_I1_";"_$P(sqlname,D,2)_D Q
	. S SQLCODE=100
	. ; Grant
	. I 'gd D setup^%qarPrivileges(type,1,$lb(sqlname),user,0,1)
	. ; revoke
	. I gd D setup1^%qarPrivileges(type,1,$lb(sqlname),user,0,0) 
	. I SQLCODE S OK=OK_I1_";"_$$Error^%apiOBJ($G(%v(5))*5000+521,"SQLCODE = "_SQLCODE_" "_$g(%msg))
	S:$L(OK) OK="0\"_OK S:'$L(OK) OK=1
	W:$g(scr) !
GRANTREVZ	Q OK
	;
MSQL()	Q "_SYSTEM"
	;
PWUSER(user,pw)	; change user password
	n (user,pw)
	D CVER^cQ13
	S %msql=$$MSQL()
	s R=$$AltUser^%qadUsers(user,pw) I R s SQLCODE=0
	Q $s('SQLCODE:1,1:"0\"_$$Error^%apiOBJ($G(%v(5))*5000+521,"SQLCODE = "_SQLCODE_" "_$g(%msg)))
	;
RO2USR(role,user,cd)	; grant/revoke role to user
	; role = naam van de role
	; user = naam van de user
	; cd = "" : create
	;       1 : drop (verwijder)
	n (role,user,cd)
	D CVER^cQ13
	S %msql=$$MSQL()
	s cd=$g(cd)
	i $p($g(%msql),$c(1))="" s SQLCODE=-112 G RO2USRZ
	s gr=$p($g(%msql),$c(1)),SQLCODE=0
	i '$$haspriv^%qarPrivileges(gr,",,,,1") d
	. s role=$zcvt(role,"U") s:role'="" role=$o(^%SYS("sql","users","role-index",role,"")) i role="" s SQLCODE=100 q
	. i gr'="_SYSTEM",$g(^%SYS("sql","users","user-role",gr,role))'=1,$lg(^%SYS("sql","users","role",role),2)'=gr s SQLCODE=-112 q
	G RO2USRZ:SQLCODE  s SQLCODE=100
	s role=$zcvt(role,"U"),role=$o(^%SYS("sql","users","role-index",role,""))
	s x=$zcvt(user,"U") i x'="" s:$d(^%SYS("sql","users","role-index",x)) user=$o(^(x,"")) s:$d(^%SYS("sql","users","name",x)) user=$o(^(x,""))
	I 'cd s ^%SYS("sql","users","role-user",role,user)="",^%SYS("sql","users","user-role",user,role)="" s:SQLCODE SQLCODE=0
	I cd k ^%SYS("sql","users","role-user",role,user),^%SYS("sql","users","user-role",user,role) s:SQLCODE SQLCODE=0
RO2USRZ	QUIT $s('SQLCODE:1,1:$$Error^%apiOBJ($G(%v(5))*5000+521,"SQLCODE = "_SQLCODE_" "_$g(%msg)))
	;
ROLE(role,cd)	; create/drop role
	; role = naam van de role
	; cd = "" : create
	;       1 : drop (verwijder)
	N (role,cd)
	D CVER^cQ13
	S %msql=$$MSQL()
	s cd=$g(cd)
	S SQLCODE=100
	i 'cd D CreateRole^%qadUsers(role,.SQLCODE)
	i cd D DropRole^%qadUsers(role,.SQLCODE)
ROLEZ	Q $s('SQLCODE:1,1:"0\"_$$Error^%apiOBJ($G(%v(5))*5000+521,"SQLCODE = "_SQLCODE_" "_$g(%msg)))
	;
SET(user,pw,scr)	; initieel opzetten role 'ReadOnly', user en alle tabellen koppelen
	n (user,pw,scr)
	S $zt="TRAP^cAN000"
	s role="ReadOnly"
	I '$L($g(user)) s ok="0\no user" G SETZ
	I '$L($g(pw)) s ok="0\no password" G SETZ
	s ok=$$ROLE(role)
	i 'ok,ok'["SQLCODE = -104" s ok="0\role : "_ok G SETZ
	s ok=$$USER(user,pw)
	i 'ok,ok'["SQLCODE = -118" s ok="0\user : "_ok G SETZ
	s ok=$$GRANTREV("*",,"s",role,$g(scr))
	; mag niet op ok getest worden
	; i 'ok s ok="0\grant : "_ok G SETZ
	s ok=$$RO2USR(role,user)
	i 'ok s ok="0\role to user : "_ok G SETZ
SETZ	Q ok
	;
SQLN(class)	; ophalen SQLName van een classe
	N R,cl
	I class'["." S class="User."_class
	D CVER^cQ13
SQLNA	I '$G(%v(5)) G SQLNB
	S cl=##class(%Dictionary.ClassDefinition).%OpenId(class)
	I 'cl D $system.Status.DecomposeStatus(cl,.err) S R="0\Error opening classdefinition for "_class_" : "_err(1) G SQLNZ
	S R=cl.ClassType="persistent" I 'R s R="0\Type is not persistent" G SQLNY
	S R=cl.SqlTableName G SQLNC
	; onder versie 5
SQLNB	I '$D(^oddCOM(class)) S R="0\Class doesn't exist" G SQLNZ
	S R=$G(^oddCOM(class,"spec","persistent")) I 'R s R="0\Wrong type" G SQLNY
	S R=$G(^oddCOM(class,"spec","sqlqualifiednameQ")) 
SQLNC	I '$L(R) S R=class
	I $P(R,".")="User" S R="SQL"_R
	F  Q:$L(R,".")=2  S R=$P(R,".")_"_"_$P(R,".",2,99)
SQLNY	I $G(cl) D cl.%Close() s cl=""
SQLNZ	Q R
	;
USER(user,pw,cd)	; create/drop user
	; user = naam van de user
	; pw   = paswoord van de user
	; cd = "" : create
	;       1 : drop (verwijder)
	N (user,pw,cd)
	D CVER^cQ13
	S %msql=$$MSQL()
	s cd=$g(cd)
	S SQLCODE=100
	I 'cd S R=$$AddUser^%qadUsers(user,pw) I R S SQLCODE=0
	i cd D DropUser^%qadUsers(user,1,.SQLCODE,.%msg)
USERZ	Q $s('SQLCODE:1,1:"0\"_$$Error^%apiOBJ($G(%v(5))*5000+521,"SQLCODE = "_SQLCODE_" "_$g(%msg)))
	;
ZZ	; 15.12.05 - 12 u 16 * V8.05