0

Is it possible to create some "system" trigger, through which it would be possible to enable / disable the execution of update, insert, delete commands on the whole database, for certain users and / or certain applications?

I know I can do a logon trigger that will not allow the users to log in and / or from applications to the SQL server. I know it would probably work by creating triggers on each table, but that's impractical. After all, the SSMS Activity Monitor sees exactly which user is running which command from which host.

Example: Application1, application2 and user1, user2

SQL Server 2014:

  • User1 from application1 can run all commands (select, update, insert, delete ...)

  • User1 from application2 is not allowed run update, insert, delete, other commands are allowed

  • User2 from any application can run all commands (select, update, insert, delete ...)

Miroslav
  • 3
  • 3
  • [Why should I "tag my RDBMS"?](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) - please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Nov 13 '21 at 07:41
  • SQL Server 2014. – Miroslav Nov 13 '21 at 10:34
  • Most of what you want is covered [here](https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-2016). Create roles with required permissions and assign users to the roles – Peter Smith Nov 13 '21 at 10:49
  • It is not possible to create some trigger ? something like that : IF (APP_NAME() NOT IN ('Application1','Application2','SuperApp3000','LegacyApp','DevApp1') ) BEGIN RAISERROR('You are not allowed to login from this application name.', 16, 1); ROLLBACK; – Miroslav Nov 13 '21 at 13:26
  • A conventional security model would have roles, as Peter Smith said. Why try to "roll your own" with hacks like triggers? What if you need to add permissions to an app/user? Update 90 triggers, or add the user to a DB role? – HardCode Nov 17 '21 at 16:38
  • OK, i will try it over roles. Is it possible to combine user and application role ? – Miroslav Nov 18 '21 at 12:45
  • Is it possible to combine user and application role ? Because I need : Application1, application2 and user1, user2 User1 from application1 can run all commands (select, update, insert, delete ...) User1 from application2 is not allowed run update, insert, delete, other commands are allowed User2 from any application can run all commands (select, update, insert, delete ...) – Miroslav Nov 30 '21 at 10:30

1 Answers1

0

I solved it by combining of linking servers (RO user) from Test SQL server and logon trigger on Prod SQL server. Thanks.

Miroslav
  • 3
  • 3