Security in Excel

[klewis10367]

I am creating an excel 2010 odc connection under a Windows account that has admin rights to the underlying sql tables that make up the excel spreadsheet. What i want to know is if it is possible to have a windows user open this odc connection and view the data if i do not grant them permissions to the underlying tables. Basically, can i imbed my credentials so the spreadsheet uses my credentials to view the data?

 

[Gruuuu]

When you establish the connection originally, it retains the login information.

But you don't really want to do that, do you? It's pretty much frowned on by any and all security standards; and will get you in a heap-a-trouble.

Do your users have their own logins? You can change the connection string to use theirs after they provide their credentials.

I guess I'm making assumptions here. The answer is "Yes." But I'd be remiss to not ask "But what are you doing?

 

[xlbo]

Yes - you can embed a username and password in your conenction string but as Gruuuu points out it is by no means secure....how insecure I hear you ask? well the username and password are stored as plain text either in the connection string property or an underlying text file depending on how you set up the query....if you're going to do that you may as well just not have security on your tables as you are bypassing it anyway

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[klewis10367]

Thanks, what i am trying to do is create an excel report with sensitive payroll information. I do not want to grant the user read rights to all payroll data. That's what i would have to do in order to have a user see the information. Even if i create a view and limit data, they still need at minimum read access to the underlying tables. If this gets more into a sql discussion i will change forum and not waste your time. Thanks for both your replies.

 

[xlbo]

If the payroll info is sensitive I would strongly recommend NOT using Excel to retrieve or display it

If you have to do it this way, you are probably better off coding the query in ADO so you can at least pasword protect the VBA project and provide a (small) amount of security

If you are using Excel 2010 it seems there are some more features around security with SSO (single sign on) but I have not used these so cannot comment further

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[klewis10367]

Thanks, one last question. If the spreadsheet that i push out uses the ODC for its data, is it the creators credentials that are used to access the data or is it the windows user who opens the worksheet?

 

[xlbo]

All depends on the connection that is set up
If you set it to integrated security then it will be whoever opens the spreadsheet. If you set it to specific user then it will be as per that user

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question