User Permissions with an Online Database

[Marc]

I've been asked to create a database that would be stored online so that various people can access/amend data with different user permissions.

I'm using Excel 2011 (Mac), but understand it equates to 2011 on Windows, and have no problem creating the database and I can use Skydrive to put it online but I'm struggling with the user permissions aspect. Within Excel, there is the functionality to create different permissions levels but, when I go into it, it asks for a verified user name and password. I've looked it up and got to a document regarding Information Rights Management and that's when my eyes glazed over as it goes on about installing thing on Windows servers and seems to be aimed at IT Managers. Any one able to shed light or am I hurtling down a cul-de-sac with this?

The IRM pdf can be found here

 

[Nikon Fan]

Have you tried Google Documents. I'm sure that they do Databases as well.

 

[admirable]

I'm not sure but does Microsoft Sharepoint allow users to share such files online?

I don't use it but I thought that was what it was for.......might be wrong?

 

[Marc]

Have you tried Google Documents. I'm sure that they do Databases as well.

Had a quick look but it doesn't really do what I need. It's not creating the database that's the issue, purely the permissions part.

I'm not sure but does Microsoft Sharepoint allow users to share such files online?

I don't use it but I thought that was what it was for.......might be wrong?

Sharepoint and Skydrive are similar in that respect but, as I said, the online side of things isn't an issue.

 

[Harvey_nikon]

Permissions for google docs I think only cover admin, edit and view.

However it does track people's edits to make it easy for you.

Ideally, I would be looking at and SQL database with a web front end and this will also allow you to set permissions, but if you don't know SQL/PHP you'll probably struggle.

We tried Sharepoint at work and found it a pita to use.

 

[Dale_tem]

Define online - what is the requirement?

Is there a need for a multiuser database?

Google docs and share point aren't really database a and can't handle databases unless you mean an excel spreadsheet.

If you want excel to access a database, your best bet is Microsoft SQL server (express is free)

You can host it on a windows web server or you can host it internally and open up a port to the outside world.

You can connect excel to sqlnserver and do all the queries you need.

 

[Marc]

Permissions for google docs I think only cover admin, edit and view.

However it does track people's edits to make it easy for you.

Ideally, I would be looking at and SQL database with a web front end and this will also allow you to set permissions, but if you don't know SQL/PHP you'll probably struggle.

We tried Sharepoint at work and found it a pita to use.

Skydrive seems a little more usable from what I can see

Define online - what is the requirement?

Is there a need for a multiuser database?

It has to be accessible to 2 or 3 people and different locations and they need to be able to add or amend records

Google docs and share point aren't really database a and can't handle databases unless you mean an excel spreadsheet.

It would be an Excel workbook

If you want excel to access a database, your best bet is Microsoft SQL server (express is free)

You can host it on a windows web server or you can host it internally and open up a port to the outside world.

You can connect excel to sqlnserver and do all the queries you need.

Not really up on SQL Server so not sure what that entails.

 

[Harvey_nikon]

If it really is just an excel wordbook that people need access to I really would look at google docs spreadsheet.

Set yourself as the owner, then share it with the others and give them edit permissions. They will have to log in to edit it so it will show their user name's / email addresses.

Everyone can edit it at the same time, updates are instant for everyone and its accessible online and even via smart phones.

Permission levels aren't great, and it requires a level of trust but we use this for shift rota's etc in our business with 400+ employees and several team leaders editing it.

 

[srichards]

If you want to do it properly then something like mysql at the back end would allow proper control of data and access to it. You can record lock and table lock by users. Whether you can do this and provide people with a simple excel spreadsheet to interface with it is another matter entirely.

With sharing things like this then it could be a nightmare if people can work on different areas of the spreadsheet at the same time and do simultaneous updates. A database at the back end is the only sensible solution. Not allowing simultaneous access to the data is one way of dealing with that but could prove inconvenient if one of them opens it then wanders off for a few hours and locks everyone else out.

If this excel workbook thing does all this reliably then it sounds quite useful and a solution I've not come across before.

 

[Craft]

Excel isn't a database..

Regardless of that you say you just want something to manage the permissions for you, but I think you've missed something..

Working on a local document (i.e. one saved on your computer) is easy - its only you making changes.
Most applications like Excel/Word etc do not provide the ability to save a document to the internet, so this means if you have a document on the internet you don't edit that copy, it actually gets downloaded to your machine and then you open and use that copy.

So we now have two copies of the document. Uh-oh.

