March 3, 2009 at 9:22 am
Hello,
For my first post, I would like to ask my most fundamental question.
For the past 15 years, I have been creating Access databases for my company, with a wide range of data categories and end-uses. We have several of these database tables that I wanted to migrate over to Sql Server to take advantage of Field-level permissions.
Unfortunately, Sql field-level permissions don't appear to carry over to my Access forms through ODBC connections. So, I assume that I need to abandon the Access Forms and go to some kind of Web App or other form of GUI.
Without having to go to school or memorize a Textbook...is there a fairly simple GUI development package out there, that I can set up some basic screens for my users? Maybe something that a MSAccess sort of fellow like m'self might understand readily?
Thanks much.
March 3, 2009 at 9:53 am
What kind of permission issues/errors are you getting? All the ODBC driver is doing is authenticating as the user setup in the connection string and SQL Server authenticates against this user. If you don't use Access you'll probably need to step up to Visual Studio. However, the learning curve can be steep.
Cheers,
Brian
March 3, 2009 at 11:30 am
Well..the main problem is that, regardless of the sql permissions setup, all users have full read/write access to the entire table.
Here's one scenario: On a Customer Specification table/Form....I have to create an environment where certain managers can have write access to an Approval field....Customer Service Reps need write access to Address and Phone fields...Some Quality Control people need write access to the Specification fields.....and all other users should have read-only access to the entire table.
In order for this project to be successful, the users should be able to log into windows as usual, they are authenticated through Active Directory, and they can open the Application without having to log in to the database application again.
Now...the problem I have is that when I set up the ODBC connection with NT Authentication, the Users Permissions don't seem to come over from the sql server, even though I have the NT users assignments made, down to the field level, on that server.
I started to think that Access was treating everyone as Admin...but what you're saying, is that the ODBC connection string is key...but doesn't that mean that there is only one user possible in that string? (on the linked table)
Sorry for rambling.
March 3, 2009 at 11:43 am
This sounds more like a SQL Server permissions issue. Have you done any thing with the server permissions on this?
If you have, you might have given everyone owner permissions. That might be why this is happening.
You can set up users through their Windows login id to SQL Server. You can then adjust each users permissions through the SQL Server interface (either SQL Server Management Studio or Query Analyzer, if you are using SQL Server 2000).
This might be a good starting point.
March 3, 2009 at 12:10 pm
If you're using Windows Authentication then the ODBC driver should be ok. I would check out the article on permissions, but it sounds like you may have this setup already (can you log directly into SQL Server and manipulate data correctly based on the credentials of the user you're logged in as?)
I did some testing and Windows Authentication using ODBC does operate correctly (it will come across with the correct user credentials -- not the user that created the ODBC driver or anything). I did notice that using Access 2003 with SQL Server 2008 there were some ODBC driver issues. What version of Access and SQL Server are you running? Did you make sure the set the ODBC driver up as a System DNS instead of a User DNS (otherwise only the user that creates the ODBC DNS can see and use it).
Just some ideas to get started. If you can get some answers I'll do some more reserach or at the very least this might give you some direction.
Cheers,
Brian
March 3, 2009 at 1:36 pm
Thanks Brian,
Well, I'm using Access 2003 and Sql Server 2000 sp2
From what you are saying, this should be working. Maybe it's just one of those situations where I did something wrong and can't backtrack to it.
One thing that might be wrong.....I created groups in the Active Directory and threw some users in there.
You know...like Quality Control...Managers....Customer Service. Then put the groups in the User List of Sql Server...then assigned permissions. I trust this is correct? Or do I have to use individual users for this type of setup?
March 3, 2009 at 1:40 pm
Thanks Nelson,
I checked out the Owner idea and Nope...it was free of that sort of thing. Good idea though. Made sense. Still makes me wonder about whether Access thinks all the users are Admin.
March 3, 2009 at 1:41 pm
Are you using Windows Authentication or a SQL Server login?
March 3, 2009 at 1:59 pm
I've tried both types of Authentication Nelson. The problem with the sql Server authentication is that it requires the users to log in each time they open the Access Database. (An inconvenience for which I received a substantial amount of grumbling). That's why I was so interested in the Windows Authentication.
March 3, 2009 at 2:07 pm
What are the permissions for a user on the database? Do you have an example? What are their permission levels?
Nelson
March 3, 2009 at 2:16 pm
See if this makes a difference. There are actually permissions inside of Access. In Access, go under Tools->Security->User and Group Permissions... see if modifying the "Read Data", Update data",etc makes any difference. It's been a few years since I've used Access, but I seem to remember having some issues with this.
Cheers,
Brian
March 3, 2009 at 2:22 pm
Nelson,
Just for an example (Since I tore down all the permissions before I started this discussion)
I have a user bsmith
She has a windows login, which has been added to the Sql Server Logins, and has been given access to the Vendor database.
In the Vendor database, there is a table called VendorTable.
I have given her Select, Insert, and Delete..With DRI not selected.
For Update, I have given her Column Update permissions on every column except Product Name.
When I go to her computer, logged on as her....I am able to change the data in every field...including Product Name.
My connection string for the Linked Table is:
ODBC;DSN=VendorDatabase;Description=Vendor Database;APP=Microsoft Office 2003;WSID=IS-HART;DATABASE=Vendor;Network=DBMSSOCN;Trusted_Connection=Yes;TABLE=dbo.VendorTable
March 3, 2009 at 2:25 pm
A few years ago I had a similar problem, where row-oriented security was needed in a medical faculty:
http://www.newdalesystems.com/articles/Article014.pdf
Although this overview doesn't mention field permissions, that was also covered by using only approved applications (usually via a Citrix app server) and a small piece of code in each app.
Basically, the idea was to keep record pointers to key tables for each user, indicating what rows/columns they could read and/or write. Although it used a huge number of pointers for 100+ users, it was surprisingly efficient with SQL Server 2000.
R Glen Cooper
March 3, 2009 at 2:30 pm
Brian,
I'll have to try your idea in the morning.(have an appointment this evening)
But I will say that within the User and Group permissions, Everything is selected...Read/Insert/Update/Delete. I can deselect any of the rights available...but these permissions are only related to the users within a workgroup that is set up in there....Not the Windows user ids....Which takes us back to the redundant logins problem.
Even so....according to all the documentation I've read....permissions should be granted in accordance with the most restrictive policy, so the denial of Update permission on a certain column in Sql Server should cause the denial of Update regardless of Write Access being granted within the Access Security section. But for some reason, it does not.
March 3, 2009 at 3:11 pm
unless you're using Access 2000 or earlier - you really should be using OLEDB and not ODBC. In addition, you should probably look at your OLE DB connection string and ensure that it's using the currently logged in user (through Trused Connection=True in the config file.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply