Protect Base document

Discuss the database features

Protect Base document

Postby Rodjer » Mon May 28, 2012 2:14 pm

Hello, is it possible to protect my database file using a password ?
Are there some options to enable this very important for all databases feature?
Or maybe it will come in later versions of OOO ?
OOO 3.3 /// Windows XP SP3
Rodjer
 
Posts: 2
Joined: Mon May 28, 2012 2:04 pm

Re: Protect Base document

Postby Villeroy » Mon May 28, 2012 3:54 pm

Which type of database is indicated in the status bar? Embedded HSQLDB? That type of database must never hold anything important anyway. Encryption would increase the propability that all your data will be destroyed some day.
[Tutorial] Avoid data loss by avoiding "Embedded databases"
Once you use a true database you have many options availlable, such as fine grained permissions for different groups of users.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Protect Base document

Postby Rodjer » Tue May 29, 2012 3:08 pm

Does it mean that without special knowledge about SQL and other stuff which concerns databases for an regular user it's impossible to use OOO database ?!
So the database can crash or doesn't really save you data and protection is also impossible ?!
It's nasty ///
OOO 3.3 /// Windows XP SP3
Rodjer
 
Posts: 2
Joined: Mon May 28, 2012 2:04 pm

Re: Protect Base document

Postby DACM » Tue May 29, 2012 8:24 pm

A Base file is a zip-archive container that serves multiple purposes:
    (1) Provides connection to data sources from all OOo applications
    (2) Stores embedded Queries, Forms, Reports and Macros
    (3) Stores embedded Database files from the built-in HSQLDB engine
There's no built-in password-protection for this zip-archive file. Furthermore, the Base container-file (.odb) concept works well until you add the "embedded database" files to the mix (option '3' above). It's best to avoid "embedded database" files by skipping the 'Create a new database' option in the Base 'New Database' wizard. Villeroy provided a link outlining the steps to setup a new, non-embedded database using the built-in, relational database seamlessly with Base. If your needs are modest, you may also choose a new, non-relational database using the 'Connect to an existing database' option of the wizard. Simply select 'dBase' and a folder to hold your dBase (.dbf) tables/files.

Either way, it's possible to password-protect your data. The first three options below (1, 2 & 3) are available with all data-sources including: dBase, spreadsheet, text-CSV, HSQLDB folder, etc. However, fine-grain access control by table/view and user/group is available only when using a relational database engine such as the built-in HSQLDB engine (options 5 & 6 below).

You have a few options when it comes to password-protected database access:

    (1) Use a common zip-file manager to password-protect the database folder with encryption. This approach is not integrated into OOo/AOO/LO so additional steps are required to encrypt/decrypt the file upon each access. There's no realistic vulnerabilities to the encryption used (AES).
    (2) Use a TrueCrypt container file. This approach is not integrated into OOo/AOO/LO, but TrueCrypt provides on-the-fly encryption which can be relatively seamless for the end-user. There's no realistic vulnerabilities to the encryption used (AES, etc.). TrueCrypt containers are recommended for all sensitive data stored in cloud-service protected folders like Dropbox.
    (3) Use a standalone Form/switchboard with password-protected Form(s). This creates a layer of protection which may deter the average user, but the database is still accessible by other means such as locating and opening the Base (.odb) file.
    (4) Use the built-in 'user' support employed by the database engine, as outlined below. This creates a layer of protection which may deter even advanced computer users, but the database is still accessible by recompiling the (open-source) database engine software. This is the most common database solution, providing both user-authentication and fine-grain access control in multi-user environments.
    (5) Use the on-the-fly encrypted database function of some relational database engines such as HSQLDB 2.x, H2, and others. This is the most flexible solution due to the true data-security and/or fine-grain access control in multi-user environments.
      NOTE: As with all software data-protection, encryption adds a layer of code to the file input/output process which contributes to the potential for data corruption. But this acknowledgment is distinct from Villeroy's comment about adding yet another layer of code to the already unstable "embedded database" file format -- which is akin to throwing a mill-stone to a drowning victim. I'm simply acknowledging that password-protection with the proper software-encryption adds a degree of risk to the data -- which should be mitigated by automated backup/versioning software.
NOTE: The following is not applicable to "embedded databases." Do not setup a password for the 'SA' user for an "embedded database" because this will disable the file requiring zip-file surgery on the internal 'database' folder for recovery.

Options (4) and (5) above require you to setup users/passwords using SQL. This as a function of the database engine, but the SQL command is quite simple.

Once setup, Base provides a pop-up for user-authentication before granting database access. You can also limit individual users or groups of users to certain data (fine-grain access control) using the SQL GRANT command, or eliminate a user with the DROP command.

NOTE: This SQL-based password-protection for databases has also been adopted by Microsoft with MS Access 2010. So the open-source community is not unique in this approach for databases.
Last edited by DACM on Wed May 30, 2012 9:29 am, edited 3 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Protect Base document

Postby Villeroy » Tue May 29, 2012 8:51 pm

Rodjer wrote:Does it mean that without special knowledge about SQL and other stuff which concerns databases for an regular user it's impossible to use OOO database ?!
So the database can crash or doesn't really save you data and protection is also impossible ?!
It's nasty ///

There is no "special knowledge" about SQL. It is the same SQL since the 90ies which applies to dozends of databases.
You can use a wide variety of relational databases (if you can) and connect them to this office suite through Base documents.
By no means this is nasty. It is plain simple.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Protect Base document

Postby MathUHenry » Tue Sep 02, 2014 4:12 pm

"There is no "special knowledge" about SQL. It is the same SQL since the 90ies which applies to dozends of databases."

That's great for those who are familiar with SQL. For those who are familiar with OpenOffice/LibreOffice and only just now have been introduced to the acronym "SQL" because they want to secure their database, it constitutes "special knowledge" because it represents an entire new programming language to be learned for the sole and exclusive purpose of adding a password.
MathUHenry
 
Posts: 4
Joined: Tue Feb 12, 2008 6:54 am

Re: Protect Base document

Postby Villeroy » Tue Sep 02, 2014 6:13 pm

No problem. Add your user groups and privileges to your database and then tell Base to prompt for a name and password before connecting to the database. It just can not work with embedded HSQL. Embedded HSQL is no more than a play ground for not so important projects. Of course you are free to use a third party encryption tool for your embedded HSQL.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 11 guests