How to create a login for a Postgresql/Access setup

[cigarprofiler]

Looking for advice on where to start educating myself on the following matter.

My project consists of a Postgresql back-end/Access 2016 front-end combination. For the time being, the two connect through a system DSN ODBC connection.

I have come to a point where I need to identify users for two purposes:

1. To tie their actions to their user names;
2. To secure the back-end (they may read everything, but write only to a few tables).

I have learned from Google that security is best handled at the back-end. I have a friend who can help me with that but he knows nothing about Access.

What I do not know, and cannot find anywhere on Google, is how to handle things at the front-end. I would expect to have to create an Access login form on start-up to inform the back-end who the user is, but I can't find anything on that. All I get is how to create the form itself, nothing on the underlying functionality required.

Can anybody point me in the right direction so I can read up on how to do this?

 

[RuralGuy]

Have you searched for "LogIn Form" on this site yet?

 

[cigarprofiler]

Have you searched for "LogIn Form" on this site yet?

I had done that, but that didn't seem to answer my questions. Still, I took your suggestion and while creating a login form, some things dawned on me that I wanted to share in case another noob stumbles onto this thread:

1. To tie their actions to their user names: this is not a security issue, it's just a matter of logging who performed a certain action. So I created a login form and when the user hits the login button, another form is opened where the user can perform a number of actions. Meanwhile, I leave the login form open but invisible, so each time the user performs an action on the second form, I can reference the text field on the login form that contains the user name, and store that together with the action.

My hunch is that there is probably a better way to pass the user name to the next form but I haven't figured out the syntax yet.

2. To secure the back-end: I solved this by creating a new ODBC-connection and leaving the "Save password" option unchecked. This causes an ODBC login screen to pop up asking for the backend PostgreSQL password.

Again, this is probably not the best solution (the ODBC login screen looks horrible and may even be a security risk) but for now, it will do.

 

[The_Doc_Man]

As to remembering the user for later actions, there are several ways to handle it.

As you noted, keeping the login form open but invisible allows you to remember the login name from the text box. That is simple enough, but the syntax gets unwieldy.

If you are in a domain-based environment and the user logs in using the same name as the domain's login name, then Environ("Username") returns the name the user used to login to the workstation. If you log in using a system with different usernames for the database vs. the workstation login, or if a formal domain environment is not in force, this might not help as much. But if you DO have a formal domain setup, your security folks would like such an approach.

If you put all of your security-related code in a general module and declare some of the variables as Public in its declaration area, that code hangs around for the life of the session unless you take a really UGLY trap that forces someone to use the RESET option on the little debug pop-up. You could put your username there and reference it from the "global" location as long as you have good trap handlers that prevent you from seeing that little debug pop-up.

There is also a way for you to create temporary variables (TempVar object, which you can look up in this forum or from the general web) which persist for the life of the session. The things you can create with a TempVar are limited - no object variables, for example - but you can surely store non-complex data items like usernames or internal user ID numbers or other security-related things that you wish to remember across multiple forms. If you do use the TempVar object, read up on the MSDN article for their suggestions on handling and removing things from it. It has a small amount of housekeeping overhead.

 

[RuralGuy]

Glad to hear you got things sorted. Thanks.

 

[cigarprofiler]

Thanks for the suggestions Doc_Man. I've seen similar suggestions on my internet searches but I am not yet skilled enough in VBA to pull them off. Right now, I'm still stuck at subroutines and will probably take a stab at functions next.

Some background info on the purpose of my project: I want to use Access to create a working front-end, so at some point a web developer can take it as a working model to turn into a web application.

I wrote a functional design but the cost of developing the site off of that is just too high for a hobby project. So I figured I'd put in the time to design the screens and functionality in Access myself and reduce the web development lead time.

In the meantime, I intend to use Amazon's AppStream to share the Access app with a few trusted test users to get all the bugs out.