Web Hosting Java Blog

Chapter 7 WORKING WITH VIEWS 435 CHAPTER SUMMARY and is accessed by many users. . The table contains private information about employees. . Your boss needs you to restrict access to some of the data in this table, without changing the structure of the table. This needs to be fixed before he starts getting complaints about the private information becoming public information. SCENARIO Entering your office room, you find a note from your boss concerning confidential and private information given out to users accessing the newly set up table Employees, and he states that he urgently needs you to find a solution. With the number of users accessing your database, it is likely that many will pry into the leaked information, so you must finish this task at the earliest possible time. Your boss states that he may receive objections from employees about disclosed personal information, such as salary, from the newly created Employees table. You know your boss is waiting for an answer so you quickly look for a solution. ANALYSIS You quickly investigate the SQL Server blogs Online and seem to find a solution. You are astonished to find such a simple solution that can be applied in less than five minutes. The solution is the view. Because you have a stronger background in the administration of SQL Server, you may have not known how to implement a view, which is often considered to be a design topic. Then you scroll down to the bottom of the article and find that a view may also be implemented with the easy-to-use interface of the SQL Server Enterprise Manager. Relieved, you read on to learn how to actually limit the rows and columns using the apparent power of the view. You come across a term called filtering and learn that it means selecting or deselecting columns (vertical) or rows (horizontal). You implement a filtering view strategy, so that users see only what they need to see, and decide to learn more about the development side of SQL Server 2000. This chapter covered a lot of material related to implementing views. To ensure that you picked up on the most important points, take a quick look at them. You saw that views resemble tables in concept and many operations that you execute against tables can also be executed against views. These include

For high quality java hosting services please check java web hosting website.

434 Part I EXAM PREPARATION Server: Msg 229, Level 14, State 1, Line 1 SELECT permission denied on object BobTable1 , database . pubs , owner Bob . Server: Msg 229, Level 14, State 1, Line 1 SELECT permission denied on object JaneView1 , database . pubs , owner Jane . Server: Msg 229, Level 14, State 1, Line 1 SELECT permission denied on object JaneView2 , database . pubs , owner Jane . To gain access to the view, Jane has to grant SELECT on JaneView1. This additional grant automatically gives Mary access to JaneTable1, but not CASE STUDY: BOSS NEEDS TO SCREEN DATA DBOTable1. With the dbo providing a grant, the last holdout would be Bob to grant SELECT on BobTable1. Even though Bob created the top-level view, there is an ownership chain between his view and his table. Each time the owner changes, permissions have to be re-granted. It makes sense that if I have created a view based on your table, that does not mean you want people who access my view to have access to your table. FIGURE 7.17 The data access hierarchy can lead to broken ownership chains. When dealing with ownership chains, the single point that will reduce permission management for you is to have a single owner for the entire chain. The dbo makes a nice owner for all objects in the database. This means that you have to apply permissions only once to the upper-level objects. If you have a broken ownership chain, then you may have to apply permissions to objects along the entire chain, which makes it more difficult to implement permissions and you will have reduced security. The reduced security is caused by the additional permissions granted to subsequent objects, which may create holes in your data security. To assign dbo as the owner of objects you are creating, you have to specify dbo in the object name your are creating. For example, to create a view for dbo, you could use CREATE VIEW dbo.NewView AS …. You must be a member of the role. ESSENCE OF THE CASE . A new table is launched onto the database

For high quality website hosting services please check tomcat web hosting website.

Chapter 7 WORKING WITH VIEWS 433 Bob.BobView1 CREATE VIEW Bob.BobView1 AS SELECT Jane.JaneView1.id, name, address, description Ownership Change Ownership Change Jane.JaneView2 Bob.BobTable1 Ownership Change Jane.JaneView1 Ownership Change Jane.JaneTable1 dbo.DBOTable1 FROM Jane.JaneView1 INNER JOIN Jane.JaneView2 ON Jane.JaneView1.id = Jane.JaneView2.id GO REVOKE all ON dbo.DBOTable1 TO public REVOKE all ON Jane.JaneTable1 TO public REVOKE all ON Bob.BobTable1 TO public GRANT SELECT ON Bob.BobView1 to Mary The hierarchy of data access can be seen in Figure 7.17. With the current permissions, Mary cannot access the upper-level view (BobView1), even though Bob has granted her SELECT permissions to BobView1. Mary s error message from SELECT * FROM Bob.BobView1 would look like this: Server: Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object DBOTable1 , database . pubs , owner dbo .

For high quality jboss hosting services please check jboss web hosting website.

