实验三：SQL SERVER20052008 账户及权限管理.docx

实验三:SQL SERVER2005/2008账户及权限管理1、实训目的熟悉SQL Server中安全主题、安全对象以及权限的概念掌握账户创建以及权限授予撤销的T-SQL语法熟悉架构的概念、用途以及创建架构的方法熟悉登录账户和数据库用户之间的区别2、实验准备专业实验室机房、SQL Server 2008R2企业版、Word、Visio3、相关知识介绍（1）、SQL Server 账户SQL Server中的账户属于安全主体的范畴，根据账户的作用范围，存在着“登 录账户”和“数据库用户”两种类型的账户。其中登录账户被设计成只是进ASQL Server服务器实例的一种账户，并非想使用它直接访问实例下的数据库，在使用 登录账户进入服务器实例后，如果想访问实例下某个数据库，还必须将登录账户 映射到该数据库中的一个数据库用户，最后以该数据库用户的身份访问数据库中 的对象。在使用登录账户登录SQL Server服务器实例的过程中，系统首先要对所使用 的登录账户及其密码是否有效进行检查（称为“身份验证”），以避免非法用户的 访问。身份验证的工作根据设置既可以由Windows进行也可以由SQL Server来进 行。SQL Server的登录账户是SQL Server服务器实例级别的安全主体，无论 windows级别的安全主体还是数据库级别的安全主体，都需要和它建立映射关 系，以便数据库的正常访问。（2）、登录账户与数据库用户的映射过程除了 windows账户需要映射到登录账户外，各数据库中的用户也需要映射 到登录账户才能实现数据库的正确访问。在设置登录账户和数据库用户的映射关 系时，可以将一个登录账户设置为同时与多个数据库中的用户相映射。但是一个 登录账户在一个数据库中只能和该数据库的一个用户相映射。（3）、架构（Schema）在SQL Server2005/2008中，架构是存放数据库对象的容器，用于将数据库中的对象按 其性质、用途、功能等在逻辑上进行分类，逻辑上不同类的数据库对象存储在不同的架构下。 架构同数据库对象之间的关系有点像Windows中的文件夹和文件的关系一样。在Windows 中，一个文件夹可以包含多个不同类型的文件，而一个文件只能存在于一个文件夹下。同样， 一个架构可以包含多个不同类型的对象，而一个对象只能存在于一个架构之下。而数据库用 户同架构之间则是一种拥有和被拥有的关系，一个用户可以拥有零个或多个架构，而一个架 构只能且必须被一个用户所拥有，数据库中不存在没有拥有者的架构。用户拥有某个架构， 表明该用户对该架构具有执行ALTER、CONTROL、SELECT、UPDATE等十多种系统预定 义操作的权力，该用户基本上可以对架构本身以及架构中包含的对象执行任何操作。数据库 用户除了可以拥有架构之外，还必须为其指定一个默认架构，默认架构是用户默认的一个“工 作区域”，用户在访问（比如创建、查询等）数据库对象时如果没有明确指出对象所处的架 构，就在这个默认的“工作区域”开始工作。一个架构可以被多个用户作为其默认架构，该 架构实际上成了这些用户公共的“默认区域”。在数据库中创建用户时，如果没有显式指定 其默认架构，系统自动将dbo架构作为该用户的默认架构，因此dbo架构是系统准备为用户 指定的默认“公共区域”，这也是其存在的主要目的。4、实验步骤及内容登录账户、数据库用户以及权限综合实验要求： 在windows操作系统中创建一个新的账号administrator2，让其隶属于users组。 基于 windows账户administrator2，创建一个与之相映射的SQL Server登录账户“MAJIANMINGadministrator2”。 以新创建的登录账户“ MAJIANMINGadministrator2 ”登录SQL Server实例，尝试访问 该登录账户的默认数据库“master”，并观察master数据库中与登录账户“MAJIANMINGadministrator 2” 相映射的数据库用户。 尝试访问非默认的非系统数据库“AdventureWorks2008R2”，分析不能访问该数据库的 原因。 给“ AdventureWorks2008R2”数据库下的用户guest授予连接数据库的权限（connect权限）， 然后再次以“ MAJIANMINGadministrator 2”登录账户登录实例并访问数据库 AdventureWorks2008R2，看能否成功。 在“AdventureWorks2008R2”数据库下创建一个新的数据库用户AW_User1，并将其映 射到登录账户。 使用该数据库用户AW_User1访问AdventureWorks2008R2，查看登录账户和数据库用户 映射情况。 使用AW_User1身份在AdventureWorks2008R2创建表t1，并在表中插入测试数据。 将AW_User1用户加入到db_owner数据库角色，使其成为该数据库角色的成员使用AW_User1 身份在AdventureWorks2008R2中创建新的架构AW_User1_Schema,并将 该架构作为自己的默认架构，在该架构下创建表t2,并将db。架构下的t1表转到 AW_User1_Schema架构下。操作步骤：第一步：实验前准备工作（1）：创建windows账户administrator?，并让其隶属于users组。确定取消（2）：以系统管理身份登录SQL SERVER,创建一个新的数据库，用于后面的测试（注意：为了确保连接的是本机的SQL实例，红色园圈处可以使用本机IP地、本机主机 名、localhost、127.0.0.1 等几种方式。）确定即可完成新数据库Mydatabasel的创建。(3)：修改登录帐户sa的密码，以便于后面的测试连接但)暮 '囱- J localhost (SQL Server 9.0.3042 - STV65student)-一J数据库+ 一J系统数冤库+ 一J数据库快照'-一J安全性-一J登录名务 EUILTINVAdmini strators R HT AUTHORITYSYSTEMsa+贸ETU 贸ETU 忿|ETU & STU Ca服务器 _J凭据 服务器对置 复制 管理新建登录名编写登录脚本为危)报表重命名册除刷新(I)属性d)I2O$MSSQLSERVERI2O$MSSQLSERVER:STU20$MSSQLSERVERNoti fi catSQL Serve修改成自己的密码后确定即可。第二步：基于windows账户administrator?，创建一个与之相映射的SQL Server登录账户。图形方式创建如下:然后确定，即可创建基于windows帐户administrator2的SQL SERVER登录帐户STU65administrator结果如下：localhost CSQL Server 9. 0. 3042 - STU65student)一J数据库一J安全性-1 D登录名® BUILTINVAdmimstrators沃 NT AUTHORITYSYSTEM.气sa® STU20SQLServer2005MSFTEUser$STU20$MSSQLSERVER® STU20SQLServer2005MSSQLUser$STU20$MSSQLSERVER® STU20SQLServer2005SQIAgentUser$STU20$MSSQLSERVERR STU65.adniin.istratorZ+ 一J服务器角色+ 一J凭据吕吕国田田田田田服务器对象复制管理Nuti fi cati on Servi cesSQL Server 代理（请大家注意： 这是登录帐户STU65student在创建新登录帐户STU65administrator2）命令方式创建如下：（如果用图形方式创建了登录帐户，就不需要再使用命令方式了-以sa登录账户或windows管理员身份登录SQL Server实例-在新的查询分析器中输入以下命令创建新的SQL Server登录账户''MAJIANMINGadministrator2”USE masterGOCREATE LOGIN STU65administrator2 FROM WINDOWS WITHDEFAULT_DATABASE=masterGO从上图观察，新的登录账户“STU65administrator2”创建成功。第三步：以新创建的登录账户“STU65administrator2”登录SQL Server实例，检查它与各 个数据库在数据库用户在映射情况。尝试访问该登录账户的默认数据库“ master”，并观察master数据库中与登录账户 “ STU65administrator 2”相映射的数据库用户。Use master goselect distinct hostname,loginame, name as user_name,program_name from master.sysprocesses a left join sysusers b on a.uid=b.uidwhere hostname!=* and islogin=1goUm 已 inast 已 izm已 1 已ut. clist iliet hostn；3irie ,r logiii；airiE r ii；ain已 as user_ii；ain已口r ugr；3i'i_ii；airi已 f roin inast-Eiz n n sysprocessps a left, join sysusers toon a n uicl=l：i n uidwhere host.ii：airiE ! = 1 1 and islogin= 1go< 一 一 皿一I> J结果L*消息hostname loginameuser_name program_nameSTU65S TIESwdiriini 或rmt顷2guestMicrosoft SQL Server Management Studio -查ifil可以清楚的发现“Microsoft SQL Server Management Studio -查询”这个程序在访问master 数据库时，是将该数据库用户guest与登录账号“STU65administrator2”相映射。> 尝试访问非默认的数据库“Mydatabase1”，分析不能访问该数据库的原因。us e iriydat. ato as e 1go口匚二二二i二二|叫一二二二消息消息泌，级别皿状态，第行、-二服务器主阵"STU6S- administrator2"无法在当前安全上下文下访问数据库"My膈匕ah点w 1即：STU65administrator2在访问Mydatabase1时出现下列提示:不能访问的原因：是因为登录帐户STU65administrator2映射到了 Mydatabase1数据库下的 guest用户，而uest用户默认是没有connect权限的；所以导致Mydatabase1不能访问。 可以发现，在 Mydatabase1数据库3”安全性”3”用户”下有：dbo、guest、 INFORMATION_SCHEMA、sys这4个默认存在的数据库用户。其中，dbo用户只能和sa 登录账户映射；另外两个未知；第四步：数据库用户dbo给数据库用户guest授予权限 请sa登录帐户所映射的dbo数据库用户给“Mydatabasel”数据库下的用户guest授予连接 数据库的权限（connect权限）。使用SA登录账户，新建一个到SQL Server实例的连接。对象资源管理器 P X连接敏，莪二+ ijj STU65 CSQL Server 9.0.3042 - STUS5adminiStrator2)+ ijj STU65 GQL Server 9.0.3042 - sa)在SA登录账户的会话中，新建查询分析，并输入以下授予语句给Mydatabase1数据库的用户 guest授予 connect权限。USE MydatabaselGOGRANT CONNECT TO guestGO> 再次切换到“STU65administrator2”登录账户的会话中，尝试访问数据库Mydatabasel。 use Mydatabasel go注意：上图是在“STU65administrator2”登录账户的会话中进行操作的，这在右边的“对象资源管理器” 窗口中进行了高亮显示，以后涉及到多个会话的，都以此方法高亮显示当前的会话环境。> 验证“STU65administrator2”登录账户与Mydatabase1数据库用户的映射关系。use Mydatabaselgo select distinct hostname,loginame, name as user_name,program_name frommaster.sysprocesses a left join sysusers b on a.uid=b.uidwhere hostname!=* and islogin=1 go对象资源管理器P X田 0 STU65 (SQL Server 9. 0. 3042 - STUB 田16STU65 (SQL Server 9.0. 3042 -羿)USE Mydat.：anas巳 1GOGRAI'-IT CONNECT TO 目u已muGOus已 Mydat.；anmm已 1goselect distinct- host-f 1 ogin：ainef left- join sysusers bon a uid=b « uidwhere hostname 1 = 1 1 and islogin=l gonarr旧 as uaeL_ri：siriEz pirograrn_riarr旧f roin mas ter .sysprocessesaSTU65. lydat. . . Query2. sql* ' STU65. Mydat. . . Queryl. sql*对象资源管理器详细信息回结果q消息hostnameloginameuser_nameprogram_name1_|STU65NT AUTHORITYSYSTEMdboSQLgerit - Generic Refresher2STU 65sadboMicrosoft SQL Server Management Studio3STU65sadboMicrosoft SQL Server Management Studio 查询4STU65STU65administrator2guestMicrosoft SQL Server Management Studio5STU65STU65administrator2guestMicrosoft SQL Server Management Studio 查询可以发现，登录账户“STU65administrator2”和数据用户guest映射成功。第五步：为数据库Mydatabase 1创建新的数据库用户AW_User1,并人为的指定AW_User1 和登录账户的映射方式在“Mydatabasel”数据库下创建一个新的数据库用户AW_User1，并将其映射到登录 账户 STU65administrator2。使用sa登录账户登录实例，撤销Mydatabase1数据库guest用户的connect权限，消除 该用户的影响。use Mydatabase1gorevoke connect from guestgoSTUBS CSQL Server 9.0.3042 - STUBSTUBS CSQL Server 9.0.3042 - sa)STU65. Ifdat. . . Query2. sql* STUBS Mydat. . . Qu« left join syausers b on a uid=b uid where hostname!=11 and islogin 1use Mydatafciasel9口revoke connect from guest go<J消息命令已成功完成。Sa 将 Mydatabasel 的 guset 用户的 connect 权限撤销后，“STU65administrator2” 登录账户再 也不能通过guest访问Mydatabase1数据库了。-为数据库 Mydatabase1创建新的数据库用户AW_User1-并将其映射到登录账户"STU65administrator2 ”USE Mydatabase1GOCREATE USER AW_User1 FOR LOGIN STU65administrator2 WITH DEFAULT_SCHEMA=dbo GO-以、'STU65administrator2登录账户登录实例，并访问数据库Mydatabase1use Mydatabase1go;消息-查看映射情况select distinct hostname,loginame, name as user_name,program_name from master.sysprocesses a left join sysusers b on a.uid=b.uid where hostname!=* and islogin=1go发现：“STU65administrator2”已经和新创建的数据库用户AWUserl建立了映射关系。查看数据库用户所具有的权限USE MydatabaselGO exec sp_helprotect username = 'AW_User1'GOOwnerObjectGranteeGrantorProtectTypeActionColumn1AW_User1dboGrantCONNECT可以发现，此时数据库用户AW_User 1仅仅只具有连接Mydatabase1数据库的权限。此时尝试在数据库中创建表t1，肯定不会成功。use Mydatabase1gocreate table t1(id int primary key not null,name varchar(20) not null)go结果：在数据库'MydatabaseL中拒绝了create table权限。> 按照提示给数据库用户AW_User1授予创建表的权限(即：CREATE TABLE权限) 注意：这里是sa给它授予CREATE TABLE权限，需要切换到SA会话中操作。USE MydatabaselGO-授予用户C REATE TABLE权限GRANT CREATE TABLE TO AW_User1GO-查看AW_User1用户的权限状况exec sp_helprotect username = *AW_User1*GO结果:> 再次尝试创建表tl。注意：此操作在“STU65administrator2”登录账户会话下执行。use Mydatabase1 go create table t1 （id int primary key not null, name varchar（20） not null ）Go结果：指定的架构名称"dbo"不存在，或者您没有使用该名称的权限。提示显示，用户AW_User1没有在默认架构dbo下创建表的权限，因此还有授予AW_User1 （安全主体）在dbo （安全对象）上的一些适当权限。> 授予AW_User1在dbo上的控制权（CONTROL）注意：这里是sa给AW_User1权限，需要切换到SA会话中操作。USE MydatabaselGO-授予用户AW_User1在dbo上的控制权限GRANT CONTROL ON SCHEMA:dbo TO AW_User1GO执行此语句后，AW_User1拥有了 dbo架构的CONTROL权限。dbo的权限崖)：权限授权者授予具有授予.拒绝查看更改跟踪dho更改db u更新db u接管所有权db u控制dbo0列权限(£).V:'显式.:有效> 再次尝试创建表tl。use Mydatabaselgocreate table t1(id int primary key not null, name varchar(20) not null )Go-| Adventur eW orks2008R2I _J数据库关系图-一I表+ 一J系统表+ dbo. AWBuildVersion+ _J dbo. D at abas eLog+ _J dbo. ErrorLogBfcldbo. tl |> 向表tl中插入数据由于表T1是AW_User1用户所创建，该用户拥有该表，具备该表的众多权限。可以对表进 行诸如：插入、查询、删除、更改等各种操作。insertintot1values(1,'xiangmeng')insertintot1values(2,'xiangsicheng')insertintot1values(3,'zhangshimeng')goselect * from t1go结果:idname1xiangmeng2xiangsicheng3zhangshimeng> 将AW_User1用户加入到db_owner数据库角色，使其成为该数据库角色的成员由于AW_User1只是Mydatabasel数据库中一个普通的用户，其对各种安全对象所具有 的权限非常少。需要Sa等高权限用户授予权限，才能执行相应的操作。将AW_User1加入 到db_owner数据库角色，该用户便自动继承了该数据库角色所具有的一切权限。注意：SA会话中操作。USE MydatabaselGOEXEC sp_addrolemember ,db_owner, 'AW_User1'GO-查看数据库用户所属数据库角色select DbRole = g.name, MemberName = u.name, MemberSID = u.sidfrom sys.database_principals u, sys.database_principals g, sys.database_role_members mwhere g.principal_id = m.role_principal_idand u.principal_id = m.member_principal_idorder by 1, 2goDbRoleMemberNameMemberSID1db_ownerAW_User10x0105000000000005150000001199B9786C79806975B9755.2db_ownerdbo：山5D5加戋5FEF7匚爵.：> 在Mydatabasel中创建新的架构AW_User1_Schema,并将该架构作为自己的默认架构。（在 stu65administrator2 会话中操作）USE Mydatabase1GO-创建新的架构CREATE SCHEMA AW_User1_SchemaGO-更改用户的默认架构ALTER USER AW_User1 WITH DEFAULT_SCHEMA=AW_User1_SchemaGO-J ST1J65 (SQL Server 9. 0. 3042 - STU65.adniini strator A.日口数据库田口系统数据库田学数据库快照-,Mydatabasel国数据库关系圈+ _1表+ £视图同曳词田可编程性+ _| Servi ce Broker+ _j存储-O安全性-以用户AW_Userl色dbo、guest命 IHFORMATION_SCHEMA sys+ P角色-O架构AW_UEer 1_S chem adb_ac c e e e ：=h1iti i n db_backup up erati db_datar eader db_datawri ter db_ddl：=LiiiriirL db_derLydat ar 已 adi db_deny dat awr i ti db_owner db_s e cur i tyaiiri i GO一一仓CREGO-JALTGO<高消息命令已新建架构更）.编写架构脚本为匿）报表册臃廷）刷新届性遂厂-STU65.adjnin.i strator AS STU65 tSQL Server 9.0. 3042-_J数据库+ _J系统数据库+ _J数据库快照-| Mydatabasel【_J数据库关系图+ n表+ _j视图_j同义词+ _j可端程性+ _J Servi ce Broker+ _|存储-_J安全性-一|用户AW_Userl& db u气 IHFOEMAT% sys+ _J角色-_J架构.W AW_Userl 蜀 db_acces 22） db_backu新建用户®-.编写用户脚本为W)报表册除Q)刷新(I)屉性®创建表t2在创建新表的时候，如果没有明确指定存放在那个架构下，新表存放在默认架构下。CREATE TABLE T2（st_id varchar（4） primary key not null,st_nm varchar（20） not null,stsex varchar （2） not null）INSERTINTOT2VALUES （'0001','张三'，'男'）INSERTINTOT2VALUES （ '0002','李四','男'）INSERTGoINTOT2VALUES （'0003','王五'，'女'）- 3 STU65 tSQL Server 9.0.3042 - STU65administrator A- 一J数据库+ _J系统数据库+ _J数据库快照-| Mydatabasel；一数据库关系国-一表+ 一系统表AW_USerl_Schema. T2+ _J dbo. tl将tl表从dbo架构下转移到AW_User1_Schema架构下 ALTER SCHEMA AW_User1_Schema TRANSFER dbo.tl GO-,jj STU65 CSQL Server 9.0.3042 - STU65administrator A -一J数据库+ 一J系统数据库+ a数据库快照-j Mydatabasel+ 一J数据库关系图-表+ _系统表AWJJserl_Schema. tl+国|+AW_Vser 1 .Schema. T2 a in riSELECT * FROM tl5、思考与总结GOidname1xiangmeng2xiangsicheng3zhangshimeng从上面可以看见将表从一个架构转移到另外一个架构，对表中的数据不会产生影响。(1) 登录账户和数据库用户有何区别？(2) 登录账户和数据库用户如何映射？(3) 默认架构的含义是什么？(4) 角色的作用是什么？(5) 用什么语句实现表在架构之间的转移?