MsAccess to SqlServer: sp_setapprole problem

  • Thread starter Thread starter adjo
  • Start date Start date
A

adjo

I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won't behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstring has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don't change at all.
I read about the '3 connections Access uses' when connecting to
Sqlserver ('How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition'). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.
 
don't beleive everything you read.

And linked tables / SQL Passthrough is a ****ing joke.

If you want to be able to BUILD objects and just use them in Access?
then you should be using Access Data Projects.

JET is obsolete; and it has been for a decade.

-Aaron
 
don't beleive everything you read.

And linked tables / SQL Passthrough is a ****ing joke.

If you want to be able to BUILD objects and just use them in Access?
then you should be using Access Data Projects.

JET is obsolete; and it has been for a decade.

-Aaron



- Tekst uit oorspronkelijk bericht weergeven -

I know Dao/Jet is obsolote, but it is still working like a charm for
me. I use Ado.net in dotnet projects. But for Access frontends I have
(had?) no reason to say goodbye. Still hope the best of both worlds
can be combined.
 
I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won't behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstring has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don't change at all.
I read about the '3 connections Access uses' when connecting to
Sqlserver ('How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition'). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.

By the way: is there another way to solve the 'get to the data via
another way than the app' problem then using the sp_setapprol
mechanism? 90% solutions are welcome as well.....
 
By the way: is there another way to solve the 'get to the data via
another way than the app' problem then using the sp_setapprol
mechanism? 90% solutions are welcome as well.....

One can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
This is as safe as the coding skills of the developer are good.
In this way users have no login or permissions of their own, so when
they create another adp, and the connection dialog opens they see no
servers. They can't login to the server, so they can't examines
usernames and passwords there.
Of course, such logins and permissions can be associated with
application roles, but if they are hidden and unknown, what's the
point of going that extra step?
Because Access opens multiple new connections erratically and
unpredictably, and because each of those connections must be
explicitly associated with an application role (where application
roles are used) this has been my practice. Actually it's not much
different than how we might do asp, where we hide connection
parameters in a special secure folder, or asp.net, where the
application hides them for us.

My experience in trying to use application roles with Access, and I've
done this on a very extensive project, is that this might double
development time. This is because Access may use connections one way
on Monday, but a slightly different way on Tuesday, depending of
course on what you had for breakfast. I know of no way to ensure that
Access will use application roles properly and consistently for pull-
downs and I resort to creating lists (strings) for them, based on very
basic ADO calls in code.

In my opinion this problem is likely to have been the determining
factor in MS abandoning ADPs. It makes ADPs potentially explosively
dangerous and MS had and has no credible solution.

There was a little girl,
Who had a little curl,
Right in the middle of her forehead.
When she was good,
She was very, very good,
But when she was bad, she had MS.
 
adjo said:
I know Dao/Jet is obsolote, but it is still working like a charm for
me. I use Ado.net in dotnet projects. But for Access frontends I have
(had?) no reason to say goodbye. Still hope the best of both worlds
can be combined.

DAO/Jet is not obsolete despite Aaron's strident postings on this topic. IN A2007
it's been updated and is now called ACE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
You're right. It's still going strong, the same old unsophisticated,
ugly, clumsy technology we knew and loved almost,twenty years ago.

Why does MS cling to it? IMO it's this way. MS wants to sell Access.
There are more people who are lazy and/or stupid than there are who
are smart and/or industrious. So their marketing is aimed at the lazy/
stupid majority. Here you go, bozos, a database you can use with no
effort, training, learning caring or education. A new, sophisticated
technology scarcely fits with that, does it?
 
lyle fairfield said:
You're right. It's still going strong, the same old unsophisticated,
ugly, clumsy technology we knew and loved almost,twenty years ago.

Why does MS cling to it? IMO it's this way. MS wants to sell Access.
There are more people who are lazy and/or stupid than there are who
are smart and/or industrious. So their marketing is aimed at the lazy/
stupid majority. Here you go, bozos, a database you can use with no
effort, training, learning caring or education. A new, sophisticated
technology scarcely fits with that, does it?