So the user makes their edits locally, they would then have to upload the new, revised document from their machine to the internet for other users to see.

So what happens if two users edit it at the same time ?
User A downloads document
User B download document
User A & B make different edits
User B finishes first and uploads the revised copy
User A finishes and uploads their revised copy (which doesn't have User Bs changes)

You just lost data, and in all likelyhood neither user actually knows.

Google docs will handle all this for you because it will collate all the changes as they happen, so no data is lost. It'll also manage the permissions side of things for you.

If you already have an excel document you should be able to upload it and it'll be able to edit it, so you won't need to re-do any work.

Failing this, as someone said you're looking at having some sort of web front end and a proper RDBMS (Sql Server, mysql etc) behind it to hold the data.

 

[Marc]

If it really is just an excel wordbook that people need access to I really would look at google docs spreadsheet.

Set yourself as the owner, then share it with the others and give them edit permissions. They will have to log in to edit it so it will show their user name's / email addresses.

Everyone can edit it at the same time, updates are instant for everyone and its accessible online and even via smart phones.

Permission levels aren't great, and it requires a level of trust but we use this for shift rota's etc in our business with 400+ employees and several team leaders editing it.

I actually need the full excel functionality as it isn't just a case of changing cells on a spreadsheet. The users would see a proper front end with data entry fields and drop down menus etc and there would be a number of vba macros included. Permissions are quite important as it will be a database holding personal information.

If you want to do it properly then something like mysql at the back end would allow proper control of data and access to it. You can record lock and table lock by users. Whether you can do this and provide people with a simple excel spreadsheet to interface with it is another matter entirely.

With sharing things like this then it could be a nightmare if people can work on different areas of the spreadsheet at the same time and do simultaneous updates. A database at the back end is the only sensible solution. Not allowing simultaneous access to the data is one way of dealing with that but could prove inconvenient if one of them opens it then wanders off for a few hours and locks everyone else out.

If this excel workbook thing does all this reliably then it sounds quite useful and a solution I've not come across before.

As I've said, I have no issues with doing what I need to do in Excel, It has the functionality to do what I need with permissions but I can't work out how to do it as t asks for a verified user name and password and I don't know where these come from.

Excel isn't a database..

Regardless of that you say you just want something to manage the permissions for you, but I think you've missed something..

Working on a local document (i.e. one saved on your computer) is easy - its only you making changes.
Most applications like Excel/Word etc do not provide the ability to save a document to the internet, so this means if you have a document on the internet you don't edit that copy, it actually gets downloaded to your machine and then you open and use that copy.

So we now have two copies of the document. Uh-oh.

So the user makes their edits locally, they would then have to upload the new, revised document from their machine to the internet for other users to see.

So what happens if two users edit it at the same time ?
User A downloads document
User B download document
User A & B make different edits
User B finishes first and uploads the revised copy
User A finishes and uploads their revised copy (which doesn't have User Bs changes)

You just lost data, and in all likelyhood neither user actually knows.

Google docs will handle all this for you because it will collate all the changes as they happen, so no data is lost. It'll also manage the permissions side of things for you.

If you already have an excel document you should be able to upload it and it'll be able to edit it, so you won't need to re-do any work.

Failing this, as someone said you're looking at having some sort of web front end and a proper RDBMS (Sql Server, mysql etc) behind it to hold the data.

Excel is a data manipulation tool and it can quite easily be used to create a database. I've done it before and, as I keep saying, I have no issues with creating what's required here.

As for Excel not having the facility to save to the internet, Skydrive allows you to do exactly that and it can be edited online so no, there won't be multiple copes. All the users will be doing is adding and amending records.

I had a quick look at Google Docs but it came up with an error when I tried to look into uploading a file so I still don't know what it actually offers but I know Excel can give me everything I need but I just want to know how I go about using the permissions functionality that it already has.

 

[Dale_tem]

Have you tested out multiple users updating the spreadsheet, not just reading from it?

Share point allows you to check out documents, but you can't have multiple people updating the same document at the same time.

 

[Marc]

Have you tested out multiple users updating the spreadsheet, not just reading from it?

Share point allows you to check out documents, but you can't have multiple people updating the same document at the same time.

They don't necessarily have to be able to edit at the same time.

 

[Dale_tem]

In that case its not multiuser or a database, just a shared document with database functionality. Any website or online storage can do this.

What are the different permissions for, what do they effect?

 

[Marc]

In that case its not multiuser or a database, just a shared document with database functionality. Any website or online storage can do this.

What are the different permissions for, what do they effect?

Well it's definitely a database, it's for the storage and updating of data. Pretty much a definition of a database isn't it?

The different user permissions are for different levels of people to be able to see different levels of data, some to be able to add & edit, some to just add.

 

[Dale_tem]

The point I'm trying to make is to you it is a database, to the rest of the world it is a spreadsheet. An online db will nearly always involve a database engine - mssql, postgre sql or ms sql being the main ones.

 

[Marc]

The point I'm trying to make is to you it is a database, to the rest of the world it is a spreadsheet. An online db will nearly always involve a database engine - mssql, postgre sql or ms sql being the main ones.

No, it's a database because of the reasons stated above. It certainly won't be just a spreadsheet because there'll be a lot more to it (I create this sort of thing for a living btw). Putting it online using Skydrive means that others can use it from different locations.

All I'm trying to find out is how to use the permissions part of Excel but people seem to be hung up on technicalities that are irrelevant to the question so I'll thank everyone for their responses and move on I think.

 

[Dale_tem]

I'll see if I cover Excel in my database administrator course this week. Something tells.me I won't.

Technically it is a database as it is storing information and you are going to filter etc.

To everyone in the world of IT, it is an excel spreadsheet.

You have asked for online database help. You should be asking for Excel help, you will have more success.

 

[KIPAX]

mirosoft office the full package come with various programs.. one is excell and another is access.. one is a spreadsheet program the other is a database program.. if excell is a database program what is the point of access?

a spreadsheet is for storing analising and manipulating data which sounds like a database..however a database if for maanging your data which is the difference and is the functionality your looking for

The fact that you want to use it online then your answer is soemthing like mysql which funnily enough allows you to import excell data But you would need to learn a hell of a lot not only about the database but also the language you choose to talk to it (php populor choice, perl/cgi most powerfull) to be able to use all the fancy bits you have setup in excell.

As I see it given the info we have here

 

[Marc]

Well I asked for help on user permissions for a database created with Excel, thought that was pretty specific tbh.

I do find it funny that people tend to assume that, because it's in Excel, it's just a spreadsheet. Excel now has full database functionality as Microsoft are phasing out Access but even before, it was easy to create a functional database including front ends for non Excel users, all within Excel.

 

[Marc]

mirosoft office the full package come with various programs.. one is excell and another is access.. one is a spreadsheet program the other is a database program.. if excell is a database program what is the point of access?

See my reply below yours.


But, as I keep saying, I'm not asking for help creating the database.

 

[KIPAX]

Microsoft are phasing out Access but even before, it was easy to create a functional database including front ends for non Excel users, all within Excel.

As a dedicated MS 2007 user i wasnt aware..oops


And yer your right.... people are answering questions you havent asked.. its common when we dont know your answer but we do know alternatives

 

[Marc]

As a dedicated MS 2007 user i wasnt aware..oops


And yer your right.... people are answering questions you havent asked.. its common when we dont know your answer but we do know alternatives

2007 pretty much has the functionality as well, it was the biggest change from 2003.

And yes, I know people are suggesting alternatives and I have looked at them. Google docs, for example, is pretty much a non starter for this as I need different levels of user permissions rather than just view/edit.

Skydrive gives the online functionality that I need and Excel has the user permissions but I'm just struggling on how to use it.

 

[Dale_tem]

Excel and access are not multi user databases. The end. They can be accessed by multiple people, but cannot be updated at the same time, without the chance of data loss.

Excel has always had database functionality.

Your thread title is 'permissions for online database' not 'excel user permissions help'

The people who know online databases are replying to this thread with their advice. Stick excel in the title and excel people might help.

 

[Marc]

Excel and access are not multi user databases. The end. They can be accessed by multiple people, but cannot be updated at the same time, without the chance of data loss.

Excel has always had database functionality.

Your thread title is 'permissions for online database' not 'excel user permissions help'

The people who know online databases are replying to this thread with their advice. Stick excel in the title and excel people might help.

Dale, I would hope that people read posts, not just thread titles. My 1st post clearly states that it was specific to Excel. You seem to be hung up on semantics.

And, as I already said, they don't have to be able to update at the same time.

 

[srichards]

http://www.excelforum.com/ Have you tried asking on there?

 

[Marc]

http://www.excelforum.com/ Have you tried asking on there?

No, not seen that one before, I usually ask on Mr Excel but didn't get much response. I'll try that one, thanks.