432 Part I EXAM PREPARATION continued was denied access to the table (DBOPermsTable). Because the dbo owns both objects, as access moves from the view to the table, the permissions are not checked, and Mary has access to the data. However, if Mary attempts to access the table directly, the permissions are checked at the table, and she is denied access. The reasoning behind the ownership chain works like this: If I own a table and I own the view, then when I grant permissions to the view, I obviously want the user to have access to the table. By not granting specific permissions to the table, you also restrict access to the data because this data is accessible only through the view. Dealing with Broken Ownership Chains One problem that can arise when you are using views occurs when you have different owners for objects in your database. Whenever there is a change in ownership, the owner of each object has to grant permissions to the object. When the ownership of objects in a chain is changed, there is a break in ownership or you have a broken ownership chain. The following script creates tables and views for a database. It then applies permissions to the upper-layer objects. This script creates a broken ownership and illustrates the issues that you should be aware of with different object owners. CREATE TABLE dbo.DBOTable1 ( id int, name varchar(20) ) CREATE TABLE bob.BobTable1 ( id int, description .varchar(20) ) CREATE TABLE jane.JaneTable1 ( id int, address varchar(20) ) GO INSERT INTO dbo.DBOTable1 VALUES (1, Buddy ) INSERT INTO Jane.JaneTable1 VALUES (1, 123 Some Street ) INSERT INTO Bob.BobTable1 VALUES (1, What was his name? ) GO CREATE VIEW Jane.JaneView1 AS SELECT dbo.DBOTable1.id, name, address FROM dbo.DBOTable1 INNER JOIN Jane.JaneTable1 ON dbo.DBOTable1.id = Jane.JaneTable1.id GO CREATE VIEW Jane.JaneView2 AS SELECT id, description FROM Bob.BobTable1 GO

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

Chapter 7 WORKING WITH VIEWS 431 There are three basic commands to set permissions and five different actions that they can control. The commands are GRANT, REVOKE, and DENY. The actions are SELECT, INSERT, UPDATE, DELETE, and DRI. GRANT and DENY allow or disallow access to the view, whereas REVOKE removes a previous GRANT or DENY. SELECT, INSERT, UPDATE, and DELETE should be self-explanatory, whereas DRI enables users to create references to the view, which would be required to create an object that refers to the view with the WITH SCHEMABINDING clause. For complete information about these statements and applying permissions, refer back to Chapter 6, Programming SQL Server 2000. If you use the following script to create a new table and view, CREATE TABLE dbo.DBOPermsTable ( id int, name varchar(20), description varchar(20), address varchar(20) ) GO CREATE VIEW dbo.DBOPermsView AS SELECT id, name FROM DBOPermsTable then you can set permissions with the following statements: REVOKE all ON DBOPermsTable TO public DENY all ON DBOPermsTable TO Mary REVOKE all ON DBOPermsView TO public GRANT SELECT ON DBOPermsView to Mary Even though you have not granted permissions to the underlying table, Mary still has permissions to the view, and that gives her access to the requested data. In this way, views provided additional data security because users do not need to be granted access to the source tables, and in this example, can actually be denied access to the base tables. This magic is accomplished through the ownership chain. Ownership chains were designed to make it easier for you to assign permissions, and to enhance security by requiring users to have permissions to only the upper-level objects, such as views or stored procedures. As long as the same person owns all the objects in the chain, permission is only checked at the first object that she accesses. In this case, Mary was granted permission to the view (DBOPermsView), but continues

For high quality website hosting services please check tomcat web hosting website.

430 Part I EXAM PREPARATION FROM Product3 If you perform a SELECT against this view, you will find that it returns the entire resultset. This complete resultset is achieved by selecting against each individual table. FIGURE 7.16 Inserting sample data into the member tables. With the completion of this section, you should be able to explain what a partitioned view is and differentiate between local partitioned views and distributed partitioned views. You should also be aware that the UNION ALL operator is used to consolidate the different tables into a single view. Lastly, you should know that the table structure for each table used in the view must be identical. If you feel comfortable with this information, then move on to the next section, which covers application security through views and ownership chains. CONTROL DATA ACCESS BY USING VIEWS . Control data access by using views. So far, we ve looked at most of the aspects of views and data modifications. What we have not examined yet is security and permissions. The next section explains how to apply permissions through several layers of views, and ownership chains. Granting Permissions and Ownership Chains Views help you apply security to your database applications by how permissions are checked in your view and underlying tables. Views make it easy to control access to this data. Managing permission for views is similar to managing permissions for tables.

For high quality website hosting services please check cheap web hosting website.

Chapter 7 WORKING WITH VIEWS 429 (Product_ID, Product) VALUES ( 1 , Zcheese ) INSERT Product1 (Product_ID, Product) VALUES ( 5 , AustralianJam ) INSERT Product2 (Product_ID, Product) VALUES ( 60 , SpicyDelights ) INSERT Product2 (Product_ID, Product) VALUES ( 69 , FarEastSpecialty ) INSERT Product3 (Product_ID, Product) VALUES ( 120 , DelicateClam ) INSERT Product3 (Product_ID, Product) VALUES ( 140 , FishNChips ) 4. Now, to create a partitioned view that collects all this information, execute the following code as shown in Figure 7.16. CREATE VIEW AllProducts AS SELECT * FROM Product1 UNION ALL SELECT * FROM Product2 UNION ALL SELECT *