ADO has some new features that I'm aware of but none seemed
particularly useful to me.

From what I've seen ADO and DAO don't have a lot of differences. So
what else or what improvements would you suggest?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
ADO has some new features that I'm aware of but none seemed
particularly useful to me.  

From what I've seen ADO and DAO don't have a lot of differences.  So
what else or what improvements would you suggest?

Tony
--
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

----------------------

GetString Method (ADO)

Returns the Recordset as a string.

Variant = recordset.GetString(StringFormat, NumRows, ColumnDelimiter,
RowDelimiter, NullExpr)

-----------------

Save Method

Saves the Recordset in a file or Stream object.

recordset.Save Destination, PersistFormat

Parameters

Destination

Optional. A Variant that represents the complete path name of the
file where the Recordset is to be saved, or a reference to a Stream
object.

PersistFormat

Optional. A PersistFormatEnum value that specifies the format in
which the Recordset is to be saved (XML or ADTG). The default value is
adPersistADTG.

--

Open Method (ADO Recordset)

Opens a cursor on a Recordset object.

Parameters

Source

Optional. A Variant that evaluates to a valid Command object, an
SQL statement, a table name, a stored procedure call, a URL, or the
name of a file or Stream object containing a persistently stored
Recordset.

ActiveConnection

Optional. Either a Variant that evaluates to a valid Connection
object variable name, or a String that contains ConnectionString
parameters.

CursorType

Optional. A CursorTypeEnum value that determines the type of
cursor that the provider should use when opening the Recordset. The
default value is adOpenForwardOnly.

LockType

Optional. A LockTypeEnum value that determines what type of
locking (concurrency) the provider should use when opening the
Recordset. The default value is adLockReadOnly.

Options

Optional. A Long value that indicates how the provider should
evaluate the Source argument if it represents something other than a
Command object, or that the Recordset should be restored from a file
where it was previously saved. Can be one or more CommandTypeEnum or
ExecuteOptionEnum values, which can be combined with a bitwise OR
operator

----------

GetChildren Method (ADO)

Returns a Recordset whose rows represent the children of a collection
Record.

Set recordset = record.GetChildren

Return Value

A Recordset object for which each row represents a child of the
current Record object. For example, the children of a Record that
represents a directory would be the files and subdirectories contained
within the parent directory
 
Lyle, do you find those operations useful in the "normal business databases"
for which Access is so well-suited, or just when using an ADP as a front-end
to MS SQL Server? I don't seem to recall, in using Access since its
inception, a _need_ to "get a table/query as a string". Most of my clients,
even those with MS SQL Server, did not want any vital applications created
that would limit their ability to change the server back end if their
company decided to do so. Thus, ADO's were unacceptable to those clients.

Refresh my memory: didn't you write that you, for good reason, stopped using
ADPs?

Or are you talking about using ADO in an MDB/MDE/ACCDB? I'm reluctant to use
a data access technology that has already been superceded in its "natural
environment" (Microsoft's developer tools) by ADO.NET which is little like
"classic ADO" (as I am sure you are aware).

Larry Linson
Microsoft Office Access MVP


ADO has some new features that I'm aware of but none seemed
particularly useful to me.

From what I've seen ADO and DAO don't have a lot of differences. So
what else or what improvements would you suggest?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems
athttp://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

----------------------

GetString Method (ADO)

Returns the Recordset as a string.

Variant = recordset.GetString(StringFormat, NumRows, ColumnDelimiter,
RowDelimiter, NullExpr)

-----------------

Save Method

Saves the Recordset in a file or Stream object.

recordset.Save Destination, PersistFormat

Parameters

Destination

Optional. A Variant that represents the complete path name of the
file where the Recordset is to be saved, or a reference to a Stream
object.

PersistFormat

Optional. A PersistFormatEnum value that specifies the format in
which the Recordset is to be saved (XML or ADTG). The default value is
adPersistADTG.

