Security strategy: Access client/SQL Server

[Bill E.]

Note: I posted this earlier in comp.databases.ms-access but thought
that it might be more appropriate here.

I will be creating an application using MS Access as a client to SQL
Server 2005. Each user will have the client installed on his/her
machine. Some users will be attached to the local network where the
SQL Server resides. Others will access the network via VPN.

I was thinking of using an mdw workgroup file located on a network
server to control access to various user interface elements (i.e.,
forms, reports) using defined groups. In addition, I was planning to
use a single SQL Server login for all clients, just as I've done with
various .NET client apps and web apps.

*Con?: Will this hurt the performance of the application (Access must
now open a workgroup file over a potentially slow WAN connection)?
*Con: The user will have to log in twice -- once to get into the
network and once for the MS Access application.
*Con: I think that permissions on objects are housed in the
application itself (not the workgroup file). Therefore, if we add
permissions for group X to open form A, users would need to get a new
version of the application.
*Pro: A user designated as an "administrator" can easily add new users
and configure group access from the application menu.

As an alternative, I could replicate the functionality of the
workgroup file by creating tables to house lists of users, groups and
objects on the SQL Server. I could then fetch a user's permissions to
various UI elements from SQL Server by passing the user's NT login and
retrieving permissions via stored procedure.

*Pro: Users need log in only once (network).
*Pro?: No MDW file need be accessed over a potentially slow speed
connection.
*Pro: The user would not need to fetch an updated client when
permissions for objects change.
*Con: I would need to create the security structures on SQL Server and
a user interface to manage them

Perhaps there are other alternatives. Can someone give me some
guidance?

Thanks,

Bill E.
Hollywood, FL

 

[Rick Brandt]

Note: I posted this earlier in comp.databases.ms-access but thought
that it might be more appropriate here.

I will be creating an application using MS Access as a client to SQL
Server 2005. Each user will have the client installed on his/her
machine. Some users will be attached to the local network where the
SQL Server resides. Others will access the network via VPN.

I was thinking of using an mdw workgroup file located on a network
server to control access to various user interface elements (i.e.,
forms, reports) using defined groups. In addition, I was planning to
use a single SQL Server login for all clients, just as I've done with
various .NET client apps and web apps.

Why? User Level Security with an MDW file is harder to implement than the
security on SQL Server and is easily defeated whereas the security for SQL
Server is solid.

As an alternative, I could replicate the functionality of the
workgroup file by creating tables to house lists of users, groups and
objects on the SQL Server. I could then fetch a user's permissions to
various UI elements from SQL Server by passing the user's NT login and
retrieving permissions via stored procedure.

I would go this route only for guidance in the front end. That is, you can
test in code and display more user-friendly messages than just allowing the
security on the server to deny people access to certain objects.

I would NOT however use one common logon for the whole app. You have a
server with a good security model and one which is a snap to set up an
maintain (especially if you use integrated security and roles). Making use
of that is the sensible thing to do.

 

[Bill E.]

Rick,

Thanks for responding:

Why? User Level Security with an MDW file is harder to implement than the
security on SQL Server and is easily defeated whereas the security for SQL
Server is solid.

There is no such thing as SQL Server security for your application--
only for the SQL Server database. That's why I was considering using
the MDW file to control access to the forms and reports.

I would go this route only for guidance in the front end. That is, you can
test in code and display more user-friendly messages than just allowing the
security on the server to deny people access to certain objects.

I would NOT however use one common logon for the whole app. You have a
server with a good security model and one which is a snap to set up an
maintain (especially if you use integrated security and roles). Making use
of that is the sensible thing to do.

I was thinking that the problem with using Widows authentication for
the database and therefore multiple logins is that I would have to
maintain two security systems -- one for the database using the built-
in SQL Server security system and the other one that I would create to
manage access to the application elements. However, now that I think
about it, I can create some new database roles on SQL Server and then
assign each database user (login) to one or more of these roles. I
can then create an ApplicationObjects table listing my application
objects (forms/reports) and a Roles_Objects table that associates the
newly defined SQL Server roles (as found in sys.database_principals)
with the objects using principal_id as a kind of foreign key. In
other words:

CREATE TABLE ApplicationObjects
(
ObjectID int identity,
ObjectName varchar(250) not null
)

CREATE TABLE Roles_ApplicationObjects
(
ObjectID int not null,
principal_id int,
CONSTRAINT UC_Roles_ApplicationObjects UNIQUE NONCLUSTERED
(
ObjectID ASC,
principal_id
)
)

I doubt that SQL Server will let me create a foreign key between
Roles_ApplicationObjects and sys.database_principals but I can live
without it.

This leaves me with only one security system to administer and makes
use of what SQL Server has built-in. What do you think?

Bill

 

[Bill E.]

Thanks, Rick. I suppose that I'm using the word "security" for both
database security and "guidance" as you call it for user or group
level access to user interface elements.

I can create a small user interface that effectively runs SQL scripts
to add new logins/users to the server/database, assigns them to groups
and deals with groups permissions to SQL Server objects. It can also
manage the ApplicationObjects and Roles_ApplicationObjects tables I
described to assign groups access to application objects. In that
way, everything can be managed in one place.

Bill