时间:2021-07-01 10:21:17 帮助过:16人阅读
授予Shema dbo下对象的定义权限给某个用户(也就是说该用户可以修改架构dbo下所有表/视图/存储过程/函数的结构)
- <span style="color: #0000ff;">use</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Your DB NAME</span><span style="color: #ff0000;">]</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">VIEW</span> DEFINITION <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">to</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">THE USER NAME</span><span style="color: #ff0000;">]</span>
回收某个用户对Shema dbo下对象的定义权限(也就是说该用户不可以修改架构dbo下所有表/视图/存储过程/函数的结构)
- <span style="color: #0000ff;">use</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Your DB NAME</span><span style="color: #ff0000;">]</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">VIEW</span> DEFINITION <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">to</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">THE USER NAME</span><span style="color: #ff0000;">]</span>
允许某个用户执行Shema dbo下定义的存储过程
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">EXEC</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span>
不允许某个用户执行Shema dbo下定义的存储过程
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">EXEC</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span>
允许某个用户可以对Schema dbo下对象进行增删改查操作
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许查询数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许更新数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许插入数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许删除数据权限</span>
不允许某个用户对Schema dbo下对象进行增删改查操作
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许查询数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许更新数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许插入数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">SCHEMA</span> :: dbo <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许删除数据权限</span>
限制用户对数据库对象(比如表/视图/存储过程等)的操作行为
允许用户修改数据库表T_Testing的结构
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">VIEW</span> DEFINITION <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">to</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">THE USER NAME</span><span style="color: #ff0000;">]</span>
不允许用户修改数据库表T_Testing的结构
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">VIEW</span> DEFINITION <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">to</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Customer</span><span style="color: #ff0000;">]</span>
允许用户对数据库表T_Testing进行增删改查操作
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许查询数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许更新数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许插入数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许删除数据权限</span>
不允许用户对数据库表T_Testing进行增删改查操作
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许查询数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许更新数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许插入数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许删除数据权限</span>
上面这些对数据库表的语句同样适用于其它数据库对象,例如视图/存储过程等,如下所示:
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">VIEW</span> DEFINITION <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">V_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">to</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;">允许用户修改视图V_Testing的定义</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">VIEW</span> DEFINITION <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">V_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">to</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span><span style="color: #008080;">--</span><span style="color: #008080;">不允许用户修改视图V_Testing的定义</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">VIEW</span> DEFINITION <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">P_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">to</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;">允许用户修改存储过程P_Testing的定义</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">VIEW</span> DEFINITION <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">P_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">to</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">your_DB_account</span><span style="color: #ff0000;">]</span><span style="color: #008080;">--</span><span style="color: #008080;">不允许用户修改存储过程P_Testing的定义</span>
但是注意SELECT/UPDATE/DELETE/INSERT这几个增删改查的权限不适用于存储过程
此外对数据库对象(比如表/试图/存储过程等)的上述操作行为,还可以直接设置在数据库角色(注意是数据库角色,不是数据库Instance角色)上,例如:
允许数据库MyDataBase的角色MyRole对表T_Testing拥有增删改查权限
- <span style="color: #0000ff;">USE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyDataBase</span><span style="color: #ff0000;">]</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许查询数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许更新数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许插入数据权限</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许删除数据权限</span>
不允许数据库MyDataBase的角色MyRole对表T_Testing进行增删改查操作
- <span style="color: #0000ff;">USE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyDataBase</span><span style="color: #ff0000;">]</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许查询数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许更新数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许插入数据权限</span>
- <span style="color: #0000ff;">DENY</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 不允许删除数据权限</span>
此外对于SELECT/UPDATE/DELETE/INSERT这几个增删改查的权限还可以直接设置到表/试图的列上,例如下面语句我们设置数据库MyDataBase的角色MyRole拥有表T_Tesing和视图V_Testing中列Name的增删改查权限
- <span style="color: #0000ff;">USE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyDataBase</span><span style="color: #ff0000;">]</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span>) <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许查询表T_Testing的Name列数据</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span>) <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许更新表T_Testing的Name列数据</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span>) <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许插入表T_Testing的Name列数据</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">T_Testing</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span>) <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许删除表T_Testing的Name列数据</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">V_Testing</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span>) <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许查询视图V_Testing的Name列数据</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">UPDATE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">V_Testing</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span>) <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许更新视图V_Testing的Name列数据</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">V_Testing</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span>) <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许插入视图V_Testing的Name列数据</span>
- <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">DELETE</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">V_Testing</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">Name</span><span style="color: #ff0000;">]</span>) <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">MyRole</span><span style="color: #ff0000;">]</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 允许删除视图V_Testing的Name列数据</span>
使用数据库权限控制用户的访问行为
如果希望某个用户只拥有某个数据库的只读权限,最简单的办法就是只将该数据库的角色db_datareader赋予用户即可:
- <span style="color: #0000ff;">exec</span> sp_addrolemember<span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> db_datareader</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">用户名</span><span style="color: #ff0000;">‘</span>
这里最后列出所有数据库Instance角色和(用户映射)数据库角色的含义:
数据库Instance角色:
(用户映射)数据库角色:
设置Sql server用户对表、视图、存储过程、架构的增删改查权限
标签:sql 读取数据 add read 角色 ase name set 操作