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
Creating accounts for multiple users of a MySQL database
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 720
- Joined: Thu Sep 11, 2014 1:49 pm
- Location: The Netherlands
Re: Creating accounts for multiple users of a MySQL database
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
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
-----------------------------------------------------------------------------------------------------------
-
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: Creating accounts for multiple users of a MySQL database
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.
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.