--

Open Method (ADO Recordset)

Opens a cursor on a Recordset object.

Parameters

Source

Optional. A Variant that evaluates to a valid Command object, an
SQL statement, a table name, a stored procedure call, a URL, or the
name of a file or Stream object containing a persistently stored
Recordset.

ActiveConnection

Optional. Either a Variant that evaluates to a valid Connection
object variable name, or a String that contains ConnectionString
parameters.

CursorType

Optional. A CursorTypeEnum value that determines the type of
cursor that the provider should use when opening the Recordset. The
default value is adOpenForwardOnly.

LockType

Optional. A LockTypeEnum value that determines what type of
locking (concurrency) the provider should use when opening the
Recordset. The default value is adLockReadOnly.

Options

Optional. A Long value that indicates how the provider should
evaluate the Source argument if it represents something other than a
Command object, or that the Recordset should be restored from a file
where it was previously saved. Can be one or more CommandTypeEnum or
ExecuteOptionEnum values, which can be combined with a bitwise OR
operator

----------

GetChildren Method (ADO)

Returns a Recordset whose rows represent the children of a collection
Record.

Set recordset = record.GetChildren

Return Value

A Recordset object for which each row represents a child of the
current Record object. For example, the children of a Record that
represents a directory would be the files and subdirectories contained
within the parent directory
 
One  can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
This is as safe as the coding skills of the developer are good.
In this way users have no login or permissions of their own, so when
they create another adp, and the connection dialog opens they see no
servers. They can't login to the server, so they can't examines
usernames and passwords there.
Of course, such logins and permissions can be associated with
application roles, but if they are hidden and unknown, what's the
point of going that extra step?
Because Access opens multiple new connections erratically and
unpredictably, and because each of those connections must be
explicitly associated with an application role (where application
roles are used) this has been my practice. Actually it's not much
different than how we might do asp, where we hide connection
parameters in a special secure folder, or asp.net, where the
application hides them for us.

My experience in trying to use application roles with Access, and I've
done this on a very extensive project, is that this might double
development time. This is because Access may use connections one way
on Monday, but a slightly different way on Tuesday, depending of
course on what you had for breakfast. I know of no way to ensure that
Access will use application roles properly and consistently for pull-
downs and I resort to creating lists (strings) for them, based on very
basic ADO calls in code.

In my opinion this problem is likely to have been the determining
factor in MS abandoning ADPs. It makes ADPs potentially explosively
dangerous and MS had and has no credible solution.

There was a little girl,
Who had a little curl,
Right in the middle of her forehead.
When she was good,
She was very, very good,
But when she was bad, she had MS.- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Thanks. This is the kind of reaction I need. I already expected that
the 'sp_setapprole' is not realy useful in my situation because Access
is not suited as a stable clientenvironment. Your experiences save me
the time and frustration of keep on trying. It's one of the very few
moments I get stuck with Access as a clienttool.
One can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
Hm. With a lot of users this is not realy attractive. Think I'd rather
stick with one useraccount and hiding the accountdata as good as
possible. This works for almost 20 years now without a problem. But in
the application I'm talking about it would have been so nice to be
able to use int.security so that users have their own account. Would
also have been easier for our Dba in case performance or locking
issues should occur.
 
One  can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
This is as safe as the coding skills of the developer are good.
In this way users have no login or permissions of their own, so when
they create another adp, and the connection dialog opens they see no
servers. They can't login to the server, so they can't examines
usernames and passwords there.
Of course, such logins and permissions can be associated with
application roles, but if they are hidden and unknown, what's the
point of going that extra step?
Because Access opens multiple new connections erratically and
unpredictably, and because each of those connections must be
explicitly associated with an application role (where application
roles are used) this has been my practice. Actually it's not much
different than how we might do asp, where we hide connection
parameters in a special secure folder, or asp.net, where the
application hides them for us.

