Sharepoint security settings for distributing Access 2010 databases via Sharepoint 2010
Bob Alston
Access Services to distribute an Access database? See posting here
Bob
Bob Alston
Bob
Bob Alston
via Sharepoint. That, combined with Sharepoint stored data, allows
Access databases to be run in multiple locations, whereas traditionally
Access users are limited to LAN (yes, replication and terminal services
can be used to expand that).
what I am very disappointed with is the difficulty in getting answers to
questions on Sharepoint 2010. MVPs here who were in the initial beta
have been very forthcoming with information and answers to questions
that are Access related. But if you have a question about Sharepoint
2010 - related to access, questions posted to the Microsoft Sharepoint
2010 forumn can sit unanswered.
So unless I can locate some beta tester who has actually figured out how
the Sharepoint 2010 aspects work, I guess I will have to wait til mid
2010 and buy some of the forthcoming books.
So if anyone here has been an early beta user of Access 2010 with
Sharepoint 2010 and has dealt with issues regarding what security
settings you need in Sharepoint 2010 for various types of end users,
please see my questions posted here.
Bob
Bob Alston
> So if anyone here has been an early beta user of Access 2010 with
> Sharepoint 2010 and has dealt with issues regarding what security
> settings you need in Sharepoint 2010 for various types of end users,
> please see my questions posted here.
>
> http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/8a028a99-a00f-4f08-86a9-98c644c3695d
>
>
> Bob
Anyone?
What are the appropriate security settings for a Sharepoint 2010 user
who is going to download and use an Access 2010 application?
What security settings for a user who will download and use an access
application but WILL NOT have rights to update/change the access
application stored at the Sharepoint server?
What security settings for a user who will download and use an access
application but WILL have rights to update/change the access application?
Is there a way to make the downloaded application behave like an Access
MDE - where the user cannot change the forms or reports at all?
What options do I have to limit what the user who downloads the Access
database can see on the Sharepoint site
Right now they must understand to click on options to download
the access app. Any easier way?
Right now they can see all tables and can export them to Excel.
What if I don't want they to be able to do that?
Where can I find more information on how to set security settings when
using Sharepoint Access services?
Bob
David W. Fenton
news:MWN_m.9464$pA1....@newsfe17.iad:
> Where can I find more information on how to set security settings
> when using Sharepoint Access services?
I investigated the Sharepoint newsgroups a few days ago, and looked
at the forums on technet, and it really doesn't look to me like
there's that much activity at all.
Are there any MVPs around who could get Bob's questions to the
attention of someone who can address them? I'd certainly like to
hear the answers, too, particularly on the issue of security.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Bob Alston
posts. Let me rephrase.
I am trying to figure out the practical access privileges/security
settings for using Sharepoint 2010 to host an Access 2010 app that users
would download from Sharepoint2010 and run on their own PC using Access
2010. The data would be stored in sharepoint 2010 tables.
So far I have been able to publish the data from an Access 2010 app to
sharepoint and publish the app itself to sharepoint. If I go to the
sharepoint site I published to, and select options, I can download the
app. From there I can run on a local PC with the data still ultimately
stired in Sharepoint 2010.
1) Tracitionally, when i develop Access apps for others, I often
restrict their access to running the app through the menus and forma I
provide. How can I restrict users who download the app from Sharepoint
to not be able to update the access app - locally on their PC and on the
Sharepoint master copy?
2) What should the security settings for a user who can update/change
the access app?
3) Is there a way to make the downloaded app behave like an access MDE?
4) When I go to the sharepoint site, I can see options allowing me to
export any table to excel. What if I do not want to allow my users to
access data in raw form and do not want them to be able to export data
to Excel?
5) Is there any easier way to allow users to download the access app
other than by knowing to click on OPTIONS and then SAVE? Perhaps
something better named?
Thanks to anyone who can provide answers.
Bob Alston
Banana
> Perhaps I was not clear enough for someone to respond to my earlier
> posts. Let me rephrase.
No you were quite clear. The trouble is that I think not many testers
has had chance to broach this subject. I can only tell you that it took
me a while to wrap my brain around Sharepoint's security model before I
could give any meaningful answer.
> 1) Tracitionally, when i develop Access apps for others, I often
> restrict their access to running the app through the menus and forma I
> provide. How can I restrict users who download the app from Sharepoint
> to not be able to update the access app - locally on their PC and on the
> Sharepoint master copy?
There is a way to modify the what I think is called "Title Bar" but I've
yet to find a means to doing this. However, I did briefly saw someone
else's demo on AccessHosting.com and noted they did away with the
default title bar so this is an nonissue.
I was able to suppress the "Actions" button by modifying a XML file. But
frankly that was quite hackish, and I'm still studying the Sharepoint
how to manipulate those. It's complicated by the fact that sites created
by Access Services isn't available in Sharepoint Designer (they probably
have a good reason- editing in Access then editing in Designer is a sure
recipe for disaster)
But the answer would lie in modifying the existing XML files so the
title bar either do not display the buttons or suppress it entirely.
> 2) What should the security settings for a user who can update/change
> the access app?
This is completely managed by Sharepoint, not Access. None of what we
would do to set up security in Access to date would apply to web
database, though keep in mind it is possible to have a hybrid database
mixing web objects with client objects. If one want to allow downloading
the file to use the full functionality, you still may want to use
traditional approach for securing those, but in context of web browsers,
it's Sharepoint security that matters. You would manage this via
Permissions page on the published site.
> 3) Is there a way to make the downloaded app behave like an access MDE?
You certainly can publish an ACCDE, but it must be realized that ACCDE
is of less value due to the fact that web databsse can only use macros
and macros are editable in ACCDE. One possible precaution is to not use
standalone macros but rather embed macros inside the forms/reports.
> 4) When I go to the sharepoint site, I can see options allowing me to
> export any table to excel. What if I do not want to allow my users to
> access data in raw form and do not want them to be able to export data
> to Excel?
You can either as mentioned earlier, suppress the button leading to the
Modify Application button or deny the users the permissions to "View
Application Views" via Sharepoint Permission page.
> 5) Is there any easier way to allow users to download the access app
> other than by knowing to click on OPTIONS and then SAVE? Perhaps
> something better named?
I would think you may want to provide this via a form within the web
database- set up a little repository on Sharepoint, upload a copy there,
then sync the database.
> Thanks to anyone who can provide answers.
Note this is just after my first successful attempt at setting up the
permissions and locking the non-administrator user out from the Modify
Application pages (as well other Sharepoint pages) while still having
the permissions to interact with the web database inside a web browser
(edit, add, delete) and no ability to download the file via Actions
button. As noted earlier, I am not all sure that what I've done could be
the best thing to do but hopefully that's start toward understanding how
things work.
David W. Fenton
news:4B57EC08...@Republic.com:
> None of what we
> would do to set up security in Access to date would apply to web
> database, though keep in mind it is possible to have a hybrid
> database mixing web objects with client objects. If one want to
> allow downloading the file to use the full functionality, you
> still may want to use traditional approach for securing those
I'm confused.
For plain old SharePoint distribution of your front end (like any
other file, i.e., not involving Access services at all), sure, you
can upload a secured MDB and the supporting workgroup file and still
use Jet ULS.
But it's only ACCDB that is compatible with Access services, so
there is no security available at all (database passwords are not
security).
So, I'm having trouble parsing the quote portion of your paragraph
above.
David W. Fenton
news:4B57EC08...@Republic.com:
> Note this is just after my first successful attempt at setting up
> the permissions and locking the non-administrator user out from
> the Modify Application pages (as well other Sharepoint pages)
> while still having the permissions to interact with the web
> database inside a web browser (edit, add, delete) and no ability
> to download the file via Actions button. As noted earlier, I am
> not all sure that what I've done could be the best thing to do but
> hopefully that's start toward understanding how things work.
I don't understand why Bob's question is not getting an answer from
somwhere. Surely somebody among the MVPs somewhere or someone at MS
knows the answers to his questions, but I'm not seeing anything (and
he's asked in the Technet 2010 beta newsgroups, too).
Bob is asking about a garden-variety distribution scenario, and it
seems nobody has given any thought to what to me seems a very
obvious use case that should already be completely worked out.
Banana
> I'm confused.
>
> For plain old SharePoint distribution of your front end (like any
> other file, i.e., not involving Access services at all), sure, you
> can upload a secured MDB and the supporting workgroup file and still
> use Jet ULS.
>
> But it's only ACCDB that is compatible with Access services, so
> there is no security available at all (database passwords are not
> security).
>
> So, I'm having trouble parsing the quote portion of your paragraph
> above.
Well, I don't consider database passwords to be a real security but it
doesn't change the fact that some people out there do want to secure the
file in some way, be it using AD queries to check the user's permissions
before allowing them to go to a certain form, using ACCDE to prevent
unauthorized editing, rolling out a DIY security (which I personally
think is waste of time and fairly risky proposition but again, I'm only
describing what is being done by others). Anyway, this is what I was
referring to as 'traditional approaches', which certainly couldn't
include ULS as you noted- not available in ACCDB/ACCDE files.
One more traditional approach was also to make use of Windows Filesystem
permissions to keep out the non-users- this wouldn't certainly apply in
context of Sharepoint distributing the file, however, I'm fairly sure
Sharepoint can be configured to behave in similar manner, not allowing
nonusers to download the file at all.
The point being made is that if you still want to secure the hybrid
database that is distributed via Sharepoint, the traditional approaches
still applies for the client objects because the Sharepoint security
model will only deal with Sharepoint objects and client objects aren't
subject to Sharepoint's security (albeit in an indirect fashion should
the objects reference Sharepoint objects).
I've yet to find out whether it is possible to tune the Sharepoint
security to act effectively as RWOP or RunAs so that only the
application itself has the permissions to update the lists and denying
the same thing to the users, and that is what I hope to find out
eventually. As I said, this is only my first attempt at understanding
the security model and at least it can be nominally secured as described
in my prior post.
I hope this help clarifies things...
Banana
> I don't understand why Bob's question is not getting an answer from
> somwhere. Surely somebody among the MVPs somewhere or someone at MS
> knows the answers to his questions, but I'm not seeing anything (and
> he's asked in the Technet 2010 beta newsgroups, too).
>
> Bob is asking about a garden-variety distribution scenario, and it
> seems nobody has given any thought to what to me seems a very
> obvious use case that should already be completely worked out.
Unfortunately, I have no answer to this question, and I do agree that
this should already have been documented/discussed _somewhere_. I can
only say that figuring this out on my own was not an easy task as I had
to learn a new security model, and work out how it correlates to a new
product.
At least I hope others will come and answer and hopefully correct &
point out where I could do better. I'd gladly welcome it. If nothing
else, at least we have a starting point for the discussion & further
analysis.
David W. Fenton
news:4B58A711...@Republic.com:
> David W. Fenton wrote:
>> I'm confused.
>>
>> For plain old SharePoint distribution of your front end (like any
>> other file, i.e., not involving Access services at all), sure,
>> you can upload a secured MDB and the supporting workgroup file
>> and still use Jet ULS.
>>
>> But it's only ACCDB that is compatible with Access services, so
>> there is no security available at all (database passwords are not
>> security).
>>
>> So, I'm having trouble parsing the quote portion of your
>> paragraph above.
>
> Well, I don't consider database passwords to be a real security
> but it doesn't change the fact that some people out there do want
> to secure the file in some way, be it using AD queries to check
> the user's permissions before allowing them to go to a certain
> form,
That's not security, that's just controlling program flow.
> using ACCDE to prevent
> unauthorized editing,
...of Access objects.
> rolling out a DIY security (which I personally
> think is waste of time and fairly risky proposition but again, I'm
> only describing what is being done by others). Anyway, this is
> what I was referring to as 'traditional approaches', which
> certainly couldn't include ULS as you noted- not available in
> ACCDB/ACCDE files.
Other than ACCDE, none of these accomplishes anything worth doing in
regard to SECURITY, in my opinion. Using NTFS user groups for
controlling program flow is sensible, seems to me (AD is not
necessary unless you want to use AD-specific features, such as
organizational units). But it's not SECURITY.
> One more traditional approach was also to make use of Windows
> Filesystem permissions to keep out the non-users- this wouldn't
> certainly apply in context of Sharepoint distributing the file,
> however, I'm fairly sure Sharepoint can be configured to behave in
> similar manner, not allowing nonusers to download the file at all.
I have certainly used Sharepoint servers pre-Office 2007 (i.e., the
simple version that ships with Windows Server 2003), and they
required authentication. But I was always using them from outside
the network, so I wasn't authenticated against the Sharepoint
servers' domain controllers.
I would expect you have builtin Sharepoint user groups when you
install Sharepoint on a machine, and some method for installing
those groups on your domain controller (when your Sharepoint server
is not your domain controller). Of course, I'm assuming things are
done *logically*, but I can't conceive of any other way that they
could be done!
> The point being made is that if you still want to secure the
> hybrid database that is distributed via Sharepoint, the
> traditional approaches still applies for the client objects
> because the Sharepoint security model will only deal with
> Sharepoint objects and client objects aren't subject to
> Sharepoint's security (albeit in an indirect fashion should the
> objects reference Sharepoint objects).
Sure. But I'm not sure that was Bob's question.
> I've yet to find out whether it is possible to tune the Sharepoint
> security to act effectively as RWOP or RunAs so that only the
> application itself has the permissions to update the lists and
> denying the same thing to the users, and that is what I hope to
> find out eventually. As I said, this is only my first attempt at
> understanding the security model and at least it can be nominally
> secured as described in my prior post.
I thought Albert or somebody had said that Sharepoint 2010 security
was majorly enhanced so that it took advantage of the underlying SQL
Server security. Obviously, that applies to data, not to front ends,
but that's a big deal, seems to me.
Banana
> But it's not SECURITY.
All right. I don't know how you are defining the security. However, many
people would tend to think all those options fall under 'security'
heading, whether this is correct or not.
> I have certainly used Sharepoint servers pre-Office 2007 (i.e., the
> simple version that ships with Windows Server 2003), and they
> required authentication. But I was always using them from outside
> the network, so I wasn't authenticated against the Sharepoint
> servers' domain controllers.
Well, it depends on how Sharepoint is configured. For example,
Sharepoint has 3 different security mechanism to choose from, and
anonymous login can be enabled. For a company I worked with, I always
had to authenticate into Sharepoint 2007, and I am fairly I was using AD
user credential even though I was using Safari & Mac OS X because it was
the same one I used for other resources with the company and my
credentials fits the AD pattern.
In case of web database I published to my Sharepoint, I usually had to
authenticate in same manner, using my AD user I created for myself every
time I visited (and started a new session). This was true whether I did
in the Windows VM or on Mac OS X's Safari. I could enable anonymous user
and not get this prompt, but I've not gotten to that point just yet.
> I would expect you have builtin Sharepoint user groups when you
> install Sharepoint on a machine, and some method for installing
> those groups on your domain controller (when your Sharepoint server
> is not your domain controller). Of course, I'm assuming things are
> done *logically*, but I can't conceive of any other way that they
> could be done!
Again, it depends on what security mechanism was chosen when Sharepoint
was installed. There are Windows Authentication (pretty much like what
we know about SQL Server), ASP.NET Forms authentication, and Web Single
Sign-On. For my server, I used the default, Windows Authentication.
Furthermore, it further depends on whether I allow Sharepoint to
auto-create users or allow anonymous users.
> Sure. But I'm not sure that was Bob's question.
Perhaps, but I was trying to be complete. I hope that is not an error.
> I thought Albert or somebody had said that Sharepoint 2010 security
> was majorly enhanced so that it took advantage of the underlying SQL
> Server security. Obviously, that applies to data, not to front ends,
> but that's a big deal, seems to me.
I don't know about this. I do think it's fairly easy to protect data
(e.g. lists) from non privileged users because this is a server-based
security, rather file-based security which is alwasy going to be
ineffectual. However, as you said, the front-end objects is going to be
more problematic. The simplest solution will be probably in form of not
allowing to download the web database and distribute the rich client
database separately.
From the POV of developing & deploying a web database, security setup
is probably going to be handled by a Sharepoint Administrator. Access
2010 provides a built-in function to interrogate the web client's group
which can be used to "control program flow" and allow them to open only
certain forms, but again, that requires understanding what kind of
security mechanism the particular Sharepoint Server was configured to use.
David W. Fenton
news:4B5A28C8...@Republic.com:
> David W. Fenton wrote:
>> But it's not SECURITY.
>
> All right. I don't know how you are defining the security.
> However, many people would tend to think all those options fall
> under 'security' heading, whether this is correct or not.
Since it's enforced at the program level, while you may be using
NTFS security groups to determine who sees what, it's still not
security. All you're doing is controlling program behavior/flow.
I'm not saying that's not incredibly useful -- I've got lots of apps
out there that use Jet ULS for no purpose other than that (i.e.,
there are no permissions set on anything other than default full
control for everyone), but it's not security. It's just a way of
piggybacking on a security infrastructure's user management
infrastructure to control program flow/behavior.
While the result of that may be that certain users don't see certain
things, it's still not what I'd call security, just a useful form of
obfuscation (i.e., security by obscurity).
Banana
> While the result of that may be that certain users don't see certain
> things, it's still not what I'd call security, just a useful form of
> obfuscation (i.e., security by obscurity).
I'm having hard time imagining how could it be termed obfuscation
because by using this technique there is no need to store any key or
password and hide it. By designing the application to depend on a
specific domain & members of group (for AD - I've really not had the
occasion to solve this for non-AD context), it cannot be used outside
the domain, or by nonusers within the domain. It may not be 'security'
but it certainly couldn't be obfuscation because there's nothing to
obfuscate unless you want to consider the machine code as obfuscation
needing to be decompiled.
Now, this would be quite useless if the requirement was not to protect
the code but also the data and the data was also stored in the ACCDE
file. In such cases, the proper answer is to move out data into a server
RDBMS, but in terms of protecting code & UI objects, this is an
effective technique. In context of using a web database inside full
Access, however, reduce the utility because of its reliance on macros
which are still editable even in an ACCDE.
David W. Fenton
news:4B5BBAFF...@Republic.com:
> David W. Fenton wrote:
>> While the result of that may be that certain users don't see
>> certain things, it's still not what I'd call security, just a
>> useful form of obfuscation (i.e., security by obscurity).
>
> I'm having hard time imagining how could it be termed obfuscation
> because by using this technique there is no need to store any key
> or password and hide it.
You're hiding the data from them in the UI, not via the database
engine itself.
> By designing the application to depend on a
> specific domain & members of group (for AD - I've really not had
> the occasion to solve this for non-AD context), it cannot be used
> outside the domain, or by nonusers within the domain. It may not
> be 'security' but it certainly couldn't be obfuscation because
> there's nothing to obfuscate unless you want to consider the
> machine code as obfuscation needing to be decompiled.
I hadn't thought about that one.
> Now, this would be quite useless if the requirement was not to
> protect the code but also the data and the data was also stored in
> the ACCDE file. In such cases, the proper answer is to move out
> data into a server RDBMS, but in terms of protecting code & UI
> objects, this is an effective technique. In context of using a web
> database inside full Access, however, reduce the utility because
> of its reliance on macros which are still editable even in an
> ACCDE.
The only way to protect the code is to compile an ACCDE.
I don't think there's a way to protect UI objects (anybody can hit
the delete key, for instance, if they can get to the database
window).
Banana
> You're hiding the data from them in the UI, not via the database
> engine itself.
Given that even with ULS configured, one still can open the file and
read the data plaintext unless an extra step is taken to encrypt the
data, it seems to me to amount to basically the same thing as the
technique of using AD or NTFS security groups - the difference being the
source for checking the security & permissions.
At least in 2010 (2007?), setting a database password also encrypt the
file in one go and (for 2007 as well) API can be changed so a different
encryption can be used, unlike the utterly useless encryption available
in 2003 and earlier.
I don't deny that it is preferable that the security is done at lowest
level possible, but the fact is that when playing with a file, there's
only so much we can do about it and while ULS is useful in managing
whether a user should interact with an object, it does really nothing to
prevent unauthorized access to the data when the engine isn't involved.
> I don't think there's a way to protect UI objects (anybody can hit
> the delete key, for instance, if they can get to the database
> window).
I'm not aware of this. In ACCDE, at least, I can still delete standalone
macros, queries & tables but not forms, reports & modules.. Is this not
the case with MDE?
David W. Fenton
news:4B5DEFFD...@Republic.com:
> In ACCDE, at least, I can still delete standalone
> macros, queries & tables but not forms, reports & modules.. Is
> this not the case with MDE?
Beats me. I haven't used MDEs since c. 1999 (well, with one
exception -- one client's bookkeeper used to be very bad typing
through error messages; grrrr that she would ignore them and never
tell me about them, but she'd end up typing into the VBA module
where the error occured and then the app wouldn't compile, so that
computer has an MDE).