January 31, 2008 at 1:16 pm
We have a database that gives read/write access to an AD group. Some users just link tables in MS-Access and run queries/update data.
I did read application level role and am not sure if I should restrict Access this way. Smart users will use WINSQL or something similar for the same purpose.
Ideally I should be able to revoke all application-level privileges and grant only a selected few applications: these being vendor developed. In other words users should be able to access the DB only from the application.
What is the best way to achieve this.
January 31, 2008 at 1:33 pm
You could revoke all the table permissions and insist that all data access is via stored procedures. In the stored procs you can check the application name. You can also do custom access if you're using procs. (your own username/password tables, application login, tokens, etc)
Application roles work, but they have a couple of downsides - no cross database access, no connection pooling,
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2008 at 1:46 pm
The application is vendor developed and I have no control over the data access.
I host the Db:sick:
January 31, 2008 at 1:58 pm
When you say it had write access to an AD group, can you explain a bit more what you mean?
K. Brian Kelley
@kbriankelley
January 31, 2008 at 2:01 pm
It has the roles of:
dbDataReader and dbDataWriter
January 31, 2008 at 2:08 pm
Now that you don't have any ability to touch most of the stuff which would allow you to prevent their access, hmmm....
It's a real challenge to be specific, since you're not providing any info as to how the vendor app accesses the data.
About the only things springing to mind would be - group policy to disable using MS Access altogether? Put a firewall around the SQL server and punch a hole that only the Vendor application server can get through? Change the IP port on the server and turn off SQL Browser (making the change only on the vendor solution, once again)?
----------------------------------------------------------------------------------
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?
January 31, 2008 at 2:15 pm
The vendor apps has a client piece that installs a DSN on each client. This DSN uses windows authentication. I explained that this like giving a copy of the key to these users.
I suggested the group policy to disable Access, this is not possible/practical.
Not sure how practical the firewall solution is as the SQL instance hosts 4 vendor developed apps/dbs!!!
January 31, 2008 at 3:15 pm
chandrika5 (1/31/2008)
It has the roles of:dbDataReader and dbDataWriter
That's permissions within the database. Are you worried about users having too many rights there? Because those rights don't carry out to AD.
Also, the app role probably won't work for you. The application has to know to use the app role. If it doesn't, then the app role never gets activated, meaning the app never gets said permissions.
K. Brian Kelley
@kbriankelley
January 31, 2008 at 6:40 pm
The application administrator just realized that there are users who access the DB by linking tables and have modified data. The administrator does not realize that we might not be able to stop this.
February 5, 2008 at 7:40 am
Is it possible to revoke all permissions at db level and grant only application level access. The application in turn has to change to accordingly this ?? I would like to suggest this to the apps administrator.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply