• (4.5)授权/权限操作/查看权限


    转自: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切换账号即可。

  • 相关阅读:
    Asp.Net生成二维码(中间加logo)
    简单的图片上传
    C#读取路径
    Asp.Net读取配置文件
    C#监听服务
    jQuery 遍历--siblings() 方法、each() 方法
    Window 6大内置对象
    MySQL中几个重要的参数
    DNS构建实战(下篇)
    DNS构建实战(上篇)
  • 原文地址:https://www.cnblogs.com/gered/p/9378937.html
Copyright © 2020-2023  润新知