My experience in trying to use application roles with Access, and I've
done this on a very extensive project, is that this might double
development time. This is because Access may use connections one way
on Monday, but a slightly different way on Tuesday, depending of
course on what you had for breakfast. I know of no way to ensure that
Access will use application roles properly and consistently for pull-
downs and I resort to creating lists (strings) for them, based on very
basic ADO calls in code.

In my opinion this problem is likely to have been the determining
factor in MS abandoning ADPs. It makes ADPs potentially explosively
dangerous and MS had and has no credible solution.

There was a little girl,
Who had a little curl,
Right in the middle of her forehead.
When she was good,
She was very, very good,
But when she was bad, she had MS.- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Come on guys stop this everlasting discussion about the quality of
MsAccess. Don't like it then don't use it is my advice.
 
Lyle, do you find those operations useful in the "normal business databases"
for which Access is so well-suited,

I confess that the majority of "my" applications are not normal
business databases; I would describe them as "algorithmic" databases,
where calculations are primary and the collection and entry of data
are secondary. I started in this business when I was member of a team
negotiating the first collective agreement for a educational system.
The other side said, "We won't implement that because its cost CANNOT
be calculated." I said, "I can calculate its cost." (The conversation
may have been a little longer and a little harsher.) That night I did,
using Lotus and its macro language. A few days later I talked this
over with a friend from Stelco and he gave me pirated copies of DBase
III. I was hooked. (But I bought DBase III right away as the pirating
part never appealed to me.)
or just when using an ADP as a front-end
to MS SQL Server?  I don't seem to recall, in using Access since its
inception, a _need_ to "get a table/query as a string".

I find GetString and the Split function especially useful in quickly
translating a recordset into a multi-dimensional array. Arrays are
very much faster than recordsets for recursive, intense calculations.
Another use is mentioned below.
Most of my clients,
even those with MS SQL Server, did not want any vital applications created
that would limit their ability to change the server back end if their
company decided to do so.  Thus, ADO's were unacceptable to those clients.

I don't understand this. After we set the Connection once for each
user (with code or the Connection Dialog) this code grabs each user's
connection from the registry and connects to the db. Thus, each user
can have a different backend, and, of course, there would be no
limiting changing the server backend.
Public Function BaseConnect()
Dim ConnectionString$
Dim ProjectName$
ProjectName = Split(CurrentProject.Name, ".")(0)
ConnectionString$ = _
GetSetting(ProjectName, "Startup", "BaseConnectionString")
With CurrentProject
If .IsConnected Then
If .BaseConnectionString <> ConnectionString Then _
SaveSetting ProjectName, _
"Startup",
"BaseConnectionString", .BaseConnectionString
Else
If Len(ConnectionString) > 0 Then _
.OpenConnection ConnectionString
End If
End With
End Function
(This code is great for application delivery. On my development
machine it connects to my server, and on the program machines it
connects to their servers, with no fuss.)
Refresh my memory: didn't you write that you, for good reason, stopped using
ADPs?

