在天堂 - 技术老男 ›

SqlServer ›
SQLServer 权限管理

点击：1517

转移对象所有权：
ALTER AUTHORIZATION on object to DB用户/windows用户
https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-authorization-transact-sql?view=sql-server-ver15

login：
GRANT SELECT ON OBJECT::HumanResources.Employee TO login;

权限介绍：

https://www.cnblogs.com/zhy-1992/p/6743511.html

https://www.cnblogs.com/w-wanglei/p/5104266.html

权限查询： https://blog.51cto.com/6252164/2323807 https://www.2cto.com/database/201509/444561.html https://www.cnblogs.com/kerrycode/p/11588227.html

--修改注册表，修改身份验证模式为混合验证方式
USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

-- 切换当前登录用户
EXECUTE AS LOGIN = 'loginInfo'
SELECT ORIGINAL_LOGIN() [原始登录账户], SUSER_SNAME() [当前登录账户]

REVERT
SELECT ORIGINAL_LOGIN() [原始登录账户], SUSER_SNAME() [当前登录账户]

--关闭xp_cmdshell/Ad Hoc Distributed Queries分布式查询功能
EXEC [sys].[sp_configure] @configname = 'xp_cmdshell', -- varchar(35)
@configvalue = 0 -- int
RECONFIGURE WITH override

-- 查看openrowset,opendatasource 远程分布查询权限：
select value from sys.configurations where name='Ad Hoc Distributed Queries';

select * from OPENROWSET('SQLOLEDB', 'server=localhost;uid=sa;pwd=sasa', 'select * from msdb.dbo.sysjobs' )
select * from OPENROWSET('SQLOLEDB', 'uid=sa;pwd=sasa;Network=DBMSSOCN;Address=localhost,1433;', 'select * from msdb.dbo.sysjobs')

-- 使用信任连接：
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server= 10.156.112.32,1433;Trusted_Connection=yes;', 'SELECT  count(1) from msdb.dbo.sysjobs') AS a

