• Welcome, Guest. Please login.
 
May 26, 2019, 04:28:07 am

News:

Welcome to the SQLitening support forums!


Building a Log-in/license manager

Started by Bern Ertl, November 18, 2008, 01:51:36 pm

Previous topic - Next topic

Bern Ertl

November 18, 2008, 01:51:36 pm Last Edit: November 18, 2008, 01:53:28 pm by Bern Ertl
I thought I'd create a new discussion thread to focus specifically on this issue (as originally touched upon here).

I want to build a log-in system to manage access in accordance with licensing restrictions.  There will be a db/table containing licensing information (# of allowed concurrent users/connections).  There will be a separate table of user accounts (with designated access levels).  The licensing restrictions only affect some of the user accounts (the ones with appropriate access levels).

Building a log-in system is not really a problem.  The client application can update the status of users who log in. My question is how to best handle dropped connections (auto-log outs)?

I'm guessing that I could write some code in the SQLiteningServerMonitor to read the SQLitening log file and analyze it for dropped connections, but how can that monitor application then communicate back with the server application?  Would it be wise to have it adjusting the db/table with the user account information directly?

mikedoty

November 18, 2008, 02:04:08 pm #1 Last Edit: November 18, 2008, 02:07:09 pm by Mike D
Here is a simple method that I've used for many years.
It could even use the machine name as part of the login since
SQLitening reads it.  Anyway,  the logic is there.

http://www.powerbasic.com/support/pbforums/showthread.php?t=22702&highlight=logon

#REGISTER NONE
#COMPILE EXE
#DIM ALL
#INCLUDE "WIN32API.INC"
DECLARE SUB LogonOff(Action&,MachineNumber$,MachineName$)
DECLARE FUNCTION ComputerName AS STRING
'
SUB Help
PRINT "LogOnOff.Bas     6/1/2000   Compiled USING PB/CC 2.0

PRINT "Some uses:
PRINT " 1 See who/when users log in and out of your program
PRINT " 2 Creates a unique terminal number for each terminal
PRINT " 3 Count number of terminals logged into your program
PRINT " 4 Modify to pass information between terminals by record number
PRINT " 5 Increment a logon counter to create an instance counter for your
PRINT "   program to create unique output files for the program.
PRINT "Action = 1
PRINT "   A file named 'machines' is searched for the computer name.
PRINT "   If found, the record number it was found in and the computer
PRINT "   name are returned.  This routine returns computer names for
PRINT "   you using the GetComputerName API.  Do not pass a name.
PRINT "   New computer names found are added as 128-byte records.
PRINT "Syntax:
PRINT "   CALL LogOnOff(Action&,MachineNumber$,MachineName$)
PRINT "     Action = 0  log user off at end of your program
PRINT "     Action = 1  log user on at beginning of your program
PRINT "     Action = 2  view log in real-time
PRINT "     MachineNumber$ and MachineName$ are returned (nothing passed)
PRINT
PRINT "     To see users logged in,  [press any key]  Action = 2
WAITKEY$
END SUB
'
Type LogType    '128 byte records  63 bytes unused
  MachineNumberOnDisk AS STRING * 6
  MachineNameOnDisk AS STRING * 17
  STATUS AS STRING * 10
  LogIn AS STRING * 16
  LogOut AS STRING * 16
  Dummy AS STRING * 63
END type
'
Type LogTypeAll
   Buffer AS STRING * 128
END type
'
FUNCTION PBMAIN AS LONG
   COLOR 7,1,1
   CLS
   CALL Help

   DIM action AS LONG
   DIM MachineNumber AS STRING
   DIM Machinename AS STRING

   Action = 1  'Call this at the beginning of your program
   CALL LogOnOff(Action,MachineNumber,MachineName)

   Action = 2  'Call this to see who is logged on
   CALL LogOnOff(Action,MachineNumber,MachineName)

   Action = 0  'Call this when a user ends program
   CALL LogOnOff(Action,MachineNumber,MachineName)
END FUNCTION
'
'
SUB LogOnOff (Action AS LONG, MachineNumber AS STRING, _
              Machinename AS STRING)
   ON ERROR GOTO LogOnError

   DIM Org AS STRING
   DIM DataBaseDir AS STRING
   DIM FileNum AS LONG
   DIM Record AS LONG
   DIM FilName AS STRING
   DIM xx AS STRING
   DIM MachineNum AS LONG

   DIM User AS LogType
   DIM Everything AS LogTypeAll
   %RefreshTime = 1000

   %RecLen = 128  'minimum = 65   63 bytes unused per record

SELECT CASE Action
   CASE 0 TO 1   '0=log out  1=log in
      Machinename$ = ComputerName  'calls ComputerName$ function
      IF LEN(Machinename$) = 0 THEN EXIT SUB
   CASE 2
      GOTO DisplayLog
   CASE ELSE
      EXIT SUB
END SELECT
   org$ = DataBaseDir$ + "MACHINES"
   filenum = FREEFILE
   OPEN org$ FOR RANDOM SHARED AS #filenum LEN = %RecLen

   FOR Record = 1 TO LOF(filenum) / %RecLen
      GET #filenum, Record,User
      IF INSTR(User.MachineNameOnDisk, Machinename$) THEN
         MachineNum = Record  'found machine in log
         EXIT FOR
      END IF
   NEXT
   IF MachineNum = 0 THEN
      MachineNum = LOF(filenum) \ %RecLen + 1

      'init the buffer TO ALL spaces
      User.MachineNumberOnDisk = ""
      User.MachineNameOnDisk = ""
      User.Status = ""
      User.LogIn = ""
      User.LogOut = ""
   END IF

   MachineNumber$ = LTRIM$(STR$(MachineNum))
   User.MachineNumberOnDisk$ = MachineNumber$
   User.MachineNameOnDisk$ = Machinename$

   org$ = LEFT$(TIME$, 5) + " " + LEFT$(DATE$, 5)
   IF Action = 0 THEN
      User.LogOut = org$
      User.Status$ = "-"
   ELSEIF Action = 1 THEN
      User.LogIn = org$
      User.Status = "ACTIVE"
   END IF
   PUT #filenum, MachineNum,User
   CLOSE #filenum
EXIT SUB
'
DisplayLog:   'Action = 2
   filenum = FREEFILE
   FILNAME$ = DataBaseDir$ + "Machines"
   OPEN FILNAME$ FOR RANDOM SHARED AS #filenum LEN = %RecLen
   CLS
   PRINT TAB(33); "USER ACTIVITY"; TAB(58); "Press any key to exit"
   LOCATE 3, 1
   PRINT "NODE  MACHINE NAME"; TAB(24); "STATUS    LAST LOGIN"; TAB(50); _
    "LAST LOGOUT"
DO
   LOCATE 1, 1: PRINT TIME$
   LOCATE 5, 1

   FOR Record = 1 TO LOF(filenum) / %RecLen
      GET #filenum, Record,Everything
      PRINT LEFT$(Everything.BUFFER, 80)
   NEXT
   SLEEP %RefreshTime '1000 = 1 second
   xx$ = INKEY$ '5/29/99
   IF LEN(xx$) THEN EXIT DO
LOOP
   CLOSE #filenum
   EXIT SUB
LogOnError:
   CLS
   PRINT "Unable to UPDATE terminal id file named MACHINES."
   PRINT "Please check that the drive is not READONLY."
   PRINT "Ending program"
   PRINT "Press any key"
   WAITKEY$
   RESUME 100
100
END SUB
'
FUNCTION ComputerName AS STRING
  %MAX_COMPUTERNAME_LENGTH = 15    'not in Win32Api.inc
  DIM RetCode AS LONG
  DIM lpBuffer AS  ASCIIZ * %MAX_COMPUTERNAME_LENGTH + 1
  DIM nSize AS LONG
  nSize = %MAX_COMPUTERNAME_LENGTH + 1
  RetCode = GetComputerName(lpBuffer,nSize) 'nSize returns length
  FUNCTION = lpBuffer
END FUNCTION




JoeByrne

Generally speaking, you need some form of 'heart-beat' process.  Assuming these are all Win32 apps (not web based), then you can have the client send a "ping" to the server on a regular interval.  If the server doesn't get the scheduled 'ping' for any number of consecutive time-slots, you can assume the user has dropped dead...or at least the connection :)

Or you can have the server send a 'ping' and check the results....

Or you can create a timer on the server.  Whenever there is access from the client, reset the timer for that computer to zero.  Every 'x' number of seconds, minutes, or hours, poll your timers and see who hasn't connected in awhile.  Then you can assume those connections are dead and force the user to re-authenticate next time they connect.

Or you can skip any 'real-time' testing until you've run out of licensed connections.  Then initiate a hand-shake from the server to each client.  If a client fails to reply, close that connection and assign it to the new incoming connection.

A lot depends on how quickly you need to detect a dead connection and how the average traffic on your physical link is.  Under normal circumstances, I like putting a 'keep-alive' ping into the client and then closing the connection when one of these pings hasn't been heard for awhile.

Bern Ertl

Quote from: JoeByrne on November 18, 2008, 02:36:36 pmOr you can create a timer on the server.  Whenever there is access from the client, reset the timer for that computer to zero.  Every 'x' number of seconds, minutes, or hours, poll your timers and see who hasn't connected in awhile.  Then you can assume those connections are dead and force the user to re-authenticate next time they connect.


I had assumed that SQLitening was already doing this (similar to how TTDS manages remote connections for Tsunami).  I'm still getting my feet wet here, so if made a mistake here, could somone explain how SQLitening detemines if a connection is dropped?

~~~

The more I look at the SQLiteningServerMonitor/log file option, the more problems I see.  The biggest problem I see is how to ensure that the log file doesn't get trimmed between monitoring sessions (so that disconnect notifications aren't missed). 

