October 1, 2008 at 10:33 am
I saw a similar post "Disabling Query Analyzer Access" Posted 8/3/2008 but the solution was to not allow the Client tool from being Installed on unauthorized machines.
I have Sql Server 2005 Enterprise edition running several Off the Shelf products that access our production database. We mainly use Windows authentication and have all users grouped into Windows Groups for access to specific databases based on the group.
After having someone delete a row in one of our tables (the trigger was disabled to capture who) I fixed that but wondered who and how people were accessing our databases. I decided to run a trace via Sql Profiler and found that users were logging into the databases and running queries from application not approved. We just assumed people would access the database with the applications provided. Since we have such a varied workforce many may have done this in past workplaces and continued the habit.
I have a number of application that I want to block from accessing the database but unsure how to to this and was wondering if there was anyone who is having the same issue and how they resolved. Yes we are putting into place some rules and practices but I want to be a little more forceful in keeping unwanted access to the database.
Any ideas or help would be greatly appreciated...Thanks in advance...:D
October 1, 2008 at 11:45 am
The only way i suggest is to set up ROLES on all databases
DatabaseA
ROLEA (Then give data tables to this role).
Only people in WINDOWS/NT Group will have access to this ROLE.
Do not give delete rights at all only give tables read access in the ROLE - once you set up the role you can set securables which is the tables it can read.
If it is a pre mad application then you have separate WINDOWS/NT accounts for each application.
That way if the app is supposed to delete then that is up to the developer to ensure this is working correctly.
None of my developers get on to production .........only the Application WINDOWS/NT and each
application has it own login account.
Hope that helps.
October 1, 2008 at 12:22 pm
Having Roles is a good Primary Choice.
consider Blocking Incoming Applications at FIREWALL. (Firewall Rules)
Create a TRIGGER to check for username and Program_name from sysprocesses and immideatly Kill the Process inside the TRIGGER Code. Or Notify the User etc.
Maninder
www.dbanation.com
October 1, 2008 at 12:36 pm
That even better i like that idea...........gosh how to become a nasty DBA overnight.
October 1, 2008 at 3:32 pm
I like the idea of using a trigger and see that there are new DDL triggers at the database and server levels. But I don't see how I can leverage this as the events that can be audited are :
The Database level events that can be audited are:
DDL Table events: Create table, Alter table, Drop table
DDL view events : Create view, Alter view, Drop view
DDL trigger events :Create trigger, Drop trigger, Alter trigger
DDL synonym events: Create synonym, drop synonym
DDL Index events: Create index, Alter index, Drop Index
DDL Database level security events:
Create User, Drop user, Alter user
Create role, Drop role, Alter role
Create application role, Drop application role, Alter Application role
Create Schema, Drop Schema, Alter Schema
Grant database access, Revoke database access, Deny Database access
DDL Service broker events:
Create Message type, Alter Message type, Drop Message type
Create contract, Drop contract, Alter contract
Create Service, Alter service, Drop Service
Create route, Drop route, Alter route
The server level events that can be audited are
Create Database, Drop Database
Create Login, Drop Login, Alter Login
I'm not sure how I would set up a trigger based on the events listed above. I understand querying sysprocess to get the application and user but unsure what type of trigger I could use???
The user(s) are not doing Inserts, Deletes, Updates just Selects...
Thanks again for any advise or direction
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply