转自:http://blog.51cto.com/jimshu/1176573
一、概述
1、GRANT
将安全对象的权限授予主体。
GRANT SELECT ON Marketing.Salesperson( SalespersonID, EmailAlias)
2、DENY
拒绝授予主体权限。防止主体通过其组或角色成员身份继承权限。
DENY SELECT ON Schema::Customers FROM Sales
3、REVOKE
取消以前授予或拒绝了的权限。
REVOKE SELECT ON Object::Regions FROM UserA
二、示例
SQL Server 2012的实例中有一个名为SalesDB的数据库。SalesDB包含一个名为Customers的架构,此架构中有一个名为Regions的表。
数据库有一个名为Sales的角色,此角色中有一个名为UserA的用户。
用户UserA被授予对SalesDB.Customers.Regions的Select权限。
角色Sales被授予对架构Customers的Select权限。
1、不允许角色Sales(包括用户UserA)在架构Customers中Select任何表。
DENY SELECT ON Schema::Customers FROM Sales
2、不允许用户UserA在架构Customers中Select任何表。
DENY SELECT ON Schema::Customers FROM UserA
3、移除用户UserA对SalesDB.Customers.Regions的Select权限,同时让用户UserA通过角色Sales的权限仍然可以访问架构Customers中的所有表。
REVOKE SELECT ON Object::Regions FROM UserA
4、不允许角色Sales(包括用户UserA)对SalesDB.Customers.Regions的Select权限。
DENY SELECT ON Object::Regions FROM sales
5、不允许用户UserA对SalesDB.Customers.Regions的Select权限。
DENY SELECT ON Object::Regions FROM UserA
6、移除角色Sales在架构Customers中的Select权限,但用户UserA有SalesDB.Customers.Regions的Select权限。
REVOKE SELECT ON Schema::Customers FROM Sales
三、列级的安全
权限可以在列一级进行分配
可以一个语句中对多个列分配权限
一个列级的 GRANT 将覆盖一个表级的 DENY
GRANT SELECT ON Marketing.Salesperson ( SalespersonID, EmailAlias) TO James; GO DENY SELECT ON Marketing.Salesperson TO Holly; GO GRANT SELECT ON Marketing.Salesperson ( SalespersonID, FirstName, LastName) TO Holly; GO |
四、再次授权(re-grant)
使用 WITH GRANT OPTION 可以使受让者将其得到的授权再次 GRANT 给其他主体
CASCADE 选项同时 REVOKE(或DENY)从受让者发出的授权
GRANT UPDATE ON Marketing.Salesperson TO James WITH GRANT OPTION; GO REVOKE UPDATE ON Marketing.Salesperson FROM James CASCADE; GO |
五、登录名与角色
--创建一个简单的登录,登录名为:newlogin;登录密码:123456;默认数据库:master,默认数据库也可以不指定。 EXEC sp_addlogin 'newlogin','123456','master' --创建用户 --创建一个简单的用户,如果不指定用户名,则添加到当前数据库登录名中,如果不指定角色,则该用户默认属于public角色。下为添加newlogin登录名。 EXEC sp_adduser 'newlogin' --创建一个带用户名的用户,用户可以与登录名相同(同上一种类似),也可以不同,但要设定当前登录名,用户角色可选,默认为public。下为将用户newuser添加到newlogin登录名中。 EXEC sp_adduser 'newlogin','newuser' --创建角色 EXEC sp_addrole 'newrole' --下为将用户下为将用户newuser添加到newlogin登录名中。并指定newrole角色。 EXEC sp_adduser 'newlogin','newuser','newrole' --为角色newrole赋予jobs表的所有权限 GRANT ALL ON jobs TO newrole --为角色newrole赋予sales表的查、改权限 GRANT SELECT,UPDATE ON sales TO newrole --禁止角色newrole使用employees表的插入权限 DENY INSERT ON employees TO newrole 另一种创建用户和赋予角色的方式 --为登录newlogin在数据库中添加安全账户newuser EXEC sp_grantdbaccess 'newlogin','newuser' --添加newuser为角色newrole的成员 EXEC sp_addrolemember 'newrole','newuser' --数据库用户、角色、登录的删除操作 --删除当前数据库用户 EXEC sp_revokedbaccess 'newuser'; --删除数据库登录 EXEC sp_droplogin 'newlogin' --删除数据库角色 EXEC sp_droprole 'newrole' --从数据库角色(newrole)中删除用户(newuser) EXEC sp_droprolemember 'newrole', 'newuser' --用SQL代码新建登录、用户 --创建带密码的mylogin登录名,MUST_CHANGE 选项需要用户首次连接服务器时更改此密码。 CREATE LOGIN mylogin WITH PASSWORD = '123456' MUST_CHANGE; --创建映射到凭据的登录名。 --以下示例将创建mylogin登录名。此登录名将映射到mycredential凭据。 CREATE LOGIN mylogin WITH PASSWORD = '123456', CREDENTIAL = mycredential; --从Windows 域帐户创建登录名 --如果从Windows 域帐户映射登录名,则登录名必须用方括号([ ]) 括起来。 CREATE LOGIN [jackxiangzhao] FROM WINDOWS; --如果指定用户名,则不使用默认登录名作为该数据库用户 CREATE USER myuser FOR LOGIN mylogin --以下示例将创建用户myuser拥有的数据库角色myrole CREATE ROLE myrole AUTHORIZATION myuser; --以下示例将创建db_role固定数据库角色拥有的数据库角色myrole CREATE ROLE myrole AUTHORIZATION db_role
ALTER SERVER ROLE [sysadmin] ADD MEMBER [WIN-IUPGVIBG48Asqladmin] 把登录名添加到角色
---------------
以下部分转自:https://www.cnblogs.com/seusoftware/p/4848940.html
在SQL Server数据库中,登录账号分类如下:
(1) SQL账号,需要单独设置密码,比如:sa;
(2) Windows账号,登录windows的账号,比如: administrator,不需要另设密码;
(3) Windows Group 账号, 为本地用户组或域用户组,将组添加到登录,组成员不需要单独创建登录;
查看Windows账号,是否属于某一个/多个用户组:
exec xp_logininfo 'windows_acount','ALL' --域用户格式为:domain_nameaccount_name
以下脚本,均假设最终登录账号为:test_login,所有数据库对应的user为test_user
一. 有没有权限
1. 检查有没有登录权限
--是否存在有效的登录账号:是否被禁用,sql login还有:密码是否过期,是否被锁定 select is_disabled, loginproperty(name,'Isexpired') is_expired, loginproperty(name,'Islocked') is_locked, * from sys.server_principals where name = 'test_login'
2. 检查有没有访问某数据库的权限
USE DBA GO --检查是否有数据库的CONNECT权限即可 select b.* from sys.database_principals a inner join sys.database_permissions b on a.principal_id = b.grantee_principal_id where SUSER_SNAME(a.sid) = 'test_login' and b.permission_name = 'CONNECT' --老的系统表sysusers也可以检查 SELECT name, hasdbaccess,* FROM sysusers a WHERE SUSER_SNAME(a.sid) = 'test_login'
如果有很多个数据库,写个游标1个个去检查即可。
3. 检查有没有某个对象的权限
检查有没有某个对象的权限,一般是去尝试运行下脚本比较直观,如果去查各种权限表,角色错综复杂时,很难分辨;
SQL Server 2008之后引入了HAS_PERMS_BY_NAME这个函数,它可以检查当前账号的各种权限,检查其他用户需要用EXECUTE AS来切换:
USE DBA GO EXECUTE AS user = 'test_user' GO --对象权限 SELECT HAS_PERMS_BY_NAME('Sales.SalesPerson', 'OBJECT', 'INSERT'); SELECT HAS_PERMS_BY_NAME('sp_send_dbmail', 'OBJECT', 'EXEC'); --架构权限 SELECT HAS_PERMS_BY_NAME('test_schema', 'SCHEMA', 'SELECT'); REVERT; GO
对于是否有登录、访问数据库的权限,用这个函数也可以判断:
USE master GO EXECUTE AS login = 'test_login' GO --登录权限,本机前2个参数为空即可 SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'CONNECT SQL'); REVERT; GO USE DBA GO EXECUTE AS user = 'test_user' GO --数据库权限 SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CONNECT'); REVERT;
二. 有哪些权限
权限可以直接分配给账号,也可以分配给账号所属的role,所以要把账号自身权限、所属role权限合并才是最终的账号权限。
Windows账号权限还可以通过用户组分配,所以还要检查这个Windows账号有没有属于某个用户组,如果有还需要加上这个用户组的权限;
下面的脚本,仅检查单个用户/用户组权限。
1. 实例级的权限
use master GO declare @svr_principal_name varchar(1024) set @svr_principal_name = 'test_login' declare @svr_principal_id int select @svr_principal_id = principal_id from sys.server_principals p where p.name = @svr_principal_name if OBJECT_ID('tempdb..#tmp_svr_role','U') is not null drop table #tmp_svr_role; create table #tmp_svr_role ( member_principal_id int, member_principal_name varchar(512), role_principal_id int, role_principal_name varchar(512) ) --获取登录账号的所有server role, 从sql server 2012开始,server role可以自定义,成员仅可为fixed server role ;with tmp as ( select * from sys.server_role_members where member_principal_id = @svr_principal_id union all select rm.* from sys.server_role_members rm inner join tmp on rm.member_principal_id = tmp.role_principal_id ) insert into #tmp_svr_role select a.member_principal_id, b.name, a.role_principal_id, c.name from tmp a inner join sys.server_principals b on a.member_principal_id = b.principal_id inner join sys.server_principals c on a.role_principal_id = c.principal_id --登录账号自身权限, sys.server_permissions不包含fixed server role权限,同时手动排除掉public权限 select a.principal_id as member_principal_id, a.name as member_principal_name, null as role_principal_id, null as role_principal_name, b.permission_name, b.state_desc from sys.server_principals a inner join sys.server_permissions b on a.principal_id = b.grantee_principal_id where a.principal_id = @svr_principal_id and b.permission_name <> 'CONNECT SQL' union all --server role权限,包含fixed server role和自定义的server role select a.member_principal_id, a.member_principal_name, a.role_principal_id, a.role_principal_name, isnull(b.permission_name,'Fixed Server-Level Role: '+role_principal_name) as permission_name, isnull(b.state_desc,'GRANT') as state_desc from #tmp_svr_role a left join sys.database_permissions b on a.role_principal_id = b.grantee_principal_id union all --public server role权限,不可以取消public权限,它是每个登录账号的最小权限,仅可连接数据库实例 select @svr_principal_id as member_principal_id,@svr_principal_name as member_principal_name, principal_id as role_principal_id, name as role_principal_name, 'CONNECT SQL' as permission_name, 'GRANT' as state_desc from sys.server_principals where name = 'public' Instance-Level Permissions
注意:服务器角色的权限可以做什么具体的事情,exec sp_srvrolepermission 有大致的介绍,但是也并没有全部列出每一种数据库操作,因为有些操作是被更高级的操作包含的。
2. 数据库级的权限
仅列出数据库级别的权限,具体的对象名称并没有列出。
use DBA GO declare @svr_principal_name varchar(1024) set @svr_principal_name = 'test_login' declare @db_principal_id int, @db_principal_name varchar(512) select @db_principal_id = principal_id, @db_principal_name = name from sys.database_principals p where SUSER_SNAME(sid) = @svr_principal_name if OBJECT_ID('tempdb..#tmp_db_role','U') is not null drop table #tmp_db_role; create table #tmp_db_role ( member_principal_id int, member_principal_name varchar(512), role_principal_id int, role_principal_name varchar(512) ) --获取登录账号在当前数据库的所有database role ;with tmp as ( select * from sys.database_role_members where member_principal_id = @db_principal_id union all select rm.* from sys.database_role_members rm inner join tmp on rm.member_principal_id = tmp.role_principal_id ) insert into #tmp_db_role select a.member_principal_id, b.name, a.role_principal_id, c.name from tmp a inner join sys.database_principals b on a.member_principal_id = b.principal_id inner join sys.database_principals c on a.role_principal_id = c.principal_id --登录账号在当前数据库的自身权限, sys.database_permissions不包含fixed database role权限,同时手动排除掉public权限 select a.principal_id as member_principal_id, a.name as member_principal_name, null as role_principal_id, null as role_principal_name, b.permission_name, b.state_desc from sys.database_principals a inner join sys.database_permissions b on a.principal_id = b.grantee_principal_id where a.principal_id = @db_principal_id and b.permission_name <> 'CONNECT' union all --database role权限,包含fixed database role和自定义的database role select a.member_principal_id, a.member_principal_name, a.role_principal_id, a.role_principal_name, isnull(b.permission_name,'Fixed Database-Level Role: '+role_principal_name) as permission_name, isnull(b.state_desc,'GRANT') as state_desc from #tmp_db_role a left join sys.database_permissions b on a.role_principal_id = b.grantee_principal_id union all --public database role权限,不可以取消public权限,它是每个登录账号映射到当前数据库的最小权限,仅可连接当前数据库 select @db_principal_id as member_principal_id, @db_principal_name as member_principal_name, principal_id as role_principal_id, name as role_principal_name, 'CONNECT' as permission_name, 'GRANT' as state_desc from sys.database_principals where name = 'public' Database-Level Permissions
注意:sysadmin的账号在数据库里可能并没有做映射,但权限是有的,隐式映射的用户是dbo
3. 对象级的权限
sys.database_permissions有很多对象类型,major_id, minor_id取决于class_desc,不同的对象关联不同的系统表/视图,脚本里仅列出了最常见的OBJECT_OR_COLUMN, SCHEMA对象权限。
--建立测试用的架构,对象,列 use DBA GO if object_id('test_grant','U') is not null drop table test_grant GO create table test_grant(c1 int, c2 int, c3 int) grant select (c1, c2) on test_grant to test_user; if object_id('test_schema.test_t1','U') is not null drop table test_schema.test_t1 GO if exists(select 1 from sys.schemas where name = 'test_schema') drop schema test_schema GO create schema test_schema create table test_schema.test_t1(c1 int, c2 int) grant select on schema::test_schema to test_user; GO --开始获取对象权限 use DBA GO declare @svr_principal_name varchar(1024) set @svr_principal_name = 'test_login' declare @db_principal_id int, @db_principal_name varchar(512) select @db_principal_id = principal_id, @db_principal_name = name from sys.database_principals p where SUSER_SNAME(sid) = @svr_principal_name if OBJECT_ID('tempdb..#tmp_db_role','U') is not null drop table #tmp_db_role; create table #tmp_db_role ( member_principal_id int, member_principal_name varchar(512), role_principal_id int, role_principal_name varchar(512) ) --获取登录账号在当前数据库的所有database role ;with tmp as ( select * from sys.database_role_members where member_principal_id = @db_principal_id union all select rm.* from sys.database_role_members rm inner join tmp on rm.member_principal_id = tmp.role_principal_id ) insert into #tmp_db_role select a.member_principal_id, b.name, a.role_principal_id, c.name from tmp a inner join sys.database_principals b on a.member_principal_id = b.principal_id inner join sys.database_principals c on a.role_principal_id = c.principal_id --登录账号在当前数据库的自身对象权限(OBJECT_OR_COLUMN) select a.principal_id as member_principal_id, a.name as member_principal_name, null as role_principal_id, null as role_principal_name, o.name as major_name, c.name as minor_name, b.permission_name, b.state_desc from sys.database_principals a inner join sys.database_permissions b on a.principal_id = b.grantee_principal_id left join sys.objects o on b.major_id = o.object_id left join sys.columns c on (b.major_id = c.object_id and b.minor_id = c.column_id) where a.principal_id = @db_principal_id and b.class_desc = 'OBJECT_OR_COLUMN' union all --登录账号在当前数据库的自身对象权限(SCHEMA) select a.principal_id as member_principal_id, a.name as member_principal_name, null as role_principal_id, null as role_principal_name, s.name as major_name, null as minor_name, b.permission_name, b.state_desc from sys.database_principals a inner join sys.database_permissions b on a.principal_id = b.grantee_principal_id left join sys.schemas s on b.major_id = s.schema_id where a.principal_id = @db_principal_id and b.class_desc = 'SCHEMA' union all --database role的对象权限(OBJECT_OR_COLUMN) select a.member_principal_id, a.member_principal_name, a.role_principal_id, a.role_principal_name, o.name as major_name, c.name as minor_name, b.permission_name, b.state_desc from #tmp_db_role a inner join sys.database_permissions b --inner join, 仅自定义的database role on a.role_principal_id = b.grantee_principal_id left join sys.objects o on b.major_id = o.object_id left join sys.columns c on (b.major_id = c.object_id and b.minor_id = c.column_id) where b.class_desc = 'OBJECT_OR_COLUMN' union all --database role的对象权限(SCHEMA) select a.member_principal_id, a.member_principal_name, a.role_principal_id, a.role_principal_name, s.name as major_name, null as minor_name, b.permission_name, b.state_desc from #tmp_db_role a inner join sys.database_permissions b --inner join, 仅自定义的database role on a.role_principal_id = b.grantee_principal_id left join sys.schemas s on b.major_id = s.schema_id where b.class_desc = 'SCHEMA' /* union all --public role有一些系统视图的select权限,可以忽略 select a.principal_id as member_principal_id, a.name as member_principal_name, null as role_principal_id, null as role_principal_name, o.name as major_name, c.name as minor_name, b.permission_name, b.state_desc from sys.database_principals a inner join sys.database_permissions b on a.principal_id = b.grantee_principal_id left join sys.all_objects o on b.major_id = o.object_id left join sys.all_columns c on (b.major_id = c.object_id and b.minor_id = c.column_id) where a.name = 'public' */
注意:如果对象的权限是通过role衍生的,而不是直接分配给user或者role,那么并不会被列出来。试想sysadmin 的角色,难道要列出所有数据库的所有对象吗?
三. 查看自己的权限
1. 有没有登录权限
登录失败并不一定是没权限,还是找别人来检查自己账号的登录权限吧;
2. 有没有数据库访问权限
--列出所有可访问的数据库 SELECT * FROM sys.databases WHERE HAS_DBACCESS(name) = 1
3. 有没有对象访问权限
用上面提到HAS_PERMS_BY_NAME函数,它可以检查当前账号的各种权限;
SELECT HAS_PERMS_BY_NAME('test_sp', 'Object' , 'Execute') SELECT HAS_PERMS_BY_NAME('test', 'Database' , 'Execute')
4. 有哪些权限
--实例级权限 SELECT * FROM fn_my_permissions(NULL, 'SERVER'); --数据库级权限 SELECT * FROM fn_my_permissions ('DBA', 'DATABASE'); --对象权限,只能一个个对象检查,不能一次返回所有对象权限,和HAS_PERMS_BY_NAME类似 SELECT * FROM fn_my_permissions ('test_grant', 'OBJECT');
用于检查自己权限的方法,同样也可以检查其他账号,用EXECUTE AS切换账号即可。