Page 308 - DCAP103_Principle of operating system
P. 308
Unit 9: System Security
9.5.1.6 Second Layer of Defense (Stored Procedures) Notes
A stored procedure is a precompiled sequence of Transact-SQL commands in the database
that are executed by calling the procedure within another SQL command or from the database
driver. It also significantly reduces the amount of data being transmitted between the database
server and the web server.
User defined stored procedures can be used to perform several activities at the database such as
authentication and authorization checks. A stored procedure can also be used to manage user
sessions by using authentication tokens. The following diagram explains how a stored procedure
can be used for authentication and authorization purposes.
Figure 9.3: Authentication and Authorization Purposes
In the above diagram, UDSP Login is the stored procedure used to perform authentication checks
and UDSP Execute is the stored procedure used to perform authorization checks.
Authentication checks
When a user submits the login credentials, the application sends it to the database in an SQL
query. The SQL query calls the stored procedure UDSP Login, which verifies the credentials and
if correct returns a randomly created token. The database server responds with authentication
success along with the token. This token is mapped with the username used for login in a
database table and is used as a Session ID in a web application. Incase the authentication fails,
the database returns only an authentication failure message. This protects the application from
various authentication attacks.
Authorization checks
After the user has logged in to the application, all transaction/execution requests contains the
token created at the time of authentication. The SQL query request calls the UDSP_Execute stored
procedure, which validates the request by using the token to identify the user mapped to the
token and the rights assigned to that user. This protects the application against unauthorized
access and privilege escalation attacks.
LOVELY PROFESSIONAL UNIVERSITY 301
