PowerBuilder – Single user sign on verification for SQL Anywhere
This method is used with a SQL Anywhere database to ensure that any given user can only sign on the application once. It uses the connection_property function so make sure you either give the users permission to execute this or set up a separate connection which uses an id which has the permissions.
There are three Powerbuilder objects, one nvo and two datawindow objects, a stored procedure, and a table.
Table
CREATE TABLE "dba"."sec_userconnection" ("connectionid" numeric(12,0) NOT NULL DEFAULT NULL , "userid" varchar(20) NOT NULL DEFAULT NULL , PRIMARY KEY ("connectionid", "userid")) ;
Stored Procedure
create procedure DBA.Conn_number(out id integer) begin select connection_property('Number') end
Datawindow objects
$PBExportHeader$d_conn_number.srd release 9; datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes hidegrayline=no ) summary(height=0 color="536870912" ) footer(height=0 color="536870912" ) detail(height=144 color="536870912" ) table(column=(type=char(254) updatewhereclause=yes name=conn_number dbname="compute_0001" ) procedure="1 execute dba.conn_number;0 id = :id" arguments=(("id", number)) ) text(band=detail alignment="1" text="Connection Property('number'):" border="0" color="33554432" x="37" y="4" height="64" width="809" html.valueishtml="0" name=compute_0001_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=1 alignment="0" tabsequence=32766 border="0" color="33554432" x="864" y="4" height="76" width="4389" format="[general]" html.valueishtml="0" name=conn_number visible="1" edit.limit=254 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) htmltable(border="1" ) htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" ) export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 ) import.xml() export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )
This second datawindow object is based on the stored procedure sa_conn_info. Again, you must have permissions set up for the user to run this.
$PBExportHeader$d_sa_conn_info.srd release 9; datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes hidegrayline=no ) summary(height=0 color="536870912" ) footer(height=0 color="536870912" ) detail(height=2048 color="536870912" ) table(column=(type=long updatewhereclause=yes name=number dbname="number" ) column=(type=char(255) updatewhereclause=yes name=name dbname="name" ) column=(type=char(255) updatewhereclause=yes name=userid dbname="userid" ) column=(type=long updatewhereclause=yes name=dbnumber dbname="dbnumber" ) column=(type=char(255) updatewhereclause=yes name=lastreqtime dbname="lastreqtime" ) column=(type=char(255) updatewhereclause=yes name=processtime dbname="processtime" ) column=(type=long updatewhereclause=yes name=port dbname="port" ) column=(type=char(255) updatewhereclause=yes name=reqtype dbname="reqtype" ) column=(type=char(255) updatewhereclause=yes name=commlink dbname="commlink" ) column=(type=char(255) updatewhereclause=yes name=nodeaddr dbname="nodeaddr" ) column=(type=long updatewhereclause=yes name=lastidle dbname="lastidle" ) column=(type=long updatewhereclause=yes name=currtasksw dbname="currtasksw" ) column=(type=long updatewhereclause=yes name=blockedon dbname="blockedon" ) column=(type=decimal(0) updatewhereclause=yes name=lockname dbname="lockname" ) column=(type=long updatewhereclause=yes name=uncmtops dbname="uncmtops" ) procedure="1 execute dbo.sa_conn_info;0 connidparm = :connidparm" arguments=(("connidparm", number)) ) text(band=detail alignment="1" text="Number:" border="0" color="33554432" x="37" y="4" height="64" width="352" html.valueishtml="0" name=number_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=1 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="4" height="76" width="329" format="[general]" html.valueishtml="0" name=number visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Name:" border="0" color="33554432" x="37" y="140" height="64" width="352" html.valueishtml="0" name=name_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=2 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="140" height="76" width="4389" format="[general]" html.valueishtml="0" name=name visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Userid:" border="0" color="33554432" x="37" y="276" height="64" width="352" html.valueishtml="0" name=userid_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=3 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="276" height="76" width="4389" format="[general]" html.valueishtml="0" name=userid visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Dbnumber:" border="0" color="33554432" x="37" y="412" height="64" width="352" html.valueishtml="0" name=dbnumber_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=4 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="412" height="76" width="329" format="[general]" html.valueishtml="0" name=dbnumber visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Lastreqtime:" border="0" color="33554432" x="37" y="548" height="64" width="352" html.valueishtml="0" name=lastreqtime_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=5 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="548" height="76" width="4389" format="[general]" html.valueishtml="0" name=lastreqtime visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Processtime:" border="0" color="33554432" x="37" y="684" height="64" width="352" html.valueishtml="0" name=processtime_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=6 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="684" height="76" width="4389" format="[general]" html.valueishtml="0" name=processtime visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Port:" border="0" color="33554432" x="37" y="820" height="64" width="352" html.valueishtml="0" name=port_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=7 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="820" height="76" width="329" format="[general]" html.valueishtml="0" name=port visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Reqtype:" border="0" color="33554432" x="37" y="956" height="64" width="352" html.valueishtml="0" name=reqtype_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=8 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="956" height="76" width="4389" format="[general]" html.valueishtml="0" name=reqtype visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Commlink:" border="0" color="33554432" x="37" y="1092" height="64" width="352" html.valueishtml="0" name=commlink_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=9 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="1092" height="76" width="4389" format="[general]" html.valueishtml="0" name=commlink visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Nodeaddr:" border="0" color="33554432" x="37" y="1228" height="64" width="352" html.valueishtml="0" name=nodeaddr_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=10 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="1228" height="76" width="4389" format="[general]" html.valueishtml="0" name=nodeaddr visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Lastidle:" border="0" color="33554432" x="37" y="1364" height="64" width="352" html.valueishtml="0" name=lastidle_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=11 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1364" height="76" width="329" format="[general]" html.valueishtml="0" name=lastidle visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Currtasksw:" border="0" color="33554432" x="37" y="1500" height="64" width="352" html.valueishtml="0" name=currtasksw_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=12 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1500" height="76" width="329" format="[general]" html.valueishtml="0" name=currtasksw visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Blockedon:" border="0" color="33554432" x="37" y="1636" height="64" width="352" html.valueishtml="0" name=blockedon_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=13 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1636" height="76" width="329" format="[general]" html.valueishtml="0" name=blockedon visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Lockname:" border="0" color="33554432" x="37" y="1772" height="64" width="352" html.valueishtml="0" name=lockname_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=14 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1772" height="76" width="329" format="[general]" html.valueishtml="0" name=lockname visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) text(band=detail alignment="1" text="Uncmtops:" border="0" color="33554432" x="37" y="1908" height="64" width="352" html.valueishtml="0" name=uncmtops_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) column(band=detail id=15 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1908" height="76" width="329" format="[general]" html.valueishtml="0" name=uncmtops visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" ) htmltable(border="1" ) htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" ) export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 ) import.xml() export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )
This nvo is PFC based but doesn’t have to be.
nvo_dbconncheck
$PBExportHeader$nvo_dbconncheck.sru forward global type nvo_dbconncheck from n_base end type end forward global type nvo_dbconncheck from n_base end type global nvo_dbconncheck nvo_dbconncheck type variables ulong iul_connection end variables forward prototypes public function boolean uf_checkconnection (string a_userid) public subroutine uf_removeconnection () end prototypes public function boolean uf_checkconnection (string a_userid);// this method looks for any connections for the current user. // If there is a connection number for the user, check to see if // it is current. // // If the connection is current, return false since users are not // allowed to log on multiple times. // // If the connection is not current, update the row with the current // connection number. // // If there is no connection number, insert the current user and connection // number into the table. // boolean lb_return = TRUE ulong lul_connection_old ulong lul_connection_new long ll_conrows, ll_connectionrow datastore lds, lds1 IF IsNull(a_userid) OR (a_userid = '') THEN RETURN FALSE lds = create datastore lds.dataobject = 'd_sa_conn_info' lds.settransobject(SQLCA) lds1 = create datastore lds1.dataobject = 'd_conn_number' lds1.settransobject(SQLCA) // is user already connected? SELECT max(connectionid) INTO :lul_connection_old FROM sec_userconnection WHERE userid = :gv_userid; IF IsNull(lul_connection_old) THEN lul_connection_old = 0 lb_return = TRUE // current connection number ll_connectionrow = lds1.retrieve(1) IF ll_connectionrow > 0 THEN iul_connection = long(lds1.getitemstring(ll_connectionrow,'conn_number')) lb_return = TRUE ELSE // can't get current connection, exit Messagebox('Access Error','Unable to determine database connection number!') lb_return = FALSE END IF IF lb_return THEN // check if users other connection number is valid (still connected) IF (lul_connection_old > 0) THEN // connection valid, user attempting second log on ll_conrows = lds.retrieve(lul_connection_old) IF (ll_conrows > 0) THEN Messagebox('Access Error','Multiple logons are not permitted!') lb_return = FALSE ELSE // old connection not valid (left over from some fatal error) // update with current connection number Update sec_userconnection set connectionid = :iul_connection WHERE connectionid = :lul_connection_old using sqlca; commit; lb_return = TRUE END IF ELSE // no previous connections so create one // insert record with connection number and user id insert into sec_userconnection select :iul_connection, :gv_userid; lb_return = TRUE END IF END IF IF IsValid(lds) THEN DESTROY lds IF IsValid(lds1) THEN DESTROY lds1 RETURN lb_return end function public subroutine uf_removeconnection ();// delete users connection number from db // so they are not seen as being logged on more than once. delete from sec_userconnection where connectionid = :iul_connection USING SQLCA; commit; end subroutine event constructor;call super::constructor;// This object used to validate that a user has only one connection to the database // when they launch the application. end event on nvo_dbconncheck.create call super::create end on on nvo_dbconncheck.destroy call super::destroy end on
Create an instance of the nvo on the application object (again this is PFC based) and create in in the constructor event. In the pfc_close event on the application object insert the following and override the ancestor:
IF ISVALID(inv_conncheck) THEN inv_conncheck.uf_removeconnection() DESTROY inv_conncheck END IF super::EVENT pfc_close()
In the event where the user logon is validated add the following:
IF (gnv_app.inv_conncheck.uf_checkconnection(gv_userid) = FALSE) THEN // gv_userid is the user id for the application and database close(parent) return END IF