November 19th, 2003, 05:50 AM
Getting UserID from Web Application
Have a Web Application written in Java and the backend is developed in Postgres. I am currently creating history tables which are updated everytime a UPDATE,INSERT,DELETE occurs to the orginal table. The history table is a mirror of the original with 3 extra fields one of which is app_user_id who is responsible for the changes.
I would like to write a function that will be able to access the Web Application side and retrieve the current app_user_id so that the value can then be stored in the history table. The details of the user are stored in my app_user table (such as name, surname, password etc) in the database which is used for validating user access when the user logs into the system. Please note I need the web application user as opposed to the Database user.
Can anyone please let me know what the best possible option is for this kind of procedure or possibly refer me to some sites. I will really appreciated the assistance.
November 19th, 2003, 08:37 AM
make a table called 'users' with id and name, password. Have a page where they 'LOGIN' to the page. I think there is an option in postgresql.conf near the middle/towards the bottom that says #Enable encrypted passwords. The stuff I found in the manual kind of looks like it says you should make a true database user for each client if you want secure authentication. BUT you can restrict them to accessing tables individually, those that you name.
Then when the user logs on, just have that passed as a parameter to each web page they visit. When you run the java app, you can just check that parameter. Then, to avoid normalization problems, just put the user's ID into your "mirror" table (I think that is 3NF, could be BCNF, depends on the rest of your mirror table). If you created another table to keep track of who modified the record last, you could either use their system name (which you could get out of the system table with users, maybe pg_user?) or the name that you have in your name/ID table.
I found this quote