权限、角色及架构 【转载】

1. /*  权限、角色、架构  */  
2.   
3. 一、权限  
4.   
5. grant 用于授予用户操作TSQL语句的相应权限  
6. deny 用于拒绝用户操作TSQL语句的相应权限  
7. revoke 回收grant或deny授予的权限  
8.   
9. 可授予对象的权限有:  
10. select 检索数据、insert 插入数据、update 更新数据、delete 删除数据、  
11. references 外键引用、execute 运行存储过程  
12. create database 创建数据库  
13.   
14. 二、角色：角色的出现方便了对权限的管理，sqlserver中很多权限，不同权限的组合或单独就构成了角色。  
15.   
16. 数据库级别的角色  
17. db_owner 可以执行数据库的所有配置和维护活动，还可以删除数据库。  
18. db_securityadmin 可以修改角色成员身份和管理权限。向此角色中添加主体可能会导致意外的权限升级。  
19. db_accessadmin 可以为 Windows 登录名、Windows 组和 SQL Server 登录名添加或删除数据库访问权限。  
20. db_backupoperator 可以备份数据库。  
21. db_ddladmin 可以在数据库中运行任何数据定义语言 (DDL) 命令。  
22. db_datawriter 可以在所有用户表中添加、删除或更改数据。  
23. db_datareader 可以从所有用户表中读取所有数据。  
24. db_denydatawriter 不能添加、修改或删除数据库内用户表中的任何数据。  
25. db_denydatareader 不能读取数据库内用户表中的任何数据。  
26.   
27. 服务器角色  
28. sp_helpdbfixedrole 返回固定数据库角色的列表。   
29. sp_dbfixedrolepermission 显示固定数据库角色的权限。   
30. sp_helprole 返回当前数据库中有关角色的信息。   
31. sp_helprolemember 返回有关当前数据库中某个角色的成员的信息。   
32. sys.database_role_members 为每个数据库角色的每个成员返回一行。   
33. IS_MEMBER 指示当前用户是否为指定 Microsoft Windows 组或 Microsoft SQL Server 数据库角色的成员。   
34. CREATE ROLE 在当前数据库中创建新的数据库角色。   
35. ALTER ROLE 更改数据库角色的名称。   
36. DROP ROLE 命令从数据库中删除角色。   
37. sp_addrole 在当前数据库中创建新的数据库角色。   
38. sp_droprole 从当前数据库中删除数据库角色。   
39. sp_addrolemember  为当前数据库中的数据库角色添加数据库用户、数据库角色、Windows 登录名或 Windows 组。   
40. sp_droprolemember 从当前数据库的 SQL Server 角色中删除安全帐户。  
41.   
42. public数据库角色  
43. 每个数据库用户都是public角色的成员，如果未来向某个用户授予或拒绝对安全对象的特定权限时，  
44. 该用户将继续授予该对象的public角色的权限  
45.   
46. 默认情况下，数据库创建时就包含一个 guest 用户。授予 guest 用户的权限由在数据库中没有帐户的用户继承。  
47. guest 用户不能删除，但可以通过在 master 和 temp 以外的任何数据库中执行   
48. revoke connect from guest 来撤消该用户的 CONNECT 权限，从而禁用该用户。  
49.   
50. 三、架构Schema  
51.   
52. 数据库对象，指的是表、视图、存储过程、触发器等对象，对于不同的用户创建的数据库对象不可能放在一起，  
53. 于是出现了架构，就是一个个容器。  
54.   
55. 架构是单个命名空间的数据库实体的集合。命名空间是一个集合，其中 每个元素的名称都是唯一的。  
56.   
57. 架构Schema : 独立于数据库用户的非重复的命名空间。  
58. 用户u1创建表t1，用户u2创建表t2  
59. 用户u2访问情况是这样的  
60. select * from u1.t1  --默认情况下架构名为用户名  
61. select * from t2 --默认架构不用写 当前用户的默认架构为u2  
62.   
63. 用户(User)和架构(Schema)的关系  
64.   
65. 一个架构有且只有一个所有者Owner。   
66. 一个用户可以拥有多个架构。   
67. 创建一个用户，系统将自动创建一个同名的架构。   
68. 创建一个架构必须指定所有者，否则将默认为当前登陆用户。   
69. 表属于不同的架构也不能重名。   
70. 删除一个用户时，架构也会被删除？  
71.   
72.   
73. 用户与架构（Schema）分开，让数据库内各对象不再绑定到某个用户账号上，可以解决“用户离开公司”  
74.   
75. 默认架构：SQLServer2005引入了"默认架构"的概念，用于解析未使用其完全限定名称引用的对象的名称。  
76. 每个用户都有一个默认架，用于指定服务器解析对象的名称时将要搜索的第一个架可以使用create user和alter user的  
77. default_schema选项设置和更改默认架构。如果未定义default_schema，则数据库将把dbo作为其默认架  
78.   
79. 使用架构的优点：  
80. 1. 多 个用户可以通过角色（role）或组（Windows groups）成员关系拥有同一个架构。在2005中，每个数据库中的固定数据库角色都有一个属于 自己的架构，如果我们创建一个表，给它指定的架构名称为db_ddladmin，那么任何一个属于db_ddladmin中的用户都是可以去查询、修改和 删除属于这个架构中的表，但是其它不属于这个组的用户是不行的，有一点必须注意，db_dbdatareader组的成员可以查看所有数据库中的 表，db_dbdatawriter组成员可以修改所有数据库中的表，db_owner组成员可以对数据库所有表进行所有操作，这几个组的成员可以通过角 色获取到在数据库中的特殊权限。  
81. 2. 删除数据库用户变得极为简单。 在 SQL Server 2000 中，用户（User）和架构是隐含关联的，即每个用户拥有与其同名的架构。因此要删除一个用户，必须先删除或修改这 个用户所拥有的所有数据库对象，就比如一个员工要离职要删除他的账户的时候，还得将他所创建的表和视图等都删除，影响过大。将架构和对象者分离后就不在存 在这样的问题，删除用户的时候，对数据库对象是没有任何影响的。  
82. 3. 共享缺省架构使得开发人员可以为特定的应用程序创建特定的架构来存放对象，这比仅使用管理员架构（DBO schema）要好。  
83. 4. 在架构和架构所包含的对象上设置权限（permissions）比以前的版本拥有更高的可管理性。  
84. 5. 区分不同业务处理需要的对象，例如，我们可以把公共的表设置成pub的架构，把销售相关的设置为sales，这样管理和访问起来更容易.  
85.   
86.   
87. --------------------code----------------------------  
88. select * from sys.objects  --name 对象名、object_id 对象id、schema_id 所属架构id  
89. select * from sys.schemas  --name 架构名,schema_id 架构id  
90.   
91. --查询当前数据库中所有表或视图名及所属架构名  
92. select obj.name 对象名,sch.name 所属架构名  
93. from sys.objects obj,sys.schemas sch  
94. where obj.type='u' and obj.schema_id=sch.schema_id  
95.   
96.   
97.   
98. --创建登录名  
99. create login marry with password='password1111',default_database=accp  
100. --创建账号并指定默认架构  
101. create user mar for login marry with default_schema=accp  
102. --授予账号mar创建表的权限  
103. grant create table to mar  
104. --切换以账号mar登录  
105. execute as user='mar'  
106. --查看当前用户  
107. select current_user  
108. --创建表  
109. create table t1(temp int)   --创建对象t1失败，因为默认的架构accp不存在  
110. --创建架构accp，并指定架构的所有者  
111. create schema accp authorization mar  --没有权限，创建失败  
112. --重新切换到用户dbo  
113. revert  
114. --以dbo身份创建架构accp  
115. create schema accp authorization  mar  --这次成功  
116. --切换用户到mar  
117. execute as user='mar'  
118. --创建表  
119. create table t1(temp int)  --成功  
120. --现在想删除用户mar，发现删除失败  
121. drop user mar  
122. --删除用户之前要删除其所有对象，或者将其的对象架构更改  
123. revert  
124. alter schema dbo transfer accp.t1  --1、更改表t1的架构名accp->dbo  
125. --查看对象的架构更改是否成功  
126. select * from accp.t1 --失败  
127. select * from dbo.t1  --成功  
128. --删除架构或更改用户的默认架构 拥有架构的账户不能删除  
129. drop schema accp   --删除用户指定的默认加构  
130. --alter user mar with default_schema=dbo --列改默认架构  
131. --现在可以删除账号mar了  
132. drop user mar   --成功  
133. --删除登录名 marry  
134. drop login marry  --成功  
135.   
136. sys.user_token 为组成用户标记的每个数据库主体返回一行  
137. name:主体数据库的名称  
138. type:主体数据库类型的说明  
139.     sql_user  
140.     windows_user  
141.     windows_group  
142.     application_role  
143.     database_role  
144. usage:参与的grant或deny权限 可以为grant|deny|authenticator  
145.   
146. select * from sys.user_token  
147.   
148.   
149.   
150. sys.login_token : 为登录名标记中包含的每个服务器主体返回一行  
151. name:服务器主体的名称  
152. type:服务器主体类型的说明  
153.     sql_login   
154.     windows_login   
155.     windows_group  
156.     server_role   
157. usage :同上  
158. select * from sys.login_token  
159.   
160.   
161.   
162. sys.server_principals : 每个服务器级别主体占一行  
163. name: 主体的名称  
164. type: 主体的类型  
165.     s=sql登录名  
166.     u=windows登录名  
167.     g=windows组  
168.     r=服务器角色  
169.       
170. type_desc : 主体类型的说明  
171.     sql_login  
172.     windows_login  
173.     windows_group  
174.     server_role  
175. is_disabled : 1=禁用登录名  
176. default_database_name:默认的数据库  
177.   
178. select * from sys.server_principals  
179.   
180.   
181. sys.database_principals : 为数据库中的每个主体返回一行  
182. name: 主体名称  
183. type: 主体类型  
184.     s=sql用户  
185.     u=windows用户  
186.     g=windows组  
187.     a=应用程序角色  
188.     r=数据库角色  
189. type_desc:主体类型的说明  
190.     sql_user  
191.     windows_user  
192.     windows_group  
193.     database_role  
194. default_schema_name: 当SQL名称未指定架构时使用的名称  
195.     对于非s、u或a类型的主体，为null  
196.   
197. select * from sys.database_principals