Anyone using application roles

[jerryk]

Hi,

I am helping port an application to SQL Server and the DBA wants to use
application roles. The application runs fine without access roles, but when
we turn them on a number of strange failures occur.

After doing some research I am starting to wonder how practical it is to use
Application roles in Access applications that have linked tables used by
local querydefs, reports, passthrough queries, etc. I can see it work for
straight ADO calls to the database, but not in these scenarios. Also, since
Access sometimes makes multiple hidden connections to a database how do you
handle these with Application Roles? Can anyone provide me some advice?

jerry

 

[Pieter Wijnen]

if you set up roles (groups) in access & test before porting to MSSQL /
Oracle I'll bet you run into the same problems...
Security is (and will always be) tricky - it is always easy to forget what
access a role must have to perform the tasks required
I say this because I'm still forgetting mundane rights after 20 years of
working with these kind of scenarios.
Stringent testing with Access Groups (aka SQL roles), will tell you what you
have forgotten, if you remove all rights from the users group, that is!

Pieter

my conversion table

SQLCONSTANT
CONSTANT
VALUE
DESCRIPTION


dbSecFullAccess
1048575
The user has full access to the object.


dbSecWriteDef
65548
The user can modify or delete the table definition, including column
and index information.


dbSecDBAdmin
8
The user can replicate a database and change the database password (not
valid for Document objects).


dbSecReadSec
131072
The user can read the object's security-related information.


dbSecReadDef
4
The user can read the table definition, including column and index
information.


dbSecDBCreate
1
The user can create new databases. This option is valid only on the
Databases container in the workgroup information file (System.mdw).


dbSecNoAccess
0
The user doesn't have access to the object (not valid for Document
objects).


dbSecWriteSec
262144
The user can alter access permissions.


dbSecDBExclusive
4
The user has exclusive access to the database.


dbSecCreate
1
The user can create new documents (not valid for Document objects).


dbSecDelete
65536
The user can delete the object.


dbSecDBOpen
2
The user can open the database.

DELETE
dbSecDeleteData
128
The user can delete records.

INSERT
dbSecInsertData
32
The user can add records.

SELECT
dbSecReadData
20
The user can retrieve data from the Document object.

UPDATE
dbSecReplaceData
64
The user can modify records.

 

[jerryk]

Pieter,

Thanks you very much for you reply. I just want to correct myself and
ensure that I have not misled you. I should not have used the term Access
Roles. I am specifically talking about MS SQL Server Application Roles.
Are MS SQL Server Application Roles related to Access Groups? If so, are
they related by name? That is, the Access Group "ADBUsers" is the same as
SQL Server Application Role "ADBUsers"?

Thanks in advance,

jerry

"Pieter Wijnen"

 

[Brendan Reynolds]

I don't have any experience of using SQL Server Application Roles myself,
Jerry, but here are links to some previous discussions on the subject ...

http://groups.google.com/groups?q=application+roles&as_ugroup=microsoft.public.access.odbcclientsvr

http://groups.google.com/groups?q=application+roles&as_ugroup=microsoft.public.access.adp.sqlserver

 

[jerryk]

Thanks Brendan,

It looks like they are best avoided!