I am developing a desktop app for a youth club. There are multiple clubs around the country that need to connect to different databases. This is to keep all data separate and no cross over.
Another way to put it is to have database connection selected dynamically after login.
Currently I have a database for each club, there is also a database with generic info that each club can access.
What is the best way to login so I don’t need to compile an app for every club with the database login credentials? I guess this would be applicable to desktop and web.
I had thought of using a login table with very limited options, like read only, with every one from every club that has a login. This would then flag the main app with which club to login to and set the appropriate database credentials.
In a desktop app, don’t store any server login data in the app. Ask the user on first start for the credentials, test the connection and store them in an encrypted text file (or database) local on the computer.
I take it one step further and when the user logs in, he identifies the database (the club db) and logs in to the database itself. Let the db handle user authentication. I do store the club id and username locally, to auto fill those fields, but never the password. Another benefit is a user can have access to more than one club db, with separate credentials for each.
You NEVER want to store database access credentials on a remote device, encrypted or not. PKCE is designed to avoid that by requesting the user logs in with specific security scopes that allows access to specific resources. Couple that with 2FA & you’ve got the current standard for secure access.
Are you suggesting that they log directly into the database? If so that would require many additional username/password credentials for the mysql db.
The app currently stores the credentials for mysql login.
Currently I am implementing a single login to the mysql db and an admin user can create a new login for another person directly in the app. Then the app checks credentials using an encryption and salt test.
However Wayne is saying that this shouldn’t ever be done.
Yes, all the users are mysql users. I see, however that your app is cloud based, while mine is desktop/LAN. That makes it more complex. Wayne has a better solution for that.
I would get a CubeSQL Server and have all each club connect with their same app, but different username/password/SQLite file. With the SQLite file database, each set of data is entirely siloed, but you have the option to have shared data if required.
Yes agree with these sentiments. I have almost exactly the setup you describe - one ‘central’ sqlite db with common info and then separate sqlite db’s for each instance. Data isolation is a thing. Does have downsides of course but clients like it.
There are a couple of ideas here. One you could have an authentication database for authenticating your users, no matter what app they have, and then you could have a column somewhere in the database that will tell you what database to use and then you can continue using that database going forward.
Another thought would be that you could add an additional login field for instance name or something like that and based on that, it would use a particular database based on the instance name used. Then instead of having a authentication database, it would authenticate using the instance_name from the login screen.
Definitely some good ideas here. Hope some of them work for you.
I do have an example of a desktop application connecting to Xero.com using the PKCE workflow and I’ll be putting together a movie demonstrating this with a look at the code which I’ll share on GitHub. I’m currently building out the server side for another project & expect to have that ready by the end of March. All code is native Xojo.
Someone should remind government departments of the risks arising from shoving everything in one database. Starting with birth certificate, full drivers license, full passport (with photo) and linked to everything else such as property ownership, superannuation, bank accounts, tax accounts, company directorships and so-on.
Hack one system and you have access to the lot, in my country.
I like this idea. Although it is not best practice, for the handful of clubs that will be using the database it will be a good solution. If there are more clubs that get on board then I will definitely look at Waynes example and see how it will integrate Xojo with the PKCE workflow.
Hi Wayne
Watched your demo Connecting to Servers via OAuth 2.0 Using Xojo - YouTube fantastic work.
Can I ask how would I implement this if I am using my own server? Not one that has its own OAuth API.
Thanks for the effort and advice