ID Number: Q66678
1.00 1.10 1.11 4.20
OS/2
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.
Additional reference words: 1.00 1.10 1.11 4.20 Transact-SQL triggers