Please help! Need to understand security in Calc

missmabel · General User Joined: 28 Apr 2006 Posts: 6

Hi,

I'm studying for my Chartered Accountant (CA). I recently completed an Excel based assignment, which is computer marked by the Institute. I received my marks and basically they have given me zero because they believe I have tampered with the security protection of the Excel spreadsheet they sent me i.e. I cheated. I have done no such thing. My only crime is that I used Calc to complete the assignment.

I am posting this here because I stand to fail this subject if I do not get my assignment remarked, and also I will waste another year of my life repeating this subject.

The feedback I received from the Institute is, and I quote is as follows: (EP stands for Extension Project) ...

"Your EP received a result of zero. The reason you received no marks is that we were specific in our FAQs for this EP and you have breached these requirements. For example, important fields such as program range names and secret IDs had been removed from your submitted Excel EP file, thus cannot be marked. Also candidates had been clearly advised not to copy any formulae from other worksheets or delete any formulae, field names or columns/rows in your workbooks as this would result in a zero result. I would again refer to you the FAQs for the EP."

From reading the other threads regarding security, it appears that whatever security features that are embedded in Excel are removed once you open the file with Calc. Is this correct? If that is the case, how can I prove this?

Thank you for assistance,
Mabel

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

The engineering to enable OpenOffice to read Microsoft formats has been designed without the designers knowing the Microsoft code.

To some extent, therefore, the results are a best guess and the conversion is not perfect. It is certainly possible that data such as that you describe was lost.

It is very difficult to track down documents. You may get better help if you ask on the Calc mailing list to which at least one Sun developer contributes. If you want to enquire there, you would be better to subscribe because replies are only sent to the list (i.e. subscribers) and your enquiry may not even be circulated if you are not subscribed first. It is a low traffic list, but there is a digest subscription if you prefer.

To write to the list: users@sc.openoffice.org
To subscribe: users-subscribe@sc.openoffice.org
Digest sub: users-digest-subscribe@sc.openoffice.org
_________________
search forum by month

missmabel · General User Joined: 28 Apr 2006 Posts: 6

Thank you for your help.
I was half wishing it would be an easy answer, but oh well, I guess I will have to wait and see.
I've posted my query to the two mailing lists you suggested.

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

As you have seen, there are threads which mention that passwords in Excel, for example, afford no protection when the file is opened in Calc.

You are evidently talking about something else. I would expect other safeguards to fail for much the same reasons - Calc is not Excel.

You are asking for proof, though. That, I believe, is not easily obtained.

Do you have some remedy such as an appeal? The effect of opening such a document with OpenOffice can be demonstrated, after all.
_________________
search forum by month

David

JohnV · Posted: Sun Dec 10, 2006 10:12 am Post subject:

There were two identical versions of the post that use to appear in this position so I deleted the last one but was told I could only delete my own message. I therefore tried deleting the other one and had no problem. When I took a look both version were gone.

I have done this often in the past without incident and have no idea what caused is unexpected result. Please post again.

John (lurking moderator)

RickRandom · Super User Joined: 27 Jan 2006 Posts: 1082 Location: UK

From the examiner's point of view, I can see that if they gave you an Excel sheet to use, AND if they told you to only use Excel, then they have a case, i.e. you would have ignored the rules.

If they only gave you the file, and told you not to mess with it, then they don't have a case. Opening a spreadsheet in a spreadsheet program is perfectly reasonable.

I have often received those silly quizes in Excel files where you have to type the answer in a cell and it automatically marks it for you. The answers are somewhere within the spreadsheet, but hidden/protected. When I can't work them out, e.g. I can't spell the answer correctly, I open the Excel file as a text file and search for relevant text. If the exam file they gave you is anything like that, it doesn't sound very secure. However it's protected, I can't believe it's truly secure. It's Windows after all, and security hasn't been their strong point for a few years.

I guess the examiners want to automate the marking process, but why have any functionality in an answer sheet, just fill in the cells with the answers.

David · Posted: Sun Dec 10, 2006 12:24 pm Post subject:

RickRandom · Super User Joined: 27 Jan 2006 Posts: 1082 Location: UK

I (naively) never even thought I was suggesting someone should do such a thing for this exam. I was merely trying to illustrate that spreadsheet protection is not guaranteed to be robust.

I totally agree with the "cheating yourself" philosophy.

If the examiners give out a spreadsheet that has the answers embedded somewhere in it, they are offering a means for people to cheat, a bit like in a written exam being given the answers on a sheet that is face down, and being told not to look.

Of course if you were going to cheat with a spreadsheet, you wouldn't be so silly as to submit the version you'd hacked, you'd hack a copy, then put the right answers in the original.

I assume that the examiners wouldn't be so stupid as to give out a "hackable" spreadsheet, merely that it has some named ranges or some other feature that the marking process relies on. If Calc changes that feature, then it can't be marked, so 0%.

I still maintain that if the examiners told "missmable" to use Excel, she deserves 0%, but if they didn't state what program to use and their spreadsheet is "sensitive" to the program then they are at least partially to blame. Of course we don't have access to the FAQs, and perhaps they stated to use Excel, and perhaps even which version. If they don't state which version, Excel 2.0 from about 1990 should have been legally acceptable, but would probably have been totally useless.

I guess the most important lesson to be learnt (for students and examiners) is not to rely totally on spreadsheets.

missmabel · General User Joined: 28 Apr 2006 Posts: 6

Hi,
No the FAQ did not specifically state to use Microsoft Excel to complete the assignment. I've sent the Institute a letter outlining my reasons why I should be allowed to re-submit my assignment. I expect to hear from them by the end of this week.
We'll see how it goes.
Mabel

RonIA · Posted: Tue Dec 12, 2006 7:45 pm Post subject:

I wish you luck. But also would follow the suggestion above to try and find a lawyer. The fact that they sound a bit behind in their understanding of security, i.e. if an MS Excel spreadsheet changes in ways we don't expect it to that means cheating, they obviously have not used MS Excel long enough.

I think most of us on this forum have had, how should we say, interesting things happen to documents, spreadsheets, etc. from time to time with no assistance from the user. How does the governing board handle those cases?

Anyway, good luck.
_________________
Ron from Iowa, USA

missmabel · General User Joined: 28 Apr 2006 Posts: 6

Good news! The Institute heard my feedback and they marked my assignment. I received 88%.

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

Congratulations.
_________________
search forum by month

RonIA · Posted: Tue Dec 26, 2006 5:46 am Post subject:

Yes, congratulations. I also hope your experience can help others by having the board look at their policy and at the least clarifying it to the point where this situation won't happen again.

Even if it is to say, "You WILL use MS Excel for this exercise." at least the situation could have been avoided. But maybe they will be persuaded to look at other options.
_________________
Ron from Iowa, USA

Nylo · General User Joined: 16 Nov 2006 Posts: 17

The mistake is not in using Excel for correcting an exam but in providing the students with something that can correct the answers if succesfully hacked. It's worse in this case, as the hacking proves possible even by accident.

It's very easy to write a Macro that reads answer sheets into a different Excel File for correction purposes. In fact, you don't even need a Macro. And by doing so you don't need to provide the students with the correction tool. So it's the examiner's mistake to do things the way she did. I'm guessing she recently learned about some features in Excel and was just playing with it without fully understanding the risks involved.

CU.