数据库

当前位置:时时彩平台 > 数据库 > SQL Server 登录名、用户、角色与权限

SQL Server 登录名、用户、角色与权限

来源:http://www.mrmtshipyard.com 作者:时时彩平台 时间:2019-10-15 07:21

1.在SQL Server中,用户和角色是分为服务器级别和数据库级别的

sql server2005安全管理之用户、角色、架构 与 权限
2008-12-04 16:47
--打开数据库
Use databaseName

2.服务器级别

登录名:指有权限登录到某服务器的用户,例如超级管理员的登录名是sa;

              登录名具体位置在  数据库——>安全性——>登录名

              图片 1

 

服务器角色:为帮助您管理服务器上的权限,SQL Server 提供了若干角色,这些角色是用于对其他主体进行分组的安全主体。 服务器级角色
的权限作用域为服务器范围。SQL Server 提供了九种固定服务器角色, 无法更改授予固定服务器角色的权限,这9组角色分别如下:
sysadmin         —— 在SQL Server中进行任何活动,该角色的权限跨越所有其它固定服务器角色
serveradmin   —— 更改服务器范围内的配置选项并关闭服务器
setupadmin     —— 添加和删除链接服务器,并执行某些系统存储过程(如 sp_serveroption)
securityadmin —— 授予数据库引擎的访问权限和配置用户权限的能力使得安全管理员可以分配大多数服务器权限,securityadmin 角色应视为与sysadmin 角色等效
processadmin   —— 管理在 SQL Server 实例中运行的进程
dbcreator          —— 创建和改变数据库,但是没有读取权限
diskadmin          —— 管理磁盘文件
bulkadmin          —— 执行 BULK INSERT 语句
public                 —— 每个 SQL Server 登录名均属于 public 服务器角色。 如果未向某个服务器主体授予或拒绝对某个安全对象的特定权
限,该用户将继承授予该对象的 public 角色的权限。当您希望该对象对所有用户可用时,只需对任何对象分配 public 权限即可。
您无法更改 public 中的成员关系。public 的实现方式与其他角色不同,但是,可以从 public 授予、拒绝或撤销权限。

           服务器角色具体位置在  数据库——>安全性——>服务器角色

           图片 2

 

注意:
>>登录名一定属于某些角色,默认为public
>>服务器角色不容许更改
>>登录后也不一定有权限操作数据库

--创建角色
create role ProgramerRole

3.数据库级别

用户:指有权限能操作数据库的用户;

          用户具体位置在  数据库——>某个具体库——>安全性——>用户

          图片 3

 

        

数据库角色:SQL Server 中有两种类型的数据库级角色:数据库中预定义的“固定数据库角色”和您可以创建的“灵活数据库角色”
固定的有某些权限的数据库角色,所有数据库中都有这些角色,默认有10个,分别如下;

public
--public 角色是一个特殊的数据库角色,每个数据库用户都属于它。public 角色:
 >>捕获数据库中用户的所有默认权限。
 >>无法将用户、组或角色指派给它,因为默认情况下它们即属于该角色。
 >>包含在每个数据库中,包括 master、msdb、tempdb、model 和所有用户数据库。
 >>无法除去。

db_owner
--进行所有数据库角色的活动,以及数据库中的其它维护和配置活动。
--该角色的权限跨越所有其它固定数据库角色。

db_accessadmin
--在数据库中添加或删除 Windows NT 4.0 或 Windows 2000 组和用户以及 SQL Server 用户。

db_datareader
--查看来自数据库中所有用户表的全部数据。

db_datawriter
--添加、更改或删除来自数据库中所有用户表的数据

db_ddladmin
--添加、修改或除去数据库中的对象(运行所有 DDL)

db_securityadmin
--管理 SQL Server 2000 数据库角色的角色和成员,并管理数据库中的语句和对象权限

db_backupoperator
--有备份数据库的权限

db_denydatareader
--拒绝选择数据库数据的权限

db_denydatawriter
--拒绝更改数据库数据的权限

        数据库角色具体位置在  数据库——>某个具体库——>安全性——>角色——>数据库角色

       图片 4

 

注意:请不要将灵活数据库角色添加为固定角色的成员,这会导致意外的权限升级。

 

架构:架构(Schema)是一组数据库对象的集合,它被单个负责人(可以是用户或角色)所拥有并构成唯一命名空间。可以将架 

