Download - SQL Server Basic Guide
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 1/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 2/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 3/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 4/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 5/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 6/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 7/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 8/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 9/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 10/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 11/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 12/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 13/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 14/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 15/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 16/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 17/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 18/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 19/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 20/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 21/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 22/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 23/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 24/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 25/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 26/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 27/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 28/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 29/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 30/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 31/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 32/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 33/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 34/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 35/40
D_Base / Microsoft SQL Server 2005: ABG / Petkovic / 226093-9 / Chapter 12
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 36/40
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
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 37/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 38/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 39/40
7/31/2019 SQL Server Basic Guide
http://slidepdf.com/reader/full/sql-server-basic-guide 40/40