[Solved] Multiple user authentication with split database

[mikemechanic]

I'm creating a new database using Base as a front end and HSQLDB (or maybe H2) as a split backend. The database will be used by 3-4 people (not simultaneously) on a shared computer. Since we'll be using it to track compliance with state law, I would like to be able to log changes to the database with a date/time stamp and user name. It would also allow me to limit the damage ordinary users could do using the database backend roles.

In order to do this, each user would have to log in with their unique username and password. The connection string examples I've seen in this forum all include a generic username/password combination of "SA" and <blank>. Is there a way to force Base to prompt for a user name and password when the frontend .odb document is opened?

The only other way I can think to do this is to give each user their own copy of the .odb file with their own username/password embedded in the connection string, but I can't guarantee that each user will stick to their own copy. Plus when I modify the .odb file I have to update all of the private versions.

Thanks for your help.

[F3K Total]

Hi,
if you have a look on this post, especially on the .script file as described, you should find in there, what you need.
The attachment is an example, how to grant Roles to users. That means you're able to prohibit e.g. write-access to selected tables for selected users.
If you like to select the current user and timestamp, you can use this SQL-statement

Code: Select all

SELECT DISTINCT CURRENT_USER, CURRENT_TIMESTAMP FROM INFORMATION_SCHEMA.SYSTEM_USERS

and insert the user and Timetamp by code.
It should also be possible to create a trigger for that INSERT Job.
R

[mikemechanic]

F3K, thanks for the speedy reply, and for the sample files. Those will probably come in handy when I'm sorting out how to add roles to the database.

It doesn't exactly solve my problem though. When I opened your example file, it logged me into U1 automatically, and I had U1's permissions. What I would like is for Base to prompt for a user name and password at each startup. That way, I could guarantee that Base is logging the activities of the actual person that is adding or updating records in the database. If it automatically selects the user and gives them privledges, User #2 could open up the file and add records under U1's login, without realizing it, defeating the purpose of all the logging.

[Sliderule]

mikemechanic:

You said / asked:

Is there a way to force Base to prompt for a user name and password when the frontend .odb document is opened?

Since, you are connecting to an EXTERNAL DATABASE ( whether HSQL or H2 or other ) . . . you can have the user be prompted ( from a pop-up ) to input both User Name and Password. NOTE: this will NOT be asked for, when the OpenOffice / LibreOffice Base file ( *.odb ) is first opened, but, rather the first time the program attempts to OPEN ( read ) the database.

So:

1. Open your OpenOffice / LibreOffice Base file ( *.odb )
2. From the Menu: Edit -> Database -> Properties...
3. In the pop-up User Authorization:
   1. For the User Name, remove SA
   2. Click on: Password required

Now, when OpenOffice / LibreOffice Base file tries to OPEN ( first read ) the database, the user will be prompted to enter the information, and, the database back-end will only permit the operations you have authorized for that user.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[mikemechanic]

Didn't quite work. I tried it from F3K's sample database. I removed the U1 username from the database properties, saved and exited. Then re-opened the .odb file.

Upon trying to access a table, I got a dialog: "The connection to the datasource "Grant_Role" could not be established. Invalid authorization specification." I could then go to Edit > Database > Properties and add U1 as the user name. It would then proceed _without_ asking for a password even though the "password required" box was checked (from F3K's referenced post, the password needed to be "1111"). I verified that U1 was actually logged in (there was U1 in the lower right corner of the status bar of the Base window, and I could read the table Persons and Read/write the table Persons_Data.)

[Sliderule]

Since, I have not downloaded the file from F3K Total . . . I will leave it him/her to help further.

Sliderule

[F3K Total]

Hi,
what i wanted to show you with the sample-file where these parts of the GB.script file:

Code: Select all

...
CREATE ROLE SUSER
...

to create a different Role, named SUSER then DBA, which is the total access.

Code: Select all

...
CREATE USER SA PASSWORD ""
CREATE USER U1 PASSWORD "1111"
...

to create Users, with or without passwords

Code: Select all

...
GRANT SELECT ON PUBLIC."Persons" TO SUSER
GRANT ALL ON PUBLIC."Persons_Data" TO SUSER
...

to define the rights for role SUSER, means a user having this role can only SELECT (read) on the table "Persons" but ALL (read and write) on table "Persons_Data"

Code: Select all

...
GRANT DBA TO SA
GRANT SUSER TO U1
...

to grant the roles to the users

Means, if you'd like to add a new user with the rights of U1, you first login as USER SA (having the permission to create new USERS), no password, and then execute e.g. these SQL-Statements:

Code: Select all

CREATE USER U2 PASSWORD "2222";
GRANT SUSER TO U2;

by using Tools/SQL... EDIT:or just add these lines to the GB.script file. Better only use Tools/SQL ..., otherwise it's dangerous and you can loose your datas.
If you then close OpenOffice and start the DB again, you will be asked for user and password, if you change to the tables or open a form as Sliderule already wrote here

NOTE: this will NOT be asked for, when the OpenOffice / LibreOffice Base file ( *.odb ) is first opened, but, rather the first time the program attempts to OPEN ( read ) the database. ....
...Now, when OpenOffice / LibreOffice Base file tries to OPEN ( first read ) the database, the user will be prompted to enter the information, and, the database back-end will only permit the operations you have authorized for that user.

R

[mikemechanic]

I got it to work. I created a small database with users and roles and it now prompts for a user name and password consistently. For the benefit of anyone else reading this, after adding the users to the database I had to completely exit out of all OO windows and restart before it would recognize the new users and allow them to log in.

Thanks everyone for your help.