Yes, I did. The reason was security and is directly related to the
subject of this thread. If I give you permissions on the server, but
control what you can do through my ADP application, there is a big
security problem, IMO. If you create a new ADP, the Connection dialog
will show, maybe even suggest is not too strong a word, the server and
database for which you have permissions. When you click Connect you
now can use those permissions without any control from my application.
All the tables and other objects show up in the DB window. OUCH! I
think this model is fundamentally flawed. But, I felt better when Rick
Brandt suggested the same pitfall existed with ODBC connections. It
seems that it does. But it may be much more unlikely that an MDB-ODBC
user will create a new ADP than an ADP-OLEDB user.
The solution is application roles as per this thread. The application
connection has permissions, not the user who has only login
permissions for the server, but nothing else. When he creates a new
ADP he can see or use nothing. His db window is blank and he can do
nothing, not even with code. But it's "application connection" that is
the killer here. It's the Connection that fills the role and has the
permissions, not the application as we might think. Access in general
and ADPs particularly are entirely undisciplined about connections. We
may think that there is just CurrentProject.Connection or
CodeProject.Connection or CurrentProject.AccessConnection etc existing
and we should because that's all we see. But I discovered, much to my
chagrin, that Access opens new connections for many things, including
ListBoxes and ComboBoxes. Once when demo-ing an application for ten
users, the dba who was monitoring the server used some quite
demonstrative language about "more than 100 connections" showing on
his screen, Yes, 100 connections for ten users. Each of those
connections must have application roles enabled properly and enabling
them is a pain. Even that would be OK if these connections, and when
and how they are created were documented. TTBOMK they are not
documented (maybe because they are unknown) and their creation seems
to be erratic. That is, a connection that was not required for a
listbox on Tuesday, may be required on Wednesday. It's difficult to
program around weekdays. I did complete a major application as ADP
with approles. But the hours piled up, maybe twice or even three times
what would have been required normally. For list and combo boxes I
used strings, created with ... guess what ... the GetString function!
Right now I am working on an ADP. Why? because there are only a few
users and the application gives them total control of all their data,
so a new ADP giving them total control of all their data is redundant,
and I hope, harmless. And the users are very sophisticated and know
the responsibility for any bad behavior on their part is theirs alone.
I'll fix it, if asked, but they will pay.

Addendum: ADPs provide a very simple way of interacting with Internet
Enabled SQL Servers. I could send you a less than one meg ADP, and you
and I could both work on an SQL DB in South Africa, anywhere in the
world, WITH all the protections of locking etc, and the beautiful
Access reports available to us. All you need is Access and an Internet
connection. This is amazingly powerful and universally ignored. Oh
well, it's also lucrative, especially when I'm the only one doing it.
Security? It requires a USERID and a Password. They can be encrypted.
The server has super Security software and hardware surrounding it.
Can it be broken? Probably. Will it be? I have a database on my
(rented) server. The challenge for two years has been, break in, and
in the table called Dog, create a new record and enter your name
there. So far, the table is bare.

And so, the poor dog has none.
 
adjo said:
Come on guys stop this everlasting discussion about the quality of
MsAccess. Don't like it then don't use it is my advice.

In my experience Lyle has always been extremely fair -- he criticizes
everything :-). Lyle's opinions regarding Access and ADP's are based on
hard-won firsthand knowledge and I respect and value such opinions
highly. He'll try new technologies and let you know exactly what he
thinks about them and why. Sometimes he is a little obscure in his
presentation, but that is a small price to pay for such valuable
knowledge and keeps developers from understanding too much too quickly
unless they have put in enough time exploring the issue to appreciate
his wry wisdom. He is merciless if you don't take the steps you should
before asking a question, but he is also quite rewarding to those who
approach the path of wisdom in the proper manner. Application roles are
more fundamental to database design than most expect, particularly in a
way that most don't expect. Lyle's discovery of Microsoft's
shortcomings in that area alerted me to watch that area carefully.
I.e., he is going to save me a lot of work and frustration. IMO, he is
not in the same class at all as those who bash Access in order to
support some marketing agenda.

James A. Fortune
(e-mail address removed)
 
James A. Fortune said:
In my experience Lyle has always been extremely fair -- he criticizes
everything :-).

<chuckle>

Lyle's opinions regarding Access and ADP's are based on
hard-won firsthand knowledge and I respect and value such opinions
highly. He'll try new technologies and let you know exactly what he
thinks about them and why.

<agreed> His postings are frequently thought provoking and open up my vision a bit
more.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Thanks, Lyle, for a very thorough explanation. Most of my work has been
relatively light on calculation and heavy on entry, edit, keeping, and
retrieving the data -- what I call "bookkeeping", though it's not classic
bookkeeping chores, just "keeping books on data".

Larry


Lyle, do you find those operations useful in the "normal business
databases"
for which Access is so well-suited,

