Beginning SQL Server Modeling- P9

Tham khảo tài liệu beginning sql server modeling- p9, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Nội dung trích xuất từ tài liệu:
Beginning SQL Server Modeling- P9CH APTER 6  SQL SERVER MODELING SERVICES – THE FOLDER PATTERNyour manufacturer not only made cars, but also toasters and lawnmowers. Then you could also define aToasterComponent type and a LawnMowerComponent type, with corresponding ToasterComponents andLawnMowerComponents tables. If the manufacturer had a single QC department that covered allmanufacturing lines, then you would have the seed for developing a QC model and application that thisdepartment could use for managing its operations across different lines. If the manufacturer decidedthat each line should have its own QC department (say, for instance, they were made in different plants),then the folder pattern of SSMod could be applied to allow each QC department to view and manage theQC data of its respective line, but not be distracted with data for the other lines. Corporate headquarterswould, of course, have access to all QC data if that was how the IT department was asked to structure thesystem. In the next chapter, which covers the security services provided by SQL Server Modeling Services,you’ll use this basic scenario as the basis of an exercise to illustrate how security can be used to exposeor hide data. 161 Download from Wow! eBook Download from Wow! eBook CHAPTER 7SQL Server ModelingServices – Security Security is an essential component of enterprise applications. Since SQL Server Modeling (SSMod) is part of the broader .NET framework, developers have access to a wide range of services and technologies, some of which provide their own security options. Applications and users can query a SQL Server database through Entity Data Modeling (EDM), LINQ, ADO.NET, ODBC, and other systems, including non-Microsoft platforms. The Modeling Services framework supports granular (row- level) and claims-based security, built on the folder pattern discussed in the previous chapter. It should be clear, however, that a wide range of other security facilities is available, depending on which platforms you involve in creating an application. Modeling Services uses updatable views in conjunction with the folder pattern to protect the base tables and provide row-levelsecurity. A SQL Server view is essentially a named read-only query of one or more tables that you or anapplication can use with subsequent queries as if it were a table. These queries can result in updating,inserting or deleting actions in the base table (and consequently the view) if the user has been grantedthe appropriate authority. A view becomes updatable by using what are called INSTEAD OF triggers. Triggers are a special kindof stored procedure that execute on the database server when a user or application attempts to modifydata through a DML (data-manipulation language) event. (There are also data-definition language(DDL) and LOGON events that can be addressed by triggers, but I won’t go into these here.) There are only three categories of DML events, created by INSERT, DELETE, or UPDATE statements. AnINSTEAD OF trigger substitutes execution of the trigger code for execution of the statement triggering theDML event. The trigger can implement the security criteria established by the developer or the databaseadministrator. An event can initiate a single trigger, or a specified sequence of triggers, to perform tasksto check user privileges and preserve the data and relational integrity. How triggers are coded and implemented is a topic beyond the level of this book. In any event, SQLServer Modeling Services already provides the infrastructure, based on the folder pattern and pre-installed INSTEAD OF triggers, to support row-level security. In this chapter, you will extend the CarComponent model introduced in Chapter 4 to a more generalmanufacturing component model. I will use this more general model to introduce the idea of using thefolder pattern to provide security in a way that manufacturing line managers will be able to see ormodify data relating only to components made on the manufacturing lines for which they areresponsible. ...