sql server basic guide

7/31/2019 SQL Server Basic Guide

http://slidepdf.com/reader/full/sql-server-basic-guide 1/40



D_Base / Microsoft SQL Server 2005: ABG / Petkovic / 226093-9 / Chapter 12



3 6 0 M i c r o s o f t S Q L S e r v e r 2 0 0 5 : A B e g i n n e r ’s G u i d e

ADD user_name CHAR(60) DEFAULT SYSTEM_USERGOCREATE VIEW v_my_rows

AS SELECT emp_no, emp_fname, emp_lname, dept_noFROM employeeWHERE user_name = SYSTEM_USER

NOTE

The Transact-SQL statements in Example 12.23 must be separately executed, because the CREAVIEW statement must be the first statement in the batch. That is why the GO statement is used (to mark the end of the first batch).

The schema of the employee table is modified in Example 12.23 by adding the newcolumn user_name . Every time a new row is inserted into the employee table, thecurrent value of the system user name is inserted into the user_name column. Afterthe creation of the corresponding view v_my_rows , a user can retrieve only the rowsthat she inserted into the table. (The same is true for the UPDATE statement.)

EXAMPLE 12.24

USE sampleGOCREATE VIEW v_analyst

AS SELECT employee.emp_no, emp_fname, emp_lname

FROM employee, works_onWHERE employee.emp_no = works_on.emp_noAND job = ‘Analyst’

The v_analyst view represents a horizontal and a vertical subset (in other words,it limits the rows and columns that can be accessed) of the employee table.

Stored Procedures and Data AccessStored procedures can also be used to restrict data access. The restriction of dataaccess using stored procedures is based upon the property that the permission toexecute a stored procedure is independent of any permissions for database objectsthat are referenced by the stored procedure. More precisely, granting permission to

ch12.indd 360 10/24/05 10:37:31 AM

sql server basic guide

Documents