I confess that the majority of "my" applications are not normal
business databases; I would describe them as "algorithmic" databases,
where calculations are primary and the collection and entry of data
are secondary. I started in this business when I was member of a team
negotiating the first collective agreement for a educational system.
The other side said, "We won't implement that because its cost CANNOT
be calculated." I said, "I can calculate its cost." (The conversation
may have been a little longer and a little harsher.) That night I did,
using Lotus and its macro language. A few days later I talked this
over with a friend from Stelco and he gave me pirated copies of DBase
III. I was hooked. (But I bought DBase III right away as the pirating
part never appealed to me.)
or just when using an ADP as a front-end
to MS SQL Server? I don't seem to recall, in using Access since its
inception, a _need_ to "get a table/query as a string".

I find GetString and the Split function especially useful in quickly
translating a recordset into a multi-dimensional array. Arrays are
very much faster than recordsets for recursive, intense calculations.
Another use is mentioned below.
Most of my clients,
even those with MS SQL Server, did not want any vital applications created
that would limit their ability to change the server back end if their
company decided to do so. Thus, ADO's were unacceptable to those clients.

I don't understand this. After we set the Connection once for each
user (with code or the Connection Dialog) this code grabs each user's
connection from the registry and connects to the db. Thus, each user
can have a different backend, and, of course, there would be no
limiting changing the server backend.
Public Function BaseConnect()
Dim ConnectionString$
Dim ProjectName$
ProjectName = Split(CurrentProject.Name, ".")(0)
ConnectionString$ = _
GetSetting(ProjectName, "Startup", "BaseConnectionString")
With CurrentProject
If .IsConnected Then
If .BaseConnectionString <> ConnectionString Then _
SaveSetting ProjectName, _
"Startup",
"BaseConnectionString", .BaseConnectionString
Else
If Len(ConnectionString) > 0 Then _
.OpenConnection ConnectionString
End If
End With
End Function
(This code is great for application delivery. On my development
machine it connects to my server, and on the program machines it
connects to their servers, with no fuss.)
Refresh my memory: didn't you write that you, for good reason, stopped
using
ADPs?

Yes, I did. The reason was security and is directly related to the
subject of this thread. If I give you permissions on the server, but
control what you can do through my ADP application, there is a big
security problem, IMO. If you create a new ADP, the Connection dialog
will show, maybe even suggest is not too strong a word, the server and
database for which you have permissions. When you click Connect you
now can use those permissions without any control from my application.
All the tables and other objects show up in the DB window. OUCH! I
think this model is fundamentally flawed. But, I felt better when Rick
Brandt suggested the same pitfall existed with ODBC connections. It
seems that it does. But it may be much more unlikely that an MDB-ODBC
user will create a new ADP than an ADP-OLEDB user.
The solution is application roles as per this thread. The application
connection has permissions, not the user who has only login
permissions for the server, but nothing else. When he creates a new
ADP he can see or use nothing. His db window is blank and he can do
nothing, not even with code. But it's "application connection" that is
the killer here. It's the Connection that fills the role and has the
permissions, not the application as we might think. Access in general
and ADPs particularly are entirely undisciplined about connections. We
may think that there is just CurrentProject.Connection or
CodeProject.Connection or CurrentProject.AccessConnection etc existing
and we should because that's all we see. But I discovered, much to my
chagrin, that Access opens new connections for many things, including
ListBoxes and ComboBoxes. Once when demo-ing an application for ten
users, the dba who was monitoring the server used some quite
demonstrative language about "more than 100 connections" showing on
his screen, Yes, 100 connections for ten users. Each of those
connections must have application roles enabled properly and enabling
them is a pain. Even that would be OK if these connections, and when
and how they are created were documented. TTBOMK they are not
documented (maybe because they are unknown) and their creation seems
to be erratic. That is, a connection that was not required for a
listbox on Tuesday, may be required on Wednesday. It's difficult to
program around weekdays. I did complete a major application as ADP
with approles. But the hours piled up, maybe twice or even three times
what would have been required normally. For list and combo boxes I
used strings, created with ... guess what ... the GetString function!
Right now I am working on an ADP. Why? because there are only a few
users and the application gives them total control of all their data,
so a new ADP giving them total control of all their data is redundant,
and I hope, harmless. And the users are very sophisticated and know
the responsibility for any bad behavior on their part is theirs alone.
I'll fix it, if asked, but they will pay.

