Blocking MS Access from linking tables...

Good morning...
I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added that group to SQL Server logins and gave it
public, datareader, and datawriter rights to the one database that's used.
My front end is locked down, but I want to stop users from creating a new
.mdb and linking SQL Server tables through DSNs or ADO connections or even
just importing the links from the actual front end.. I've tried setting the
"denydatareader" security policy - that keeps the SQL tables from being seen
in the import/link list- but also blocks read rights from the actual front
end database. I could set an Access database password on the front end to
block importing the links, but that only solves one of the three problems
and I want to stay away from Access security altogether.
Is there a way to stop users from creating their own DSNs or connection
objects or linking tables while still using Windows authentication?
Thanks.
Matthew Wells
MWells@.FirstByte.netNo. If you're using Windows authentication, and have granted
users/roles permissions on the base tables, then they can get at the
data no matter what tool they use. An alternative would be to revoke
permissions to public on the tables, and create an Access application
that does not use linked tables, but instead uses pass-through queries
to execute stored procedures. This is a lot more work since you'll
need to create an unbound FE, but it can be done. Only users who are
comfortable working with stored procedures would be able to get at the
data. Another option would be application roles, but they are a really
poor choice for linked table apps. see
http://support.microsoft.com/defaul...;EN-US;Q229564.
--Mary
On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>Good morning...
>I have an Access front end that uses SQL Server linked tables. SQL Server
>uses Windows authentication. I have one Windows group that all Access user
s
>are a member of. I added that group to SQL Server logins and gave it
>public, datareader, and datawriter rights to the one database that's used.
>My front end is locked down, but I want to stop users from creating a new
>.mdb and linking SQL Server tables through DSNs or ADO connections or even
>just importing the links from the actual front end.. I've tried setting th
e
>"denydatareader" security policy - that keeps the SQL tables from being see
n
>in the import/link list- but also blocks read rights from the actual front
>end database. I could set an Access database password on the front end to
>block importing the links, but that only solves one of the three problems
>and I want to stay away from Access security altogether.
>Is there a way to stop users from creating their own DSNs or connection
>objects or linking tables while still using Windows authentication?
>Thanks.
>Matthew Wells
>MWells@.FirstByte.net
>|||I read that article. It seems to apply only to ADO conenctions. Aren't
linked tables DAO? Does connection pooling work the same way? This is a
database that was converted from Access to SQL Server. We have to lock down
the data from any outside attempts to get it. I don't want to use SQL
authentication because I don't want to maintain two sets of security logins.
I know that using an Access form can create multiple SPIDs on SQL Server
(combo box rowsources et al). What is the downside of using Application
Roles?
Thanks.
Matthew Wells
MWells@.FirstFleet.com
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
> No. If you're using Windows authentication, and have granted
> users/roles permissions on the base tables, then they can get at the
> data no matter what tool they use. An alternative would be to revoke
> permissions to public on the tables, and create an Access application
> that does not use linked tables, but instead uses pass-through queries
> to execute stored procedures. This is a lot more work since you'll
> need to create an unbound FE, but it can be done. Only users who are
> comfortable working with stored procedures would be able to get at the
> data. Another option would be application roles, but they are a really
> poor choice for linked table apps. see
> http://support.microsoft.com/defaul...;EN-US;Q229564.
> --Mary
> On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
> <MWells@.FirstByte.net> wrote:
>
Server[vbcol=seagreen]
users[vbcol=seagreen]
used.[vbcol=seagreen]
even[vbcol=seagreen]
the[vbcol=seagreen]
seen[vbcol=seagreen]
front[vbcol=seagreen]
to[vbcol=seagreen]
>|||Yes, connection pooling works the same way. If you are serious about
locking down the SQL Server database, then DO NOT use Access as a
front-end unless you use it in an unbound scenario as I described
below. You will need to revoke or deny permissions on the tables and
create parameterized stored procedures for all DML operations so that
users can interact with the data only through your stored procedures,
which are executed using a least-priviledged account.
Application roles are intrinsically insecure because you must store
the password that activates them on the client, where it can be
discovered by a determined attacker. If you create an unbound
application that executes under least priviledges, then there likely
won't be much penalty for using them (other than performance) or much
harm if the password is uncovered, but then there's not much benefit,
either. Using Windows authentication is more secure than SQL logins,
and if all users belong to roles that have extremely restricted
permissions that only allow them to execute parameterized stored
procedures, then that's about the best you can do. You can also
provide additional verification and validation in your stored
procedure code (for example, only allowing users to access rows that
they "own").
There is code and discussion of the techniques involved in writing an
unbound Access application in the book described in my sig. It's a lot
of work, but if security is a priority then you have to do it. Access
was designed to be easy to use, not secure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 01 Nov 2004 14:29:21 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>I read that article. It seems to apply only to ADO conenctions. Aren't
>linked tables DAO? Does connection pooling work the same way? This is a
>database that was converted from Access to SQL Server. We have to lock dow
n
>the data from any outside attempts to get it. I don't want to use SQL
>authentication because I don't want to maintain two sets of security logins
.
>I know that using an Access form can create multiple SPIDs on SQL Server
>(combo box rowsources et al). What is the downside of using Application
>Roles?
>Thanks.
>Matthew Wells
>MWells@.FirstFleet.com
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
>Server
>users
>used.
>even
>the
>seen
>front
>to
>