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
