Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P5

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
53
lượt xem
9
download

Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P5

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'programming microsoft sql server 2000 with microsoft visual basic .net - p5', 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ả

Chủ đề:
Lưu

Nội dung Text: Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P5

  1. using t he OPENROWSET funct ion because t he link ed serv er sy nt ax is m or e st raight forward, and y ou no longer hav e t o perform t he first t wo st eps. Creat e a link ed serv er for a r em ot e or het er ogeneous dat a source w it h t he sp_addlinkedserv er syst em st ored procedure. This procedure can t ak e as m any as seven argum ent s, but you can use as few as t wo argum ent s for cr eat ing a reference t o a rem ot e SQL Server source and as few as four argum ent s for a linked ser ver point ing t o an Access dat a source. Aft er cor rect ly init ializing t he linked ser ver reference wit h t he sp_addlink edserver syst em st ored pr ocedur e, inv oke sp_addlink edsrv login for m apping logins on t he curr ent SQL Ser v er 2000 inst ance t o logins for t he rem ot e or het er ogeneous dat a source. When a user runs a query on t he local ser ver against t he link ed ser ver, t he local serv er logs in t o t he link ed serv er w it h t he credent ials specified when t he sp_addlink edsrv login syst em st ored procedur e was last r un for t he link ed serv er. You can invok e t he sp_linkedserv ers syst em st ored pr ocedur e t o it em ize in a r esult set t he link ed serv ers defined on a local serv er . Se cu r it y for Vir t ua l D ir e ct or ie s Virt ual direct ories ar e necessary for Web dat a access t o SQL Serv er dat a sources via XML. Each dat abase t hat r equires Web access via XML m ust have a virt ual dir ect or y point ing t o it . As described in t he “ Virt ual Direct ory Managem ent ” sect ion of Chapt er 6, y ou m ust designat e a login for t he virt ual dir ect or y. All access t o t he dat abase is m apped t hrough t he login t hat y ou specify on t he Secur it y t ab of t he Pr opert ies dialog for a dir ect ory . Figur e 7- 1 shows t he Propert ies dialog box used for t he MyNwind v irt ual direct ory t hat ser ved as t he source for m ost of t he sam ples in Chapt er 6. Not ice t hat t he Secur it y t ab specifies I USR_CCS1 in t he User Nam e t ext box. The User Nam e t ext box cont ains t he login nam e for t he v irt ual dir ect ory . Select ing Windows as t he Account Type aut om at ically inst alls I USR_ser ver nam e as t he login. Windows 2000 Ser ver aut om at ically inst alls t he I USR_ser ver nam e user account . I I S aut om at ically uses t his Windows user account for anonym ous login. Since t he sam ples for Chapt er 6 r an fr om a serv er nam ed ccs1, t he dialog replaced serv er nam e w it h CCS1. Figu r e 7 - 1 . Use t h e Se cu r it y t a b for a vir t u a l dire ct or y t o spe cify t h e login by w h ich u se r s of t h e virt u a l dire ct or y w ill ga in a cce ss t o a SQL Se rve r. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. I f you decide t o allow access t o your dat abase t hr ough t he I USR_serv ernam e Windows account , y ou m ust m anually cr eat e a login for t he Windows user on your SQL Serv er inst ance. Then y ou m ust creat e a user secur it y account in t he dat abase t o w hich t he v irt ual direct ory point s. Finally y ou m ust assign perm issions t o t he I USR_serv er nam e securit y account appropriat e t o t he needs of your applicat ion. For ex am ple, if y ou want t o enable br owsers t o r ead from any row source in t he dat abase, y ou can assign t he I USR_serv er nam e user account t o t he db_dat areader fix ed dat abase role. I f you have m ore r est rict ive requir em ent s, use t he T- SQL GRANT st at em ent t o specify m or e granular perm issions, such as t he abilit y t o view j ust one t able or v iew. Mak e sure t he dat abase has perm issions for t he public role t hat don’t allow t he I USR_ser vernam e account t o access t he dat abase wit h a differ ent set of perm issions t han t he one y ou specify explicit ly for t he v irt ual dir ect or y user account . When y ou decide t o per m it updat es, insert s, and delet es t o a dat abase t hr ough a virt ual ser ver, t he user securit y account for t he virt ual dir ect ory ’s login m ust enable t hese act ions. My adv ice is t o carefully r est rict t he row sources t hat y ou m ake available for updat ing over t he Web. Av oid assigning t he I USR_ser vernam e account t o t he db_dat awr it er fix ed dat abase r ole. I nst ead, assign I NSERT, UPDATE, or DELETE per m issions w it h t he T- SQL GRANT st at em ent for whichever dat abase obj ect s requir e m odificat ion ov er t he Web. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. Sa m ple s for Login s a n d Use r s Login and user secur it y account s com plem ent one anot her. Recall t hat a login aut horizes access t o a ser ver, but a user account grant s access t o a dat abase on a serv er. The users of y our applicat ions t ypically need bot h t ypes of secur it y account s t o access a dat abase on a SQL Serv er inst ance. I n addit ion, t her e are t wo dist inct t ypes of logins. The sam ples in t his sect ion explore t he different k inds of logins for SQL Serv er and how t hey relat e t o user secur it y account s. All t he script s in t his sect ion ar e in t he LoginAndDr opUsers.sql sam ple file. Add a SQL Se r ve r Login a n d Use r Recall t hat a login get s a user int o a ser ver but not necessarily int o any dat abases on t he serv er . This is because a login t ypically r equires a m at ching securit y account for each dat abase t o w hich a user is t o hav e access. Howev er, t her e are t wo ways in w hich a user can access a dat abase wit hout a user account for t he dat abase. First , t he dat abase can hav e a guest account . The user w ill t hen enj oy any perm issions assigned explicit ly t o t he guest account or indirect ly t o t he guest account t hr ough perm issions for a dat abase’s public role. Second, if a login is a m em ber of t he sysadm in fixed serv er role, it can access any dat abase on a serv er wit hout any r est rict ions on it s funct ionalit y . For t his reason, y ou want t o lim it t he num ber of logins w it h m em bership in t he sysadm in role. I f y ou need t o carefully specify how t he user of a login can int eract wit h a dat abase, y ou m ust creat e a user securit y account for t he login in t he dat abase. I nvok e t he sp_addlogin syst em st ored procedur e t o creat e a new SQL Ser ver login. Wit h t he sp_addlogin syst em st ored pr ocedur e, you can cr eat e a login t hat SQL Ser ver m anages. When users at t em pt t o gain access t o a SQL Serv er inst ance w it h t his login, t hey m ust explicit ly designat e bot h t he login nam e and it s associat ed password. To creat e a SQL Serv er login, y ou m ust be a m em ber of eit her t he sysadm in or securit yadm in fixed ser v er r ole. Any user can change her own password w it h t he sp_password syst em st or ed procedur e. Only m em bers of t he sysadm in and secur it yadm in fixed serv er r oles can invoke sp_password t o change t he password for a login differ ent fr om t heir ow n. N ot e While a SQL Ser ver login enables a user t o connect t o a SQL Server inst ance by specifying a login nam e and password, it is t he SI D ( secur it y ident ifier) t hat SQL Ser ver uses t o ident ify and t r ack t he user. SQL Ser ver int ernally generat es a GUI D t o r epr esent t he SI D for SQL Server logins. I nvok e t he sp_grant dbaccess syst em st ored procedure t o cr eat e a user secur it y account in a dat abase for a login. Only m em bers of t he sysadm in fixed serv er r ole as well as t he db_owner and db_accessadm in fix ed dat abase r oles can run sp_grant dbaccess. Before r unning sp_grant dbaccess, m ake sur e t he dat abase cont ext is set t o t he dat abase in which you want t o creat e a user securit y account . For exam ple, inv ok e t he USE st at em ent for a dat abase nam e befor e running sp_grant dbaccess. The follow ing T- SQL script uses sp_addlogin t o creat e a new SQL Serv er login. I t is m andat or y t o specify t he @loginam e and @passwd argum ent s for t he sp_addlogin syst em st ored pr ocedure. You can opt ionally specify sever al ot her argum ent s t o change t he default set t ings deriv ed from y our SQL Serv er configurat ion. For exam ple, t he script dem onst r at es t he synt ax for designat ing a default dat abase of Chapt er07, t he sam ple dat abase for t his chapt er . I f t he script didn’t m ak e t his assignm ent for t he @defdb ar gum ent , t he default dat abase Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. would have been t he m ast er dat abase. The m ast er dat abase is one of t he built - in dat abases t hat SQL Ser ver uses t o adm inist er it self. While all users r equir e access t o t his dat abase, you probably don’t want t o m ake it t he default dat abase for t ypical users. Not ice t hat t he scr ipt ex plicit ly refer ences t he m ast er dat abase befor e invok ing sp_addlogin. This r efer ence isn’t st rict ly necessary since you can cr eat e a login secur it y account fr om any dat abase on a serv er . How ev er , t he sam ple script inv okes t he USE st at em ent t wo m ore t im es, and t hese t wo r efer ences are necessary . You m ust invok e t he USE st at em ent befor e r unning t he sp_grant dbaccess syst em st ored pr ocedur e. Recall t hat t his syst em st ored procedur e cr eat es a user secur it y account . Set t ing t he dat abase cont ext befor e inv ok ing sp_grant dbaccess det erm ines t he dat abase for w hich t he syst em st ored procedur e cr eat es a user secur it y account . --LoginAndDropUsers --Create a SQL Server login with access --to the Chapter07 and Northwind databases. USE master EXEC sp_addlogin @loginame = ’vbdotnet1’, @passwd= ’passvbdotnet1’, @defdb = ’Chapter07’ USE Chapter07 EXEC sp_grantdbaccess ’vbdotnet1’ USE Northwind EXEC sp_grantdbaccess ’vbdotnet1’ The vbdot net 1 login doesn’t st rict ly requir e a user secur it y account for t he Nort hwind dat abase because t his sam ple dat abase has a guest account , and t he public role for t he dat abase grant s perm issions t o all dat abase obj ect s in t he init ial version of t he dat abase. Howev er, cr eat ing a user account for t he vbdot net 1 login allows y ou t o rem ove t he guest account for t he dat abase and st ill m aint ain dat a access privileges. I n addit ion, a user account for t he vbdot net 1 login enables a dat abase designer t o fine- t une t he perm issions available t o t he login relat iv e t o ot her dat abase users. Re m ove a SQL Se r ve r Login a n d Use r I n t he norm al course of dat abase m anagem ent , it becom es necessary t o rem ov e as well as add dat abase users. Since a SQL Ser ver dat abase user has t wo different secur it y account t ypes, y ou m ust rem ov e bot h t o flush a user com plet ely from a dat abase serv er. To pr ev ent orphaned user account s, SQL Ser v er doesn’t allow y ou t o delet e t he login for a user w it hout delet ing t he user account s associat ed wit h t hat login. Rem ov ing t he user account s wit hout elim inat ing t heir login st ill allows a user t o access a dat abase ser ver, and t he login can access any dat abases wit h a guest account . N ot e I n addit ion t o being unable t o rem ove a login wit h one or m ore associat ed user account s, y ou cannot rem ove a login t hat is current ly in use, owns a dat abase, or owns a j ob in t he m sdb dat abase. A j ob is a sequence of st eps for aut om at ing a t ask t hat is defined in t he m sdb dat abase, one of t he built - in dat abases t hat SQL Server uses t o m anage it self. As m ent ioned prev iously, you can nev er r em ove t he sa Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. login from a SQL Server inst ance. Befor e y ou at t em pt t o r em ove a login, it ’s useful t o sur vey any associat ed user secur it y account s associat ed w it h t he login. This perm it s y ou t o m ak e sur e t hat you can r em ove all of t he user securit y account s associat ed w it h a login before at t em pt ing t o rem ov e t he login. I nv ok e t he sp_helplogins syst em st or ed procedur e wit h t he nam e of t he login for which you’re seek ing infor m at ion, as show n in t he follow ing code. The syst em st or ed procedur e r et urns a r esult set com prising t wo recordset s. The first r ecordset cont ains a single row for t he login t hat y ou specify. The second recordset cont ains a r ow for each user account associat ed wit h t he login nam ed as t he argum ent for t he sp_helplogins syst em st ored procedure. I f you don’t specify a login nam e as an argum ent w hen y ou inv oke sp_helplogins, t he syst em st or ed procedur e st ill r et urns t wo recordset s. Howev er, t hese recordset s ret urn inform at ion for all t he logins on t he cur rent SQL Ser ver inst ance. --Return info about a login, including --its database user accounts. EXEC sp_helplogins @LoginNamePattern=‘vbdotnet1’ Figur e 7- 2 shows t he t w o r ecordset s t hat result from running sp_helplogins vbdot net 1 aft er first inv ok ing t he scr ipt in t he preceding sect ion. The fir st recordset st art s wit h t he login nam e follow ed by a part ial display of t he login’s SI D. The nex t t wo colum ns indicat e t he default dat abase and language for t he login. The next - t o- last colum n, AUser, is yes w hen t he login has at least one corr esponding user account . The last colum n, ARem ot e, indicat es whet her t he login specifies a r em ot e login for a link ed serv er. The second recordset prov ides inform at ion about each user account for t he login. The first and t hird colum ns denot e, respect ively , t he login nam e and t he user nam e. By default , t hese are t he sam e, but you can ov er ride t his convent ion. The second colum n designat es t he dat abase t o w hich t he user account belongs. The last colum n specifies whet her t he user account is for an indiv idual user or a role. Figu r e 7 - 2 . Use t h e sp_ h e lplogin s syst e m st ore d pr oce d u r e t o le a rn a bou t a log in on a da t ab a se ser ve r . Arm ed w it h t he inform at ion in Figure 7- 2, y ou can const ruct a T- SQL script like t he follow ing t o r em ove t he vbdot net 1 secur it y account s fr om t he serv er. St art by inv ok ing t he sp_r ev okedbaccess syst em st ored procedure in each dat abase wit h a user account for t he vbdot net 1 login. Specify t he user account nam e as t he argum ent for t he sp_r evokedbaccess syst em st or ed procedur e. Not ice t hat t he script inv ok es sp_rev ok edbaccess t wice— once in each dat abase for w hich t he vbdot net 1 login has a user account . The scr ipt closes by r unning t he sp_droplogin syst em st ored procedur e. This syst em st or ed pr ocedur e r equir es j ust one argum ent specify ing t he nam e of t he login t o r em ov e. The perm issions for rem ov ing user account s and logins m at ch t hose for adding t hem : a login at t em pt ing t o rem ov e a login m ust be a m em ber of t he sy sadm in or secur it yadm in fix ed ser ver r ole t o r un sp_droplogin. --Drop a SQL Server login, --first revoking its user accounts. USE Northwind Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. EXEC sp_revokedbaccess ’vbdotnet1’ USE Chapter07 EXEC sp_revokedbaccess ’vbdotnet1’ EXEC sp_droplogin @loginame = ’vbdotnet1’ Addin g a n d Re m ovin g Logins for a W in dow s Use r Managing a login based on a Windows user account for Windows NT, Windows 2000, or Windows XP is sim ilar t o m anaging a SQL Ser ver login. By a Windows user account , I m ean t he account by w hich Windows validat es a user . From a user perspect iv e, t he m ain difference is t hat a login based on a Windows user account doesn’t hav e t o specify a login and password w hen connect ing t o a SQL Ser ver inst ance. For a dat abase user w it h a login based on a Windows user account , all a user has t o do is select t he Windows Aut hent icat ion opt ion in t he Connect To SQL Serv er dialog box of Quer y Analyzer. I f t he t arget SQL Ser ver inst ance has a login for t he Windows user account , t he connect ion at t em pt succeeds. Howev er, a m em ber of t he sysadm in group m ust first creat e a login for t he Windows account in order for t he at t em pt t o succeed. The process for cr eat ing login and user secur it y account s based on a Windows user account is sim ilar t o t hat for m anaging SQL Serv er logins. When creat ing a login for a Windows user account , inv ok e t he sp_grant login syst em st or ed procedur e t o cr eat e a login for t he Windows user. When y ou designat e a login nam e for a Windows user account , t he nam e m ust hav e t w o part s delim it ed by a backslash ( \ ) . The part befor e t he backslash is t he nam e of t he Windows serv er. The part aft er t he backslash is t he nam e of t he Windows user. The sp_grant login syst em st ored pr ocedur e is analogous t o t he sp_addlogin syst em st ored procedur e. Bot h of t hese syst em st ored procedures creat e a new login. SQL Serv er saves bot h of t he logins in t he syslogins t able. SQL Ser ver also report s bot h t ypes of logins in t he sam e colum n of t he r esult set from t he sp_helplogins syst em st or ed procedur e. How ev er, t he login creat ed w it h sp_grant login is aut hent icat ed by a Windows 2000 or Windows NT serv er. When a Windows user at t em pt s t o connect , SQL Serv er st ores t he Windows secur it y ident ifier for t he Windows user . The Windows secur it y ident ifier is analogous t o t he SQL Serv er SI D. Howev er , t he Windows securit y ident ifier is m anaged by t he Windows serv er, and t he Windows secur it y ident ifier is longer t han t he SQL Ser ver SI D ( 85 byt es for Windows and 16 byt es for SQL Ser ver ) . Aft er y ou creat e a login for a Windows user account , t he login cannot connect t o any dat abase w it hout a user secur it y account unless t he dat abase has a guest account . You can creat e a user secur it y account for a login based on a Windows user account wit h t he ident ical procedure for a SQL Ser ver login. First set t he dat abase cont ext for t he user secur it y account . For exam ple, inv ok e t he USE st at em ent t o specify t he nam e of t he dat abase for which y ou want t o creat e a user account . Second r un sp_grant dbaccess wit h t he nam e of t he login as it s argum ent . The follow ing short script dem onst rat es t he synt ax for cr eat ing a login based on a Windows user account . The Windows user account resides on a Windows 2000 Ser ver nam ed CCS1. The nam e of t he account on t he Windows serv er is winvbdot net 1. The last t wo lines of t he script cr eat e a user secur it y account in t he Chapt er07 dat abase based on t he login cr eat ed wit h sp_grant login. --Create a Windows login with --access to Chapter07 database. EXEC sp_grantlogin ’CCS1\winvbdotnet1’ USE Chapter07 EXEC sp_grantdbaccess ’CCS1\winvbdotnet1’ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. N ot e I f t he Windows user account is for a Window s serv er t hat isn’t a dom ain ser ver but m er ely an applicat ion serv er , you m ust cr eat e a local account on a Windows NT Wor kst at ion or Windows 2000 Professional client com put er wit h t he sam e nam e and password as on t he Windows server . Rem oving t he login is a t wo- st ep pr ocess because t he login has a single user secur it y account associat ed w it h it . First r em ov e t he user account for t he Chapt er07 dat abase. The syst em st or ed procedur e for elim inat ing a user secur it y account based on a login for a Windows user account is t he sam e as for delet ing a user account based on a SQL Serv er login. Second r ev ok e t he login. When dropping a login, y ou use a differ ent sy st em st ored procedure for one based on a Windows user account t han for one creat ed by SQL Ser ver. Here is t he T- SQL code for im plem ent ing t he st eps. --Drop a Windows login with sp_revokelogin, --but first revoke its user accounts. USE Chapter07 EXEC sp_revokedbaccess ’CCS1\winvbdotnet1’ EXEC sp_revokelogin ’CCS1\winvbdotnet1’ W ho’s Using Your Applica t ion? By now, y ou should feel com fort able wit h t he idea t hat t her e are act ually t wo r easonable answer s t o t his quest ion. The fir st answer is t he login nam e. This nam e ident ifies a user as she ent er s a SQL Serv er inst ance. The second answer is t he nam e of t he user securit y account . This ident ifies a user w it hin a dat abase. I f a login doesn’t hav e a user securit y account assigned explicit ly t o it for a dat abase and t he dat abase has a guest account , t he login can ent er t he dat abase wit h t he guest user account . SQL Server 2000 offer s t wo built - in funct ions for t elling you t he login nam e and user account nam e of t he user perform ing a t ask in your dat abase. The SYSTEM_USER funct ion ret urns t he login nam e. The CURRENT_USER funct ion r et urns t he user account nam e. Befor e discussing a list ing t o clarify t he operat ion of t hese funct ions, I want t o m ent ion t he DB_NAME funct ion. When you ent er DB_NAME( ) in a SELECT st at em ent , it r et ur ns t he nam e of t he cur rent dat abase. The following short script invokes t he SYSTEM_USER and CURRENT_USER funct ions in t hree different dat abases— m ast er, Nor t hwind, and Chapt er 07. I f y ou r un t his script aft er connect ing t o a SQL Serv er inst ance wit h t he CCS1\ winvbdot net 1 login, you obt ain an ident ical result set fr om each SELECT st at em ent . However , t wo differ ent values are display ed for t he CURRENT_USER funct ion. I n t he m ast er and Nor t hw ind dat abases, t he CURRENT_USER funct ion Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. ret ur ns guest . I n t he Chapt er07 dat abase, t he CURRENT_USER funct ion ret urns CCS1\ winvbdot net 1. This is because t he login has a user account nam ed aft er it in t he Chapt er 07 dat abase. -- Demonstrate functions telling who’s using a database. USE master SELECT DB_NAME(), SYSTEM_USER, CURRENT_USER USE Northwind SELECT DB_NAME(), SYSTEM_USER, CURRENT_USER USE Chapter07 SELECT DB_NAME(), SYSTEM_USER, CURRENT_USER Pr oce ssin g Logins Ba se d on W in dow s Gr ou ps I n addit ion t o basing a login on an indiv idual Windows user account , you can also creat e a login for a Windows group account . The lat t er t y pe of Window s account prov ides a single nam e for r efer encing m or e t han one indiv idual Windows account . When you cr eat e a login based on a Windows group, all t he indiv idual m em bers of t he group inher it t he login assigned t o t he gr oup. I n addit ion, y ou can creat e separat e logins for a subset of t he indiv idual m em bers of a Windows group. These logins for indiv idual Windows account s com plem ent t he login based on t he Windows group account by pr ov iding an alt er nat ive r out e int o a SQL Ser ver inst ance and t he dat abases on it . The sam ple for t his sect ion works w it h a Windows group nam ed w invbdot net . The group cont ains t wo individual Windows user account s nam ed w invbdot net 1 and winvbdot net 2. All t he account s reside on a CCS1 Windows 2000 serv er. The follow ing T- SQL script shows t he code for cr eat ing dist inct logins for t he Windows group and t he indiv idual Windows account s t hat belong t o t he Windows group. Aft er t he ex ecut ion of t he script , bot h t he w inv bdot net 1 and w inv bdot net 2 users connect t o t he SQL Ser v er inst ance w it h t heir own logins as w ell as t he login for t he Windows group. I n addit ion, bot h individual Windows user account s hav e t heir own user account s in t he Chapt er07 dat abase, and t he Windows user account s m ap t o t he Chapt er07 user account for t he Windows group. --Create login for winvbdotnet Windows group. EXEC sp_grantlogin ’CCS1\winvbdotnet’ USE Chapter07 EXEC sp_grantdbaccess ’CCS1\winvbdotnet’ --Also create logins for group members individually. EXEC sp_grantlogin ’CCS1\winvbdotnet1’ EXEC sp_grantdbaccess ’CCS1\winvbdotnet1’ EXEC sp_grantlogin ’CCS1\winvbdotnet2’ EXEC sp_grantdbaccess ’CCS1\winvbdotnet2’ GO Ther e ar e act ually t w o ways t o m ak e a login unavailable for use. First , you can run t he sp_revok elogin syst em st ored procedur e as dem onst rat ed in t he preceding sect ion. This approach r em oves t he login for t he Windows user fr om t he dat abase server. Wit h t his approach in t he curr ent cont ext , r ev ok ing t he CCS1\ w invbdot net 1 Windows user login st ill per m it s t he w invbdot net 1 Windows m em ber of t he w invbdot net group t o connect t o t he dat abase serv er. This capabilit y is possible because t he Windows user can access t he dat abase serv er t hr ough t he login for t he w invbdot net Windows group. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. The follow ing scr ipt shows t he sy nt ax for a second appr oach. I t denies login perm ission t o an ex ist ing login— in t his case, t he one for t he w invbdot net 1 Windows user. This approach st ill perm it s t he w invbdot net 2 Windows user t o access t he dat abase ser ver. However, by denying t he login perm ission for t he CCS1\ w invbdot net 1 login, t he script ov er rides t he abilit y of t he w invbdot net 1 Windows user t o access t he dat abase serv er t hr ough t he CCS1\ w invbdot net login. --This does not affect winvbdotnet2, --which is a member in winvbdotnet group. EXEC sp_denylogin ’CCS1\winvbdotnet1’ GO The follow ing one- line script blocks t he winvbdot net 2 Windows user fr om accessing t he dat abase serv er. The logins for t he w invbdot net 1 and w invbdot net 2 Windows users ar e st ill on t he dat abase serv er. I n addit ion, t he CCS1\ w invbdot net login st ill aut hor izes it s m em bers t o log in t o t he ser ver. A deny set t ing ( inst it ut ed by t he sp_deny login syst em st or ed procedur e) for t he indiv idual Windows account s ov er r ides t he access grant ed by t he sp_gr ant login syst em st ored procedur e for t he CCS1\ winvbdot net Windows gr oup account . This general r ule is t r ue for all perm issions. A deny set t ing ov er r ides a grant set t ing. --This does affect winvbdotnet2, --which is a member in winvbdotnet group. EXEC sp_denylogin ’CCS1\winvbdotnet2’ GO To rem ov e t he logins for t he indiv idual Window s users and t he Window s group t o which t he users belong, you should r ev ok e t he dat abase access t o t he user secur it y account s cor responding t o logins. Then you can r ev ok e t he specific logins for t he Windows users and Windows group. The follow ing scr ipt shows t he synt ax for accom plishing t hese t asks. While t he sp_denylogin syst em st ored pr ocedure disables a login fr om accessing a serv er , t his sy st em st ored pr ocedur e doesn’t rem ov e t he login from a SQL Serv er inst ance— inst ead, you need t he sp_revok elogin syst em st ored procedure t o accom plish t he t ask. --Cleanup account settings. USE Chapter07 EXEC sp_revokedbaccess ’CCS1\winvbdotnet’ EXEC sp_revokedbaccess ’CCS1\winvbdotnet1’ EXEC sp_revokedbaccess ’CCS1\winvbdotnet2’ EXEC sp_revokelogin ’CCS1\winvbdotnet’ EXEC sp_revokelogin ’CCS1\winvbdotnet1’ EXEC sp_revokelogin ’CCS1\winvbdotnet2’ GO Sa m ple s for Assign in g Per m ission s This sect ion dem onst rat es t he essent ial T- SQL st at em ent s for organizing perm issions w it hin a dat abase. Specific t echniques ex ist for obj ect and st at em ent perm issions. I n addit ion, t he final t opic in t he sect ion rev eals how t o m anage perm issions w hen a user account can possess a perm ission dir ect ly as well as indirect ly t hrough it s m em bership in one or m ore Windows account s or SQL Ser ver roles. The sam ples in t his sect ion rely on a v ersion of t he Em ailCont act s t able. The “Scr ipt ing Tables” sect ion of Chapt er 2 init ially present ed t he T- SQL code for t his t able. For t he purposes of t his chapt er, y ou can r e- cr eat e t his t able in t he Chapt er07 dat abase sim ply by changing t he references t o t he Chapt er02 dat abase in Chapt er 2 t o t he Chapt er07 dat abase. A copy of t he m odified code Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. ex ist s in t he sam ple file Creat eEm ailCont act sTable.sql for y our easy r eference. This sect ion also r elies on t he ex ist ence of t he four logins w it h t heir m at ching user securit y account s creat ed so far in t his chapt er. Recall t hat one login is a SQL Ser ver login ( vbdot net 1) , anot her t w o are Windows user logins ( CCS1\ w invbdot net 1 and CCS1\ winvbdot net 2) , and a fourt h login is a Windows group login ( CCS1\ winv bdot net ) com pr ising each of t he t wo Windows user account s. This sect ion present s t he T- SQL code for assigning perm issions t o t he user account s for t he logins. The perm issions relat e t o t he Em ailCont act s t able. Ther efor e, creat e t he Em ailCont act s t able w it h a m em ber of t he sysadm in fix ed serv er r ole, such as t he Windows Adm inist rat or user account or t he SQL Ser ver sa login. Se le ct , I nse r t , a nd D e le t e Pe r m ission s for a Ta ble To evaluat e t he effect of perm ission assignm ent s, you w ill need t w o concurr ent act ive connect ions t o your dat abase server. Connect once as a m em ber of t he sysadm in fixed serv er role, and connect a second t im e w it h a SQL Ser ver login— nam ely, vbdot net 1. Not e t hat if you ran t he code show n ear lier t o drop t he vbdot net 1 login account , y ou’ll need t o rer un t he code t hat creat es t he account . To confirm t hat t he user account for t he vbdot net 1 login has no perm issions in t he Chapt er07 dat abase, at t em pt t o r un t he follow ing script w it h t he user account for t he login. Not ice t hat t he at t em pt ret urns an error m essage say ing, in effect , t hat SELECT perm ission is denied on t he Em ailCont act s obj ect in t he Chapt er07 dat abase. --SelectInsertDeletePermission --The SELECT succeeds if the user has --SELECT permission. USE Chapter07 SELECT * FROM EmailContacts To rem edy t he er r or condit ion, y ou need t o assign SELECT perm ission for t he Em ailCont act s t able t o t he vbdot net 1 user account . Fr om your session init iat ed by a sysadm in m em ber, run t he follow ing line of T- SQL. You m ust inv ok e t his line of code from y our session for t he sysadm in r ole m em ber. You can also always assign perm issions from a session w it h any m em ber of t he db_ow ner fixed dat abase roles. Sessions for select ed ot her user account s w ill w or k in special circum st ances; see t he “GRANT” t opic in Books Online for det ails. Recall also t hat m em bers of t he sysadm in r ole hav e perm ission t o perform all t asks on a dat abase serv er . --Assign SELECT permission for the EmailContacts --table to the vbdotnet1 user account. GRANT SELECT ON EmailContacts TO vbdotnet1 Not ice t hat y ou can assign a SELECT perm ission wit h t he GRANT T- SQL st at em ent . The sam ple in t he preceding T- SQL st at em ent uses t he SELECT keyw ord. This k eyw ord denot es t he perm ission t o r un a SELECT st at em ent , such as t he sam ple t o select all colum ns for all rows from t he Em ailCont act s t able. You can opt ionally assign I NSERT, UPDATE, DELETE, and REFERENCES per m issions for a t able. When concurr ent ly assigning m or e t han one perm ission, delim it t he it em s in y our list of per m issions w it h com m as. Aft er t he perm issions, use t he keyw ord ON and t hen specify t he row source, which is t he Em ailCont act s t able in t his dem onst rat ion. Conclude t he GRANT st at em ent w it h t he TO k eyword followed by t he account t o w hich you ar e grant ing perm ission. The pr eceding GRANT st at em ent designat es t he user secur it y account for t he vbdot net 1 login. You can alt er nat ively specify a SQL Ser ver role for one or m or e user account s or t he user secur it y account s for a Windows user or a Windows gr oup account . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. Aft er invok ing t he pr eceding GRANT st at em ent , t he session for t he vbdot net 1 user can ex ecut e a SELECT st at em ent against t he Em ailCont act s t able. Howev er, t he follow ing at t em pt s from t he vbdot net 1 connect ion t o insert a r ow and t hen delet e t he row fail w it h a pair of er r or m essages about denied I NSERT and DELETE perm issions. Again, t he problem is t hat t he vbdot net 1 user doesn’t have t he proper perm issions. --Run from Chapter07 database context for vbdotnet1 user. INSERT INTO EmailContacts VALUES(3,’Tony’, ’Hill’, ’thill@cabinc.net’) SELECT * FROM EmailContacts GO DELETE FROM EmailContacts WHERE Email1 = ’thill@cabinc.net’ SELECT * FROM EmailContacts GO Running t he follow ing st at em ent fr om t he sysadm in session enables t he vbdot net 1 user account wit h t he pr oper perm issions t o ex ecut e t he pr eceding script . Not ice t hat t he synt ax for adding m ult iple perm issions is t he sam e as for adding a single perm ission except t hat you delim it perm issions w it h a com m a. The follow ing st at em ent adds I NSERT and DELETE per m issions t o t he exist ing SELECT perm ission for t he vbdot net 1 user account . --Delimit more than one permission in a GRANT --statement by using a comma. GRANT INSERT, DELETE ON EmailContacts TO vbdotnet1 You can drop all perm issions for t he vbdot net 1 user account by r evok ing or denying t hem . When you are work ing w it h an indiv idual user account t hat doesn’t belong t o any role, you can eit her revoke or deny ex ist ing perm issions for t he account . Use t he REVOKE st at em ent w it h t he ALL k ey word t o r em ove any ex ist ing perm issions from a user account . The follow ing one- line script dem onst rat es t he synt ax for dr opping t he SELECT, I NSERT, and DELETE perm issions from vbdot net 1. --Use the ALL keyword to concurrently --drop all existing permissions. REVOKE ALL ON EmailContacts TO vbdotnet1 Pe r m ission t o Cr e a t e a Ta ble When y ou assign t he perm ission t o creat e a t able t o user account s for any login not in t he sysadm in fix ed serv er role, y ou com plicat e how an applicat ion m ust refer t o t ables. This is because all m em bers of t he sysadm in fix ed serv er r ole ar e t he dbo user. This dbo user belongs t o all dat abases. You cannot dr op t he dbo user from a dat abase— j ust as no one can dr op t he sa login fr om an inst ance of SQL Ser ver. The rules for r efer encing t ables cr eat ed by t he dbo user are differ ent t han t hose for t ables cr eat ed by any ot her dat abase user. Ev er y user can refer im plicit ly t o t ables cr eat ed by t he dbo user . When t he sam ples in t he preceding sect ion referenced Em ailCont act s, t hey im plicit ly referred t o dbo.Em ailCont act s because t he t able was cr eat ed by a m em ber of t he sysadm in fixed serv er role. SQL Serv er requires you t o explicit ly refer t o t ables creat ed by ot her users. When a user w ho doesn’t qualify as a dbo user creat es a t able, ot her users can refer t o t he t able by t he nam e of t he t able’s ow ner and t he t able’s nam e. For exam ple, if vbdot net 1, who isn’t a dbo user, cr eat es a t able nam ed Em ailCont act s in t he Chapt er07 dat abase, ot her users m ust r efer t o t he t able as Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. vbdot net 1.Em ailCont act s. The vbdot net 1 user can refer t o t he Em ailCont act s t able t hat it cr eat ed as eit her vbdot net 1.Em ailCont act s or j ust Em ailCont act s. Howev er, if t hat user w ant s t o reference t he dbo Em ailCont act s t able, he m ust specify dbo.Em ailCont act s. I f any ot her user, w ho didn’t herself cr eat e a t able nam ed Em ailCont act s, r efers t o a t able w it h Em ailCont act s, SQL Serv er aut om at ically int erpr et s t his as a r efer ence t o dbo. Em ailCont act s. N ot e When y ou perm it non- dbo users t o creat e t ables, a best pract ice is always t o use t he owner qualifier when r eferring t o a t able. I f a dbo user cr eat es a t able nam ed Em ailCont act s, refer t o it as dbo.Em ailCont act s. I f a non- dbo user, such as v bdotnet 1, cr eat es a t able nam ed Em ailCont act s, refer t o it as vbdot net 1.Em ailCont act s. Because users who writ e t heir own T- SQL st at em ent s can dev iat e fr om t hese rules and t he rules lengt hen T- SQL st at em ent s in any ev ent , rest r ict t he per m ission t o creat e t ables t o t he dbo user if at all possible. The follow ing line of script shows t he sy nt ax for enabling t he vbdot net 1 user t o creat e a t able. Set t he dat abase cont ext if it isn’t already set t o t he dat abase for which you want t o grant t he perm ission. Not ice t hat t he sy nt ax for grant ing perm ission t o ex ecut e a st at em ent is slight ly different t han for an obj ect perm ission. Aft er t he GRANT k eyw ord, y ou list t he st at em ent for w hich you ar e grant ing perm ission, but t her e’s no need t o follow t his st at em ent w it h t he ON keyw ord. I n addit ion t o CREATE TABLE, y ou can refer ence CREATE DATABASE, CREATE VI EW, CREATE PROCEDURE, CREATE FUNCTI ON, and select ed ot her st at em ent s. ( See t he “GRANT” t opic in Books Online for t he com plet e list .) As wit h grant ing obj ect per m issions, y ou can use a com m a delim it er when concur r ent ly grant ing perm ission for m or e t han one st at em ent . Close t he GRANT st at em ent wit h t he TO k eyw ord follow ed by t he nam e of t he account t hat is t o receiv e t he st at em ent perm ission. --PermissionToCreateATable --Set the database context before invoking. GRANT CREATE TABLE TO vbdotnet1 Aft er execut ing t he pr eceding GRANT st at em ent , t he vbdot net 1 user can cr eat e a t able, such as one nam ed Em ailCont act s. Because vbdot net 1 ow ns vbdot net 1.Em ailCont act s, it can aut om at ically insert and delet e r ows fr om t he t able— j ust like m em bers of t he sysadm in fix ed serv er r ole and t he db_ow ner fix ed dat abase r ole. Howev er , ow ning an obj ect doesn’t aut om at ically conv ey m em bership in any r ole. Since t he vbdot net 1 login isn’t a m em ber of t he sysadm in fixed dat abase role, t he vbdot net 1 user cannot be a dbo user. The follow ing scr ipt shows t he code for cr eat ing t he vbdot net 1.Em ailCont act s t able. Running t he script from t he session connect ion based on t he vbdot net 1 login m akes t he vbdot net 1 user t he t able’s owner . --Invoke the DROP TABLE statement if the EmailContacts --table already exists for the vbdotnet1 user. CREATE TABLE EmailContacts ( ContactID int Not Null PRIMARY KEY, FirstName nvarchar(20) NULL, LastName nvarchar(35) NULL, Email1 nvarchar (255) NULL ) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. List ing t he t ables from t he sysadm in session now shows t w o t ables w it h t he nam e Em ailCont act s. Use t he follow ing scr ipt t o display t he list of t ables wit h Em ailCont act s as t heir nam e locat ed in t he Chapt er07 dat abase. Figure 7- 3 shows t he r esult set from t he script . One r ow in t he result set is for t he dbo user , and t he ot her is for t he vbdot net 1 user . --List the EmailContacts tables after creating --a second one with the vbdotnet1 user. USE Chapter07 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ’EmailContacts’ Figu r e 7 - 3 . Th e Tab le _ Sch e m a colu m n in t h e r e su lt se t from t h e I N FORM ATI ON _ SCH EM A.TABLES vie w d en ot e s a t a ble ow n e r ’s u ser n am e . N ot e You cannot dr op a user and it s corr esponding login if t he user owns an obj ect , such as a t able, in a dat abase. I f t he obj ect s for a user are no longer required, sim ply drop t hem and t hen drop t he user and it s login. I f you requir e t he obj ect s t hat are owned by a user who m ust be dropped, inv oke t he sp_changeobj ect owner sy st em st ored procedure t o t r ansfer obj ect ownership t o a user who will rem ain in t he dat abase. Then dr op t he user and login. You can add r ows t o and delet e rows fr om t he vbdot net 1.Em ailCont act s t able wit h a script such as t he follow ing. Because t he script references t he t able wit h it s owner qualifier, you can r un t he script fr om any connect ion based on a login wit h a user hav ing perm ission t o select , insert , and delet e r ows from t he t able— for exam ple, t he dbo user or t he vbdot net 1 user . The script generat es a r esult set wit h t hr ee r ecordset s. The first recordset is em pt y because t he preceding script creat ing t he t able doesn’t insert any rows. The second r ecordset shows t he new row for Tony Hill. The t hird r ow shows t he t able em pt y again aft er t he delet ion of t he r ow for Tony Hill. --Run from Chapter07 database context. SELECT * FROM vbdotnet1.EmailContacts INSERT INTO vbdotnet1.EmailContacts VALUES(3,’Tony’, ’Hill’, ’thill@cabinc.net’) SELECT * FROM vbdotnet1.EmailContacts DELETE FROM vbdotnet1.EmailContacts WHERE Email1 = ’thill@cabinc.net’ SELECT * FROM vbdotnet1.EmailContacts W indow s Use r s a nd Gr ou ps Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. Windows users t hat ar e part of Windows group account s in SQL Serv er creat e special challenges for set t ing secur it y . This is because an indiv idual Windows user account can deriv e it s perm ission for a t ask fr om m ult iple sources. Ev en if you revok e a perm ission fr om t he user account for a Windows user, t he Windows user m ay st ill be able t o perform t he t ask cont r olled by t he perm ission. This can happen because t he user account for a Windows group, t o which a Windows user belongs, grant s t he sam e perm ission r evoked for t he indiv idual Window s user account . I n fact , t his sam e scenar io applies t o SQL Ser ver user- defined roles. A SQL Ser ver account can belong t o m ult iple roles and hav e perm issions applied dir ect ly t o it . Revoking one perm ission m ay not fully close all t he r out es by which a SQL Serv er user account can der iv e perm ission t o per form t he t ask. N ot e When work ing wit h a Windows user account t hat can belong t o a Windows group or a SQL Ser ver user account t hat can belong t o one or m or e user- defined roles, consider using a DENY st at em ent t o rem ov e a per m ission. This st at em ent block s t he per m ission t o per for m a t ask even if t he account is grant ed perm ission for t he t ask by virt ue of it s m em bership in anot her Windows gr oup or SQL Ser ver role. The sp_helpr ot ect syst em st ored pr ocedur e helps you m onit or t he per m ission assignm ent s for user account s. By default , sp_helpr ot ect r et ur ns a result set wit h t he obj ect and st at em ent perm issions for all t he user account s in all dat abases on a dat abase ser ver. You can filt er t he r esult set by specify ing select ed argum ent s. For exam ple, designat ing a dat abase in t he @nam e argum ent ret ur ns t he perm issions for j ust t hat dat abase. You can also filt er by t ype of perm ission ( obj ect or st at em ent ) , by account t o whom a perm ission is grant ed, and by w ho grant ed t he perm ission. I f you assign filt ers so t hat t he r esult set from sp_helpr ot ect is em pt y, t he pr ocedur e r et ur ns an error m essage for t he condit ion. The follow ing scr ipt t racks t he assignm ent of perm issions in t he Chapt er07 dat abase. Before t he ex ecut ion of any GRANT st at em ent in t he script , a dat abase connect ion t o t he Chapt er07 dat abase t hat is based on t he login for CCS1\ w invbdot net 1 cannot perform a SELECT st at em ent on t he dbo.Em ailCont act s t able. Aft er t he first set of GRANT st at em ent s, t he CCS1\ w invbdot net 1 user account can perform a SELECT st at em ent based on t wo dist inct perm issions. One perm ission is grant ed dir ect ly t o t he user in t he second GRANT st at em ent . The ot her perm ission is grant ed t o t he user account t hr ough t he CCS1\ w invbdot net Windows gr oup because CCS1\ w invbdot net 1 is a m em ber of t his Windows gr oup. The inv ocat ion of t he sp_helprot ect sy st em st or ed procedur e aft er t he first t hr ee GRANT st at em ent s confirm s t hese t wo perm issions and one m or e for t he CCS1\ w invbdot net 2 Windows user account . The next T- SQL st at em ent in t he script r evokes t he SELECT perm ission for t he dbo.Em ailCont act s t able for t he CCS1\ w invbdot net 1 Windows user . This rem ov es t he perm ission from t he collect ion of perm issions in t he dat abase. The execut ion of sp_helpr ot ect in t he next st at em ent confirm s t hat t he perm ission is m issing. Howev er, rem ov ing t he perm ission doesn’t block t he CCS1\ winvbdot net 1 Windows user fr om perform ing a SELECT st at em ent w it h t he dbo. Em ailCont act s t able as it s source. This is because t he CCS1\ w invbdot net 1 Windows user der ives SELECT perm ission for t he t able fr om it s m em bership in t he CCS1\ w inv bdot net Windows gr oup. Revok ing SELECT perm ission for t he CCS1\ w inv bdot net Windows group account in t he dat abase w ill block t he CCS1\ winvbdot net 1 Windows user fr om perform ing a SELECT st at em ent on t he Em ailCont act s t able. How ev er, t his act ion w ill also rem ov e SELECT perm ission for t he CCS1\ winvbdot net 2 Windows user. The script Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. inst ead invokes a DENY st at em ent for SELECT perm ission on t he dbo.Em ailCont act s t able for t he CCS1\ w invbdot net 1 user account . This st at em ent rest r ict s j ust t he abilit y of t he CCS1\ w invbdot net 1 Windows user t o perform a SELECT st at em ent w it h Em ailCont act s as t he source. Any ot her user in t he CCS1\ w invbdot net Windows gr oup st ill r et ains perm ission for a SELECT st at em ent against t he dbo. Em ailCont act s t able. The final execut ion of sp_helprot ect rev eals an explicit perm ission deny ing t he CCS1\ w invbdot net 1 user account fr om perform ing a SELECT st at em ent on t he dbo.Em ailCont act s t able. --DenyPermission --Before granting SELECT permissions, SELECT statements from --either CCS1\winvbdotnet1 or CCS1\winvbdotnet2 were denied. --Grant SELECT permission for dbo.EmailContacts for --a Windows group and its two individual Windows accounts. GRANT SELECT ON dbo.EmailContacts TO [CCS1\winvbdotnet] GRANT SELECT ON dbo.EmailContacts TO [CCS1\winvbdotnet1] GRANT SELECT ON dbo.EmailContacts TO [CCS1\winvbdotnet2] EXEC sp_helprotect @name=‘dbo.EmailContacts’ --After granting SELECT permission, SELECT statements from --either CCS1\winvbdotnet1 or CCS1\winvbdotnet2 were granted. --Revoke SELECT permission for dbo.EmailContacts --for CCS1\winvbdotnet1. REVOKE SELECT ON dbo.EmailContacts TO [CCS1\winvbdotnet1] EXEC sp_helprotect @name=‘dbo.EmailContacts’ --After revoking SELECT permission for CCS1\winvbdotnet1, the --account could still perform a SELECT statement for EmailContacts. --Deny SELECT permission for dbo.EmailContacts --for CCS1\winvbdotnet1. DENY SELECT ON dbo.EmailContacts TO [CCS1\winvbdotnet1] EXEC sp_helprotect @name=‘dbo.EmailContacts’ --Denying SELECT permission makes it impossible --for CCS1\winvbdotnet1 to SELECT from EmailContacts. --Clean up permission assignments. REVOKE SELECT ON dbo.EmailContacts TO [CCS1\winvbdotnet] REVOKE SELECT ON dbo.EmailContacts TO [CCS1\winvbdotnet1] REVOKE SELECT ON dbo.EmailContacts TO [CCS1\winvbdotnet2] Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. Cha pt e r 8 . Ove r vie w of t he .N ET Fr a m e w or k This book is aim ed at pr ofessional developers w ho have an int er est in program m ing SQL Serv er 2000 w it h Visual Basic .NET. Up t o t his point , t he book ’s focus was pr im ar ily on SQL Ser ver. I believ e t hat y ou cannot opt im ally program SQL Ser ver in any language w it hout a firm underst anding of it s basic wor k ings. Chapt ers 2 t hrough 7 provide a foundat ion in SQL Serv er t hat will serv e you especially well for dat a access and m anipulat ion t asks, as w ell as r elat ed dat a definit ion t asks. Chapt er 1 int roduces you t o beginning Visual Basic .NET and ADO.NET t echniques so t hat y ou hav e som e cont ext for underst anding how t o apply t he SQL Serv er 2000 t opics present ed in Chapt ers 2 t hr ough 7. This chapt er builds on t he init ial exposur e t o t echnologies for t he . NET Fr am ew ork t hat appears in Chapt er 1. I f you j um ped t o t his chapt er wit hout any pr ior ex posur e t o t he .NET Fram ework , now is a gr eat t im e t o look over Chapt er 1. To t ake m axim um adv ant age of Visual Basic .NET for cr eat ing SQL Ser ver solut ions, y ou need t his backgr ound. Chapt er 1 st art s t o conv ey t his background, and t his chapt er finishes t he t ask so you are ready t o dig int o t he .NET Fram ework code sam ples t hr oughout t he rest of t he book . Visual Basic .NET is one of t he core pr ogram m ing languages for t he . NET Fram ew ork, which Microsoft defines as “a new com put ing plat form designed t o sim plify applicat ion dev elopm ent in t he highly dist ribut ed envir onm ent of t he I nt er net .” Micr osoft is t aking a whole new init iat iv e w it h t he .NET Fram ework t hat radically redefines how businesses can program and deploy solut ions as well as access resources ov er corporat e int ranet s or t he I nt ernet . I n m any present at ions on t he bet a v ersions, it was popular t o hear t hat Micr osoft w as bet t ing it s business on t he .NET Fr am ew ork . Whet her or not t his is pr ecisely t r ue, it is clear t hat Microsoft has inv est ed heav ily in pr ov iding a com pr ehensiv e new st ruct ur e for building solut ions, and t he firm has changed in a m aj or way it s m ost popular program m ing language— Visual Basic. The scope and m agnit ude of t he changes prov ide Visual Basic dat abase dev elopers wit h challenges and opport unit ies. This chapt er at t em pt s t o fam iliarize y ou w it h t he archit ect ure of t he .NET Fram ew ork and relat ed t echnologies, including ASP.NET and XML Web serv ices. See Chapt er 1 for int roduct ory m at er ial on Visual Basic .NET and ADO.NET. My goal in t his chapt er isn’t t o em pow er y ou as a program m er wit h t hese t echnologies. I nst ead, I aim t o show how t he t echnologies com plem ent one anot her. I n t he process, I feel you will dev elop an appreciat ion of w hy it is im port ant for y ou t o adopt t he .NET Fram ework and st art program m ing it wit h Visual Basic .NET. This book ’s r em aining chapt ers exam ine t he program m ing y ou use for t he t opics int roduced concept ually in t his chapt er and Chapt er 1. This chapt er cont ains a pr ogram m ing sam ple, but I put it t here j ust for reference purposes. This chapt er is about concept s— not code. ADO.NET, ASP.NET, and XML Web serv ices each are cov er ed in a separat e chapt er t hat drills down int o t echniques for dev eloping solut ions wit h t hem . Plus, t here’s anot her chapt er— Chapt er 12— on m anaging XML w it h Visual Basic .NET. An I n t rodu ct ion t o t h e .N ET Fr a m e w or k This sect ion int roduces you t o cor e .NET Fram ewor k concept s. I t st art s wit h an ov erv iew of t he .NET Fr am ew ork archit ect ure. Next it m ov es on t o w hat ’s new Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. about source code com pilat ion. This is a nat ural ent ry point t o discussing how y ou m anage t he r efer encing of solut ions by client s and how t o deploy solut ions. The sect ion closes w it h brief looks at select ed .NET Fram ew ork feat ures t hat build on m at er ial cov ered earlier in t he sect ion and ar e im port ant t o how you will use .NET Fram ew ork solut ions. .N ET Fr a m e w or k Ar chit e ct u r e Perhaps t he m ost dom inant archit ect ural elem ent of t he .NET Fram ew ork is it s com m on language runt im e. The r unt im e sit s on t op of t he operat ing sy st em . Program m ers w r it e t o t he r unt im e in any com pliant language. The runt im e ev ent ually wr it es w hat is called m anaged code t o t he specific operat ing sy st em on which it r uns. As I wr it e t his chapt er, t he operat ing syst em s t hat support t he com m on language runt im e include t hose based on t he 32- bit v ersions of Windows, including Windows 98, Windows Millennium , Windows NT, Windows 2000, and Windows XP. Micr osoft has a Window s .NET Serv er oper at ing syst em in bet a t hat likely w ill include t he .NET Fram ework . I n addit ion, y ou can ex pect t he runt im e t o pr oduce code suit able for t he fort hcom ing 64- bit version of Windows. While t he com m on language r unt im e r uns on t op of Windows syst em s, one of t he great st r engt hs of runt im e- com pliant solut ions is t heir int er operabilit y wit h ot her operat ing syst em s. This follows fr om r unt im e support for XML and XML Web serv ices. The core t echnologies for XML and XML Web serv ices r ely on indust ry wide st andards. Because ot her vendors are endorsing t hese st andards along w it h Microsoft , y ou can be assured of a level of int er operabilit y for t he solut ions t hat y ou cr eat e w it h t he r unt im e. I f v endors follow t hr ough on t heir endorsem ent s for t he st andards and y ou build y our solut ions w it h code m anaged by t he runt im e, y ou can achiev e lev els of int er operabilit y acr oss operat ing syst em s not pr ev iously enj oy ed by applicat ion dev elopers. N ot e Learn m or e about XML in Chapt er 6 and Chapt er 12. XML Web services is t he t opic of t he closing sect ion in t his chapt er as well as t he whole of Ch a pt e r 1 3 . When y ou develop solut ions for SQL Serv er, y ou will benefit fr om t he fact t hat t he com m on language runt im e can be host ed by SQL Ser ver 7 and lat er v ersions and Micr osoft I nt er net I nfor m at ion Serv ices v ersions 4.0 and lat er ; I I S is t he Micr osoft Web serv er for Windows NT and Windows 2000. This giv es you a chance t o int egrat e t ight ly y our dat abase and Web solut ions w it h t he m anaged code generat ed by t he runt im e. For exam ple, t he .NET Fram ewor k ships w it h m anaged prov iders for SQL Ser ver and OLE DB dat a sour ces. The SQL Serv er provider offers subst ant ial per for m ance advant ages because of it s opt im izat ion for SQL Ser ver 7 and SQL Serv er 2000. I n addit ion, ASP.NET is a part of t he .NET Fram ew ork t hat I I S host s. ASP.NET is t he next generat ion of dev elopm ent t echniques for t hose creat ing solut ions w it h ASP now. I n order for ASP.NET pages t o r un, t hey m ust be com piled by t he r unt im e. ASP.NET is an int egr al part of I I S 4, j ust as I I S 3 host s t he ASP obj ect m odel. I n addit ion, ASP.NET can int eract wit h SQL Ser ver t hr ough t he .NET Fram ew ork dat a prov iders. ( See Chapt er 11.) Figur e 8- 1 shows a sim plified schem at ic of t he pat h from source code in Visual Basic .NET ( or anot her r unt im e- com pliant language) t hrough t o int eract ions w it h SQL Ser ver and browser s on a Web. The com m on language r unt im e t r anslat es t he source code t o m anaged code. This m anaged code can, in t urn, int eract wit h t he Windows operat ing syst em , SQL Serv er , and browsers. Wit h t he aid of a m anaged pr ov ider, such as t he one for SQL Serv er, y our solut ions can access and m anipulat e dat a. You can use t he ASP.NET com ponent of t he .NET Fram ework t o Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. creat e ASP.NET pages t hat r eside on an I I S ser ver. These pages can serv e dynam ic elem ent s t o br owsers on a Web. I n addit ion, t he pages can offer t he browsers t he opport unit y t o access and m anipulat e dat a on a SQL Ser v er . Figu re 8 - 1 . A sch e m a t ic illu st r a t ing t h e role of t h e com m on lan gu a ge ru n t im e an d it s m an ag e d cod e in in t e ra ct ing w it h t h e W ind ow s op er a t in g syst e m , SQL Se r ve r , a n d I I S. Com pilin g Sou r ce Code The .NET Fram ew ork support s m ult iple program m ing languages in a com m on way . I n addit ion t o Visual Basic .NET, Visual St udio .NET support s t he preparat ion of source code in ot her languages, such as C# and Visual C+ + . Web developers who are used t o building solut ions in JScr ipt w ill appreciat e t he fact t hat t hey can creat e ASP.NET solut ions wit h JScript .NET. I n fact , t hese dev elopers can use JScr ipt .NET t o im plem ent solut ions across t he full range of .NET Fram ewor k capabilit ies because JScript .NET is runt im e- com pliant . I n addit ion, t hird- part y vendors ar e readying ot her languages for r unt im e com pliance. This pr oliferat ion of languages w ill offer dev elopers a wide range of opt ions in which t hey can program t he .NET Fram ework . N ot e JScript .NET is an ext ension of t he Microsoft JScr ipt language, which was based on ECMAScript ( ECMA- 262) . ECMA is t he European Com put er Manufact urers Associat ion. JScript .NET is ex plicit ly developed for use w it h t he runt im e. Since JScr ipt .NET generally follows t he ECMAScript convent ions, it offers a st andards- based rout e t o creat ing .NET Fram ework solut ions w it h a popular script ing language am ong Web developer s. A w onderful t hing about t he .NET Fram ew or k is t hat all languages can have t he sam e capabilit ies if t hey ar e fully r unt im e- com pliant . For exam ple, Visual Basic .NET has t he sam e capabilit ies as C# ( and so does JScr ipt .NET) . I n addit ion, dev elopers in one language can fr eely use obj ect s creat ed by dev eloper s in ot her languages. This cross- language funct ionalit y wasn’t alway s easy t o im plem ent befor e t he .NET Fram ework because of slight incom pat ibilit ies in source code language com pilat ion pr ocessing. The .NET Fram ework act ually readies source code for execut ion t hrough a series of t w o com pilat ions. The first com pilat ion Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. conv ert s t he source code t o Microsoft I nt erm ediat e Language ( MSI L) . The second com pilat ion conv ert s MSI L t o CPU- specific code for t he com put er r unning t he code. The first com pilat ion from source code t o MSI L generat es a represent at ion of y our program t hat capt ur es it s program m ing inst ruct ions and m et adat a about t he program . The com pilat ion st or es it s out put in a port able ex ecut ion ( PE) file. MSI L is a language- independent way of expressing y our program m ing logic. The m et adat a describes t he t ypes t hat your code cr eat es as well as t heir m em bers, such as m et hods, propert ies, and ev ent s. A t ype is an elem ent , such as a class. Anot her im port ant m et adat a elem ent is t he descript ion of t he assem bly for an applicat ion. An assem bly is t he unit for st or ing a solut ion in t he .NET Fram ework . The assem bly descript ion in t he m et adat a includes an ident it y specificat ion for t he assem bly, export ed t y pes, r efer enced t ypes, and secur it y per m issions needed t o run. A r eference t o a t y pe is like a reference t o a class in a t ype library . Because t he m et adat a for an assem bly includes int ernal t ypes and ext er nally r efer enced t ypes, t here is no need for r efer ences t o t ype librar ies in Visual Basic .NET and ot her runt im e- com pliant languages. The second com pilat ion from MSI L t o m achine code readies y our code for ex ecut ion on a specific processor. The .NET Fram ework can accom plish t his wit h a Just - I n- Tim e ( JI T) com piler. JI T com pilers ar e specific t o each support ed CPU archit ect ure. JI T com pilat ion com piles t he cont ent s of t he PE file as a user references it s elem ent s dur ing a session. PE file elem ent s, such as a t y pe m em ber, ar en’t com piled unt il a user r efer ences t hem . Aft er t he init ial com pilat ion, t he runt im e aut om at ically r efers t o t he com piled v ersion, t hus reducing t he t im e t o ex ecut e t he code. This pr ocess also saves com pilat ion t im e by not com piling t hose elem ent s t hat a user doesn’t refer ence dur ing a session. Unless an adm inist rat or explicit ly designat es ot herw ise, t he com pilat ion t o m achine code exam ines t he MSI L and it s m et adat a t o det erm ine w het her it is t ype safe. The t erm t ype safe r efers t o t he fact t hat a t ype accesses only m em ory locat ions for which it has access perm ission. This securit y check allows t he .NET Fram ew ork t o enforce secur it y r est r ict ions. Asse m blie s a nd M a n ife st s Assem blies and t heir m anifest s are an excit ing innovat ion int roduced w it h t he .NET Fr am ew ork. They are excit ing because t hey can clear ly elim inat e m any opport unit ies for .dll conflict s— popular ly referr ed t o as “dll hell.” A .dll conflict can em erge w hen a user inst alls a new applicat ion t hat wr it es over an ex ist ing .dll file wit h a new version t hat isn’t fully backward com pat ible. I f anot her, prev iously inst alled, applicat ion relies on a t ype m em ber t hat is changed or elim inat ed in t he new .dll, t he pr ev iously inst alled applicat ion w ill fail. Assem blies and m anifest s offer a couple of workarounds t o t his pr oblem for solut ions based on COM com ponent s. A .NET Fram ework solut ion ex ist s as an assem bly of one or m ore files. These files can include t he MSI L as well as ot her r esources, such as im age files or ot her docum ent files t hat a solut ion references. An assem bly m ust include a m anifest , which cont ains m et adat a about t he assem bly. This m et adat a describes t he files in t he assem bly . I n t he case of a single- file assem bly, t he m anifest r esides wit hin t he solut ion’s .dll file, but ot herwise an assem bly ’s m anifest r esides in a separat e file. A solut ion’s assem bly can consist of up t o four t ypes of elem ent s. • The assem bly’s m anifest • The MSI L code for t he solut ion • The t ype m et adat a for t he MSI L code • Resource files r equir ed by t he solut ion Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. The assem bly is t he deploym ent unit for solut ions in t he .NET Fram ew ork. Because all t he elem ent s for a solut ion can ex ist wit hin a single assem bly , y ou can deploy a solut ion by dist ribut ing t he solut ion’s assem bly of files. St or e t he assem bly as a dir ect ory or subdir ect or y on a t arget workst at ion. The com m on language r unt im e m ust be inst alled on t he work st at ion in order t o t ransfor m t he MSI L t o nat ive m achine code. This appr oach is part icular ly convenient w her e a solut ion perform s t asks t hat y ou don’t care t o share wit h ot her solut ions. Som e solut ions are ut ilit ies. When t hese ut ilit y solut ions are likely t o be a part of m any ot her solut ions, y ou can st ore t he ut ilit y solut ions in t he Global Assem bly Cache ( GAC) . There is one GAC per com put er. When y ou place an assem bly in t he GAC for shar ing by one or m ore ot her solut ions, t he shared assem bly in t he GAC m ust hav e a st rong nam e. The st r ong nam e uniquely ident ifies an assem bly in t he GAC t o avoid conflict s from t wo assem blies t hat m ay hav e t he sam e t ext for a nam e. Visual St udio .NET includes t ools t o sim plify t he cr eat ion of st rong nam es t hat are based on t he t ext for an assem bly’s nam e, it s v ersion num ber, cult ur e inform at ion, public key , and a digit al signat ure. The .NET Fram ew ork SDK discourages locat ing assem blies in t he GAC unless essent ial because it can com plicat e deploym ent and adm inist rat ion. For exam ple, deploy ing a solut ion can r equire copy ing t wo dir ect or ies— one for t he m ain assem bly and t he ot her for t he shar ed assem bly in t he GAC. I n addit ion, t he GAC resides in t he syst em direct ory. This direct ory oft en has r est rict ed access. These access rest r ict ions m ay necessit at e perm issions for copy ing an assem bly t o t he GAC t hat t he user inst alling an applicat ion doesn’t hav e. D e ploy a Solut ion—X COPY a Folde r You can creat e .NET Fram ew ork solut ions for Windows t hat are t ot ally self- cont ained in a single folder . When y ou cr eat e a .NET Fr am ework solut ion using t he Windows Applicat ion t em plat e, Visual St udio .NET by default cr eat es a folder for your solut ion in t he last direct or y in w hich you sav ed a previous solut ion. This folder has a root folder and at least t wo subfolders— bin and obj . You can st or e t he resour ces for your solut ions, such as cust om classes, im age files, and XML schem a files, anywhere y ou need in t he r oot folder ( or even out side t he root ) . The advant age of st oring all files for a solut ion in t he r oot folder , or any of it s subfolders, is t hat you can t hen deploy y our solut ion wit h an XCOPY com m and, or any equiv alent t echnique, t hat copies t he solut ion’s folder. All t he Visual Basic .NET solut ions included in t his book ’s sam ple files ar e av ailable as folders t hat you can copy t o your com put er. I f y ou copy t hem t o a m achine wit h t he proper configurat ion— for ex am ple, one wit h t he com m on language runt im e— y ou can run t he solut ions from t he folder t o w hich y ou copy t hem . While I am t alk ing about solut ion folders, it is probably wort h m ent ioning a couple of special files w it hin a solut ion folder . The solut ion’s .exe file resides in t he bin subfolder . You can launch t he solut ion by inv oking t his file. By default , t he .exe file has t he sam e nam e as t he solut ion. Therefor e, if your solut ion has t he nam e WindowsApplicat ion1, t he .exe file for Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

CÓ THỂ BẠN MUỐN DOWNLOAD

Đồng bộ tài khoản