Adp and SQL Server is there a way to query SQL permissions to controluserforms?

[Access]

In my user interface if a user doesn't have access to a field in the
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 fairfield]

Tom van Stiphout <no*************@cox.netwrote in
news:ok********************************@4ax.com:

On Sat, 22 Mar 2008 13:45:20 -0700 (PDT), lyle
<ly************@gmail.comwrote:

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.

[Tom van Stiphout]

On Sat, 22 Mar 2008 22:37:40 GMT, lyle fairfield <ly******@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.

>Tom van Stiphout <no*************@cox.netwrote in
news:ok********************************@4ax.com :

>On Sat, 22 Mar 2008 13:45:20 -0700 (PDT), lyle
<ly************@gmail.comwrote:

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.

[Tony Toews [MVP]]

Access <al*********@gmail.comwrote:

>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 Mar 22, 6:29 pm, Access <[e-mail]:

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]

On Mar 22, 9:49*pm, "Tony Toews [MVP]" <[e-mail]:

Access <[e-mail]:

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 athttp://www.granite.ab.ca/accsmstr.htm
* *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

[Tony Toews [MVP]]

Access <al*********@gmail.comwrote:

>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.

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/

[aaron.kempf]

yah; I just think that it's _FUNNY_ that Tony Toews doesn't know what
'application roles' are.

They could prevent this very thing from happening.

-Aaron

On Mar 22, 6:49*pm, "Tony Toews [MVP]" <[e-mail]:

Access <[e-mail]:

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/

[aaron.kempf]

convert away from ADP ?

are you kidding me?

http://doc.ddart.net/mssql/sql70/sp_ca-cz_24.htm

-Aaron


On Mar 23, 3:50*am, Access <[e-mail]:

On Mar 22, 9:49*pm, "TonyToews[MVP]" <[e-mail]:

Access <[e-mail]:

>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/

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

[David W. Fenton]

lyle <ly************@gmail.comwrote in
news:15**********************************@c19g2000 prf.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/