构看成是对象的容器。

 >>数据库用户对应于服务器登录名以便登录者可以操作数据库
 >>数据库角色可以添加,可以定制不同权限  
 >>数据库架构,类似于数据库对象的命名空间,用户通过架构访问数据库对象

      架构数据库角色具体位置在  数据库——>某个具体库——>安全性——>架构

      图片 5

 

--用于创建表 存储过程 视图
grant create table,create procedure,create view to ProgramerRole

4.数据库级别设置

服务器级 -> 数据库级 -> 架构级 - > 数据对象级,比如说:Server.DataBase1.dbo.Table1;这里的意思就是Table1这个表属于dbo这
个架构,dbo这个架构属于DataBase1这个数据库,DataBase1这个数据库属于Server这个服务器。里面的架构其实就是一个容器,好像
就是面向对象里面的命名空间,一个用户可以拥有多个架构,但是不能对没有拥有的架构进行操作。一个数据库角色,是对不同架构里
面数据对象的权限组织,也有可能涉及到多个架构,当某一个用户被转换成一种数据库角色的时候,假如这个用户本身不拥有某一个架
构而该数据库角色拥有,那它当它对那个架构进行操作的时候就会出错。

--execute用以执行存储过程,alter用以创建、修改存储过程和视图,
--并可以新建立表,但不能修改表,但也可以删除表和对表改名了
grant select,insert,execute,alter on schema::dbo to ProgramerRole

5.相互之间的关系

(1)登录名与数据库用户在服务器级别是一对多的,在数据库级别是一对一的
    登录名可以理解为进入整个大楼的钥匙,用户名可以理解为一个房间的钥匙,这里所说的大楼就是sql server服务器,而房间就是
这个sql server服务器中的具体的库,要注意登录名是存在于master数据库的syslogins表中,用户名是在各个具体的库下建立的(也就
是要先进入各个库), 最关键的是:一个登录名可以和多个不同库下的用户做映射,但是在同一个库下只能和一个用户做映射,并且一
个用户名可以和多个登录名有映射关系, 真正有权限的是用户名,只不过是用登录名去登进数据库,然后去找映射的用户名,这样就
有了相应的权限,刚开始建立的登录名只要把该登录名的服务器角色设置为Database Creator,就可以创建新的数据库了,并且新建立
的这个数据库中会自动生成两个用户名:dbo和guest。并且刚刚建立的登录名就和dbo映射了,还有就是rbo也和sa映射了,因为sa作为
管理员的登录名和每个库中的rbo用户映射。

(2)一个数据库角色有可能涉及多个架构,数据库角色对应的是权限
(3)一个用户对应一个数据库角色
(4)一个数据库用户可以对应多个架构(架构是表容器),架构里面包含的是数据库表。

--用于允许用户查看 显示估计的执行计划(081205)
grant showplan to ProgramerRole

6.用T_SQL创建用户和登录用户例子

-- 1.创建登录用户(在master库下面执行)
USE master
GO
CREATE LOGIN readonlylogin WITH password='abc123...';

-- 2.创建数据库只读用户(在某个用户数据库下面执行)
USE TestDB
GO
CREATE USER readonlyuser FROM LOGIN readonlylogin;

-- 3.将用户加入数据库角色,例如 所有者角色db_owner、只读角色db_datareader等(在某个用户数据库下面执行)
USE TestDB
GO
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

--EXEC sp_addrolemember 'db_owner', 'readonlyuser';----可以将用户关联到多个数据库角色中

--EXEC sp_droprolemember 'db_owner', 'readonlyuser'----从特定的数据角色中删除用户

-- 4.用我们刚刚创建的readonlylogin用户进行登录,登录时要注意将“选项(英文是Options)-->连接属性(Connection Properties)-->连接数据库(Connect to database)”改为我们的目标数据库例如TestDB下面

  注意:

  1.登录名和用户名可以起一样的名字;

  2.如果是在普通的SQL Server中,就按上面操作即可,如果微软云Azure中的SQL Server,可能有些语句不能用,例如use master,default_schema=dbo等

 

 

参考地址:

--创建登录账号
--create login username with password='password'
--创建数据库用户
create user username for login username

--将用户TestUser添加到TestRole角色中
exec sp_addrolemember 'ProgramerRole','username '

--执行Sql Server Profiler是服务器级权限,所以在master库中授权
USE master;grant alter trace to auto;

注:据库引擎优化顾问必须由具有系统管理员权限的用户进行初始化。在具有系统管理员权限的用户对数据库引擎优化顾问进行初始化之后,任何是 db_owner 固定数据库角色成员的用户,都可以使用数据库引擎优化顾问来优化他们拥有的数据库上的表。
详优化物理数据库设计

