Adp and SQL Server is there a way to query SQL permissions to control userforms?
Access
database I lock it and make the background color yellow. Problem is
I'm maintaining permissions in two different places. On the server
and in my client. I was wondering if there is a way to query SQL
server on the client side and find out if the user has access to a
field. If no update then lock it. If no delete on the table then
disable delete on the form etc.
Just wondering if this is a waste of time. It would be great not to
have maintain this in two places.
lyle
A user can create another application, connect to the database and do
anything he/she wants within the permissions granted on the server.
Your/my application might just as well not exist. IMO, this makes
application level security and notifications about permissions
redundant.
As an aside, I think this renders most ADP and ODBC Access dbs that
connect to MS-SQL extremely vulnerable. Application Roles were
introduced to deal with this probem but they seem to have had a very
rocky ride and I do not see them being heavily promoted. I estimate
that using Application Roles with an ADP might rerquire five times as
much time to develop as one not using Application Roles. (I have no
experience using Application Roles with an Access application that
uses ODBC). This is because ADPs use multipe connections to the SQL-
Server and there seems to be no documentation, rhyme nor reason as to
how many and which ones exist at any given time. [An SA reported 126
concurrent connections while six people were testing an app (but he
didn't use "nice" words.)]. I swear ADP connections change according
to days of the week and phases of the moon.
MS does not seem to trumpet this fundamental security issue. Of
course, I could be all wrong and there might not be a fundamental
security issue. Until this is demonstrated to me I will continue to
use Access and MS-SQL server only in very restricted environments,
such as single-user applications.
Tom van Stiphout
<lyle.fa...@gmail.com> wrote:
How about if you revoke access to the tables, and work exclusively
with sprocs and views? Use Windows authentication, and give some
groups access to some sprocs and views and not to others.
-Tom.
Access
My users are totally dense, there isn't a chance in hell any of them
are going to create another adp and connect to this database. I just
don't want them getting a nasty odbc error that might make them cry.
My security is basically each role has read/write to all tables and
then I explicitly do a deny select, delete, insert and update. Some
tables the deny update is at the column level.
I was thinking of just creating a table which would be populated by
the results of this stored procedure
sp_helprotect
The tag property on the form would be the name of the underlying
table. I could check the security table for the table listed in the
form tag on open and change the behavior of the form to match any
denies at the table level and then loop through the controls and
check
for denies at the field level.
That would work. If I changed permissions I would only have to
delete
my security table and repopulate it using sp_helprotect.
I agree with you about adp. It really stinks that you can't give
permission to views without giving permission to the underlying
table. You can give permission to views and not tables and link to
the view with an mdb and it works fine.
The other problem I have is this Access will crash every once in a
while and when it does the file becomes corrupted. Just lucky I keep
lots of backups. Never had that problem with mdb's
-well maybe I'm dense too because I just started a new thread trying
to reply to your post :)
lyle fairfield
news:ok0bu35k9a3o4fcj8...@4ax.com:
> On Sat, 22 Mar 2008 13:45:20 -0700 (PDT), lyle
> <lyle.fa...@gmail.com> wrote:
>
> How about if you revoke access to the tables, and work exclusively
> with sprocs and views? Use Windows authentication, and give some
> groups access to some sprocs and views and not to others.
When one does this carefully I am sure security is enhamced.
If you give me delete permissions for a view, and control how I use that
permission in an application then I will probably not do anything untoward
within the application.
When I create a new application, and start examining the connections
available to me I will see the connection I use for your application, even
if I'm not looking for that. And I will be able to connect from my new
application.
And now I can use that delete permission on that view without your
knowledge, without the safeguards you have built into your application.
If I'm feeling frustrated by your application safeguards I may do just
that.
Rick Brandt
> Tom van Stiphout <no.spam...@cox.net> wrote in
> news:ok0bu35k9a3o4fcj8...@4ax.com:
>
>> On Sat, 22 Mar 2008 13:45:20 -0700 (PDT), lyle
>> <lyle.fa...@gmail.com> wrote:
>>
>> How about if you revoke access to the tables, and work exclusively
>> with sprocs and views? Use Windows authentication, and give some
>> groups access to some sprocs and views and not to others.
>
> When one does this carefully I am sure security is enhamced.
>
> If you give me delete permissions for a view, and control how I use
> that permission in an application then I will probably not do
> anything untoward within the application.
> When I create a new application, and start examining the connections
> available to me I will see the connection I use for your application,
> even if I'm not looking for that. And I will be able to connect from
> my new application.
Wouldn't using DSNless connections eliminate this?
> And now I can use that delete permission on that view without your
> knowledge, without the safeguards you have built into your
> application. If I'm feeling frustrated by your application safeguards
> I may do just that.
Can you not design your app so it connects with a SQL Server account rather
than the user's Windows account and hide the credentials of that account
from the user?
I suppose that in the minds of those that designed SQL Server when you give
an account permissions to do something they are not qualifying that by
assuming that the user is also restricted by the method he uses to connect
to the database. If an account can do damage by granting him certain
permissions then I assume you are not to give them those permissions.
If I give a user delete permissions then that is because they are authorized
to do deletions. In what manner they perform those deletions is not my
concern, or rather if it is my concern then I don't grant them those
permissions and provide some other mechanism like only having an ability to
flag records as "void" or similar.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Tom van Stiphout
wrote:
I think I'm with Rick. I start with giving the user access to certain
data. My app is just one way those rights can be exercised. Nothing
you can do with your app will exceed what I wanted you to be able to
do in the first place.
-Tom.
![Tony Toews [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjVFNFhAO8kxGtutFBSPYzx5Lbrrty7WRKPHNhjuWtTEHvsoQdk=s40-c)
Tony Toews [MVP]
>My users are totally dense, there isn't a chance in hell any of them
>are going to create another adp and connect to this database.
However you can't depend on them being dense. There could be a very expert person
masquerading as a dense user who has just procured a job with your organization.
Highly unlikely but possible.
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/
lyle
> On Sat, 22 Mar 2008 22:37:40 GMT, lyle fairfield <lylef...@yah00.ca>
> wrote:
>
> I think I'm with Rick. I start with giving the user access to certain
> data. My app is just one way those rights can be exercised. Nothing
> you can do with your app will exceed what I wanted you to be able to
> do in the first place.
>
> -Tom.
I think that means that you agree that application level control and
security may be circumvented simply by connecting with another
application.
I think "giving the user access to certain data" is fine. But what
about "users"? Think of an organization with 100 sites. We manage site
employees and other site resources in a central database. When site
users log on through an Access application, the application restricts
them (through examination of their Windows Authentication) to viewing
and editing their own site's data. This can work well, but if one of
those one hundred site managers connects through another application
all the organizations data will be available to him/her unless we have
created individual permissions for each of the site users at the
server level. Managing 100 server logins may be problematic. I have
yet to see the situation where everyone has the correct permissions
but not any more than the correct permissions.
Application Roles seem to be a good answer. But as I pointed out
Access and Application Roles seem to be like oil and water. In my
experience, inevitably, problems with connections arise, sometimes
with connections that have worked properly for weeks or months. Last
time I checked, data-bound pull-downs were impossible in this setup;
recordsets had to be instantiated and delimited strings built from
them.
Application Roles and an ADP are fine if one programs each form and
report form to use an ADO recordset, created in the object's module.
But I think that means that Application Roles + Access <> RAD.
lyle
> My users are totally dense, there isn't a chance in hell any of them
> are going to create another adp and connect to this database. I just
> don't want them getting a nasty odbc error that might make them cry.
Maybe an OLEDB error with an ADP (http://office.microsoft.com/en-us/
access/HP052731031033.aspx)
Access
> Access <alderran...@gmail.com> wrote:
> >My users are totally dense, there isn't a chance in hell any of them
> >are going to create another adp and connect to this database.
>
> However you can't depend on them being dense. There could be a very expert person
> masquerading as a dense user who has just procured a job with your organization.
> Highly unlikely but possible.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Let's say I convert this thing to .mdb and only give users access to
views which filter the data appropriately. I'm not arguing about the
limitations in adp.
The question of locking down fields on the form based on the users SQL
permission level remains. I have three groups PowerUsers (internal
people), Site Coordinators (remote sites connecting to the database
using a terminal server) and ReadOnly users. I want to have one app
not three and when a readonly person logs in all fields are locked and
the form doesn't present a new record button etc, if they are site
coordinators some of the fields are locked and some are not. Some
tables they can add to others they cannot. PowerUsers have access to
more but not all.
Maybe there isn't a way
Rick Brandt
When one requires restricting the rows a user sees in a table based on who
they are that is a specific case where I can see your dilemma. Surely SQL
Server has this capability without relying on the application does it not?
I have never tried it, but there are functions that recognize the current
user. Wouldn't that mean that a view could be built that would only return
those rows? If not a View certainly a Stored Procedure.
Since most security is based on roles rather than individuals I suspect that
you would need 100 roles representing the sites and then have a function
that filters based on that role.
lyle
Yes, I agree. The problem is that 100 roles may be costly to maintain.
DBAs that I know are not enthusiastic about creating logins and roles.
When Freda is ill and Jarod is substituting for her, it seems that
DBAs are very busy, and getting Jarod permissions to do Freda's work
can take days.
<Rambling>
Computers are very easy to blame. When I go into a store and the clerk
says, "Sorry, about that, Computer Glitch!" I say, "Uh Huh!"
I wrote an application once that searched addresses and identified the
local school trustee for same. The first try was for "23 Oriole
Court". It brought up Mr. Smith. But it should have brought up Mrs.
Jones. This meant that the Computer Program FAILED. I investigated. It
seems that the caller misspoke. Actually she lived at 23 Oriole
Crescent. Twenty-three Oriole Crescent returned, properly, Mrs. Jones.
But the reputation of the application was forever flawed. You couldn't
trust it!
And to that I say, "Uh Huh".
When the DBA frowns when an application is mentioned it undermines the
application's credibility in the entire organization. Recently I have
tried to rely as little as possible on the DBA, altering my SPROCS,
VIEWS and FUNCTIONs in VBA code (example below). So the DBA smiles and
says, "Oh that application is so cool. It NEVER requires any
updating".
And to that I say, "Uh Huh".
</Rambling>
' this is developer's script
' it is recommended that it not be run or messed with
'Private Sub UpdateFTEBaseView()
' Dim roles As ADODB.Recordset
' Dim alterView$
'
' alterView = alterView & vbNewLine & "ALTER VIEW [dbo].[FTEBase]"
' alterView = alterView & vbNewLine & "AS"
' alterView = alterView & vbNewLine & "SELECT"
'
' Set roles = CurrentProject.Connection.Execute("SELECT ID, Name
FROM ClassStaffRoles ORDER BY Sequence")
'
' With roles
' While Not .EOF
' alterView = alterView & vbNewLine & "["
& .Fields("ID").Value & "] AS " _
' & "[FTE" & .Fields("Name").Value & "]"
' .MoveNext
' If Not .EOF Then alterView = alterView & ", "
' Wend
' End With
'
' alterView = alterView & vbNewLine & "FROM"
' alterView = alterView & vbNewLine & "(SELECT ID, Minutes FROM
ClassStaffRoles)"
' alterView = alterView & vbNewLine & "FTEStaff"
' alterView = alterView & vbNewLine & "PIVOT"
' alterView = alterView & vbNewLine & "("
' alterView = alterView & vbNewLine & "Sum (Minutes)"
' alterView = alterView & vbNewLine & "FOR ID IN"
'
' alterView = alterView & vbNewLine & "("
' With roles
' .MoveFirst
' While Not .EOF
' alterView = alterView & "[" & .Fields("ID").Value & "]"
' .MoveNext
' If Not .EOF Then
' alterView = alterView & ", "
' Else
' alterView = alterView & ")"
' End If
' Wend
' End With
' alterView = alterView & vbNewLine & ")"
' alterView = alterView & vbNewLine & "AS pvt"
'
' If MsgBox("Execute" & alterView, vbQuestion Or vbYesNo, "ffdba")
= vbYes Then
' CurrentProject.Connection.Execute alterView
' RefreshViews
' End If
'
'End Sub
'
'Private Sub UpdateStaffBaseView()
' Dim roles As ADODB.Recordset
' Dim alterView$
'
' alterView = alterView & vbNewLine & "ALTER VIEW [dbo].
[StaffBase]"
' alterView = alterView & vbNewLine & "AS"
' alterView = alterView & vbNewLine & "SELECT Program, Class,"
'
' Set roles = CurrentProject.Connection.Execute("SELECT ID, Name
FROM ClassStaffRoles ORDER BY Sequence")
'
' With roles
' While Not .EOF
' alterView = alterView & vbNewLine & "["
& .Fields("ID").Value & "] AS " _
' & "[" & .Fields("Name").Value & "]"
' .MoveNext
' If Not .EOF Then alterView = alterView & ", "
' Wend
' End With
'
' alterView = alterView & vbNewLine & "FROM"
' alterView = alterView & vbNewLine & "(SELECT Program, Class,
[Role], Minutes FROM ClassStaff)"
' alterView = alterView & vbNewLine & "ClassStaff"
' alterView = alterView & vbNewLine & "PIVOT"
' alterView = alterView & vbNewLine & "("
' alterView = alterView & vbNewLine & "Sum (Minutes)"
' alterView = alterView & vbNewLine & "FOR Role IN"
'
' alterView = alterView & vbNewLine & "("
' With roles
' .MoveFirst
' While Not .EOF
' alterView = alterView & "[" & .Fields("ID").Value & "]"
' .MoveNext
' If Not .EOF Then
' alterView = alterView & ", "
' Else
' alterView = alterView & ")"
' End If
' Wend
' End With
' alterView = alterView & vbNewLine & ")"
' alterView = alterView & vbNewLine & "AS pvt"
'
' If MsgBox("Execute" & alterView, vbQuestion Or vbYesNo, "ffdba")
= vbYes Then
' CurrentProject.Connection.Execute alterView
' RefreshViews
' End If
'End Sub
Rick Brandt
> Yes, I agree. The problem is that 100 roles may be costly to maintain.
> DBAs that I know are not enthusiastic about creating logins and roles.
> When Freda is ill and Jarod is substituting for her, it seems that
> DBAs are very busy, and getting Jarod permissions to do Freda's work
> can take days.
I guess I'm spoiled in that regard because I am my own DBA.
Tom van Stiphout
<lyle.fa...@gmail.com> wrote:
To be sure, I don't often deal with that many sites. I was assuming
all users were in Active Directory, assigned to groups (Accounting,
Production, RegionalManagers, etc.) and those groups were given access
to certain SQL Server objects. That may not be the case in your
situation, although it is possible to organize the company that way.
One of our largest clients manages about 250 sites in North America
and all their employees are in the same Active Directory.
-Tom.
Tony Toews [MVP]
>> >My users are totally dense, there isn't a chance in hell any of them
>> >are going to create another adp and connect to this database.
>>
>> However you can't depend on them being dense. There could be a very expert person
>> masquerading as a dense user who has just procured a job with your organization.
>> Highly unlikely but possible.
>
>Let's say I convert this thing to .mdb and only give users access to
>views which filter the data appropriately. I'm not arguing about the
>limitations in adp.
>
>The question of locking down fields on the form based on the users SQL
>permission level remains. I have three groups PowerUsers (internal
>people), Site Coordinators (remote sites connecting to the database
>using a terminal server) and ReadOnly users. I want to have one app
>not three and when a readonly person logs in all fields are locked and
>the form doesn't present a new record button etc, if they are site
>coordinators some of the fields are locked and some are not. Some
>tables they can add to others they cannot. PowerUsers have access to
>more but not all.
>
>
>
>Maybe there isn't a way
I've done very little work with SQL Server so I can't answer your question on how you
can interrogate SQL Server to get the information you need. A long term solution
would be Active Directory, creating appropriate groups of users and interrogating
those settings.
lyle
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
There are many ways to canvass the Server. Since the op is using an
ADP, he/she is using ADO so maybe that's a good route. Can/May I
update the [Name] column of the School Table. Let's see:
Public Function Privileged( _
ByVal Table$, _
ByVal Column$, _
ByVal Action$) As Boolean
Dim r As ADODB.Recordset
Set r = CurrentProject.Connection.OpenSchema( _
adSchemaColumnPrivileges, _
Array(Empty, Empty, Table, Column))
With r
If Not .EOF Then
r.Filter = "PRIVILEGE_TYPE = '" & Action & "'"
Privileged = Not r.EOF
End If
End With
End Function
Sub temp()
Debug.Print Privileged("Schools", "Name", "Update")
End Sub
'true
Yes, I can. The function is !!!!!!!!!!!!air code = not tested at
all!!!!!!!!!!!!!!!. Of course there are SQL functions that will return
the same information. And we could poll the tables where the info is
held directly.
I consider this entirely redundant. If memory serves me correctly for
an ADP when one tries to do something for which one does not have
permission, one gets a polite message saying just that. I assume the
message needs to appear only once or twice before people get the idea
and stop trying.
aaron...@gmail.com
'application roles' are.
They could prevent this very thing from happening.
-Aaron
On Mar 22, 6:49 pm, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote:
> Access <alderran...@gmail.com> wrote:
> >My users are totally dense, there isn't a chance in hell any of them
> >are going to create another adp and connect to this database.
>
> However you can't depend on them being dense. There could be a very expert person
> masquerading as a dense user who has just procured a job with your organization.
> Highly unlikely but possible.
>
> Tony
> --TonyToews, Microsoft Access MVP
aaron...@gmail.com
are you kidding me?
http://doc.ddart.net/mssql/sql70/sp_ca-cz_24.htm
-Aaron
On Mar 23, 3:50 am, Access <alderran...@gmail.com> wrote:
> On Mar 22, 9:49 pm, "TonyToews[MVP]" <tto...@telusplanet.net> wrote:
>
> > Access <alderran...@gmail.com> wrote:
> > >My users are totally dense, there isn't a chance in hell any of them
> > >are going to create another adp and connect to this database.
>
> > However you can't depend on them being dense. There could be a very expert person
> > masquerading as a dense user who has just procured a job with your organization.
> > Highly unlikely but possible.
>
> >Tony
> > --
> >TonyToews, 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'sMicrosoft Access Blog -http://msmvps.com/blogs/access/
David W. Fenton
news:15e28e6f-9540-4000...@c19g2000prf.googlegroups.co
m:
> Application Roles were
> introduced to deal with this probem but they seem to have had a
> very rocky ride and I do not see them being heavily promoted.
Lyle, I spent extensive time and effort back in 1998 learning about
NT security, because that was the point at which I was asked to take
on administering an NT server. What I've found is that nobody but me
seems to know a damned thing about NT security and the proper ways
to set it up. People just use the default groups, instead of setting
up security groups specific to their organization. One would have
thought that Active Directory would have caused people to be more
sensible, but I haven't seen it at all.
People use the security as it's set up out of the box, and give no
thought to anything else.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton
news:ccf8b1a6-d8ea-4e96...@p25g2000hsf.googlegroups.co
m:
> I consider this entirely redundant. If memory serves me correctly
> for an ADP when one tries to do something for which one does not
> have permission, one gets a polite message saying just that. I
> assume the message needs to appear only once or twice before
> people get the idea and stop trying.
You shouldn't allow someone to attempt something they can't complete
-- that's a basic principle of UI design. So, I think it's perfectly
worthwhile to display the read-only fields differently from the
editable fields.
David W. Fenton
news:e8b4de00-63b2-4745...@h11g2000prf.googlegroups.co
m:
> The problem is that 100 roles may be costly to maintain.
> DBAs that I know are not enthusiastic about creating logins and
> roles. When Freda is ill and Jarod is substituting for her, it
> seems that DBAs are very busy, and getting Jarod permissions to do
> Freda's work can take days.
This is what security groups are for. One logon can be a member of
multiple security groups. If every security group (i.e., "role") has
only one member, then the person setting up the security groups IS
AN IDIOT.