For high quality website hosting services please check java web hosting website.

428 Part I EXAM PREPARATION continued Creating Partitioned Views After reviewing all the guidelines for creating partitioned views, you are ready to implement them in SQL Server. When creating partitioned views, as noted earlier, the first step is cutting the table into horizontal sections, each section being called a member table and having the same number of columns and same attributes as the original table. To create a partitioned view, follow Step by Step 7.8. STEP BY STEP 7.8 Creating a Partitioned View 1. Initiate the SQL Query Analyzer by selecting Query Analyzer from the Start menu. NOTE Using WHERE with Your Partitioned View When you query a distributed partitioned view based on the partitioning criteria in the WHERE clause, you are querying against only the servers that fall within the scope of the WHERE clause. This yields a performance increase for you because of the way the distributed partitioned view uses the mandatory CHECK constraint. You receive the same advantage if you use CHECK constraints on local partitioned views. 2. You need the member tables to exist before you can gather the partitioned data. For this example, you will create three member tables to hold products for a multi-national food company. This company makes products to be sold in several different countries, and the products have been separated into tables related to their regions or countries. The following code creates the three tables: CREATE TABLE Product1 ( Product_ID INT PRIMARY KEY CHECK (Product_ID .BETWEEN 1 and 50), Product CHAR(30) ) CREATE TABLE Product2 ( Product_ID INT PRIMARY KEY CHECK (Product_ID .BETWEEN 51 and 100), Product CHAR(30) ) CREATE TABLE Product3 ( Product_ID INT PRIMARY KEY CHECK (Product_ID .BETWEEN 101 and 150), Product CHAR(30) ) 3. These tables have to have some data before you can combine them into a view. INSERT two records for each as follows and as shown in Figure 7.16. INSERT Product1

For high quality website hosting services please check tomcat web hosting website.

Chapter 7 WORKING WITH VIEWS 427 each member table. It is possible to create updateable partitioned views. This requires that each table s SELECT statement refers to only one base table, the UNION ALL operator is used to join the resultsets together, and non-local tables use the full four-part identifier in their names. To decide whether you should create a partitioned view or not, you have to examine the data you will be working with, and consider how it is used. If you have a table that is used by many different department or regions (each with its own server), then you can look at partitioning the database along those lines. For example, a company sells five major product lines. Each product line is managed by a department and each department has its own SQL Server for its specific data. All customers in the organization buy products from only one department, and there is no crossover between product lines, but all customers are stored in one table, which is stored on a central server. When looking for its own customers, each department must then SELECT against the central table, sifting through the entire customer base. Thought has been given to splitting the data into separate customer tables, but the central billing application requires that all the data be stored in one table. In this scenario, you have an ideal candidate for a distributed (updateable) partitioned view. The customer table can be divided into tables based on product line, and joined together by a partitioned view. This enables each table to be queried individually or through the view. The CHECK constraint can be based on the product line that the customer purchases, enforcing which server is to hold the data. Even if the view is queried, the CHECK constraint is used to determine which servers and tables actually have to be queried. If the SELECT against the view uses a WHERE clause to specify product1 and product2, then only the servers that contain those products are queried. This reduces the volume of data that is actually queried against. In this scenario, the central billing application can make use of the partitioned view, and it appears that all the data still resides in one table. Now you have enough knowledge of partitioned views to actually implement them, which you will do in the next section. continues

For high quality website hosting services please check tomcat web hosting website.

426 Part I EXAM PREPARATION more instances of SQL Server, hence the name distributed. Distributed partitioned views are used when processing needs to be spread throughout a group of servers, as shown in Figure 7.15. FIGURE 7.15 An illustration of a how a distributed partitioned view works. In simple words, with partitioned views, tables that store large amounts of data can be split up (using horizontal partitioning) into smaller member tables. This data in the member table holds the same number of columns as the original table; it is only the number of rows that is decreased. After the data is broken down into smaller member tables, a view defined with UNION ALL is used to bring all member tables together. This view looks like a single large resultset. When a SELECT query is run against the partitioned view, SQL Server uses CHECK constraints in determining which member table the data is from. The CHECK constraint is usually created on the Primary Key column. Partitioned View Considerations and Guidelines When creating partitioned views, you should give careful thought to a few considerations: Local partitioned views do not need to use CHECK constraints. Not using CHECK constraints also provides the same results as using a CHECK constraint, except that the Query Optimizer has to perform a lengthy search against all member tables meeting the query search condition. Using CHECK constraints reduces the cost of queries. When creating partitioned views, be sure that all columns of each member table are included in the partitioned view definition. Also, make sure that the same column is not referenced twice in the SELECT list. Make sure that all identical columns in all tables are of the same data type. When referencing member tables in a view, be sure to use the FROM clause to specifically declare a reference each table will use. Be sure that Primary Keys are defined on the same column for

For high quality java hosting services please check java web hosting website.