Need help on making a password login using mysql lazarus / 16-Databases ---------------------------------------------------------------------- workinghard · 2012-03-01 14:17 So in my program I decided to include a login procedure where users must fill their id and passcode to be able to access their data on a database What do you guys think is the best way to do it? ---------------------------------------------------------------------- BigChimp · 2012-03-01 14:23 Best way? Get me a new crystal ball, this one is broken  Are you creating a Lazarus program? GUI? Command line based? What might be handy: operating system, Lazarus, FPC version. If using a GUI, I'd probably put the mysql components on a data module and use a modal form to get username, password, servername and set the properties of the mysql connection component... ---------------------------------------------------------------------- marcov · 2012-03-02 12:19 A bugreport ( http://bugs.freepascal.org/view.php?id=7203 ) names the events that can be hooked. ---------------------------------------------------------------------- workinghard · 2012-03-11 16:05 What should i do if i want to make a login form? I've already put some edittext boxes for id and password What other tools should i use? And what sql query command should i use? And if i were to proceed to the next form, how can i keep the user to stay logged on until they exit that form? I'm running on win7 x64 and lazarus 0.9.30 with freepascal compiler 2.4.2 abd mysql 5.1 ---------------------------------------------------------------------- JD · 2012-03-11 17:37 Providing security is more than just putting a login form in front of a user. Have you also considered how and where the user id and password are stored? ---------------------------------------------------------------------- workinghard · 2012-03-11 22:14 I put another table for the login IDs on my database ---------------------------------------------------------------------- BigChimp · 2012-03-12 05:22 It helps if you describe what you want to achieve with some architectural detail. Do you want users to use their operating system login and password to log in to your application? Do you want to store separate user id and password for your application somewhere (which your last post indicated you seem to be doing)? (If so, why?) Do you want to use the database login and password? Does your application need to connect to one database or more? If your application is tied to one database, I would use the database login to authenticate the user. Advantage is that you can limit user authorizations in the database. Even if a user connected directly to a database he wouldn't be able to do more than in the application. Additionally, changing passwords, creating users etc can be done either using the standard database tools or, if necessary, perhaps by code in your application. You would remember username and password (or have just one, permanently open database connection on a data module - I'm sure others may have more suggestions) when moving from form to form. This would let the database know the user is who he is... ---------------------------------------------------------------------- workinghard · 2012-03-12 10:55 Yes, i would like to store a separate user id and password in a database since my program will be used by many people The program will be tied into just one database What is a database login by the way? I'm still quite new to this  so i don't really know that much jargons... :p ---------------------------------------------------------------------- ludob · 2012-03-12 11:52 QuoteWhat is a database login by the way?To connect to a MySQL database you need to provide a username and password. When you install a fresh MySQL environment you have just created a root user with its password that has only local access (no network access). So the question is (as BigChimp tried to explain): are you going to use MySQL username/password system as your application login OR are you going to create your own user table with passwords etc. and connect to the MySQL database with a single application login (all users use the same MySQL Username to connect to the database)? If you are a novice I would recommend to use the MySQL username/password system. If the user enters wrong credentials, the connection to the database will simply fail. Creating a new user or revoking an existing one can be done easily with any MySQL admin tool. All info can be found here: http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html ---------------------------------------------------------------------- workinghard · 2012-03-12 12:17 Well, if the condition is suittable I would like to make another table for users But if that's too complicated, maybe i should try the mysql logon system On making a separate table for users, what should i do if i were to make it? ---------------------------------------------------------------------- workinghard · 2012-03-12 12:21 If i were to use the MySQL login system What should i do to make the system work? Is it any different from the usual querying process? ---------------------------------------------------------------------- Ocye · 2012-03-12 12:31 If you are talking about registration, password setup etc. you could open a browser session and use php. A nice script is here: http://www.html-form-guide.com/php-form/php-registration-form.html Access to your data could be given only when user and password are found in the db. ---------------------------------------------------------------------- BigChimp · 2012-03-12 13:24 Quote from: workinghard on March 12, 2012, 01:21:21 pmWhat should i do to make the system work? Is it any different from the usual querying process? Hint: do you have to specify a username and password when connecting to MySQL? Create users and passwords in mysql (using phpmyadmin, mysqladministrator, whatever). Now make sure those users can only access the tables/views/procedures they should access within mysql (see the mysql documentation) Let the user enter username and password on a login form. Set the SQLDB connection's properties to these variables. Done. If you are still thinking about using a separate table with users, think about what happens when the administrator changes the mysql root password (or whatever mysql account you use to connect to the database - looking at your questions I'm guessing root). Do you need to recompile your applications each time that happens? You do know that you could thrash the data of the entire mysql server if your program runs the wrong queries or your users can exploit sql injection (i.e. because of not properly parametrised queries) attacks? What about users trying to get access to your database by sniffing the traffic over the wire, or using a proxy (might not be doable depending on mysql encryption, I don't know about their architecture), decompiling the application (or looking with grep/a hex editor through the executable) etc? Those scenarios might not all be equally likely, but I would strongly suggest starting with mysql users/passwords. If the problem is that you know nothing about mysql users but do know how to create a table, I suggest you first take some time to learn a bit more about database authentication/security. That will surely come in helpful. If you then decide to use your table-based scheme - by all means, be my guest, but then you will have made an informed decision. ---------------------------------------------------------------------- workinghard · 2012-03-12 15:15 Thanks! I better go check the documentations first.. ---------------------------------------------------------------------- Shebuka · 2012-03-12 17:20 I don't think that give to user the login and pass from your db are a good way to secure it, with login and pass a user can connect to your db directly with any application (if that malicious user can hex/disassemble your app to get db username and password, he can also get db name and address) and do whatever he permitted (like select * from some_table, if he has some grants he can request complete db structure, but even if he get only select/update grants he can actually make all your data inconsistent). You can try to brainfck your db username and password with random characters and symbols as also variables name that contain them to make difficult to find them with simple hex and desassemble, but if hacker is really good, you can't protect your client software without a server side application that double check all the things that a user sends. So i think that it will be easier to you to use user table to check for credentials, creating database users is a bit too complicated to easy manage, when you are more secure with all sql stuff you can upgrade your security to db users. ---------------------------------------------------------------------- ludob · 2012-03-12 19:41 QuoteYou can try to brainfck your db username and password with random characters and symbols as also variables name that contain them to make difficult to find them with simple hex and desassemble, but if hacker is really good, you can't protect your client software without a server side application that double check all the things that a user sends.All it takes is to break the program in a debugger at the call to mysql_real_connect in libmysql.  Symbol is exported and parameters are clear text.  No need to be a "really good hacker" for that and it's less work than disassembling the program. Any security manual will tell you that protection by obfuscation is the worst level of protection. The database credentials for an application login has per definition the highest database permissions needed for any user. If the big boss uses the app, the common login credentials has at least the permissions of the big boss.  Quotewith login and pass a user can connect to your db directly with any application ... and do whatever he permittedThe last word is important in your statement. You really give to every user the minimal permissions required to do his job. If you are really concerned about security, you don't give the user update or even select rights to a table but you limit his "view" with views and his write actions with stored procedures.  Views hide the real table structures including vital elements such as keys and foreign keys. Stored procedures accomplish the same for updates.  QuoteSo i think that it will be easier to you to use user table to check for credentials, creating database users is a bit too complicated to easy manage, when you are more secure with all sql stuff you can upgrade your security to db users.   Ever used any of the MySQL admin GUI's? It is just mouse clicks to grant permissions. The keyboard is only needed to enter the username. IIRC on some even the password can be generated for you with a mouse click. Not a single sql line is involved there. Implementing user and password management (creation of users, hashing passwords, revoking users, permissions) is for a novice not easy at all. Or do you suggest storing passwords in clear text in the database? ---------------------------------------------------------------------- workinghard · 2012-03-13 00:56 Quote from: ludob on March 12, 2012, 08:41:12 pmImplementing user and password management (creation of users, hashing passwords, revoking users, permissions) is for a novice not easy at all. Or do you suggest storing passwords in clear text in the database?    To be honest, this is my first idea on storing password data... it seemed much simpler than having to make another setup for password registration. In my database, so far, there are the customer name, their registration number (for the bills), and their bills plus the admin fee on a certain date. The progam itself will have many forms. Now, my question is, if I opened another form after the login page, will the user login (the ID & password) still be used in the new form? And then to filter all of the user data (bills, name, date and time of payment) into that logged on user, which way is the best? using the MySQL login or using the login using the password? (which one is easier) Is there any specific commands that I must use? ---------------------------------------------------------------------- Shebuka · 2012-03-13 09:25 Quote from: ludob on March 12, 2012, 08:41:12 pmQuoteYou can try to brainfck your db username and password with random characters and symbols as also variables name that contain them to make difficult to find them with simple hex and desassemble, but if hacker is really good, you can't protect your client software without a server side application that double check all the things that a user sends.All it takes is to break the program in a debugger at the call to mysql_real_connect in libmysql.  Symbol is exported and parameters are clear text.  No need to be a "really good hacker" for that and it's less work than disassembling the program. Any security manual will tell you that protection by obfuscation is the worst level of protection.But it something to start with. And i'm surprised that libmysql has debug info... QuoteThe database credentials for an application login has per definition the highest database permissions needed for any user. If the big boss uses the app, the common login credentials has at least the permissions of the big boss.Good point. QuoteQuotewith login and pass a user can connect to your db directly with any application ... and do whatever he permittedThe last word is important in your statement. You really give to every user the minimal permissions required to do his job. If you are really concerned about security, you don't give the user update or even select rights to a table but you limit his "view" with views and his write actions with stored procedures.  Views hide the real table structures including vital elements such as keys and foreign keys. Stored procedures accomplish the same for updates.To workinghard this means that he need to put away keyboard for a month and start read some manuals... QuoteQuoteSo i think that it will be easier to you to use user table to check for credentials, creating database users is a bit too complicated to easy manage, when you are more secure with all sql stuff you can upgrade your security to db users.   Ever used any of the MySQL admin GUI's? It is just mouse clicks to grant permissions. The keyboard is only needed to enter the username. IIRC on some even the password can be generated for you with a mouse click. Not a single sql line is involved there.This mean that there must be some human administrator that make all this by hand for every user, make sens for small business. QuoteImplementing user and password management (creation of users, hashing passwords, revoking users, permissions) is for a novice not easy at all. Or do you suggest storing passwords in clear text in the database?    It's much easier for a novice to learn how to implement password hashing with like simple sha algorythm with seed and a simple server side application, that create db users, views and stored procedures. And you can create a singe user that read views, use stored procedures and still use login + hashed password for authorization. I think we can merge on a point that your method is more for pros. users, mine is for a novice. ---------------------------------------------------------------------- Shebuka · 2012-03-13 09:39 Quote from: workinghard on March 13, 2012, 01:56:54 amQuote from: ludob on March 12, 2012, 08:41:12 pmImplementing user and password management (creation of users, hashing passwords, revoking users, permissions) is for a novice not easy at all. Or do you suggest storing passwords in clear text in the database?    To be honest, this is my first idea on storing password data... it seemed much simpler than having to make another setup for password registration.Store clear passwords is always a bad idea. But if you store them hashed, then you need also a password reset mechanism (by email, secret question and answer, telephone or whatever you want, even smoke signals ). QuoteIn my database, so far, there are the customer name, their registration number (for the bills), and their bills plus the admin fee on a certain date. The progam itself will have many forms. Now, my question is, if I opened another form after the login page, will the user login (the ID & password) still be used in the new form? And then to filter all of the user data (bills, name, date and time of payment) into that logged on user, which way is the best? using the MySQL login or using the login using the password? (which one is easier) Is there any specific commands that I must use? Yes, share the connection object that you have used in login form to all other forms, and don't destroy it until you need to disconnect. To filter you use the user ID, no? For security, read post above this one and choose your way. ---------------------------------------------------------------------- workinghard · 2012-03-13 09:44 to filter it, yes, i will use the user ID the program is mainly targetted for small business, so maybe that'll work... ---------------------------------------------------------------------- ludob · 2012-03-13 12:41 QuoteAnd i'm surprised that libmysql has debug info...You don't need debug info to find the address of an exported function. Any library viewer will show you the address. QuoteIt's much easier for a novice to learn how to implement password hashing with like simple sha algorythm with seed and a simple server side application, that create db users, views and stored procedures. And it is also much easier for a novice to get that completely wrong compared to using the build in security. A minimum of reading up on security is required for mysql anyways, unless you connect only as root on localhost.  A new mysql server installation listens only on localhost and the only defined user is root@localhost. ---------------------------------------------------------------------- workinghard · 2012-03-13 14:17 Quote from: ludob on March 13, 2012, 01:41:48 pmQuoteIt's much easier for a novice to learn how to implement password hashing with like simple sha algorythm with seed and a simple server side application, that create db users, views and stored procedures. And it is also much easier for a novice to get that completely wrong compared to using the build in security. A minimum of reading up on security is required for mysql anyways, unless you connect only as root on localhost.  A new mysql server installation listens only on localhost and the only defined user is root@localhost. I'm sorry, I don't really get the meaning of this... ---------------------------------------------------------------------- BigChimp · 2012-03-13 14:46 He means that you shouldn't write your own security layers (password hashing methods, reset procedures etc) unless: 1. you really know what you're doing 2. tried and tested methods do not suffice Often, if you think 2 is the case, you're lacking in 1. Shebuka and Ludo/me propose 2 different ways of authenticating: 1. Roll your own security This requires that your application logs in to the database with every possible permission that any user of your application could want. This will probably mean logging in as mysql root - or another user that has full control over the database. Your application then limits the activities of the user of your application (e.g. by looking up a username and their permissions in some tables). When you're trying to log on to the database as mysql root, you're giving your application full control over the data and it's your job to make sure people can't misuse that. You need to handle permissions management, password management (store passwords encrypted or let any administrator/user with permissions to the user table impersonate any other user, password reset functionality) etc. Your application would need to be either compiled with the mysql root password or need to have it in some configuration file somewhere, or theoretically, an administrator could enter it every time the program starts. Users could try to directly access the database using other tools and log in using the root user. 2. Use built in database security When you let users log in via your application with their own mysql userid/password and proper permissions of those users on the mysql tables/views/procedures, you hand off a lot of those problems to mysql. (Perhaps mysql may even allow users to authenticate with their operating system credentials against an openldap/active directory database, but that would be a bit more advanced as well). You can let administrators use generally available mysql tools to manage your application's users and passwords, or, if you want to, you could create your own user management functionality using SQL to manipulate mysql users. Your application does not need to do much permission checking - when the user tries something that he's not supposed to do, you will get a database error that you can catch. Users could try to directly access the database using other tools and log in using the root user, or any of the other user accounts. Obviously, Shebuka on the one hand and Ludo/me on the other disagree on which way to go: we prefer the latter, while Shebuka prefers the first way. If you're still not clear about the differences, I suggest you open another thread in the database section. This way, you might attract the attention of other people. And there you go, thinking you asked a nice, simple beginner question Still, it was a very good question - many people ignore these problems and end up with something unusable, horribly insecure, or both.. ---------------------------------------------------------------------- sfeinst · 2012-03-13 16:15 There is a third option (I am not recommending it, just pointing it out to confuse the issue more ) Do a hybrid of options 1 and 2.  You have a set of IDs that have different permissions and you track your various users in your own location, but login to the DB based on which DB user is associated with the logging in user.  In this way you can limit a set of users without having to define each to the database (if for some reason you find that type of management easier). In a way each user is associated with a "group" and its the groups that have different logins to the database. ---------------------------------------------------------------------- Shebuka · 2012-03-13 16:40 I only like to point that i never says to login as root, i'v pointed on the end of my post that you can create a user that has all grants restrictions that you need, even a big bos doesn't need to have create/drop table grants nor create user grants. (some sort of what sfeinst says) Yes, this in not the level of security that db user approach grants, but it's still a valid and less complicated approach. Actually a default mysql setup root user can login only from localhost and afaik you can't change this. P.s. How you make a password reset procedure for db users? How to associate id's in db with db user? It's not so easy to use db users approach. ---------------------------------------------------------------------- ludob · 2012-03-13 17:38 QuoteActually a default mysql setup root user can login only from localhost and afaik you can't change this.As root do: Code: [Select]GRANT ALL ON *.* TO root@'%' identified by 'password'; or, if you want to play it dirty: Code: [Select]UPDATE mysql.user SET host='%' WHERE host='localhost' AND user='root'; FLUSH PRIVILEGES; QuoteP.s. How you make a password reset procedure for db users? A user can change his password with Code: [Select]SET PASSWORD = PASSWORD('newpass');Easy to make a password reset procedure with that. QuoteHow to associate id's in db with db user? It's not so easy to use db users approach.The logged in user is Code: [Select]substring_index(user(),'@',1)So to create a view on a table that returns only the records created by a user you do something like this; Code: [Select]CREATE VIEW `MyView` AS SELECT only,needed,columns FROM `table` WHERE theuser = substring_index(user(),'@',1) ; theuser can be filled in with a before insert trigger. Simple and no way to mess around with other user's data. ---------------------------------------------------------------------- Shebuka · 2012-03-13 17:56 QuoteQuote from: ludob on March 13, 2012, 06:38:38 pmActually a default mysql setup root user can login only from localhost and afaik you can't change this.As root do: Code: [Select]GRANT ALL ON *.* TO root@'%' identified by 'password'; or, if you want to play it dirty: Code: [Select]UPDATE mysql.user SET host='%' WHERE host='localhost' AND user='root'; FLUSH PRIVILEGES;When i was testing MySQL Cluster NDB i have tried do this, and it refuse my connection, no matter how hard i'v tried to set host, it refuse even a specified remote ip from which a was connecting. Maybe it was a bug/feature of that particular distribution. QuoteQuoteP.s. How you make a password reset procedure for db users? A user can change his password with Code: [Select]SET PASSWORD = PASSWORD('newpass');Easy to make a password reset procedure with that. QuoteHow to associate id's in db with db user? It's not so easy to use db users approach.The logged in user is Code: [Select]substring_index(user(),'@',1)So to create a view on a table that returns only the records created by a user you do something like this; Code: [Select]CREATE VIEW `MyView` AS SELECT only,needed,columns FROM `table` WHERE theuser = substring_index(user(),'@',1) ; theuser can be filled in with a before insert trigger. Simple and no way to mess around with other user's data. Nice still you need some extra effort to make it usable, you need to store somewhere email, secret question and answer or something else. Not really a problem with sort of general password_reset_user that can access only a password_reset_helper table/view. ---------------------------------------------------------------------- ludob · 2012-03-13 18:36 QuoteNice still you need some extra effort to make it usable, you need to store somewhere email, secret question and answer or something else. Not really a problem with sort of general password_reset_user that can access only a password_reset_helper table/view. In a business environment, secret questions aren't used a lot. An app admin creates a user, assigns roles/permissions and a temp password is created that the user has to change before doing anything. A simple method is to generate temp passwords that contains a fixed pattern and have the app trigger a change password screen when the user connects with such a password.  For example the temp password is something like '7g#9qfg8tg_5' with '7g'????????'_5' meaning 'change me' for the app. No db user changes required (a SET PASSWORD for the current user will do) and little chance that the user selects a 12 digit password that starts with 7g' and ends with '_5'. In case the user loses his password, the admin creates a new temp password and that's it. ---------------------------------------------------------------------- workinghard · 2012-03-13 22:49 So, handing over the task to mysql will make the whole task easier? ---------------------------------------------------------------------- ludob · 2012-03-14 07:38 Quote from: workinghard on March 13, 2012, 11:49:34 pmSo, handing over the task to mysql will make the whole task easier? That is not the right conclusion. As Bigchimp resumed correctly there are 2 different approaches to security: use db security or use custom app security. The same goes for the business logic: have the business logic in the db (stored procedures, triggers, etc.) or have the app do the work using simple tables. With business logic l mean processes like: - if an article is added to an order, the article is substracted from the stock - if the stocking level for an article drops below a threshold a todo is generated for the buyer Both approaches have their pros and cons as well as their supporters and detractors. What is common to both approaches is that they require a certain level of database knowledge. The all in database approach requires more db knowledge than the other but improves reliability and security and makes life easier for the application development. The important message you need to retain from this thread is not "use this or that approach" but the need to read up on databases and on how to use databases from an application. You'll find plenty of tutorials on the web. Only then will you be able to make the decision that is right for your particular circumstances. One side note: the debate on security and business logic is a little bit overshadowed by your choice of MySQL. MySQL is fine for simple non-business critical applications but I would never run a business on MySQL. If you want an open source db use postgresql or firebird. ---------------------------------------------------------------------- BigChimp · 2012-03-14 08:39 Especially agreed with Ludo's comments on mysql versus other databases. If you have the choice, I would certainly look into Firebird, possibly PostgreSQL (for larger environments). Apparently MySQL often change the API their drivers expose from release to release so using a newer driver with a DB connection library like Zeos or SQLDB is not always possible. This means you have to wait for a new SQLDB/Zeos connector to be released. I'd stay away from mysql if you can... Note: mysql is still a very wide-spread database, but I think the licensing restrictions, and their new owner, Oracle, does not help. Oracle's interest presumably lies in moving people from mysql to the Oracle DB. Even if you discount that, Oracle's track record with open source software has not been... stellar (OpenOffice, Java), leading many (well, at least me) to doubt the long term viability of mysql. Apart from that, mysql still has an aura of "toy database" back from the days when it didn't do basic RDBMS things like referential integrity (well or at all, can't remember) and it was heavily used in web sites with PHP etc. This criticism may no longer be correct, but the stigma still attaches to it. Due to these reasons, I think a lot of people that used to automatically chose mysql would now go to sqlite for embedded development and PostgreSQL for client/server. And the Delphi oriented and/or smart people might choose Firebird *) Yes, Firebird fanboy here... Even more OT: I think the database tutorials on the wiki might do with sections on data modules (i.e. reusing connections/query objects throughout an application), and e.g. sqldbtutorial1 might benefit from an added login form... the thing is, it's coming in at the bottom of my todo list... and I presume a lot of other peoples', too.