SQL2005中的权限查询问题

2014-01-17 03:52:02 270 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Wireshark实战解析IEC 61850 GOOSE报文：智能变电站通信调试指南网络协议分析是理解现代工业通信系统的基础，其核心原理在于对数据包的捕获、解码与解析。在电力自动化领域，IEC 61850标准定义了智能变电站内设备间高效、可靠的通信框架，其中GOOSE（通用面向对象变电站事件）协议因其基于以太网多播和快速重传机制，成为保护跳闸、联锁等关键信号传输的基石，其技术价值在于实现了微秒级延迟的高可靠性通信。掌握GOOSE报文的分析能力，对于现场调试、故障定位及系统验证至关重要。本文聚焦于使用Wireshark这一网络分析工具，深入解析GOOSE报文结构，涵盖从以太网帧头、协议标识到阅读详情

SQL2005中怎么用SQL语句或者存储过程查询到某个用户名对某数据库中的某表中的某字段的增删改查及其他的所有权限？

...全文

ThingsBoard 3.1.1版本在window本地运行之TB-Gateway服务搭建(三) TB-gateway服务是TB不可或缺的部分，很多的协议都需要TB-gateway来做分类处理，今天我们就来做TB-Gateway服务搭建。阅读详情

lxyspy

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

LongRui888 2014-01-20

打赏
举报

引用 15 楼 lxyspy 的回复:

[quote=引用 14 楼 yupeigu 的回复:] 给你一个例子：


--1.创建登陆
create login wcc
with password = '123'


--2.模拟登陆wcc
execute as login='wcc'


--3.查询权限,发现没有任何权限
select *  
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称  
                           'object')    --要列出权限的安全对象类 
where subentity_name <>''
go


--4.回到当前的登录名,也就是sa用户
revert
go

--5.返回了对表spgj_yjm的各个列的权限
select *  
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称  
                           'object')    --要列出权限的安全对象类 
where subentity_name <>''
/*
entity_name	subentity_name	permission_name
spgj_yjm	ydh	SELECT
spgj_yjm	yjm	SELECT
spgj_yjm	flag	SELECT
spgj_yjm	ydh	UPDATE
spgj_yjm	yjm	UPDATE
spgj_yjm	flag	UPDATE
spgj_yjm	ydh	REFERENCES
spgj_yjm	yjm	REFERENCES
spgj_yjm	flag	REFERENCES
*/

多谢大虾，你给的东西很有启发，只是我想根据不同的用户名来查询该用户名下的权限，用户名这个变量该怎么加进去呢[/quote] 可以这样：


declare @login nvarchar(100)

set @login = 'wcc'

--2.模拟登陆wcc
execute as login=@login

lxyspy 2014-01-20

打赏
举报

引用 14 楼 yupeigu 的回复:

给你一个例子：


--1.创建登陆
create login wcc
with password = '123'


--2.模拟登陆wcc
execute as login='wcc'


--3.查询权限,发现没有任何权限
select *  
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称  
                           'object')    --要列出权限的安全对象类 
where subentity_name <>''
go


--4.回到当前的登录名,也就是sa用户
revert
go

--5.返回了对表spgj_yjm的各个列的权限
select *  
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称  
                           'object')    --要列出权限的安全对象类 
where subentity_name <>''
/*
entity_name	subentity_name	permission_name
spgj_yjm	ydh	SELECT
spgj_yjm	yjm	SELECT
spgj_yjm	flag	SELECT
spgj_yjm	ydh	UPDATE
spgj_yjm	yjm	UPDATE
spgj_yjm	flag	UPDATE
spgj_yjm	ydh	REFERENCES
spgj_yjm	yjm	REFERENCES
spgj_yjm	flag	REFERENCES
*/

多谢大虾，你给的东西很有启发，只是我想根据不同的用户名来查询该用户名下的权限，用户名这个变量该怎么加进去呢

LongRui888 2014-01-17

打赏
举报

给你一个例子：


--1.创建登陆
create login wcc
with password = '123'


--2.模拟登陆wcc
execute as login='wcc'


--3.查询权限,发现没有任何权限
select *  
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称  
                           'object')    --要列出权限的安全对象类 