-- 创建SQL Server登陆帐户
create login [UserName] with password='[password]', default_database=[DatabaseName] ; 
CREATE LOGIN [DC\user1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

-- 创建数据库用户
-- 如果数据库迁移了，用户需要重新创建并授权或用 sp_change_users_login进行重新关联！
create user [UserName] for login [UserName] with default_schema=dbo

-- 如果 登陆名是数据库所有者owner(对应数据库dbo用户,其默认有db_owner角色)，如果需要删除登陆名或收回权限，使用sp_changedbowner收回所有者权限：
-- 数据库改变所有者
use [test]
sp_changedbowner 'sa'

-- 授予登陆帐户sysadmin服务器主体中的服务器角色 server role
EXEC master..sp_addsrvrolemember @loginame = N'DC\user1', @rolename = N'sysadmin'

-- 授予用户对数据库dbname的数据库主体的db_owner数据库角色 database role
use [dbname]
exec sp_addrolemember 'db_owner', '[UserName]' 

-- 授予用户只具备查询表的权限
use [dbname]
SELECT 'GRANT  SELECT ON '+ '['+ name+']' +'to UserName' FROM  sys.tables WHERE NAME LIKE 'WB%';

-- 禁用数据库用户：--?-- 
alter user [UserName]  with default_schema= dbo

-- 禁用SQL Server登陆帐户：
ALTER LOGIN [sa] DISABLE

--删除 SQL Server登陆帐户：
drop user [UserName]

-- 查询sql server的所有权限,很重要，要授予权限，总得先知道有哪些权限可以授予
select * from sys.fn_builtin_permissions(DEFAULT)

-- 查询schema的所有权限 (架构)
select * from sys.fn_builtin_permissions('schema')

-- 查询server的所有权限
select * from sys.fn_builtin_permissions('server')

-- 查询database的所有权限
select * from sys.fn_builtin_permissions('database')

-- 查询数据库服务状态：
EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser'
EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'NetLogon'

-- SQL2008 SP2 R1:
SELECT servicename
    ,process_id
    ,startup_type_desc
    ,status_desc
    ,last_startup_time
    ,service_account
    ,is_clustered
    ,cluster_nodename
    ,[filename]
FROM sys.dm_server_services WITH (NOLOCK)
OPTION (RECOMPILE);

--判断当前用户是否是系统管理员
select IS_SRVROLEMEMBER('sysadmin') sysadmin
--判断是否是库权限
Select IS_MEMBER('db_owner') db_owner
--判断是否有库读取权限
select HAS_DBACCESS(db_name()) db_access


/*      !!!!!!!
sys.sql_login -> sys.sysusers -> sys.server_principals| sys.database_principals -> default schema -> sys.schemas -> schema_id + principal_id
*/

--------------------------------------------服务器范围内的权限开始--------------------------------------------
-- 服务器范围主体principals是被授权对象      is object -?-
--服务器范围内的权限是授予给windows级或者sql server级的主体的
-- x1为登录名
--授予服务器范围内的权限时，必须切换到master数据库
use master

--授予创建任何数据库，查看任何数据库的权限给x1
grant create any database, view any database to x1

--明确登陆名x1拒绝关闭实例的权限
deny shutdown to x1

--撤销授予的权限
revoke create any database, view any database from x1

-- 查看服务器主体server principal， 三种服务器级别主体
-- Windows登录账号WINDOWS_LOGIN/WINDOWS_GROUP, SqlServer登录账号SQL_LOGIN, 服务器角色SERVER_ROLE
-- 其中服务器角色是固定不变的，服务器角色授权数据保存在系统视图sys.syslogins中
CERTIFICATE_MAPPED_LOGIN
SERVER_ROLE
SQL_LOGIN
WINDOWS_GROUP
WINDOWS_LOGIN

select principal_id, name, type, type_desc from sys.server_principals

--查看windows级别主体或者sql server级别主体被授予的权限
select ps.class_desc, ps.permission_name, ps.state_desc, pr.name from sys.server_permissions as ps
inner join sys.server_principals as pr 
on ps.grantee_principal_id = pr.principal_id
where pr.name='x1'

-- 授予登陆帐户sysadmin服务器主体中的服务器角色 server role
EXEC master..sp_addsrvrolemember @loginame = N'DC\user1', @rolename = N'sysadmin'
--------------------------------------------服务器范围内的权限结束--------------------------------------------

----

--------------------------------------------数据库范围内的权限开始--------------------------------------------
-- 数据库级的主体principals是被授权对象，
-- 数据库范围内的权限授予数据库级的主体
-- d1为数据库用户名

-- 查询所有数据库级别数据库主体 database principal，三种数据库级别主体
-- Windows用户WINDOWS_USER, Sql Server用户SQL_USER, 数据库角色DATABASE_ROLE
-- == sys.sysusers
CERTIFICATE_MAPPED_USER
DATABASE_ROLE
SQL_USER
WINDOWS_USER

select principal_id, name, type, type_desc from sys.database_principals;



--切换数据库，授予数据库范围内的权限时，要先连接数据库
use mybatiesTest1

--授予数据库级主体d1连接mybatiesTest1数据库的权限
grant connect to d1

--授予数据库级主体d1完全控制mybatiesTest1数据库的权限
grant control to d1

--授予数据库级主体d2对数据库mybatiesTest1中的表或者视图 select 的权限
--授予select权限之后，数据库mybatiesTest1中的表对d2都是可见的
grant select to d2

--取消授予数据库级主体d1连接mybatiesTest1数据库的权限
revoke connect to d1

--取消授予数据库级主体d1完全控制mybatiesTest1数据库的权限
revoke control to d1

-- 查看数据库主体所授与的角色

USE db;
select dbp1.principal_id as Id, dbp1.name as Name, dbp1.type as Type 
from sys.database_role_members rm 
inner join sys.database_principals dbp1 on rm.role_principal_id = dbp1.principal_id 
inner join sys.database_principals dbp2 on rm.member_principal_id = dbp2.principal_id 
where dbp2.name = 'test'

-- 授予用户对数据库dbname的数据库主体的db_owner数据库角色 database role
use [dbname]
exec sp_addrolemember 'db_owner', '[UserName]' 
--------------------------------------------数据库范围内的权限结束--------------------------------------------

----
--------------------------------------------架构相关管理开始---------------------------------------------
架构是对象的owner，架构默认和用户对应，也可以单独建立架构仅仅是为了方便对象的管理。
用户可以有登陆名对应，也可以没有。
CREATE USER [test] WITHOUT LOGIN
CREATE USER [test] for LOGIN
-- CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS

-- 用户和机构是分离的，架构是对象的容器，架构和用户有交集也可能互相不同

-- 查询所有的架构信息
select * from sys.schemas

-- 修改用户默认架构
ALTER USER user1 WITH DEFAULT_SCHEMA = dbo

--创建名为 sch1 的schema
--create schema sch1

--创建架构sch2，将sch2的所有者设置成数据库级主体d1
--create schema sch2 authorization d1

--使用名为sch1的schema创建一张表
--create table sch1.stu(id int identity(1, 1) primary key, name varchar(32), age int)

--insert into sch1.stu(name, age) values('zhangsan', 20)

--select * from sch1.stu

--将架构sch1中的stu表移动到架构dbo中去，只有将架构中的对象清空了（移动到其他架构中去），才能删除架构
--alter schema dbo transfer sch1.stu

--删除schema, 如果schema中含有对象（表，视图之类的），则无法删除schema
drop schema sch1

--授予数据库级主体d2 对架构sch2（这个架构属于数据库级主体d1）中的表或者视图执行select的权限
grant select on schema ::sch2 to d2

--取消授予数据库级主体d2对数据库mybatiesTest1中的表 select 的权限
revoke select on schema ::sch2 to d2

-- 数据库角色db_datareader 可以访问所有的schema架构
--------------------------------------------架构相关管理结束--------------------------------------------

-- login登入名表 (sa)
  select * from master.sys.syslogins 
  -- 登入名与服务器角色关联表
  select * from sys.server_role_members

  -- role服务器角色表 （sysadmin）
  select * from sys.server_principals
  -- 查询登入名拥有的服务器角色
  select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid
  from sys.server_role_members m  inner join sys.server_principals g on  g.principal_id = m.role_principal_id 
  inner join sys.server_principals u on u.principal_id = m.member_principal_id

  -- 每个数据库的用户表
  select * from sysusers
  -- 数据库用户表角色关联表
  select * from sysmembers 
  -- 数据库角色表 （db_*）
  select * from sys.database_principals
  -- 查询数据库用户拥有的角色
  select ta.name as username,tc.name as databaserole  from sysusers ta inner join sysmembers tb on ta.uid=tb.memberuid
  inner join  sys.database_principals tc on tb.groupuid=tc.principal_id  


  -- 查询当前数据库用户关联的登入名
  select ta.name as loginname,tb.name as databaseusername from master.sys.syslogins ta inner join sysusers tb on ta.sid=tb.sid 

  /*如果将当前数据库还原到另一台服务器实例上,刚好那台服务器上也存在person登入用户，你会发现二者的sid不一样，
  由于sid不一样,所以登入用户不具有当前数据库的访问权限,我们要想办法将二者关联起来。
  */
  -- 关联登入名与数据库用户（将数据库用户的sid刷成登入名的sid）
    use AdventureWorks2008R2
    EXEC sp_change_users_login 'Update_One', 'person', 'person'
    Go

-- 查询数据库用户被授予的权限
exec sp_helprotect @username = 'person'


-- 架构 schema (对象owner： dbo默认架构,sys,guest,INFORMATION_SCHEMA)
https://docs.microsoft.com/zh-cn/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server

 架构是数据库对象的命名容器，使你能够将对象分组到不同的命名空间中，实现用户和架构分离
 用户不从架构继承权限；架构权限由架构中包含的数据库对象继承
 create schema
 drop schema 

-- 查看

-- 查询对象的拥有者所有者owner：

1. 如果这个对象的所有者没有被更改过，那么它对应的架构的所有者就是它的所有者。
2. 如果这个对象的所有者被更改了，我需要去查找更改后的所有者是谁。

在对象目录视图sys.objects中，principal_id字段隐含了这个信息。
如果这个字段值是NULL，那么这个对象的所有者是它架构的所有者。
如果不是NULL，那么这个principal_id就代表它的所有者。

SELECT 
    o.name AS OBJECT_NAME,
    CASE
        WHEN o.principal_id IS NULL THEN dp2.name
    ELSE dp.name END AS Object_Owner
FROM
    sys.objects AS o
    LEFT JOIN sys.schemas AS s
    ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals AS dp
    ON o.principal_id = dp.principal_id
    INNER JOIN sys.database_principals AS dp2
    ON s.principal_id = dp2.principal_id

-- loop循环批处理执行语句：
EXEC Sp_msforeachdb
  'use [?]; select ''use [?]; revoke '' + b.permission_name + '' on ['' + object_name(b.major_id) + ''] from PUBLIC'' TSQL_Fix, ''?'' [Database], ''Found'' as [Accecc to PUBLIC Role], object_name(b.major_id) as [ObjectName], b.permission_name as [PermissionDesc], b.state_desc as [PermissionState] from [?].sys.database_principals a inner join [?].sys.database_permissions b on a.principal_id = b.grantee_principal_id where a.principal_id=0 and b.major_id > 0 order by b.major_id desc';

EXEC Sp_msforeachdb
  'use [?]; select ''?'' [Database], a.name as [Accecc to GUEST User], object_name(b.major_id) as [ObjectName], b.permission_name as [PermissionDesc],  b.state_desc as [PermissionState] from [?].sys.database_principals a inner join [?].sys.database_permissions b on a.principal_id = b.grantee_principal_id where a.name = ''guest'' and b.major_id > 0 order by b.major_id desc' 

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''msdb'', ''tempdb'') BEGIN USE ? EXEC(''

CREATE USER [DC\apps_zbx_serv] FOR LOGIN [DC\apps_zbx_serv]
EXEC sp_addrolemember N''db_datareader'', N''DC\apps_zbx_serv''

'') END'
EXEC sp_MSforeachdb @command


  select name from sys.syslogins where sysadmin=1
  select name from sys.syslogins where PWDCOMPARE('''', password) = 1
  select * from  sys.server_principals

权限查询语句：

/*
select * from sysusers
select * from syspermissions
*/

-- 当前登录名
select system_user,ORIGINAL_LOGIN() [原始登录账户], SUSER_SNAME() [当前登录账户]
-- 当前数据库用户名
SELECT current_user,session_user,user_name()

-- 当前数据库
select DB_NAME()

-- 查询用户的object权限
exec sp_helprotect NULL, 'sa'

-- 查询用户拥有的role
exec sp_helpuser 'public'

-- 查询哪些用户拥有指定的系统role
exec sp_helpsrvrolemember 'sysadmin'

-- 查看当前连接：SP_WHO 'active'
select session_id, client_net_address,protocol_type,client_tcp_port,local_tcp_port
-- select *
from master.sys.dm_exec_connections;

select session_id,host_name,program_name,login_name,status,is_user_process 
-- select *
from  master.sys.dm_exec_sessions
where status not in ('sleeping')

SELECT spid,dbid,blocked,status,hostname,program_name,loginame FROM
[Master].[dbo].[SYSPROCESSES]
where status not in ('background','sleeping')

-- 清理kill当前数据库连接：
USE master

declare @programName   nvarchar(200),
@spid nvarchar(20)

declare cDblogin cursor for
select cast(spid as varchar(20))  AS spid from master..sysprocesses where dbid=db_id('db_id_demo')
open cDblogin
fetch next from cDblogin into @spid
while @@fetch_status=0
begin
--防止自己终止自己的进程
--否则会报错不能用KILL 来终止您自己的进程 
IF  @spid <> @@SPID  
  exec( 'kill '+@spid) 
fetch next from  cDblogin into @spid 
end  
close cDblogin 
deallocate cDblogin

-- Who has access to my SQL Server instance?

SELECT
 name as UserName, type_desc as UserType, is_disabled as IsDisabled
FROM sys.server_principals
where type_desc in('WINDOWS_LOGIN', 'SQL_LOGIN')
order by UserType, name, IsDisabled

-- check if current user have sysadmin role
SELECT
 p.name as UserName, p.type_desc as UserType, pp.name as ServerRoleName, pp.type_desc as ServerRoleType
from sys.server_role_members roles
join sys.server_principals p on roles.member_principal_id = p.principal_id
join sys.server_principals pp on roles.role_principal_id = pp.principal_id
where pp.name in('sysadmin')
and p.name= SUSER_SNAME();


-- Who has access to my Databases?
 select * from sys.database_principals

SELECT
 dp.name as UserName, dp.type_desc as UserType, sp.name as LoginName, sp.type_desc as LoginType
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id
order by UserType

-- Server Roles

select
 p.name as UserName, p.type_desc as UserType, pp.name as ServerRoleName, pp.type_desc as ServerRoleType
from sys.server_role_members roles
join sys.server_principals p on roles.member_principal_id = p.principal_id
join sys.server_principals pp on roles.role_principal_id = pp.principal_id
where pp.name in('sysadmin')
order by ServerRoleName, UserName


-- Database Roles

SELECT
 p.name as UserName, p.type_desc as UserType, pp.name as DBRoleName, pp.type_desc as DBRoleType, pp.is_fixed_role as IfFixedRole
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where pp.name in('db_owner', 'db_datawriter')

-- What can these users do?

SELECT
 grantor.name as GrantorName, dp.state_desc as StateDesc, dp.class_desc as ClassDesc, dp.permission_name as PermissionName ,
OBJECT_NAME(major_id) as ObjectName, GranteeName = grantee.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id
where permission_name like '%UPDATE%'

为windows本地管理员账户组builtin创建登陆用户：

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] 或 sp_grantlogin 'builtin/administrators'

alter login sa with password=

GO

查看本地组

exec xp_enumgroups

SQL Server服务启动账号必须有3个基本权限：


l 数据库本地目录的读写权限；
l 启动本地服务的权限；
l 读取注册表的权限；
3． 赋予sqlserver用户mssql(WINDOWS平台上强大的数据库平台)目录的读写权限；
因为我的SQL SERVER是安装在D盘，所以我在权限管理中，将D:\PROGRMAM FILE\Microsoft SQL Server\mssql(WINDOWS平台上强大的数据库平台)读写权限赋予sqlserver用户。
4． 分配sqlserver用户启动本地服务的权限；
这个比较复杂，我只举例作为成员服务器的情况。
l 启动“Local Security Setting” MMC 管理单元。
l 展开Local Policy，然后单击User Rights Assignment。
l 在右侧窗格中，右键单击Log on as Service，将用户添加到该策略，然后单击OK。
l 在右侧窗格中，右键单击Log on as a batch job，将用户添加到该策略，然后单击OK
l 在右侧窗格中，右键单击Locks pages in memory，将用户添加到该策略，然后单击OK
l 在右侧窗格中，右键单击Act as part of the operating systme，将用户添加到该策略，然后单击OK
l 在右侧窗格中，右键单击Bypass traverse checking，将用户添加到该策略，然后单击OK
l 在右侧窗格中，右键单击Replace a process level token，将用户添加到该策略，然后单击OK
l 关闭“Local Security Setting” MMC 管理单元。
如图：
5． 重新启动系统，用sqlserver用户登陆系统；
6． 再重新启动系统，已administrator用户登陆，打开SERVICES管理工具，配置用该用户启动mssql(WINDOWS平台上强大的数据库平台)SERVER服务；
这样我们就可以通过限制SQLSERVER用户的权限来控制SQLSERVER扩展存储过程的权限

评论0

您还没有登录，请您注册 登录后发表评论。