参考资料:
用户架构分离:
数据库架构是一个独立于数据库用户的非重复命名空间。您可以将架构视为对象的容器
主体:
是可以请求 SQL Server 资源的实体

Windows 级别的主体

Windows 域登录名
Windows 本地登录名
SQL Server 级的主体

SQL Server 登录名
数据库级的主体

数据库用户
数据库角色
应用程序角色
安全对象:
安全对象是 SQL Server 数据库引擎 授权系统控制对其进行访问的资源
安全对象范围有服务器、数据库和架构
安全对象范围:服务器包含以下安全对象:
端点
登录帐户
数据库

安全对象范围:数据库包含以下安全对象:
用户
角色
应用程序角色
程序集
消息类型
路由
服务
远程服务绑定
全文目录
证书
非对称密钥
对称密钥
约定
架构

安全对象范围:架构包含以下安全对象:
类型
XML 架构集合
对象

对象 下面是对象类的成员:
聚合
约束
函数
过程
队列
统计信息
同义词

视图

架构:
架构是指包含表、视图、过程等的容器。它位于数据库内部,而数据库位于服务器内部
特定架构中的每个安全对象都必须有唯一的名称。架构中安全对象的完全指定名称包括此安全对象所在的架构的名称。因此,架构也是命名空间

权限:
每个 SQL Server 2005 安全对象都有可以授予主体的关联权限

数据库级别的角色:
固定数据库角色是在数据库级别定义的,并且存在于每个数据库中。db_owner 和 db_securityadmin 数据库角色的成员可以管理固定数据库角色成员身份;但是,只有 db_owner 数据库的成员可以向 db_owner 固定数据库角色中添加成员。
每个数据库用户都属于 public 数据库角色。当尚未对某个用户授予或拒绝对安全对象的特定权限时,则该用户将继承授予该安全对象的 public 角色的权限
服务器级别角色:
固定服务器角色在其作用域内属于服务器范围。固定服务器角色的每个成员都可以向其所属角色添加其他登录名。

GRANT 架构权限:

如何配置用户以创建和管理 SQL Server 代理作业

若要配置用户以创建或执行 Microsoft SQL Server 代理作业,必须先将某个现有 SQL Server 登录名或 msdb 角色添加到 msdb 数据库中的下列 SQL Server 代理固定数据库角色之一:SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole。

默认情况下,这些数据库角色的成员可以创建各自的作业步骤,这些作业步骤不执行其他作业步骤。如果这些非管理用户要运行那些执行其他作业步骤类型(例如,SSIS 包)的作业,它们需要对代理帐户具有访问权限。sysadmin 固定服务器角色的所有成员都有创建、修改和删除代理帐户的权限

 

 

为具体的用户设置具体的访问权限 收藏
use 你的库名
go

--新增用户
exec sp_addlogin 'test' --添加登录
exec sp_grantdbaccess N'test' --使其成为当前数据库的合法用户
exec sp_addrolemember N'db_owner', N'test' --授予对自己数据库的所有权限

--这样创建的用户就只能访问自己的数据库,及数据库中包含了guest用户的公共表
go

--删除测试用户
exec sp_revokedbaccess N'test' --移除对数据库的访问权限
exec sp_droplogin N'test' --删除登录

如果在企业管理器中创建的话,就用:

企业管理器--安全性--右键登录--新建登录

常规项
--名称中输入用户名
--身份验证方式根据你的需要选择(如果是使用windows身份验证,则要先在操作系统的用户中新建用户)
--默认设置中,选择你新建的用户要访问的数据库名

服务器角色项
  这个里面不要选择任何东西

数据库访问项
  勾选你创建的用户需要访问的数据库名
  数据库角色中允许,勾选"public","db_ownew"

  确定,这样建好的用户与上面语句建立的用户一样

最后一步,为具体的用户设置具体的访问权限,这个可以参考下面的最简示例:

--添加只允许访问指定表的用户:
exec sp_addlogin '用户名','密码','默认数据库名'

--添加到数据库
exec sp_grantdbaccess '用户名'

--分配整表权限
GRANT SELECT , INSERT , UPDATE , DELETE ON table1 TO [用户名]

--分配权限到具体的列
GRANT SELECT , UPDATE ON table1(id,AA) TO [用户名]

本文由时时彩平台发布于数据库,转载请注明出处:SQL Server 登录名、用户、角色与权限

关键词:

上一篇:没有了

下一篇:没有了