INF: Providing Application Security Through Triggers in SQL (66678)

SUMMARY

This articles discusses how a form of application security can be achieved without hard-coding user names and passwords into an application. This allows people to use many different front-ends to view the information, but allows data modification to be done only from specified applications.

MORE INFORMATION

Using triggers and SQL Server system functions, it is relatively easy to provide this type of application security. The following example illustrates one of the ways you can implement application-level security:

  CREATE TRIGGER trigname
  ON tblname
  FOR UPDATE, INSERT, DELETE
  AS
    IF (SELECT DISTINCT(program_name)
      FROM master..sysprocess
      WHERE hostprocess = HOST_ID()) != "app_name"
      BEGIN
        RAISEERROR 3000 "Table may only be modified by app_name"
      END

NOTE: "app_name" is the name of the application you want to allow to have permission to modify the table.

This trigger could be modified by placing the app_name(s) into a table and checking to see if the name of the application that wants to modify the table has permission (existing in the application table). This would allow several applications to have modification power. You could even allow certain applications to only update certain fields or tables.

NOTE: Applications must make a call to DBSETLAPP() to register their names.