Hi All,
I have an android app that reads and writes data to a hosted mySQL database. this works fine. My question is that i need each user to only be able to view/read/write to their own data and not see everyone elses records etc. I'd expect i need my app to authenticate or have some sort of 'login' screen and maybe verify via an email??. Is there any easy ways of controlling this? Can I simply ask a user to login using their facebook login for exxample? I'd be looking for a 'one time' login that the app remembers for all future use so the user doesnt have to re-login each time they use the app...
Any suggestions?
Thanks in advance.
Gary
Best way to manage different users on remote MYsql?
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
-
ghettocottage
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: Best way to manage different users on remote MYsql?
If you have each users data in its own table, you could create a mysql user for each person and limit that user to their own table.
This is probably not the best approach if you have a lot of users, or want people to be able to change their own passwords and such. But if you just have a handful of users and you were the full time admin it could work.
How do you access the database? Are you using PHP or Livecode server as an intermediary?
This is probably not the best approach if you have a lot of users, or want people to be able to change their own passwords and such. But if you just have a handful of users and you were the full time admin it could work.
How do you access the database? Are you using PHP or Livecode server as an intermediary?
Re: Best way to manage different users on remote MYsql?
My app i am developing connects to my remote Mysql database using revOpenDatabase livecode command. I am hoping for thousands of users so having a table per user is probably not ideal. Currently i have a 'users' table setup which has 1 record per user which contains user name, user number, email address etc, and then i have a second table with all the real 'data' in it which has the user number as an indexed field.
-
ghettocottage
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: Best way to manage different users on remote MYsql?
I think someone more knowledgeable should chime in on this.
It sounds like you have your MySQL database open to connect from anywhere, which is (to my limited understanding) very risky.
A different approach is to have PHP or Livecode Server act as in intermediary, and you send your queries there. Here is a post where we discuss this in detail:
http://forums.livecode.com/viewtopic.php?f=12&t=23614
It sounds like you have your MySQL database open to connect from anywhere, which is (to my limited understanding) very risky.
A different approach is to have PHP or Livecode Server act as in intermediary, and you send your queries there. Here is a post where we discuss this in detail:
http://forums.livecode.com/viewtopic.php?f=12&t=23614
Re: Best way to manage different users on remote MYsql?
So I am now using Livecode server to connect to my MySQL database which is working perfectly. What I need is to work out how to manage different users. I.e. is there a 'best practice' for managing logins etc? I.e. A new user would first need to create a login before they can use the app? I am sure many people have had to manage this same task and also manage problems such as 'lost password?', 'user name already taken' etc. Is there a demo app or somewhere that this is explained how to manage? Can I use people's facebook login instead of creating their new one for this app or their google login?
thanks in advance.
thanks in advance.
Re: Best way to manage different users on remote MYsql?
Hi Gazzafish,
Once you've figured out the a more secure way to connect to your MySQL (the link provided is the response above looks to be a very promising solution if your using LiveCode server), you could possibly create a login table in your mySQL, and put all the things you're looking to include for a user in as a column.
Make the username column unique so if two people try to use the same username it'll toss up an error message, and in your app you can customize what that message will say. Then encrypt the password field and set up your query on login to decrypt. possibly look at BINARY when the person is logging in too. it may help check whether the password has capitals in the correct place, and such.
As for "forgot your password" when they set up an account have them select a security question and answer, connect it to their email or username or what ever your main identifier is (something they'd remember) and use that to do password changes, or updates. You may want to set it up so it generates an email to the individual that could provide an added layer of security.
I wish i had an answer for you on the google/facebook login, but I haven't really looked into that before.
Not sure if anyone else has a cleaner/more secure method of doing this, but that may help you get started or give you some ideas to explore.
Someone please correct me if I'm wrong, but the option I've listed here is pretty similar to how Wordpress handles username, and password logins. Though there are a lot of different methods to doing a login system.
Hope this helps!
-Sean
Once you've figured out the a more secure way to connect to your MySQL (the link provided is the response above looks to be a very promising solution if your using LiveCode server), you could possibly create a login table in your mySQL, and put all the things you're looking to include for a user in as a column.
Make the username column unique so if two people try to use the same username it'll toss up an error message, and in your app you can customize what that message will say. Then encrypt the password field and set up your query on login to decrypt. possibly look at BINARY when the person is logging in too. it may help check whether the password has capitals in the correct place, and such.
As for "forgot your password" when they set up an account have them select a security question and answer, connect it to their email or username or what ever your main identifier is (something they'd remember) and use that to do password changes, or updates. You may want to set it up so it generates an email to the individual that could provide an added layer of security.
I wish i had an answer for you on the google/facebook login, but I haven't really looked into that before.
Not sure if anyone else has a cleaner/more secure method of doing this, but that may help you get started or give you some ideas to explore.
Someone please correct me if I'm wrong, but the option I've listed here is pretty similar to how Wordpress handles username, and password logins. Though there are a lot of different methods to doing a login system.
Hope this helps!
-Sean