Server查看login所授予的切实可行权限难点,登陆和客商

在SQL
Server数据库中怎样查看二个登入名(login卡塔尔(قطر‎的切实可行权限呢,假使利用SSMS的UI分界面查看登入名的实际权限的话,客户数据库超级多的话,要梳理完它抱有的权杖,操作又耗时又费力,个人非常崇尚轻易、高效的章程,不喜欢那么些急需多量手工操作的UI界面操作办法,哪怕就是本子,固然不能叁遍化解,手工业多操作四遍(比方,切换数据库卡塔尔国,都以不可选拔的。近些日子遇见那些需要,就全盘了须臾间事情发生前的台本get_login_rights_script.sql,输入登入名参数,将以此登陆名所具有的服务器剧中人物、数据库剧中人物、以致所授予具体对象的有关权限制行驶使脚本查询出来,脚本分享如下:

授予权限的思绪,能够用一句话来归纳,那正是:付与 Principal 操作
Securable 的 Permission,在相当多文书档案中,把 Principal
翻译为安全珍视,Securable翻译为平安指标,Permission翻译为权力,付与权限的子句细分为三片段:Permission,Securable

Principal。最令人猜忌的是Principal和Securable,那多个概念有的时候候会互相转变。

--==================================================================================================================-- ScriptName : get_login_rights_script.sql-- Author : 潇湘隐者 -- CreateDate : 2015-12-18-- Description : 查看某个登录名被授予的数据库对象的权限的脚本-- Note : /****************************************************************************************************************** Parameters : 参数说明******************************************************************************************************************** @login_name : 你要查看权限的登录名(需要输入替换的参数)******************************************************************************************************************** Modified Date Modified User Version Modified Reason******************************************************************************************************************** 2018-08-03 潇湘隐者 V01.00.00 新建该脚本。 2019-04-04 潇湘隐者 V01.01.00 Fix掉一个bug,某个表只允许更新某个字段,但是这里显示更新整个表。 2019-09-25 潇湘隐者 V01.02.00 解决只能查看某个用户数据库,不能查看所有数据库的权限问题。 2019-09-25 潇湘隐者 V01.03.00 解决数据库名包含中划线[-], 出现下面错误问题-------------------------------------------------------------------------------------------------------------------Msg 911, Level 16, State 1, Line 1Database 'xxxx' does not exist. Make sure that the name is entered correctly.-------------------------------------------------------------------------------------------------------------------*******************************************************************************************************************/DECLARE @login_name NVARCHAR(32)= 'test1';DECLARE @database_name NVARCHAR(64);DECLARE @cmdText NVARCHAR(MAX);IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL DROP TABLE dbo.#databases;CREATE TABLE #databases( database_id INT, database_name sysname);IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL DROP TABLE dbo.#user_db_roles;CREATE TABLE dbo.#user_db_roles( [DB_NAME] NVARCHAR(64) ,[USER_NAME] NVARCHAR(64) ,[ROLE_NAME] NVARCHAR(64));IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL DROP TABLE dbo.#user_object_rights;CREATE TABLE dbo.#user_object_rights( [DATABASE_NAME] NVARCHAR(128), [SCHEMA_NAME] NVARCHAR(64), [OBJECT_NAME] NVARCHAR(128), [USER_NAME] NVARCHAR(32), [PERMISSIONS_TYPE] CHAR(12), [PERMISSION_NAME] NVARCHAR(128), [PERMISSION_STATE] NVARCHAR(64), [CLASS_DESC] NVARCHAR(64), [COLUMN_NAME] NVARCHAR(32), [STATE_DESC] NVARCHAR(64), [GRANT_STMT] NVARCHAR(MAX), [REVOKE_STMT] NVARCHAR(MAX))INSERT INTO #databasesSELECT database_id , nameFROM sys.databasesWHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE --登录名授予的服务器角色SELECT UserName = u.name , ServerRole = g.name , Type = u.type, Type_Desc = u.Type_Desc, Create_Date = u.create_date, Modify_Date = u.modify_date, DenyLogin = l.denyloginFROM 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 INNER JOIN sys.syslogins l ON u.name = l.nameWHERE l.name=@login_nameORDER BY u.name,g.name;WHILE 1= 1BEGIN SELECT TOP 1 @database_name= database_name FROM #databases ORDER BY database_id; IF @@ROWCOUNT =0 BREAK; SET @cmdText = N'USE ' + QUOTENAME(@database_name) + N';' +CHAR(10) --登录名授予的数据库角色 SELECT @cmdText += N'INSERT INTO #user_db_roles SELECT DB_NAME() AS [DB_NAME] ,M.NAME AS [USER_NAME] ,R.NAME AS [ROLE_NAME] FROM sys.DATABASE_ROLE_MEMBERS RM INNER JOIN sys.DATABASE_PRINCIPALS R ON RM.ROLE_PRINCIPAL_ID = R.PRINCIPAL_ID INNER JOIN sys.DATABASE_PRINCIPALS M ON RM.MEMBER_PRINCIPAL_ID = M.PRINCIPAL_ID WHERE M.NAME=@p_login_name' + CHAR(10); EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name; SET @cmdText = N'USE ' +QUOTENAME(@database_name) + N';' +CHAR(10); --查看具体对象的授权问题 SELECT @cmdText +=N'INSERT INTO dbo.#user_object_rights ( [DATABASE_NAME] , [SCHEMA_NAME] , [OBJECT_NAME] , [USER_NAME] , [PERMISSIONS_TYPE] , [PERMISSION_NAME] , [PERMISSION_STATE] , [CLASS_DESC] , [COLUMN_NAME] , [STATE_DESC] , [GRANT_STMT] , [REVOKE_STMT] ) SELECT DB_NAME() AS [DATABASE_NAME] , SYS.SCHEMAS.NAME AS [SCHEMA_NAME] , ob.NAME AS [OBJECT_NAME] , SYS.DATABASE_PRINCIPALS.NAME AS [USER_NAME] , dp.TYPE AS [PERMISSIONS_TYPE] , dp.PERMISSION_NAME AS [PERMISSION_NAME] , dp.STATE AS [PERMISSION_STATE] , dp.CLASS_DESC AS [CLASS_DESC] , sc.name AS [COLUMN_NAME] , dp.STATE_DESC AS [STATE_DESC] , dp.STATE_DESC + '' '' + dp.PERMISSION_NAME + '' ON [''+ SYS.SCHEMAS.NAME + ''].['' + ob.NAME + ''] TO ['' + SYS.DATABASE_PRINCIPALS.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS AS [GRANT_STMT] , ''REVOKE '' + dp.PERMISSION_NAME + '' ON [''+ SYS.SCHEMAS.NAME + ''].['' + ob.NAME + ''] FROM ['' + SYS.DATABASE_PRINCIPALS.NAME + ''];'' COLLATE LATIN1_GENERAL_CI_AS AS [REVOKE_STMT] FROM SYS.DATABASE_PERMISSIONS dp LEFT OUTER JOIN SYS.OBJECTS ob ON dp.MAJOR_ID = ob.OBJECT_ID LEFT OUTER JOIN SYS.SCHEMAS ON ob.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID LEFT OUTER JOIN SYS.DATABASE_PRINCIPALS ON dp.GRANTEE_PRINCIPAL_ID = SYS.DATABASE_PRINCIPALS.PRINCIPAL_ID LEFT OUTER JOIN SYS.columns sc ON ob.object_id = sc.object_id AND sc.column_id = dp.minor_id WHERE SYS.DATABASE_PRINCIPALS.NAME =@p_login_name ORDER BY PERMISSIONS_TYPE;' PRINT(@cmdText); EXEC SP_EXECUTESQL @cmdText, N'@p_login_name NVARCHAR(32)',@p_login_name=@login_name; DELETE FROM #databases WHERE database_name=@database_name;ENDSELECT * FROM tempdb.dbo.#user_db_roles;SELECT * FROM dbo.#user_object_rights;IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL DROP TABLE dbo.#databases;IF OBJECT_ID('tempdb.dbo.#user_db_roles') IS NOT NULL DROP TABLE dbo.#user_db_roles;IF OBJECT_ID('tempdb.dbo.#user_object_rights') IS NOT NULL DROP TABLE dbo.#user_object_rights;

Grants permissions on a securable to a principal

总结

Principal是被授予权限的实业,富含login、user、role等,Securable是Principal操作的靶子,能够是table、view、SP、函数等,也能够是login、user、role等目的;有的时候Principal会作为Securable,被Principal操纵,约等于说,Principal
是空洞意义上的本溪主体,能够向长治核心给与权限,Principal分为Server
level和Database
level,最要害的七个Principal是Login和User,他们的界别是:

上述所述是笔者给我们介绍的SQL
Server查看login所授予的实际权限难点,希望对大家有所帮忙,倘若大家有其余疑问请给自家留言,作者会及时回复大家的。在这里也极度多谢大家对台本之家网址的支撑!要是您以为本文对你有援救,招待转发,烦请注明出处,谢谢!

  • 登入名(Login)是Server级其他Principal,效能域是全数数据库引擎。若要连接
    SQL Server
    实例上的特定数据库,登陆名必得映射到数据库客商。数据库内的权杖是向数据库顾客并非登入名付与的。
  • 数据库客户(User)是数据库级其他Principal,效用域是数据库,登陆名必得映射到数据库顾客工夫连接纳数据库。三个报到名能够作为分歧客商映射到差别的数据库,但在每一个数据库中不得不当作三个顾客张开映射。

Login和User最基本的区分是Login用于调控客商是或不是有权力登入SQL
Server实例,User用于顾客调节是还是不是有权力访谈数据库:

  • Login是登入SQL Server的凭据,验证方式有二种,Windows 验证和SQL
    Server 验证。
  • User用于访谈数据库,使用Login登录到SQL
    Server实例之后,必须映射到User,本事访问数据库。
  • Login提供登入Server的帐号和密码,User用于访谈数据库,Role是权力的汇集,调整User访谈的能源。User只有担任某二个剧中人物或享有某个权力,才具访问数据库。
  • 创设Login之后,该Login可以登陆到SQL
    Server实例,然则,仅仅被给与Public角色,在探访数据库时,必得依据Login创造相应的数据库客户(User),Login和User通过SID关联。

一,创建Login

依附表明类型,Login分为SQL
Server登入和Windows域登入,在创设登陆时,能够设置默许数据库(Default
Database),钦命Login登陆的暗中同意数据库,默许值是master数据库。

1,创建SQL Server 登录

CREATE LOGIN login_name 
WITH PASSWORD = { 'password' }
,DEFAULT_DATABASE = database

2,创建Windows域登录

就算创制Windows域登入,映射到一个Windows域账户,登入名必得接受Windows域格式: [<domainName><login_name>].

CREATE LOGIN [<domain_Name><login_name>] 
FROM WINDOWS
WITH DEFAULT_DATABASE = database

二,创建数据库客商(User)

在SQL Server中,平日状态下,User的创造都以依赖master
数据库中的Login,该Login的来源有三种:基于Windows 域客户,Windows
域客户组,或SQL Server 验证。

1,创建User

CREATE USER user_name 
FOR LOGIN login_name 
WITH DEFAULT_SCHEMA = schema_name 

参数解释:

  • user_name:是成立的数据库顾客的名字,该名字在数据库中是独一的,用于唯一标志叁个客户;
  • login_name:是客商登陆SQL
    Server实例的登陆名,分为Windows域登陆名和SQL
    Server登陆名。Windows域登录名的格式是:[<domainName><loginName>]
    通过SID将登陆名(Login)和顾客名(User)关联在联合签字。
  • WITH DEFAULT_SCHEMA = schema_name:内定客户暗中认可的结构(Schema卡塔尔(قطر‎,暗中认可的Schema是指客户在援引数据库对象时,借使简单框架名,那么SQL
    Server将首先从私下认可框架下展开寻找数据库对象。

在开立新的客商时,未有一些名顾客默许的布局,有二种管理方式:

  • 万一该客户是某一个Windows
    客商组的成员,该组有暗中认可的结构,那么该顾客私下认可的构造是组的暗许结构;
  • 对此sysadmin剧中人物(Fixed Server
    Role)的积极分子,其默认的布局始终是dbo,忽视Create User命令的装置;
  • 即使客户不归属组,亦不是sysadmin剧中人物的积极分子,SQL
    Server不能够明显该客户的结构,设置暗中同意的布局名dbo;

2,成立顾客名,基于SQL Server登入

CREATE LOGIN WanidaBenshoof 
WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';
USE AdventureWorks2012;
go
CREATE USER Wanida 
FOR LOGIN WanidaBenshoof 
WITH DEFAULT_SCHEMA = Marketing;

3,成立客户名,基于Windows 域登陆** **

CREATE USER [Domain1WindowsUserBarry] 
FOR LOGIN [Domain1WindowsUserBarry];
use database_name
go
CREATE USER [Domain1WindowsGroupManagers] 
FOR LOGIN [Domain1WindowsGroupManagers];

三,模拟权限

在SQL
Server的平安模型中,模拟(IMPE本田CR-VSONATE )权限的天水指标是User或Login,被付与者(grantee
)有权力模拟特定客户的平安上下文试行一定的操作。

举例,user1付与模拟user2的权力,当user2的平安上下文有丰硕的权柄,而user1没临时,通过权限模拟,user1能够在user2的权位上下文中实践查询伏乞:

GRANT IMPERSONATE 
ON USER:: user2 
TO user1;

经超过实际践EXECUTE AS
命令模拟客户的权柄,顾客user1就运转在user2的百色上下文中,例如,user1在登录数据库之后,模拟user2的权位:

EXECUTE AS USER = 'user2';

透超过实际践 REVERT 命令退出权限模拟,再次回到到顾客原来的百色上下文中:

REVERT;

四,顾客和组

SQL Server 能够创建二个Windows
Group作为登入和顾客(User),全部归属该组的积极分子,都具备相符的权位。

1,列出组的具有成员

在当数据库中,查看某一个Security Group的有着成员:

exec master..xp_logininfo 
    @acctname = 'domaingroup',
    @option = 'members'

2,检查组的积极分子

当下数据库中,检查脚下的客户是还是不是归于特定的Security Group:

select is_member('domaingroup')

函数is_member的再次回到值是:

  • 0:当前的顾客(User)不是组的成员;
  • 1:当前的客户(User)是组的成员;
  • NULL:组名或剧中人物名不设有于当下的数据库中;

在乎,数据库是现阶段的数据库,假设用于财富处理(Resource
Governor)中的分类函数(Classifier
Function
),那么,要当心分类函数所在的数据库,要是分类函数创立于master数据库,那么is_member函数的当下数据库正是master,检查的结果是当前客商是还是不是归属当前数据库的组或剧中人物。

五,特殊的客商

SQL
Server在各类数据库中都停放三个极度的客户:dboguest,在各种数据库中都有那三个顾客(User):

  • dbodatabase
    owner的简单的称呼):是数据库的全部者(Owner),SQL
    Server把牢固服务器剧中人物 sysadmin
    的积极分子,固定数据库db_owner剧中人物的积极分子,sa登入,都映射到数据库客商dbo上。
  • guest:是客商,SQL Server
    允许任何未有映射到数据库客户的得力登录(Login),使用guest账户登入数据库。在登入数据库在此之前,要求dbo分配权限给guest,
    经常给她查看的权限select。

 当叁个Login登录到SQL
Server之后,若无在数据库中映射相应的客商,那么该Login能够利用guest客商登录数据库。guest顾客暗许存在每种数据库中,默许意况下,guest客商是剥夺的,能够通过命令启用guest客商。

GRANT CONNECT TO GUEST

要是启用guest客商,全数能够登陆到SQL
Server的Login,都能够利用guest身份来访问数据库,并富有guest客户的权柄。能够因此命令禁止使用guest客商:

REVOKE CONNECT FROM GUEST;

注意:只可以在 master 和 tempdb
以外的数据库中实践禁止使用GUEST帐号的操作,那是因为不菲客商都急需以GUEST身份访谈master
和 tempdb系统数据库,GUEST客商在master 和 tempdb数据库的权位被严谨节制。

其余三个极度的客商:INFORMATION_SCHEMA 和 sys,每个数据库都包含这两个用户,这两个用于是数据库引擎内部使用,不能被修改和删除。

 

参谋文书档案:

Principals (Database
Engine)

EXECUTE AS Clause
(Transact-SQL).aspx)

SQL
Server顾客GUEST的回顾介绍

发表评论

电子邮件地址不会被公开。 必填项已用*标注