How do I filter the users?
lazarus / 16-Databases

----------------------------------------------------------------------
workinghard · 2012-03-15 09:12

I need to be able to filter the users in my program. There are some
tables for basic identity, bills, and registration numbers. These
users (the normal users) are granted permission to use all those
tables, but i don't really know how to filter the records so that it
links to specifically that user
what should i do?

----------------------------------------------------------------------
JD · 2012-03-15 12:34

There are two options to consider:

a) The filter is for the client program which displays the data

In this case, the appropriate use of queries or even stored procedures
can limit what the user sees/edits to his access level.

b) The objective is to give users permission to view/edit tables on
the database end

In this case, you better look at the documentation of the database you
are using and get acquainted with SQL commands like GRANT. You can
read this brief tutorial for more information
http://databases.about.com/od/security/a/accesscontrols_2.htm.

----------------------------------------------------------------------
workinghard · 2012-03-18 09:23

About the first option, how do i do it?
There are many user in my table and i need to filter it to
specifically their data

So, if, for example if steve logs in to his mysql database account, in
the memo pad, it will display steve's recent payment and date, not
others
How can i do it?

----------------------------------------------------------------------
BigChimp · 2012-03-18 11:50

Quote from: workinghard on March 18, 2012, 10:23:04 amAbout the first
option, how do i do it?
There are many user in my table and i need to filter it to
specifically their data
The appropriate use of queries, as JD mentioned.  Something like
(don't know if this is valid SQL, let alone MySQL SQL):
Code: [Select]// assuming you have a Bills table with ID, Year, Month,
Total, Description, User fields....
// see note below on joins
SELECT ID, Year, Month, Total, Description FROM Bills WHERE User =
'steve';

Depending on MySQL functionality, you could put this into a stored
procedure... or used a parametrised query
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Parameters_i
n_TSQLQuery.SQL
Something like: (air code as well, does probably not compile):
Code: [Select]StatementQuery.SQL.Text:='SELECT Year, Month, Total,
Description FROM Bills WHERE User = :TheUserIWant;';
StatementQuery..params.parambyname('TheUserIWant').asstring :=
'steve';

Quote from: workinghard on March 18, 2012, 10:23:04 amSo, if, for
example if steve logs in to his mysql database account, in the memo
pad, it will display steve's recent payment and date, not others
How can i do it?
See above; it might be a good idea to link your records to a mysql
user id, something like a table ElectricityUsers with fields ID,
MySQLUserName.
Make the ID an autonumber/autoincrement field.
Now you get e.g.
Code: [Select]ID MySQLUserName
2  Bill
3  Steve
18 John
... in this table you can also add his real name, birthdate, and other
relevant info about that person.

Then you can store the numeric userid in all your tables instead of
the full name:
eg
Bills table with ID, Year, Month, Total, Description, User fields....
becomes
Bills table with ID, Year, Month, Total, Description, UserID fields.
UserID is the number for Steve

When a user logs in, you know his username. You can retrieve his
ElectricityUsers.ID value and use that in all your queries.

I suggest you first try and get some database/Lazarus experience by
going through the tutorials, e.g.
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1 on the wiki page,
if you haven't already.

Good luck,
BigChimp

----------------------------------------------------------------------
workinghard · 2012-03-18 14:19

how do i link up the mysqluser with the incremental id number then?

should i make a new table? but that will defeat the purpose of doing
it using mysql login right?