Creating accounts for multiple users of a MySQL database

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
montymay
Posts: 142
Joined: Thu Jul 18, 2013 5:23 am

Creating accounts for multiple users of a MySQL database

Post by montymay » Sat Dec 21, 2019 9:20 am

I have a LC standalone that is the front-end of several MySQL databases on a local WAMP MySQL server, i.e., the host that runs WAMP and a MySQL server and the client machines are on a LAN. I want to distribute the standalone to each user. Using phpMyAdmin, I know I can create multiple user accounts and passwords from my workstation, but, using LC script powering a login stack, can code be written so that the user creates his or her own account with a username and a password that the user chooses? (I am assuming that MySQL requires a password for each user account, but I read somewhere that it doesn't.) If it can be done, can anyone who knows referred me to a discussion elsewhere or tell me the method to use, if not the specific script? Alternatively, for a LC & database beginner such as myself, is the best method just creating multiple user accounts with passwords via phpMyAdmin, and telling the users what their username and password are and hard writing the username and password in the login script? I hope the above was clear enough and not a question discussed elsewhere in this forum. Thanks for any suggestions.

Monty

mrcoollion
Posts: 578
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Creating accounts for multiple users of a MySQL database

Post by mrcoollion » Sat Dec 21, 2019 11:03 am

Hi Monty,

Here are some routines I build in the past to add, remove, update and find records, in an SQLite / MySQL database. No guarantees but you are free to use, explore and enhance the code. If you do enhance the routines please post for others to use?

Regards,

Paul

Code: Select all

#######################################################################################
// Add a new record to the DB AddRecordV3 04-01-2017
// DatabaseID, TableName, KeyFields, KeysData, FieldNamesString, DataArray,CheckIfExistsYN, @AddResult @AddMsg
// KeysData is array with name of the field and data of the key field e.g. KeysData["userID"] 
// DataArray is array that holds all the record data for each column e.g. DataArray["userID"] and DataArray["password"] and DataArray["firstname"] ect...
// Use like this: AddRecordV3 DatabaseID, TableName, KeysDataArray, FieldsDataArray, CheckIfExistsYN, OutResult, OutMsg
// if record exists then AddMsg is "Record exists!"
#######################################################################################
command AddRecordV3 DatabaseID, TableName, KeysData, DataArray, CheckIfExistsYN, @OutResult @AddMsg
    set itemdelimiter to ","
    -- get the keyFields names from the KeysData 
    put the keys of KeysData into tKeysDataArrayFieldNames
    put the number of lines of (the keys of tKeysDataArrayFieldNames) into NumberOfKeyFields
    replace return with "," in tKeysDataArrayFieldNames
    put the number of items of tKeysDataArrayFieldNames into NumberOfKeys
    put tKeysDataArrayFieldNames into KeyFields
    -- Get FieldDataArray info
    put the keys of DataArray into tFieldsDataArrayFieldNames
    replace return with "," in tFieldsDataArrayFieldNames
    put the number of items of tFieldsDataArrayFieldNames into NumberOfDataArrayFields
    -----------------------------------------
    if NumberOfKeys is 0 or NumberOfKeys is empty
    then
        put "Error" into OutResult
        put "Could not get the key fields from the KeysDataArray !" into AddMsg
        exit AddRecordV3
    end if
    // Get all field names. Because we add a new record we can get and use all fieldnames from DB (01-01-2017)
    put revDatabaseColumnNames(DatabaseID, TableName) into FieldNamesString
    if FieldNamesString is error
    then 
        put "Error" into OutResult
        put "Could not get the table fields from DB !" into AddMsg
        exit AddRecordV3
    end if
    put the number of items of FieldNamesString  into NumberOfFields // Also Must at least be 1 !!
    if NumberOfDataArrayFields is not NumberOfFields
    then
        put "Error" into OutResult 
        put "Number of fields in database is not the same as the number of fields in the FieldsDataArray. Should be the same!" into AddMsg 
        exit AddRecordV3
    end if
    // First check if record exists, only if user wants it to be checked.
    if CheckIfExistsYN contains "Y" then
        CheckIfRecordExistsV2 DatabaseID, TableName,  KeyFields, KeysData, OutExistsYesNo, OutRecordData, OuttheFieldNames, OutRecordDataArray
        if  OutExistsYesNo is "Yes" then 
            put "Error" into OutResult 
            put "Record exists!" into AddMsg  // Use this for checking if record does not exists
            put OutExistsYesNo into CheckIfExistsYN
            exit AddRecordV3
        else
            put "No" into OutExistsYesNo
            put OutExistsYesNo into CheckIfExistsYN
        end if
    else
        put "Not Checked" into CheckIfExistsYN
    end if
    -- Don't do anything with CheckIfExistsYN
    // End check if record exist
    put "','" into Sep
    put "'" into StartEndSep
    put StartEndSep into dbFieldData
    put NumberOfDataArrayFields into NumberOfDataFields
    put tFieldsDataArrayFieldNames into tDataArrayFieldNames
    put 1 into counter
    repeat for NumberOfDataFields times // Now create the field data part for the sql query
        if counter is 1 then put DataArray [item counter of tDataArrayFieldNames ] after dbFieldData  
        else
            put Sep&DataArray [item counter of tDataArrayFieldNames] after dbFieldData
        end if 
        add 1 to counter
    end repeat
    put dbFieldData & StartEndSep into dbFieldData
    // Add the data to the database
    put "INSERT into "&TableName&" ("&tDataArrayFieldNames&") VALUES ("& dbFieldData & ");" into tSQL
    replace tab with "" in tSQL // Make sure there are no tabs in the data because the database field seperator is also a tab
    revExecuteSQL DatabaseID, tSQL
    put the result into tResult
    if tResult is not a number then 
        put "Error" into OutResult
        put "There was a problem adding the record to the table "&gTableName&", result: " & tResult into AddMsg
        exit AddRecordV3
    else
        put "Ok" into OutResult
        put "Record has been added to table "&gTableName into AddMsg
    end if   
end AddRecordV3
-----------------------------------------------------------------------------------------------------------

Code: Select all

//======================================================================
## COMMAND: Check how many records exists and get them VERSION 6
## More robust, now gets data per record while looping through (should have no problem with returns and graphics)
// No need for KeyFields , get those from the DataArrayKeyFields 05-01-2017
## However. No tabs in data allowed, no returns or tabs in keyfield data allowed.
## http://www.tutorialspoint.com/sqlite/sqlite_order_by.htm
##   Use like this :    CheckHowmanyRecordsExistsV6 DatabaseID, TableName, UniqueSingleKeyField, DataArrayKeyFields, ResultFields, SortByFields, ASCDEC,OutExistsYesNo,OuttheFieldNames,OutNbrOfFields,OutNbrrecords,OutDataArrayRecords,OutResult,OutMsg
## Nbr of records and field info is in:  RecordDataArray["nbrrecords"], RecordDataArray["nbrfields"], RecordDataArray["fieldslist"]
## The data is in :  RecordDataArray [reccounter][theArrayFieldName]
## The SortByFields is in structure 'fieldname1,fieldname2'   and ASCDEC needs to be ASC or DEC (or put empty into ASCDEC,  will result in ASC sort)
//======================================================================
command CheckHowmanyRecordsExistsV6 DatabaseID, TableName, UniqueSingleKeyField, DataArrayKeyFields, ResultFields, SortByFields, ASCDEC, @ExistsYesNo @theFieldNames @NbrOfFields @Nbrrecords @DataArrayRecords @OutResult @OutMsg
  --
   -- get the keyFields names from the DataArrayKeyFields 
   put the keys of DataArrayKeyFields into tKeysDataArrayFieldNames
   put the number of lines of (the keys of tKeysDataArrayFieldNames) into NumberOfKeyFields // With only 1 intem this is 0 and not 1 !!
   replace return with "," in tKeysDataArrayFieldNames
   put the number of items of tKeysDataArrayFieldNames into NumberOfKeys // Always use this one
   put tKeysDataArrayFieldNames into KeyFields
   if NumberOfKeys is 0 or NumberOfKeys is empty
   then
      put "Error" into OutResult
      put "Could not get the key fields from the DataArrayKeyFields !" into AddMsg
      exit CheckHowmanyRecordsExistsV6
   end if
  --If nothing is enteren then I need to empty those
   if ResultFields is "ResultFields" then put empty into ResultFields
   if SortByFields is "SortByFields" then put empty into SortByFields
   if ASCDEC is "ASCDEC" then put empty into ASCDEC
   set the caseSensitive to true
   if ASCDEC is not "ASC" or ASCDEC is not "DEC" then put "ASC" into ASCDEC // To make sure nothing goes wrong
   if  DatabaseID is empty then 
      put "CheckHowmanyRecordsExistsV6 routine error!: I need at least to have a database id number of the open database for it to work!" into OutMsg
      Put "Error" into OutResult
   end if
   
   if  TableName is empty  then 
      put "CheckHowmanyRecordsExistsV6 routine error!: You need to enter a table name to get the records from for it to work!" into OutMsg
      Put "Error" into OutResult
   end if
   
   if  DataArrayKeyFields is empty  then 
      put "CheckHowmanyRecordsExistsV6 routine error!: You need to enter of the key search field(s) data to determine what records to search for or get!" into OutMsg
      Put "Error" into OutResult
   end if
   
   if  KeyFields is empty  then 
      put "CheckHowmanyRecordsExistsV6 routine error!: You need to enter at least one key search fieldname from the table as it is defined in the database for it to work!" into OutMsg
      Put "Error" into OutResult
   end if  
   
   if UniqueSingleKeyField is empty 
   then
      put "CheckHowmanyRecordsExistsV6 routine error!: You need to enter UniqueSingleKeyField for version 6 to work!" into OutMsg
      Put "Error" into OutResult
   end if  
   
   if OutResult is "Error" 
   then
      exit CheckHowmanyRecordsExistsV6
   end if
   if ResultFields is empty 
   then 
      put "*" into ResultFields
   else
      if  UniqueSingleKeyField is not in ResultFields 
      then 
         put ResultFields&","&UniqueSingleKeyField into ResultFields
      end if
   end if
   // Build the sql string to get the records
   set itemdelimiter to ","
   put the number of items of KeyFields into nbrofKeyFields
   put 1 into counter
   repeat for nbrofKeyFields times
      put item counter of KeyFields into tFieldname
      if counter is 1 then Put tFieldname &" GLOB '" &  DataArrayKeyFields[tFieldname] & "'" into AndString
      if counter > 1 then put AndString &" AND " & tFieldname &" GLOB '" & DataArrayKeyFields[tFieldname]  & "'" into AndString
      -- if counter is 1 then Put tFieldname &" LIKE '" &  DataArrayKeyFields[tFieldname] & "'" into AndString // Org
      --if counter > 1 then put AndString &" AND " & tFieldname &" LIKE '" & DataArrayKeyFields[tFieldname]  & "'" into AndString // Org
      add 1 to counter
   end repeat
   if AndString contains "GLOB" 
   then 
       replace "%" with "*" in AndString // GLOB needs Unix type wildcards added 04-jan-2017
   else
        replace "*" with "%" in AndString // with no GLOB and there is a  Unix type wildcards then change to % added 23-june-2017
   end if
   // SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC
   // SortByFields, ASCDEC
   if SortByFields is not empty then
      put " ORDER BY " & SortByFields into  OrderByString
      put OrderByString & " " & ASCDEC into OrderByString
   else 
      put empty into OrderByString
   end if
   // You could try adding a COLLATE to the query to specify an overriding collation to use in the comparison, that is case sensitive, if you have it configured on the server (eg Latin1_general_cs).
   --put "SELECT "&UniqueSingleKeyField&" FROM "& TableName&" WHERE " & AndString & " COLLATE Latin1_general_cs " & OrderByString into tSQL // Only get the keyfields and the unique ID field in the result
   //For utf data you might find that MySQL queries with the utf8_general_cs collation are not supported even if the collation is configured on the server, you could use
   //Comparing with binary collation as above should be case sensitive but ordering collation would be thrown out, hence the second COLLATE for the ORDER BY part of the query.
   -- put "SELECT "&UniqueSingleKeyField&" FROM "& TableName&" WHERE " & AndString &  " COLLATE utf8_bin "  & OrderByString & " COLLATE utf8_general_ci " into tSQL // Only get the keyfields and the unique ID field in the result
      put "SELECT "&UniqueSingleKeyField&" FROM "& TableName&" WHERE " & AndString & OrderByString into tSQL // Only get the keyfields and the unique ID field in the result
   // Original. Works but not case sensitive
   --put "SELECT "&UniqueSingleKeyField&" FROM "& TableName&" WHERE " & AndString & OrderByString into tSQL // Org works but not case senistive. Only get the keyfields and the unique ID field in the result
   put revDataFromQuery(,,DatabaseID,tSQL) into tRec
   if tRec begins with "revdberr"
   then
      put "There was a problem accessing the "&TableName&" Table: " & tRec  into OutMsg
      Put "Error" into OutResult
      exit CheckHowmanyRecordsExistsV6
   else
      put "Yes"into ExistsYesNo 
      // Get all field names
      put revDatabaseColumnNames(DatabaseID, TableName) into theFieldNames
      put the number of items in theFieldNames into NbrOfFields
      If tRec is empty 
      then 
         put "No" into ExistsYesNo
         put 0 into nbrrecords
         put empty into DataArrayRecords
      else
         //Build the dataArray
         -------------------------------------------------
         // First get number of records (19-12-2016)
         --put "SELECT COUNT (*) FROM "& TableName&" LIKE " & AndString into tSQL2
         put "SELECT COUNT (*) FROM "& TableName&" WHERE " & AndString into tSQL2
         put revDataFromQuery(tab, cr, DatabaseID, tSQL2) into Nbrrecords
         if Nbrrecords begins with "revdberr"
         then
            put "There was a problem getting the number of records from the "&TableName&" Table: " & tRec  into OutMsg
            Put "Error" into OutResult
            exit CheckHowmanyRecordsExistsV6
         end if
         put Nbrrecords into DataArrayRecords["NumberOfrecords"]
         put NbrOfFields into DataArrayRecords["NumberOfFields"]
         put theFieldNames into DataArrayRecords["FieldNames"]
         if ResultFields is  "*" 
         then 
            put theFieldNames into ResultFields
         end if
         put ResultFields into DataArrayRecords["RequestedFieldNames"]
         put the number of items in ResultFields into RequestedNbrOfFields
         put RequestedNbrOfFields into DataArrayRecords["RequestedNbrOfFields"]
         ----------------------------------------------------
         // Now loop through all selected records
         put 1 into tRecCounter
         repeat for Nbrrecords times
            put line tRecCounter of tRec into theKeysRecord 
            put theKeysRecord into tUniqueKeyFieldData
            --put "SELECT * FROM "& TableName&" WHERE "&UniqueSingleKeyField&" =" & "'"&tUniqueKeyFieldData&"'" & ";" into tSQLString
            put "SELECT * FROM "& TableName&" WHERE "&UniqueSingleKeyField&" =" & "'"&tUniqueKeyFieldData&"'" & ";" into tSQLString
            put revQueryDatabase(DatabaseID, tSQLString) into tRecordSet // revQueryDatabase
            if tRecordSet begins with "revdberr"
            then
               put "There was a problem getting a record from the "&TableName&" Table: " & tRec  into OutMsg
               Put "Error" into OutResult
               exit CheckHowmanyRecordsExistsV6
            end if
            put 1 into theFieldCounter
            repeat for RequestedNbrOfFields
               put item theFieldCounter of ResultFields into theFieldName
               get revDatabaseColumnNamed(tRecordSet, theFieldName, theFieldData)
               if theFieldData begins with "revdberr"
               then
                  put "There was a problem getting a record from the "&TableName&" Table: " & tRec  into OutMsg
                  Put "Error" into OutResult
                  exit CheckHowmanyRecordsExistsV6
               end if
               put  theFieldData into DataArrayRecords[tRecCounter][theFieldName]
               add 1 to theFieldCounter
            end repeat
            add 1 to tRecCounter
         end repeat
      end if
      set itemdelimiter to comma
   end if
end CheckHowmanyRecordsExistsV6
// End check how many records exists V6
----------------------------------------------------------------------------------------------------------------------------------

Code: Select all

#######################################################################################
// Delete records frem DB DeleteRecordsV2
// Removed KeyFields because i get that from KeysData  array.  05-01-2017
// Use like this: DeleteRecordsV2 DatabaseID, TableName, KeysDataArray, CheckIfExistsYN, OutResult, OutMsg
#######################################################################################
command DeleteRecordsV2 DatabaseID, TableName, KeysData, CheckIfExistsYN, @OutResult @OutMsg
   // Do check on parameters
   If DatabaseID is empty or TableName is empty or KeysData is empty
   then 
      put "Error"into OutResult
      put "Not all parameters have data in them!" into OutMsg
      exit DeleteRecordsV2
   end if
   -- get the keyfields from KeysData
    put the keys of KeysData into tKeysDataArrayFieldNames
   put the number of lines of (the keys of tKeysDataArrayFieldNames) into NumberOfKeyFields // With only 1 intem this is 0 and not 1 !!
   replace return with "," in tKeysDataArrayFieldNames
   put the number of items of tKeysDataArrayFieldNames into NumberOfKeys // Always use this one
   put tKeysDataArrayFieldNames into KeyFields
   --
   --set itemdelimiter to ","
   --put the number of items of KeyFields  into NumberOfKeys // Must at least be 1 !!
   // First check if record exists, only if user wants it to be checked.
   if CheckIfExistsYN contains "Y" then
      CheckIfRecordExistsV2 DatabaseID, TableName,  KeyFields, KeysData, OutExistsYesNo, OutRecordData, OuttheFieldNames, OutRecordDataArray
      if  OutExistsYesNo is "No" then 
         put "Error" into OutResult 
         put "Record does not exists, nothing to remove!" into OutMsg 
         exit DeleteRecordsV2
      end if    
   else
      put "Yes" into OutExistsYesNo
   end if
   // End check if record exist
   put "','" into Sep
   put "'" into StartEndSep
   put StartEndSep into dbFieldData
   put the number of items of KeyFields into NumberOfFields
   put 1 into counter
   repeat for NumberOfFields times // Now create the field data part for the sql query
      put item counter of KeyFields  into theFieldName
      put KeysData[theFieldName] into theFieldData
      if counter is 1 
      then 
         put theFieldName&" = "& StartEndSep & theFieldData into dbFieldData
      else
         put dbFieldData &StartEndSep& " AND "&theFieldName&" = "&StartEndSep& theFieldData into dbFieldData
      end if
      add 1 to counter
   end repeat
   put dbFieldData & StartEndSep into dbFieldData
   // Add the data to the database
   // put "DELETE FROM "&TableName&" WHERE "&RemoveKeyfield&" = " & tDBUniqueDataTreeID into tCmd
   put "DELETE FROM "&TableName&" WHERE "& dbFieldData & "" into tSQL
   revExecuteSQL DatabaseID, tSQL
   put the result into tResult
   if tResult is not a number then 
      put "Error" into OutResult
      put "There was a problem deleting record(s) from the table "&gTableName&", result: " & tResult into OutMsg
      exit DeleteRecordsV2
   else
      put "Ok" into OutResult
      put "Record has been deleted from table "&gTableName into OutMsg
   end if   
end DeleteRecordsV2
//============================================================================

Code: Select all

#######################################################################################
// Update multiple fields in Database. RECORD MUST EXIST. 
// Do not need KeyFields  in this version. Get them from the array!
// Do not need UpdateFieldNames in this version. Get them from the array!
// Added @OutMsg  in this version.
// Added error routine
// Usage: UpdateFieldsInRecordV2 DatabaseID, TableName, DataArrayKeyFields, DataArrayUpdateFields, OutResult, OutMsg
#######################################################################################
command UpdateFieldsInRecordV2 DatabaseID, TableName, DataArrayKeyFields, UpdateFieldNamesDataArray @OutResult @OutMsg
   if  DatabaseID is empty or TableName is empty or DataArrayKeyFields is empty or UpdateFieldNamesDataArray is empty
   then
      put "Error" into OutResult 
      put "One or more parameters are empty !" into OutMsg 
      exit UpdateFieldsInRecordV2
   end if
   // Now get the KeyFields from Array
   put the keys of DataArrayKeyFields into KeyFields
   put the number of lines of (the keys of DataArrayKeyFields) into tnumberofKeyFields
   replace return with "," in KeyFields
   put the number of items of KeyFields into nbrofKeyFields
   // Now get the UpdateFieldNames from Array
   // Now get the KeyFields from Array
   put the keys of UpdateFieldNamesDataArray into UpdateFieldNames
   put the number of lines of (the keys of UpdateFieldNamesDataArray) into  NumberOfUpdateFields
   replace return with "," in UpdateFieldNames
   put the number of items of UpdateFieldNames into nbrofUpdateFields
   -------
   set itemdelimiter to ","
   --put the number of items of KeyFields into nbrofKeyFields
   put 1 into counter
   repeat for nbrofKeyFields times
      put item counter of KeyFields into tFieldname
      if counter is 1 then Put tFieldname &" is '" & DataArrayKeyFields[tFieldname] & "'" into AndString
      if counter > 1 then put AndString &" and " & tFieldname &" is '" & DataArrayKeyFields[tFieldname]  & "'" into AndString
      add 1 to counter 
   end repeat
   set itemdelimiter to ","
   -- put the number of items of UpdateFieldNames into nbrofUpdateFields
   put 1 into counter
   repeat for nbrofUpdateFields times
      put item counter of UpdateFieldNames into tFieldname
      if counter is 1 then Put tFieldname &" = '" & UpdateFieldNamesDataArray[tFieldname] & "'" into FieldNamesAndData
      if counter > 1 then put FieldNamesAndData &" , " & tFieldname &" = '" & UpdateFieldNamesDataArray[tFieldname]  & "'" into FieldNamesAndData
      add 1 to counter 
   end repeat
   // fieldname = fielddata,  fieldname = fielddata
   put "UPDATE "&TableName&" SET "&FieldNamesAndData&" WHERE "&AndString into tQuery
    replace tab with "" in tQuery // Make sure there are no tabs in the data because the database field seperator is alo a tab
   revExecuteSQL DatabaseID, tQuery
   put the result into tResult
   if tResult is not a number then 
      put "Error" into OutResult
      put "There was a problem updating the record in table "&TableName&", result: " & tResult into OutMsg
      exit UpdateFieldsInRecordV2
   else
      put "Ok" into OutResult
      put "Record has been updated in table "&TableName into OutMsg
   end if   
end UpdateFieldsInRecordV2
-----------------------------------------------------------------------------------------------------------

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 362
Joined: Tue Apr 10, 2012 9:18 am

Re: Creating accounts for multiple users of a MySQL database

Post by ghettocottage » Sat Dec 21, 2019 6:28 pm

do you need separate user accounts?
you said that this will be on the LAN; will all of the users only be using it on the LAN?

As with everything, there are several approaches to this, but from what you described so far, my thinking is that it would be better to have one MySQL user that is for the apps (for example: app_user ). You can give this user only privileges for each database it will be using, probably:

SELECT
INSERT
UPDATE
DELETE
FILE
CREATE
ALTER
INDEX
DROP

..and then set that user to only be able to access from the subnet of your LAN by setting the hostname of that user, so (if your LAN uses 192.169.1.1) you would set it to 192.168.1.%

Now you can just hard-code that user-account with limited access into your app. It will not work outside your LAN.

If you need separate user accounts, I would not make those accounts a MySQL account, but rather have a database for app-accounts and new users would just be using the above MySQL user-account to create a new record in the MySQL database. Users who already have an account would just sign in with their app-account and your app checks that against the user-accounts table.

Post Reply

Return to “Databases”