where subentity_name <>''
go


--4.回到当前的登录名,也就是sa用户
revert
go

--5.返回了对表spgj_yjm的各个列的权限
select *  
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称  
                           'object')    --要列出权限的安全对象类 
where subentity_name <>''
/*
entity_name	subentity_name	permission_name
spgj_yjm	ydh	SELECT
spgj_yjm	yjm	SELECT
spgj_yjm	flag	SELECT
spgj_yjm	ydh	UPDATE
spgj_yjm	yjm	UPDATE
spgj_yjm	flag	UPDATE
spgj_yjm	ydh	REFERENCES
spgj_yjm	yjm	REFERENCES
spgj_yjm	flag	REFERENCES
*/

LongRui888 2014-01-17

打赏
举报

引用 10 楼 lxyspy 的回复:

[quote=引用 7 楼 yupeigu 的回复:] 用这个试试： select * from sys.fn_my_permissions('表的名称',--安全对象的名称 'object') --要列出权限的安全对象类

请教大虾这个怎么用啊，object这个用什么来代替？我想得到某个SQL用户名是否对表里某个字段拥有增删改查的权限，谢谢您的回复[/quote] 这个代码是显示了spgj_yjm表中，对列的权限：

select *  
from sys.fn_my_permissions('spgj_yjm',--安全对象的名称  
                           'object')    --要列出权限的安全对象类 
where subentity_name <>''
/*
entity_name	subentity_name	permission_name
spgj_yjm	ydh	SELECT
spgj_yjm	yjm	SELECT
spgj_yjm	flag	SELECT
spgj_yjm	ydh	UPDATE
spgj_yjm	yjm	UPDATE
spgj_yjm	flag	UPDATE
spgj_yjm	ydh	REFERENCES
spgj_yjm	yjm	REFERENCES
spgj_yjm	flag	REFERENCES
*/

發糞塗牆 2014-01-17

打赏
举报

SELECT * FROM fn_my_permissions('表名', 'OBJECT'); 这个查出来的可用不？可用的话可以考虑用游标遍历全库

lxyspy 2014-01-17

打赏
举报

引用 9 楼 DBA_Huangzj 的回复:

那是国外大牛的代码，执行了一下可用

我也执行了一下，出现三个查询结果集，但是没看到字段的权限在哪里，哎，我太弱了

lxyspy 2014-01-17

打赏
举报

引用 7 楼 yupeigu 的回复:

用这个试试： select * from sys.fn_my_permissions('表的名称',--安全对象的名称 'object') --要列出权限的安全对象类

请教大虾这个怎么用啊，object这个用什么来代替？我想得到某个SQL用户名是否对表里某个字段拥有增删改查的权限，谢谢您的回复

發糞塗牆 2014-01-17

打赏
举报

那是国外大牛的代码，执行了一下可用

lxyspy 2014-01-17

打赏
举报

哇，这个这么复杂么，看来是我太差劲了，多谢斑竹提点，我研究一下你上面给出的这么长的代码段，谢谢

LongRui888 2014-01-17

打赏
举报

用这个试试： select * from sys.fn_my_permissions('表的名称',--安全对象的名称 'object') --要列出权限的安全对象类

發糞塗牆 2014-01-17

打赏
举报

你这个要是能做出来，都差不多可以拿去卖了

發糞塗牆 2014-01-17

打赏
举报

你这个是再做一个SSMS的级别了，有需要搞那么复杂？要是给客户用的，不建议让客户有DDL的权限，如果是给内部人员用的，直接用SSMS就可以了，或者下个第三方软件

lxyspy 2014-01-17

打赏
举报

背景是这样，本来打算做一个给数据库授权的功能模块，要求精确到对数据库中某个表中的某个或多个字段进行增删改查的授权，现在授权功能已经实现，但是想查询某个用户具体对哪些数据库中的哪些表中的哪些字段拥有增删改查中的一项或者多项权限，这个该怎么处理呢？谢谢斑竹的回复。

發糞塗牆 2014-01-17

打赏
举报

