SQL Login Trigger
-
I need to setup a trigger that will check to see if the connection is an ODBC connection and if so directs that session to a read only databasem, otherwise lets the program connect to the live application db. I have been trying different triggers and all i have been able to accomplish is to kill the connection of the ODBC login. Any assistance would be greatly appreciated.
Thank you
-
I don't believe you can redirect the connection to another database... essentially the connection asks for login info/permissions only
yes maybe with an initial database gets passed in with the credentials, but after it connects, the active connection can try to query any databases the login has a user in...
so my connection my default with master, but i query sandbox after i connect, for example.
i like to think of a login as keys to the front door of a house...once i get inside, i can go to any room that's not locked(master,tempdb) , or if i have a key(user) to one of the rooms inside(Production,SandBox,msdb).
just like a house, if i try to open the master bedroom, you cannot make it go to the guest bedroom instead...you have to teach the people to go to the "guest bedroom" (the readonly reporting database) if they need to run reports.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The problem i am faced with is my company uses a thrid party application. This application needs to have all the users assigned to a role which has just about full access to the database. What we want to avoid is having certain users use ODBC to access the database and be able to inadvertantly insert,delete or update data. We want to limit them to read only but them being part of this one role will give them access to do what ever they want to the live database.
-
zulmanclock (11/12/2012)
The problem i am faced with is my company uses a thrid party application. This application needs to have all the users assigned to a role which has just about full access to the database. What we want to avoid is having certain users use ODBC to access the database and be able to inadvertantly insert,delete or update data. We want to limit them to read only but them being part of this one role will give them access to do what ever they want to the live database.how about using the login trigger to add them to a role, or takes the role away away again.
That role would have deny db_datawriter? then you can prevent them from update/delete/insert?
i'd have to test it to even know it would work, as it would make an assumption about each login...
any chance you can change the application to use an application role?
that would fix it easily...the app role would be the only thing that has permissions to do anything but SELECT.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I thought of doing that, i am actually trying to write the trigger and test to see if that works. The problem i am facing is that the users are all assigned to an active directory group, that AD group is then assigned to that main all access role so i have no idea how to assign them to a role with only read only since they are still assigned to that all access role..
It looks like there is already an application role setup by the vendor but not sure if it is being used by the app. I started here last week and this was the first project my boss gave me. I had the same thought for the app to use the role but maybe the vendor doesnt want to redo their application to use that role.i guess i can reach out to them to see the why behind it..
thanks for your suggestions.
-
A login trigger may help you here, but in conjunction with an application role. You might be able to trigger an application role as part of the login trigger, whic would change their role over to a read only role.
-
Steve Jones - SSC Editor (11/12/2012)
A login trigger may help you here, but in conjunction with an application role. You might be able to trigger an application role as part of the login trigger, whic would change their role over to a read only role.Would i create a new application role that is just read only and assign the instance of the ODBC login to that or how could i assign users to use that?
-
You need to read about how Application roles work.
http://msdn.microsoft.com/en-us/library/ms190998.aspx
You would invoke the role for certain people from the login trigger.
-
Steve Jones - SSC Editor (11/12/2012)
A login trigger may help you here, but in conjunction with an application role. You might be able to trigger an application role as part of the login trigger, whic would change their role over to a read only role.i was looking at it backwards;
that makes more sense Steve;
so you'd take away everyone's rights in AD to be read only, and only if the ApplicationName contain the name of the app, you'd change to an application role.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
well i cannot get a prototype to work;
this is the code i used for testing; i don't get any errors in the trigger, and a rollback command works fine, but i can still update tables .
--first we need a sysadmin role with no login, which will beused
--for execution context in any DDL/LOGON trigger.
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'superman' AND type = 'S')
BEGIN
--create our super user
CREATE LOGIN [superman]
WITH PASSWORD=N'NotARealPassword',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON;
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [superman] DISABLE
END
GO
--now we create an approle that only has read permissions...we don't need take away permissions like db_denydatawriter,
--since we are switching user contexts to the app role instead of whatever they logged in with.
USE SandBox;
GO
CREATE APPLICATION ROLE ReadOnlyForSneakyBastards
WITH PASSWORD = 'NotTheRealPassword'
, DEFAULT_SCHEMA = dbo;
exec sp_addrolemember 'db_datareader','ReadOnlyForSneakyBastards'
GO
--now a logon trigger:
ALTER TRIGGER [Logon_Trigger_ForceAppRoleOnUsers]
--we need to use a power users context to do a lot of work here.
ON ALL SERVER WITH EXECUTE AS 'superman'
FOR LOGON
AS
BEGIN --LOGON TRIGGER
IF APP_NAME() LIKE '%HaxxorPadPlusPlus%' OR APP_NAME() LIKE '%EXCEL%' OR APP_NAME() LIKE '%ACCESS%'
BEGIN
--RAISERROR('Unauthorized use of login from inpermissible host.', 16, 1)
--ROLLBACK
EXEC SandBox.sys.sp_setapprole 'ReadOnlyForSneakyBastards', 'NotTheRealPassword';
END
END --LOGON TRIGGER
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Logon_Trigger_ForceAppRoleOnUsers] ON ALL SERVER
GO
in another window, i specifically created a connection using the offending application name.
i even made sure the default database was SandBox
i was able to update tables, run procs, etc for things i would think are outside of the scope of the app role.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here is what i came up with. What i am trying to accomplish is say if the sql command is a DELETE, INSERT or UPDATE and it was requested from someone that is not in my list of approved users(P21PLAY.dbo.ODBC_LOGIN) then rollback, basically allowing only select statements.
CREATE TRIGGER [application_limit_trigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (SELECT count(SPID)
FROM master..sysprocesses (NOLOCK)
INNER JOIN SYS.DM_EXEC_REQUESTS ON master..sysprocesses.SPID = SYS.DM_EXEC_REQUESTS.SESSION_ID
INNER JOIN sys.dm_exec_sessions on sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id
CROSS APPLY sys.dm_exec_sql_text(SYS.DM_EXEC_REQUESTS.sql_handle) AS st
WHERE nt_Username NOT IN(SELECT NAME FROM P21PLAY.dbo.ODBC_LOGIN) AND
(CLIENT_INTERFACE_NAME = 'ODBC'
AND (ST.TEXT LIKE '%DELETE%'OR ST.TEXT LIKE '%UPDATE%' OR ST.TEXT LIKE '%INSERT%'))
)>0
BEGIN
ROLLBACK
END
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [application_limit_trigger] ON ALL SERVER
GO
I am using myself as a test, i dont have myself as an approved member but if i run the above i get error, if i change the NOT IN to an IN i get thru but that is not what i want. The P21PLAY.dbo.ODBC_LOGIN table is my list of approved so the NOT IN should say if the DELETE, INSERT or UPDATE came from someone in that list its ok to do if not rollback...ugh i have such a headache
🙂
-
doesn't work.
My apologies for not checking this out first. I was thinking in text here.
The idea of preventing some users from changing things would be in a DML trigger, not a DDL trigger.
-
So i was able to get a trigger that would see if the connection made was trying an insert, delete or update and if so it would block that connection and only allow a select..loop hole was if the select statement is performed first complete access is granted and you can now insert,delete and update since you already passed the login trigger....had my boss email our software vendor to see why they are not using the application role they created for their software otherwise i will have to make the reports for the end user and limit their ability to create odbc connections on their machines...
thank you for your suggestions they helped a lot.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply