Terminate LibreOffice

[snarpel]

Windows 7 32-bit

Libreoffice: 6.2.4.2

MariaSQL: 10.3.13 (ODBC connection)

Hi All,
I have a macro running from the Open File event in the .odb file, which connects to the MySQL database and checks the logged in username and tries to terminate LibreOffice if it is not ‘root’ (Ideally I would prefer if the connection to the database was ended and the user had to log in again,instead of terminating LO, but I’m not sure this can be done from code).

After opening the .odb file, the macro should be executed and connect to database and bring up to login dialog without having to open a form. If the user is not root then close LibreOffice.

The problem is that even though the application windows close as expected and LO seems to be terminated, the sbase.exe, soffice.bin and soffice.exe are still running. Launching the .odb again just doubles the process count without any window showing and I need to kill the processes using task manager.

Any Ideas?

Sub CheckRoot
	On Error GoTo ErrorHandler
	oDataSource = ThisComponent.CurrentController
	If NOT (oDataSource.isConnected()) Then
		oDataSource.connect()
	End IF
	oConnection = oDataSource.ActiveConnection()
	oSQL_Statement = oConnection.createStatement()
	
	stSql = "SELECT REGEXP_REPLACE(USER(), '@.*','') AS user"
	oQuery_Result = oSQL_Statement.executeQuery(stSql)
	If NOT ISNULL(oQuery_Result) Then
		oQuery_result.next
		dbUser = oQuery_Result.getString(1)
	END IF	
	If (dbUser <> "root") Then
	  msgbox("You are not authorised to use this system" & dbUser)
      oConnection.close()
      ThisComponent.close(True)
	  Wait 2000
	  starDesktop.terminate ' NOT WORKING '
	End If   
	Exit Sub
	ErrorHandler:
    	Reset
    	If Err =1 Then
    		MsgBox "Cannot connect to database" , 16 ,"an error occurred"
    	Else
    		MsgBox "Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13) + Now , 16 ,"an error occurred"
    	End If
	
End Sub

[Ratslinger]

Hello,

Not certain the amount of security you are trying to apply but there are a lot of holes to cover in stopping users in gaining access.

Have spent a good portion of the day trying to resolve this. Solving one item led to a problem with another.

It is easy enough to shut down LO from a menu - it is an .uno:Quit command that is used. You can run this from a macro but it creates problems such as crashes. It causes crashes because it is trying to close all LO and basic is still running. Now I was also able to close LO if only the applicable Base file was running - all other LO files needed to be closed. Not good if someone is working on say a Writer or Calc doc.

Another item to consider, if Base is opened and correctly signed in, Base is still connected until all LO files are closed. The following helps alleviate that to an extent.

So there is a different approach which can be taken. Control the sign-in through the macro. Eliminate the checking after signing and stop it before it starts. Happens I had posted similar to this in the past here → Can’t remove password from LO Base connected to MySQL. Have modified this for your needs:

Sub SetLogIn
  dim sURL as string
  dim oArgs(0) As New com.sun.star.beans.PropertyValue
  dim oDocStatus as object
  dim oihandler
  dim sUserPW AS String
Rem Change to your liking
  sUserPW = InputBox("Enter Password for User 'root'", "Password Entry", "password here")
  if Len(answer) = 0  then
      sUserPW = "GARBAGE"
  end if
  oihandler = CreateUnoListener("InteractionHandler_", "com.sun.star.task.XInteractionHandler")
Rem Change following for your Base file
  sURL = "file:///BASE_LOCATION_HERE/BASE_FILE_NAME.odb"
  oArgs(0).Name = "InteractionHandler"
  oArgs(0).Value = oihandler
  oDocStatus = StarDesktop.loadComponentFromURL(sURL, "_default", 0, oArgs)
  oDocStatus.DataSource.User = "root"
  oDocStatus.DataSource.Password = sUserPW
  oDocStatus.setModified(0)
End Sub

This is attached to the Open Document event of the Base file. It only offers the user one shot - can do more mods to improve this.

Some other considerations. A knowledgeable user can still modify macros - password protect them. They could also delete the attachment to the macro - can set up Base to start up without the main screen or using a switchboard.

There may be more but these are just some thoughts.

The routine above was tested using MySQL and PostgreSQL and both worked.

If log in was no good, user will get a message that the connection could not be established when they attempt to access anything - tables, forms, etc. Then Base needs to be restarted & log-in tried again.

Think this is about the best approach with maybe some tweaks here & there depending on your requirements.

[Ratslinger]

FYI - testing was done on Linux Mint & Ubuntu systems using LO v6.2.x series.

[Ratslinger]

Hello,

Wanted to add another note. If you don’t want the password displayed in plain text during user entry, use a dialog instead of an Input Box. You can set a text field in a dialog for password protection.

[Ratslinger]

The IF statement in above code should be:

  if (Len(sUserPW) = 0) OR (sUserPW = "password here") then
      sUserPW = "GARBAGE"
  end if

Sorry for any inconvenience.

[snarpel]

Thanks for the detailed reply. It works (after fixing a small typo in the variable name). I was going to comment on how using a InputBox wasn’t great as the text was in plain text, but you have already answered that! Will look at using a dialog as suggested.

[Ratslinger]

@snarpel,

For future reference, there is a method to close base. See comments in this post (German) → Close Base application with button.

Some basic tests show this works. If used, more testing should be done.

[snarpel]

Doesn’t work for me I’m afraid. The windows close, as before, but the Base and LibreOffice processes are still running. Not sure if it’s something to do with the fact I’m running the 32-bit client.