/**************************************
Name: The Complete SQL Server 2005 Permissions Report

Author: Richard Lu

Date Created: 05/04/2009

Description:
The script provides detailed access permission report on all levels, i.e. server, database, 
object and column, of SQL Server 2005 for all logins. Users would also be able to customize 
the report result by specifying two parameters: @loginName and @dbName at the beginning of 
the script to retrieve permission assignments on particular logins and databases. 
Reports of permissions possessed by System fixed roles, e.g. public and SQLAgentOperatorRole, 
can be excluded from the report by simply removing the comment marks on all the "type NOT IN ('R')" 
condition in the Where-clause in the script.

****************************************/
USE master
GO
SET NOCOUNT ON
DECLARE @loginName sysname, @dbName sysname

/* Set the two Parameters here. By defaul All logins and databases will be reported */
SET @loginName = '%' -- e.g. 'NorthAmerica\JSmith1'
SET @dbName = '%' -- e.g. 'ReportServer'


-- Get Server Role Level Info
SELECT 
 suser_sname(p.sid) AS Login_Name, 
 p.type_desc AS [Login_Type], 
 r.is_disabled,
 r.name AS Permission_Name, 
 r.type_desc AS Permission_Type, 
 p.create_date, p.modify_date
FROM
 master.sys.server_principals r
 LEFT OUTER JOIN master.sys.server_role_members m ON r.principal_id = m.role_principal_id
 LEFT OUTER JOIN master.sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE p.name LIKE @loginName 
 --AND p.type NOT IN ('R')
UNION
SELECT 
 suser_sname(prin.sid) AS Login_Name, 
 prin.type_desc AS [Login_Type], 
 prin.is_disabled,
 perm.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission_Name, 
 perm.class_desc AS Permission_Type, 
 prin.create_date, prin.modify_date
FROM master.sys.server_permissions perm
 LEFT OUTER JOIN master.sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
 LEFT OUTER JOIN master.sys.server_principals grantor ON perm.grantor_principal_id = grantor.principal_id
WHERE prin.name LIKE @loginName 
 --AND prin.type NOT IN ('R')
ORDER BY Login_Name, r.name


-- Retrieve DB Role Level Info
DECLARE @DBRolePermissions TABLE(
 DatabaseName varchar(300), 
 Principal_Name sysname, 
 Login_Name sysname NULL, 
 DB_RoleMember varchar(300), 
 Permission_Type sysname)

INSERT INTO @DBRolePermissions
EXEC sp_MSforeachdb '
 SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name, 
 roles.Name AS Role_Member_Name, roles.type_desc
 FROM [?].sys.database_role_members r 
 LEFT OUTER JOIN [?].sys.database_principals users on r.member_principal_id = users.principal_id
 LEFT OUTER JOIN [?].sys.database_principals roles on r.role_principal_id = roles.principal_id
 --WHERE users.type not in (''R'')'

