《数据库管理系统：ch08 Application Design and Development》由会员分享，可在线阅读，更多相关《数据库管理系统：ch08 Application Design and Development（39页珍藏版）》请在金锄头文库上搜索。
1、Database System ConceptsSilberschatz, Korth and SudarshanSee www.db- for conditions on re-use Silberschatz, Korth and SudarshanDatabase System ConceptsChapter 8: Application Design and Development Silberschatz, Korth and Sudarshan8.2Database System Concepts - 5th Edition, Aug 9, 2005.Chapter 8: Appl
2、ication Design and Development Chapter 8: Application Design and Development nUser Interfaces and ToolsnWeb Interfaces to DatabasesnWeb FundamentalsnServlets and JSPnBuilding Large Web ApplicationsnTriggersnAuthorization in SQLnApplication Security2Silberschatz, Korth and Sudarshan8.3Database System
3、 Concepts - 5th Edition, Aug 9, 2005.User Interfaces and ToolsnMost database users do not use a query language like SQL.lFormslGraphical user interfaceslReport generatorslData analysis tools (see Chapter 18)nMany interfaces are Web-basednBack-end (Web server) uses such technologies aslJava servletsl
4、Java Server Pages (JSP)lActive Server Pages (ASP)3Silberschatz, Korth and Sudarshan8.4Database System Concepts - 5th Edition, Aug 9, 2005.The World Wide WebnThe Web is a distributed information system based on hypertext.nMost Web documents are hypertext documents formatted via the HyperText Markup L
5、anguage (HTML)nHTML documents containltext along with font specifications, and other formatting instructionslhypertext links to other documents, which can be associated with regions of the text.lforms, enabling users to enter data which can then be sent back to the Web server4Silberschatz, Korth and
6、 Sudarshan8.5Database System Concepts - 5th Edition, Aug 9, 2005.A formatted report5Silberschatz, Korth and Sudarshan8.6Database System Concepts - 5th Edition, Aug 9, 2005.Web Interfaces to DatabasesWhy interface databases to the Web?1.Web browsers have become the de-facto standard user interface to
7、 databaseslEnable large numbers of users to access databases from anywherelAvoid the need for downloading/installing specialized code, while providing a good graphical user interfacelExamples: banks, airline and rental car reservations, university course registration and grading, an so on.6Silbersch
8、atz, Korth and Sudarshan8.7Database System Concepts - 5th Edition, Aug 9, 2005.Web Interfaces to Database (Cont.)2.Dynamic generation of documentslLimitations of static HTML documents4Cannot customize fixed Web documents for individual users.4Problematic to update Web documents, especially if multip
9、le Web documents replicate data.lSolution: Generate Web documents dynamically from data stored in a database. 4Can tailor the display based on user information stored in the database.E.g. tailored ads, tailored weather and local news, 4Displayed information is up-to-date, unlike the static Web pages
10、E.g. stock market information, .7Silberschatz, Korth and Sudarshan8.8Database System Concepts - 5th Edition, Aug 9, 2005.Uniform Resources LocatorsnIn the Web, functionality of pointers is provided by Uniform Resource Locators (URLs).nURL example: http:/www.bell- lThe first part indicates how the do
11、cument is to be accessed4 “http” indicates that the document is to be accessed using the Hyper Text Transfer Protocol.lThe second part gives the unique name of a machine on the Internet.lThe rest of the URL identifies the document within the machine.nThe local identification can be:4The path name of
12、 a file on the machine, or4An identifier (path name) of a program, plus arguments to be passed to the programE.g. http:/ Korth and Sudarshan8.9Database System Concepts - 5th Edition, Aug 9, 2005.HTML and HTTPnHTML provides formatting, hypertext link, and image display features.nHTML also provides in
13、put features4Select from a set of optionsPop-up menus, radio buttons, check lists4Enter valuesText boxeslFilled in input sent back to the server, to be acted upon by an executable at the servernHyperText Transfer Protocol (HTTP) used for communication with the Web server9Silberschatz, Korth and Suda
14、rshan8.10Database System Concepts - 5th Edition, Aug 9, 2005.Sample HTML Source Text A-101 Downtown 500 The account relation Select account/loan and enter number Account value=“Loan” Loan 10Silberschatz, Korth and Sudarshan8.11Database System Concepts - 5th Edition, Aug 9, 2005.Display of Sample HTM
15、L Source11Silberschatz, Korth and Sudarshan8.12Database System Concepts - 5th Edition, Aug 9, 2005.Client Side Scripting and AppletsnBrowsers can fetch certain scripts (client-side scripts) or programs along with documents, and execute them in “safe mode” at the client sitelJavascriptlMacromedia Fla
16、sh and Shockwave for animation/gameslVRMLlAppletsnClient-side scripts/programs allow documents to be activelE.g., animation by executing programs at the local sitelE.g. ensure that values entered by users satisfy some correctness checkslPermit flexible interaction with the user.4Executing programs a
17、t the client site speeds up interaction by avoiding many round trips to server12Silberschatz, Korth and Sudarshan8.13Database System Concepts - 5th Edition, Aug 9, 2005.Client Side Scripting and SecuritynSecurity mechanisms needed to ensure that malicious scripts do not cause damage to the client ma
18、chinelEasy for limited capability scripting languages, harder for general purpose programming languages like JavanE.g. Javas security system ensures that the Java applet code does not make any system calls directlylDisallows dangerous actions such as file writeslNotifies the user about potentially d
19、angerous actions, and allows the option to abort the program or to continue execution.13Silberschatz, Korth and Sudarshan8.14Database System Concepts - 5th Edition, Aug 9, 2005.Web ServersnA Web server can easily serve as a front end to a variety of information services.nThe document name in a URL m
20、ay identify an executable program, that, when run, generates a HTML document.lWhen a HTTP server receives a request for such a document, it executes the program, and sends back the HTML document that is generated.lThe Web client can pass extra arguments with the name of the document.nTo install a ne
21、w service on the Web, one simply needs to create and install an executable that provides that service.lThe Web browser provides a graphical user interface to the information service.nCommon Gateway Interface (CGI): a standard interface between web and application server14Silberschatz, Korth and Suda
22、rshan8.15Database System Concepts - 5th Edition, Aug 9, 2005.Three-Tier Web Architecture15Silberschatz, Korth and Sudarshan8.16Database System Concepts - 5th Edition, Aug 9, 2005.Two-Tier Web ArchitecturenMultiple levels of indirection have overheadsHAlternative: two-tier architecture16Silberschatz,
23、 Korth and Sudarshan8.17Database System Concepts - 5th Edition, Aug 9, 2005.TriggersnA trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.nTo design a trigger mechanism, we must:lSpecify the conditions under which the trigger is to
24、be executed.lSpecify the actions to be taken when the trigger executes.nTriggers introduced to SQL standard in SQL:1999, but supported even earlier using non-standard syntax by most databases.17Silberschatz, Korth and Sudarshan8.18Database System Concepts - 5th Edition, Aug 9, 2005.Trigger Example n
25、Suppose that instead of allowing negative account balances, the bank deals with overdrafts by lsetting the account balance to zerolcreating a loan in the amount of the overdraftlgiving this loan a loan number identical to the account number of the overdrawn accountnThe condition for executing the tr
26、igger is an update to the account relation that results in a negative balance value.18Silberschatz, Korth and Sudarshan8.19Database System Concepts - 5th Edition, Aug 9, 2005.Trigger Example in SQL:1999create trigger overdraft-trigger after update on account referencing new row as nrow for each roww
27、hen nrow.balance 0begin atomicinsert into borrower (select customer-name, account-number from depositor where nrow.account-number = depositor.account-number); insert into loan values(nrow.account-number, nrow.branch-name, nrow.balance); update account set balance = 0where account.account-number = nr
28、ow.account-numberend19Silberschatz, Korth and Sudarshan8.20Database System Concepts - 5th Edition, Aug 9, 2005.Triggering Events and Actions in SQLnTriggering event can be insert, delete or updatenTriggers on update can be restricted to specific attributeslE.g. create trigger overdraft-trigger after
29、 update of balance on accountnValues of attributes before and after an update can be referencedlreferencing old row as : for deletes and updateslreferencing new row as : for inserts and updatesnTriggers can be activated before an event, which can serve as extra constraints. E.g. convert blanks to nu
30、ll.create trigger setnull-trigger before update on rreferencing new row as nrowfor each row when nrow.phone-number = set nrow.phone-number = null20Silberschatz, Korth and Sudarshan8.21Database System Concepts - 5th Edition, Aug 9, 2005.Statement Level TriggersnInstead of executing a separate action
31、for each affected row, a single action can be executed for all rows affected by a transactionlUse for each statement instead of for each rowlUse referencing old table or referencing new table to refer to temporary tables (called transition tables) containing the affected rowslCan be more efficient w
32、hen dealing with SQL statements that update a large number of rows21Silberschatz, Korth and Sudarshan8.22Database System Concepts - 5th Edition, Aug 9, 2005.Triggers in MS-SQLServer Syntax create trigger overdraft-trigger on accountfor updateas if inserted.balance 0begin insert into borrower (select
33、 customer-name,account-number from depositor, inserted where inserted.account-number = depositor.account-number) insert into loan values (inserted.account-number, inserted.branch-name, inserted.balance) update account set balance = 0 from account, inserted where account.account-number = inserted.acc
34、ount-numberend22Silberschatz, Korth and Sudarshan8.23Database System Concepts - 5th Edition, Aug 9, 2005.When Not To Use TriggersnTriggers were used earlier for tasks such as lmaintaining summary data (e.g. total salary of each department)lReplicating databases by recording changes to special relati
35、ons (called change or delta relations) and having a separate process that applies the changes over to a replica nThere are better ways of doing these now:lDatabases today provide built in materialized view facilities to maintain summary datalDatabases provide built-in support for replicationnEncapsu
36、lation facilities can be used instead of triggers in many caseslDefine methods to update fieldslCarry out actions as part of the update methods instead of through a trigger nOne trigger can set off another trigger. In the worst case, leading to an infinite chain of triggering. 23Silberschatz, Korth
37、and Sudarshan8.24Database System Concepts - 5th Edition, Aug 9, 2005.Authorization in SQL (see also Section 4.3)(see also Section 4.3)Forms of authorization on parts of the database:nRead authorization - allows reading, but not modification of data.nInsert authorization - allows insertion of new dat
38、a, but not modification of existing data.nUpdate authorization - allows modification, but not deletion of data.nDelete authorization - allows deletion of data24Silberschatz, Korth and Sudarshan8.25Database System Concepts - 5th Edition, Aug 9, 2005.Authorization (Cont.)Forms of authorization to modi
39、fy the database schema:nIndex authorization - allows creation and deletion of indices.nResources authorization - allows creation of new relations.nAlteration authorization - allows addition or deletion of attributes in a relation.nDrop authorization - allows deletion of relations.25Silberschatz, Kor
40、th and Sudarshan8.26Database System Concepts - 5th Edition, Aug 9, 2005.Authorization and ViewsnUsers can be given authorization on views, without being given any authorization on the relations used in the view definitionnAbility of views to hide data serves both to simplify usage of the system and
41、to enhance security by allowing users access only to data they need for their jobnA combination or relational-level security and view-level security can be used to limit a users access to precisely the data that user needs.26Silberschatz, Korth and Sudarshan8.27Database System Concepts - 5th Edition
42、, Aug 9, 2005.View ExamplenSuppose a bank clerk needs to know the names of the customers of each branch, but is not authorized to see specific loan information.lApproach: Deny direct access to the loan relation, but grant access to the view cust-loan, which consists only of the names of customers an
43、d the branches at which they have a loan.lThe cust-loan view is defined in SQL as follows:create view cust-loan as select branch_name, customer-name from borrower, loan where borrower.loan-number = loan.loan-number27Silberschatz, Korth and Sudarshan8.28Database System Concepts - 5th Edition, Aug 9,
44、2005.View Example (Cont.)nThe clerk is authorized to see the result of the query: select *from cust-loannWhen the query processor translates the result into a query on the actual relations in the database, we obtain a query on borrower and loan.nAuthorization must be checked on the clerks query befo
45、re query processing replaces a view by the definition of the view.28Silberschatz, Korth and Sudarshan8.29Database System Concepts - 5th Edition, Aug 9, 2005.Granting of PrivilegesnThe passage of authorization from one user to another may be represented by an authorization graph.nThe nodes of this gr
46、aph are the users.nThe root of the graph is the database administrator.nConsider graph for update authorization on loan. An edge Ui Uj indicates that user Ui has granted update authorization on loan to Uj.U1U4U2U5U3DBA29Silberschatz, Korth and Sudarshan8.30Database System Concepts - 5th Edition, Aug
47、 9, 2005.Authorization Grant GraphnRequirement: All edges in an authorization graph must be part of some path originating with the database administratornIf DBA revokes grant from U1:lGrant must be revoked from U4 since U1 no longer has authorizationlGrant must not be revoked from U5 since U5 has an
48、other authorization path from DBA through U2nMust prevent cycles of grants with no path from the root:lDBA grants authorization to U7lU7 grants authorization to U8lU8 grants authorization to U7lDBA revokes authorization from U7nMust revoke grant U7 to U8 and from U8 to U7 since there is no path from
49、 DBA to U7 or to U8 anymore.30Silberschatz, Korth and Sudarshan8.31Database System Concepts - 5th Edition, Aug 9, 2005.Security Specification in SQLnThe grant statement is used to confer authorizationgrant on to n is:la user-id orlpublic, which allows all valid users the privilege granted orlA role
50、(more on this later)nGranting a privilege on a view does not imply granting any privileges on the underlying relations.nThe grantor of the privilege must already hold the privilege on the specified item (or be the database administrator).31Silberschatz, Korth and Sudarshan8.32Database System Concept
51、s - 5th Edition, Aug 9, 2005.Privileges in SQLnselect: allows read access to relation,or the ability to query using the viewlExample: grant users U1, U2, and U3 select authorization on the branch relation:grant select on branch to U1, U2, U3ninsert: the ability to insert tuplesnupdate: the ability t
52、o update using the SQL update statementndelete: the ability to delete tuples.nreferences: ability to declare foreign keys when creating relations.nusage: In SQL-92; authorizes a user to use a specified domainnall privileges: used as a short form for all the allowable privileges32Silberschatz, Korth
53、and Sudarshan8.33Database System Concepts - 5th Edition, Aug 9, 2005.Privilege To Grant Privilegesnwith grant option: allows a user who is granted a privilege to pass the privilege on to other users. lExample:grant select on branch to U1 with grant optiongives U1 the select privileges on branch and
54、allows U1 to grant this privilege to others33Silberschatz, Korth and Sudarshan8.34Database System Concepts - 5th Edition, Aug 9, 2005.RolesnRoles permit common privileges for a class of users can be specified just once by creating a corresponding “role”nPrivileges can be granted to or revoked from r
55、oles, just like usernRoles can be assigned to users, and even to other rolesnSQL:1999 supports roles create role tellercreate role manager grant select on branch to tellergrant update (balance) on account to tellergrant all privileges on account to managergrant teller to managergrant teller to alice
56、, bobgrant manager to avi34Silberschatz, Korth and Sudarshan8.35Database System Concepts - 5th Edition, Aug 9, 2005.Revoking Authorization in SQLnThe revoke statement is used to revoke authorization.revokeon from restrict|cascadenExample:revoke select on branch from U1, U2, U3 cascadenRevocation of
57、a privilege from a user may cause other users also to lose that privilege; referred to as cascading of the revoke.nWe can prevent cascading by specifying restrict:revoke select on branch from U1, U2, U3 restrictWith restrict, the revoke command fails if cascading revokes are required.35Silberschatz,
58、 Korth and Sudarshan8.36Database System Concepts - 5th Edition, Aug 9, 2005.Limitations of SQL AuthorizationnSQL does not support authorization at a tuple levellE.g. we cannot restrict students to see only (the tuples storing) their own gradesnWith the growth in Web access to databases, database acc
59、esses come primarily from application servers.l End users dont have database user ids, they are all mapped to the same database user idnAll end-users of an application (such as a web application) may be mapped to a single database usernThe task of authorization in above cases falls on the applicatio
60、n program, with no support from SQLlBenefit: fine grained authorizations, such as to individual tuples, can be implemented by the application.lDrawback: Authorization must be done in application code, and may be dispersed all over an applicationlChecking for absence of authorization loopholes become
61、s very difficult since it requires reading large amounts of application code36Silberschatz, Korth and Sudarshan8.37Database System Concepts - 5th Edition, Aug 9, 2005.Audit TrailsnAn audit trail is a log of all changes (inserts/deletes/updates) to the database along with information such as which us
62、er performed the change, and when the change was performed.nUsed to track erroneous/fraudulent updates.nCan be implemented using triggers, but many database systems provide direct support.37Silberschatz, Korth and Sudarshan8.38Database System Concepts - 5th Edition, Aug 9, 2005.Application Securityn
63、Data may be encrypted when database authorization provisions do not offer sufficient protection.nProperties of good encryption technique:lRelatively simple for authorized users to encrypt and decrypt data.lEncryption scheme depends not on the secrecy of the algorithm but on the secrecy of a parameter of the algorithm called the encryption key.lExtremely difficult for an intruder to determine the encryption key.38Database System ConceptsSilberschatz, Korth and SudarshanSee www.db- for conditions on re-use Silberschatz, Korth and SudarshanDatabase System ConceptsEnd of Chapter