JoeByrne

Bern,

Fred will probably have to chime in here on this.  I haven't worked with the remote server to the degree of worrying about connections.  If TTDS did this type of checking automatically, I missed that part in the docs.  When I worked on a client/server app with TTDS, I just had a single user account that connected to the database server and then managed the rest of the user connections myself.

Bern Ertl

TTDS required the client app to ping the server occassionally.

Looking at the SQLiteningServer code, it appears that SQLitening is checking for closed sockets.  I'm not sure how that compares to the ping/timeout system.

Fred Meier

QuoteThe biggest problem I see is how to ensure that the log file doesn't get trimmed between monitoring sessions (so that disconnect notifications aren't missed).

There is a setting in the config file to control the trimming of the log.  Below is from the .Cfg file.
QuoteTrimLogManually = Yes or No  ---  Controls the trimming of the log when it becomes large (> 600K).  If Yes
                                        then no automatic trimming will occur.  If no then will automatically
                                        trim 100K from front of log when it becomes large. If omitted then will
                                        default to No


Bern Ertl

I've built a licensing and log-in system using SQLitening 1.11 utilizing the new Exits system (SQLiteningServerExits.DLL) and server side processing (SQLiteningProcsX.DLL).  Early testing looks great.  This looks to be a solid platform for the applications I am designing.

Huge thanks to Fred for developing SQLitening and adding some modifications to facilitate the building of a log-in system.  I can really appreciate the SQLitening wrapper functions for SQLite after having to code with SQLite for the server side processes.