-- Capture permissions generated FROM sys.database_permissions
INSERT INTO @DBRolePermissions
EXEC sp_msforeachdb '
 SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name, 
 r.Permission_Name AS DB_RoleMember, r.class_desc
 FROM [?].sys.database_permissions r 
 LEFT OUTER JOIN [?].sys.database_principals users on r.Grantee_principal_id = users.principal_id
 WHERE r.class_desc = ''DATABASE'''

SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, DB_RoleMember AS Permission_Name, Permission_Type
FROM @DBRolePermissions 
WHERE (ISNULL(Login_Name, '') LIKE @loginName OR ISNULL(Principal_Name, '') LIKE @loginName)
 AND DatabaseName LIKE @dbName
ORDER BY Principal_Name, DatabaseName, DB_RoleMember


-- Get Object/Column Level Permissions
DECLARE @ObjectPermissions TABLE (
 DatabaseName varchar(300), 
 Principal_Name sysname, 
 Login_Name sysname NULL, 
 ObjectType sysname, 
 ObjectName varchar(500), 
 PermissionName varchar(300), 
 state_desc varchar(300), 
 Grantor varchar(300))

DECLARE @CurrentDB sysname, @SQLCmd varchar(4000)
DECLARE DBCursor CURSOR FOR 
 SELECT [name] 
 FROM master.sys.databases 
 WHERE state = 0 AND [name] LIKE @dbName

OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @CurrentDB

WHILE (@@fetch_status = 0)
BEGIN

-- Capture permissions generated FROM sys.database_permissions
SET @SQLCmd = 'USE [' + @CurrentDB + '] 
 SELECT ''' + @CurrentDB + ''' AS DatabaseName,
 grantee.name AS Principal_Name, 
 suser_sname(grantee.sid) AS Login_Name, 
 p.class_desc AS ObjectType,
 CASE p.class_desc
 WHEN ''SCHEMA'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.schemas objects WHERE objects.schema_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''DATABASE'' THEN CONVERT(sysname, p.class_desc) COLLATE Latin1_General_CI_AS
 WHEN ''OBJECT_OR_COLUMN'' THEN 
 CONVERT(sysname, ISNULL((SELECT o.name + ''.'' + objects.name FROM sys.columns objects WHERE objects.[object_id] = p.major_id and objects.column_id = p.minor_id), o.name)) COLLATE Latin1_General_CI_AS

 WHEN ''DATABASE_PRINCIPAL'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.database_principals objects WHERE objects.principal_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''ASSEMBLY'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.assemblies objects WHERE objects.assembly_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''TYPE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.types objects WHERE objects.user_type_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''XML_SCHEMA_COLLECTION'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.xml_schema_collections objects WHERE objects.xml_collection_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''MESSAGE_TYPE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.service_message_types objects WHERE objects.message_type_id = p.major_id)) COLLATE Latin1_General_CI_AS

 WHEN ''SERVICE_CONTRACT'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.service_contracts objects WHERE objects.service_contract_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''REMOTE_SERVICE_BINDING'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.remote_service_bindings objects WHERE objects.remote_service_binding_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''ROUTE'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.routes objects WHERE objects.route_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''FULLTEXT_CATALOG'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.fulltext_catalogs objects WHERE objects.fulltext_catalog_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''SYMMETRIC_KEY'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.symmetric_keys objects WHERE objects.symmetric_key_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''CERTIFICATE'' THEN CONVERT(sysname, (SELECT distinct objects.name FROM sys.certificates objects WHERE objects.certificate_id = p.major_id)) COLLATE Latin1_General_CI_AS
 WHEN ''ASYMMETRIC_KEY'' THEN CONVERT(sysname, (SELECT objects.name FROM sys.asymmetric_keys objects WHERE objects.asymmetric_key_id = p.major_id)) COLLATE Latin1_General_CI_AS
 ELSE CONVERT(sysname, ''n\a'') COLLATE Latin1_General_CI_AS
 END AS ObjectName,
 Permission_name, state_desc, grantor.name AS Grantor
 FROM [' + @CurrentDB + '].sys.database_permissions p 
 LEFT OUTER JOIN [' + @CurrentDB + '].sys.database_principals grantee on p.grantee_principal_id = grantee.principal_id
 LEFT OUTER JOIN [' + @CurrentDB + '].sys.all_objects o on p.major_id = o.[object_id]
 LEFT OUTER JOIN [' + @CurrentDB + '].sys.database_principals grantor on p.grantor_principal_id = grantor.principal_id
 WHERE p.class_desc not in (''DATABASE'') 
 --AND grantee.type not in (''R'') '

INSERT INTO @ObjectPermissions
EXEC (@SQLCmd)

FETCH NEXT FROM DBCursor into @CurrentDB
END

CLOSE DBCursor
DEALLOCATE DBCursor


SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, ObjectName, ObjectType,
 PermissionName, state_desc, Grantor
FROM @ObjectPermissions 
WHERE ISNULL(Login_Name, '') like @loginName OR ISNULL(Principal_Name, '') like @loginName
ORDER BY DatabaseName, Principal_Name, ObjectName, PermissionName

lxyspy 2014-01-17

打赏
举报

查不到具体对哪个字段拥有权限么？大虾帮帮忙,好么

發糞塗牆 2014-01-17

打赏
举报

我只有这个程度的

SELECT  dp.class_desc ,
        dp.permission_name ,
        dp.state_desc ,
        ObjectName = OBJECT_NAME(major_id) ,
        GranteeName = grantee.name ,
        GrantorName = grantor.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

SQL2005中的权限查询问题

相关推荐