People using triggers for this just don't understand proper security - its a little harder but having your deployment process have the ability to drop tables and having your general user having read only query access (or insert powers on your own schema which nobody else can read) is stuff DBAs figured out in 90s and we keep forgetting.
Well, it depends on your database, but honestly the knowledge was handed down to me by database enthusiasts of the times before.
I was studying SQL Server at the time, so Kendra Little, Brent Ozar, that whole crew were a good starting point.
Once you learn the basics of the DCL for your database, a lot of the stuff just becomes a natural extension, and almost always you're just talking about the official documentation.
Usually you have a server and a database level identity, and manipulating those usually break out the connection vs object level permissions.
Once you understand the maps of those and grouping users, you can do stuff like detect a user did a bad thing and add them to a group of users who are default denied from the database, insert a row in a table, and roll back their changes. A logon trigger that does rude things to people's sessions, or certainty that your developers can view some parts of some data without exposing your secrets.
