Base will not ask for db credentials after closing and reopening

[marus_b]

Version: 7.5.2.2 (X86_64) / LibreOffice Community
Build ID: 53bb9681a964705cf672590721dbc85eb4d0c3a2
CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: en-CA (en_CA); UI: en-US
Calc: threaded

MySQL connected via native connector.

The background LO processes which are responsible for the “LibreOffice Quickstarter” feature manifest the following behavior (at least in the aforementioned configuration):

When reopening a previously closed session of Base, which is connected to MySQL database, the MySQL credentials still stay in memory and LO is not going through the verification process once again. If I turn off the option called “Load LibreOffice during system start-up”, go into task manager to end those processes, then the software once again starts asking for the credentials, specified on MySQL server, each time you open Base session.

I do not have means of verifying that with other connectors, as I have never set them up.

[RobertG]

It’s the normal behavior here: Every connection to a database will be disabled automatically when the whole LO will be shut down. Might be you could close a connection when closing the database file by macro. Will have a look.

Have seen I have tried this before - but doesn’t work. I could kill the connection ID, but the connection will be set new when reopening the Base document without closing LO. Don’t know if the password has been cached somewhere else.
Code for killing the connection through Tools → SQL:
KILL CONNECTION_ID();
But you will get an error when closing the Base document.

[marus_b]

I wasn’t even using a macro, just conventionally closing the program by pressing “x” in the top right corner.

I touched on this subject because of possible security concerns. Imagine a data entry clerk working on a database which contains sensitive information. They, close the base application, and walk away from their computer without locking the operating system. Assuming that the “Load LibreOffice during system start-up” is enabled, anyone can literally walk up to their workstation, open the database and have direct access.

[mariosv]

Don’t look at best sample about safety way of work.

Could be the option

https://help.libreoffice.org/7.6/en-US/text/sdatabase/dabawiz03auth.html?&DbPAR=BASE&System=WIN

[RobertG]

Problem is: You will be asked for the password when opening the Base file the first time. After this first opening you could close the Base file, reopen it - no password is asked for. The connection will automatically work. You could try to kill the connection, but it will be opened again when reopening the Base file.

You have to close LibreOffice complete. So you shouldn’t start LO with Quickstarter. LO is running in the background if Quickstarter has been enabled. And: There won’t be asked for a password again in Base.

[mikekaganski]

See if this has something to build upon.

[Wanderer]

@RobertG

could kill the connection ID, but the connection will be set new when reopening the Base document without closing LO. Don’t know if the password has been cached somewhere else.

I’d assume this is stored in the connection pool. @mikekaganski recently linked to the wiki in this thread (ok - he answered himself at the same time …)

Assigning DataSourceName to mailmerge locks .ODB and fails to unlock later

The following partial VB script assigns a DataSourceName to a mailmerge template and then merges to create a new document. At the moment that the DataSourceName is assigned, an OS level read-write lock is applied to the .ODB file - prohibiting other users from using it. Note that even after the original template is disposed, the file lock remains until Writer is completely closed and the desktop instance is unloaded. Environment: Windows Server 2019. LibreOffice 7.3.7.2 (x64) The kludge/work…

Some discussion also here:

Whats the effect of "Connection Pooling"?

In LO you could set values for connection pooling: Tools → Options → LibreOffice Base → Connections. It has nothing to do with holding a connection for example to a MySQL server somewhere in the www. This kind of connections will be limited by the server, not by a driver of LO. Connection pooling seems to be used in a multi user environment. A connection from one user to the database will be saved for the pooling delay. So another user could use this connection and mustn’t establish a new conn…

Just from reading the wiki I don’t see a call to cause the pool to forget a connection. It seems being fully transparent to the client is a feature…

[Wanderer]

@marus_b

walk away from their computer without locking the operating system.

Concerning security, this is the main failure. With access to the desktop I can do a lot of things, wich LibreOffice can not prevent.
.
I have to check, but I remember also have seen the possibility to store the password/connection-string in some settings for odbc-drivers - indipendent from LO.

[mikekaganski]

@RobertG: while looking into that other Q, I saw a number of bugs in the MailMerge service implementation, making it impossible to pass e.g. ActiveConnection there instead of specifying a registered DB name. But generally, we have a way to create isolated connections; and while I myself never worked with them, the isolated bit looks promising wrt avoiding sharing (and pooling?) of the connection…

[marus_b]

@Wanderer After reading all the information within the links in the comments above I can see how design of such complex software package comes with a rather large degree of compromises. It makes sense to follow that logic, and it also makes perfect sense what you have just stated about security in general.

[RobertG]

Have tried it with getIsolatedConnection.

thisDatabaseDocument.CurrentController.DataSource.getIsolatedConnection("username","password")

This connection is so isolated I couldn’t open the query pane or table pane without getting a new dialog for username and password. Without this dialog appears: Connection without password is denied. Tried it also with activating connection pooling. Doesn’t change the behavior.

[sokol92]

Assigning an empty value to the attribute Password resets the password.
The next time the .odb file is opened, a new authorization will be required.

Sub TestDbConn
  Dim oBaseContext, oDataSource, oDoc, file
  oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")

  file=ConvertToUrl("C:\Temp\PostgreDummy.odb")
  oDataSource=oBaseContext.getByName(file)
  oDataSource.User="test3"
  oDataSource.Password="test3"
  oDoc=StarDesktop.LoadComponentFromUrl(file,"_default",0,Array())
  With oDoc.CurrentController
    If Not .isConnected Then
      .connect
       Wait 200
    End If
  End With
  
  oDataSource.Password=""   ' reset Password
End Sub

[RobertG]

Have tried this before. Doesn’t work here. Set the password to “”, closed the Base file. When opened the Base file again it connects directly - without asking for a new password.

But: Started this directly in the *.odb-file, not through DatabaseContext …

[sokol92]

@RobertG

Doesn’t work here

My workflow.

1. Run the TestDbConn macro. The .odb file opens, in which I can view a list of tables without specifying a user code and password. Close the file.
2. Open the same .odb file through the LO menu. When I try to view the list of tables, a dialog is displayed asking for a user code and password.

Version: 7.4.2.3 (x64) / LibreOffice Community
Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf
CPU threads: 6; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL

[RobertG]

@sokol92 : OK, get it working when starting the whole document by macro. Added oDataSource.DatabaseDocument.setModified(False), because it will set the document as modified.

But: Won’t get it working when starting the connection inside the database document. This couldn’t be the solution.

It would work like this: Open the database document. Dialog for password should appear. No password should be written down in any macro, because it isn’t encrypted.
Then a macro should delete the password for the connection (which is established) and it should be asked for new password when the Base document has been opened again.

[sokol92]

A possible workaround is to specify a non-existent username (eg *) when creating the .odb. Then, each time the .odb is opened, the user will be prompted for a username and password, after closing the .odb file is not blocked (?).

[RobertG]

I could get the same when deleting oDataSource.User and oDataSource.Password from the procedure. The database document will be opened and I will be asked for a password. Username is part of the database document.

But: When trying this the connection won’t be closed after closing the database file. I have to close the whole LO - same behavior as without any macro code.

[sokol92]

@RobertG, I meant something else.
When creating a new .odb file, we specify in the Wizard step Set up user authentication
User name: * and check Password required.
Now, every time we open the .odb file, we will have to enter a username and password. The file does not seem to be locked.
Sorry for possibly misleading you.