[Tutorial] Protected document, sheets, cells and shapes

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Tutorial] Protected document, sheets, cells and shapes

Post by Villeroy »

The attachment demonstrates 5 levels of protection. The first password you need for opening the document is Open Sesame. All the other passwords to unveil all contents can be found in the document.

None of these levels is related to any restrictions imposed by the file system. If your document resides in a read-only folder or if it is locked on file system level, the office suite can't write that file no matter what. This occurs with temporary files downloaded from the web and with mail attachments in particular. However, you get a new, editable copy when you hit the edit button. The modified document needs to be saved to another file in some folder where you have write access.
Image
The rightmost button on the screenshot is the edit button. It has 2 states on and off. LibreOffice shows a colored bar with a push button on top of locked documents.

Level 1: Encrypted contents
The content of the attached document is encrypted. It is not possible to decrypt and load the document without entering the Open Sesame password.

If you are a programmer you may be interested in a second protection feature on the same level. You can encrypt Basic libraries. The libraries can be compiled and called without being able to read the code. The attachment includes a simple "hello world" macro in an encrypted library and a calling push button on Sheet3.

This level of protection applies to Writer (*.odt), Calc (*.ods), Impress (*.odp), Draw (*.odg) and Math (*.odf) documents.
To encrypt document content, check the password option in the save-as dialog and you will be prompted for the encryption password. A Basic library that must not be named "Standard" can be password protected on the library tab of the macro organizer. Choose the right container, the library and hit the passord button.

Level 2: Write protection
In Writer and Calc you can specify that some document should always be loaded in read-only mode. The read-only mode can be protected with a password. Hit the edit button to lift the write protection temporarily.
It is possible to overcome this weak protection level without knowing the password.
This level of protection applies to Writer (*.odt) and Calc (*.ods) documents.
There are 2 ways to access this option:
1) Check the password option in the save-as dialog and you will be prompted for the encryption password. Below the encryption password entry there is a [More Options] button which enlarges the dialog to unveil a check box labeled "open read-only" and another password entry. If you check the read-only option without entering any of the passwords, the document opens for reading straight away and it can be edited after hitting the edit button on the standard toolbar (or on the coloured message bar in LibreOffice).
2) menu:Files>Properties... tab "Security".
This read-only option must not be confused with read-only modes that are imposed by the file system. If your file resides in a folder where you have no write access or if the file itself is write protected, OpenOffice can't do anything to overcome this restriction.
How to remove this protection level:
Hit the edit button, enter the password (if any), save as another file. Saving to the same file while this protection level is lifted does not permanently remove the protection.

Level 3: Protect sheets collection (Calc only)
You can protect the collection of sheets with menu:Tools>Protect>Document. The password is optional. When the collection of sheets is protected you can not rename, move, delete, hide or unhide any sheet without affecting the sheet contents.
It is possible to overcome this weak protection level without knowing the password. Even when you can not see everything in the user interface, a most simple macro program can read and copy any hidden content. Simple formulas can read contents and formulas from hidden sheets and cell ranges.
After you removed this protection from the demo document, you will be able to unhide Sheet2 and Sheet3.
Contrary to level 2, this protection is removed permanently when you save and close the document.

Level 4: Protect sheet contents (Calc only)
You can protect the contents of every single sheet with menu:Tools>Protect>Sheet. The password is optional. When the collection of sheets is protected you can not edit the locked cells on the sheet. By default all cells are locked. Before protecting the sheet you have to unlock the cells you want to keep editable when the sheet is protected. In the demo document, blue cells are unlocked cells. They can be edited while the sheet is protected.
Caveat: You have to unprotect all sheets before you can modify cell styles and page styles which can be very annoying. Unless you did not protect sheet contents with password(s), you may use a macro to protect or unprotect all sheets. You find it under [OpenOffice Macros]>[Tools]>[Misc]>UnprotectSheets and ProtectSheets
It is possible to overcome this protection level without knowing the password.
Contrary to level 2, this protection is removed permanently when you save and close the document.

Level 5: Protected shapes
Shapes are rectangular objects attached to an office document, for instance images, drawings, form controls, charts. Sheet3 of the demo document has 2 examples. When you right-click the image and choose "Position and Size..." from the context menu, you can find simple options to lock the position, the size and the height/width ratio of this shape. This is a simple lock with no password.
The push button is a form control. While in working mode, form controls can be used but not modified. The context menu for form controls is availlable when you turn on the design mode for input forms using the 2nd button on either toolbar "Form Controls" or "Form Design".
Attachments
cascading_locks.ods
Encrypted demo spreadsheet with encrypted Basic library, write-protection, sheets protection, protected sheets and locked shapes.
(29.45 KiB) Downloaded 776 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
aleeinator
Posts: 1
Joined: Wed Feb 14, 2018 7:09 pm

Re: [Tutorial] Protected document, sheets, cells and shapes

Post by aleeinator »

Unlock an Open Office or libreoffice Calc protected worksheet in an Windows Server Environment.
I found an easier way to unlock a spreadsheet modifying the suggestion given by fellow opensource users. This works in the Server 2008 environment unlike some other changes that only truly work in a Linux environment. You do have to change some simple things in the server first.
Start Windows Explorer, you can do this by opening any folder.
• Click Organize.
• Click Folder and search options.
• Click the View tab.
• Scroll down until you notice Hide extensions for known file types, un-check this line by clicking the check box. ...
• Click OK.

1. Change extention .ods to .zip (Right click file and change extention)
2. Extract the .zip file in a folder using Archive Manager
3. Open the: content.xml file with Writer
4. Find: table:protected="true"
5. Change "true" to "false"
6. Save the content.xml file and close Writer
7. From Files, drag and drop the previously modified content.xml file into the .zip file
8. Change extension from .zip to .ods
9. The document is now unprotected, and you can edit it.
OpenOffice 2.4 on Ubuntu 9.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Protected document, sheets, cells and shapes

Post by Villeroy »

Under Tools>Macros>Organize>Basic... "Open/LibreOffice Macros", library "Tools", module "Misc" you find a pair of macros to protect/unprotect all sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply