Module Signing For Database Rights

Solomon Rutzky shows how to use module signing to grant granular permissions to users:

Scenario: We want to allow one or more Users and/or Database Roles to be able to truncate certain Tables, but not all Tables. We certainly do not want to allow anyone the ability to make structural changes to the Table.

Also, it is likely that, over time, at least one more Tables will be added that the User(s) and/or Role(s) should be able to truncate, and less likely, though not impossible, that one or more tables that they should be able to truncate now might be removed.

Truncation is a great example of the kind of right you’d want behind a signed stored procedure, as the level of right necessary to truncate a table is absurd:  practically full control of the table.  Module signing is something that I wish more DBAs knew and implemented.

Related Posts

SQL Server Agent Security

Claudio Silva explains how you can provide secure access to manage SQL Agent jobs: It is common having services accounts that are job owners so they can run within the proper context. In my humble opinion, this starts to be a little strange when it comes to modifying the agent job. It means that the […]

Read More

Storing Passwords in the Database

Randolph West explains the problems with storing passwords in the database and explains the best alternative: If you are storing passwords in a database, you should stop doing that immediately. We, as software developers and data professionals, should never know what passwords our customers are using. The same goes for most sensitive data: we technical […]

Read More

Categories

March 2018
MTWTFSS
« Feb Apr »
 1234
567891011
12131415161718
19202122232425
262728293031