1 / 6

BA372 Stored Procedures and Triggers Lab

BA372 Stored Procedures and Triggers Lab. What needs to be done to change a customer’s credit limit?. Get the user name from the system Windows handles this when it connects to the DB. Who am I? May I? Do it Log it Display. A database lists users in roles

kalil
Télécharger la présentation

BA372 Stored Procedures and Triggers Lab

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BA372 Stored Procedures and Triggers Lab

  2. What needs to be done to change a customer’s credit limit? Get the user name from the system Windows handles this when it connects to the DB • Who am I? • May I? • Do it • Log it • Display A database lists users in roles This role is called ChgClientCreditLimit if ( (Select count(*) where Person, Role) > 0) OK Update Clients Set CreditLimit=? , this customer Worked? Remember what was done by whom Forbidden? Remember who tried Tell the user what happened

  3. C#.Net Scenario 1 – Client Heavy 3 pages of C# code with embedded table/column names, authorization rules, and business logic Connect, Authenticate, Check for success Specify authorization parameters Specify tables, columns, and SQL ‘Blindly’ perform SQL instructions Execute and check success Specify update parameters Specify tables, columns, and SQL Execute and check success Specify logging parameters Specify tables, columns, and SQL Execute and check success Specify Results parameters Specify tables, columns, and SQL DB Server Execute and check success Display results

  4. C#.Net DB Server Scenario 2 – Stored Proc Stored Procedure ChgClientCreditLimit Connect, Authenticate, Check for success Specify authorization parameters Specify tables, columns, and SQL Execute and check success Exec Stored Proc  Specify update parameters Specify tables, columns, and SQL Execute and check success Specify logging parameters Half the C# code but involved DB procedure code: authorization logic, logging functions, and table/column details are not included in the C# program Specify tables, columns, and SQL Execute and check success Specify Results parameters Specify tables, columns, and SQL Execute and check success Display results

  5. C#.Net DB Server Scenario 3 – Proc + Trigger Stored Procedure ChgClientCreditLimit Connect, Authenticate, Check for success Logging is moved into a trigger. Changes are logged no matter how the updates are made: code, proc, or utility In our lab, authorization is also moved to its own proc, AuthCheck ,which logs denied attempts Specify authorization parameters Specify update parameters Specify tables, columns, and SQL Exec Stored Proc  Execute and check success Display results Specify tables, columns, and SQL Execute and check success Specify logging parameters Specify tables, columns, and SQL Database Trigger Logs the Activity Execute and check success Specify Results parameters Specify tables, columns, and SQL Trigger Fires Automatically 

  6. Things to Ponder • Which solution has the most cohesive modules? • How is data independence affected? • Heterogeneity: Web? Automated? Mobile? • What will an interface programmer need to know? • Reliability, performance, and control • DB locks, speed, memory, impact of an error, restoring data, cross-platform consistency • Compare the security of a single logging proc and auth proc vs. SQL in multiple code modules Moving functionality from client, to web server, to DB code profoundly affects a variety of important issues. Which is best? IT DEPENDS

More Related