Addendum: ADPs provide a very simple way of interacting with Internet
Enabled SQL Servers. I could send you a less than one meg ADP, and you
and I could both work on an SQL DB in South Africa, anywhere in the
world, WITH all the protections of locking etc, and the beautiful
Access reports available to us. All you need is Access and an Internet
connection. This is amazingly powerful and universally ignored. Oh
well, it's also lucrative, especially when I'm the only one doing it.
Security? It requires a USERID and a Password. They can be encrypted.
The server has super Security software and hardware surrounding it.
Can it be broken? Probably. Will it be? I have a database on my
(rented) server. The challenge for two years has been, break in, and
in the table called Dog, create a new record and enter your name
there. So far, the table is bare.

And so, the poor dog has none.
 
The solution is application roles as per this thread. The application
connection has permissions, not the user who has only login
permissions for the server, but nothing else. When he creates a new
ADP he can see or use nothing. His db window is blank and he can do
nothing, not even with code. But it's "application connection" that is
the killer here. It's the Connection that fills the role and has the
permissions, not the application as we might think. Access in general
and ADPs particularly are entirely undisciplined about connections. ...
them is a pain. Even that would be OK if these connections, and when
and how they are created were documented. TTBOMK they are not
documented (maybe because they are unknown) and their creation seems
to be erratic.

Okay. I conclude you Access in a way I like to do as well. Although it
´s not a perfect product it may function allright in a number of
situations and is a wonderfull tool for development.
My conclusion is that sp_setapprole is THE mechanism for my problem,
but that it's not possible to use it in a good way from Access. For
that reason I think I will not use the "integrated security" way in
the project I do now, but go back to the wellknown "1 useraccount and
secure the mde" approach. I still have to do some research about the
performance effects when using local account names (instead of
loginnames on the server) but I think this will do the job as well.
And no... I won't get a price for the architecture of the solution but
that's not my goal now.
Addendum: ADPs provide a very simple way of interacting with Internet
Enabled SQL Servers. I could send you a less than one meg ADP, and you
and I could both work on an SQL DB in South Africa, anywhere in the
world, WITH all the protections of locking etc, and the beautiful
Access reports available to us. All you need is Access and an Internet
connection. This is amazingly powerful and universally ignored. Oh
well, it's also lucrative, especially when I'm the only one doing it.
Security? It requires a USERID and a Password. They can be encrypted.
The server has super Security software and hardware surrounding it.
Can it be broken? Probably. Will it be? I have a database on my
(rented) server. The challenge for two years has been, break in, and
in the table called Dog, create a new record and enter your name
there. So far, the table is bare.

And so, the poor dog has none.

Wow. That's new to me. Quite interesting for certain circumstances.
And good luck with your nameless dog!
 
Wow. That's new to me. Quite interesting for certain circumstances.
And good luck with your nameless dog!

Any dog is good luck!

This cannot be said for any human, or even many humans.

As Cat (Ewwwwwww!) Stevens sang ...

I love my dog as much as I love you
But you may fade, my dog will always come through.

All he asks from me is the food to give him strength
All he ever needs is love and that he knows hell get

So, I love my dog as much as I love you
But you may fade, my dog will always come through.

All the pay I need comes shining through his eyes
I don't need no cold water to make me realize that

I love my dog as much as I love you
But you may fade, my dog will always come through.

Na, na, na, na, na, na, nana...

I love my dog as much as I love you
But you may fade, my dog will always come through.

Na, na, na, na, na, na, nana...

I love my dog, baby, I love my dog. na, na, na...
I love my dog, baby, I love my dog. na, na, na...
 
Back
Top