CLR/SQL:在CLR中实现模拟以进行文件访问 [英] CLR/SQL: Implement impersonation in CLR for file access

问题描述

我正在尝试模仿SQL xp_getfiledetails过程，但是我还无法弄清楚如何进行模拟.如何在下面的代码中实现它(当SQL Server可以访问共享时，它可以很好地工作.)程序集已经具有外部访问"权限.

I''m trying to mimic the SQL xp_getfiledetails procedure, but I haven''t been able to figure out how to do impersonation. How can it be implemented in the following code (which works fine when the SQL Server has access to the share.) The assembly already has External Access permission.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.Sql
Imports System.Runtime.InteropServices
Imports System.IO
Imports System.Security.Permissions

' works as long as SQL Server has access to the file

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub spGetFileDetails(ByVal path As String)

        Dim record As New SqlDataRecord( _
            New SqlMetaData("Alternate Name", SqlDbType.NVarChar, 255),
            New SqlMetaData("Size", SqlDbType.NVarChar, 255),
            New SqlMetaData("Creation Date", SqlDbType.NVarChar, 8),
            New SqlMetaData("Creation Time", SqlDbType.NVarChar, 8),
            New SqlMetaData("Last Written Date", SqlDbType.NVarChar, 8),
            New SqlMetaData("Last Written Time", SqlDbType.NVarChar, 8),
            New SqlMetaData("Last Accessed Date", SqlDbType.NVarChar, 8),
            New SqlMetaData("Last Accessed Time", SqlDbType.NVarChar, 8),
            New SqlMetaData("Attributes", SqlDbType.NVarChar, 255) _
        )

        'Dim newContext As System.Security.Principal.WindowsImpersonationContext ???
        'newContext = SqlContext.WindowsIdentity.Impersonate() ???

        Dim filePerm As New FileIOPermission(FileIOPermissionAccess.Read, path)
        filePerm.Assert()
        Dim fi As New FileInfo(path)
        If fi.Exists Then
            'populate the record.
            record.SetString(0, fi.FullName)
            record.SetString(1, fi.Length.ToString)
            record.SetString(2, fi.CreationTime.Date.ToString("yyyyMMdd"))
            record.SetString(3, fi.CreationTime.TimeOfDay.ToString)
            record.SetString(4, fi.LastWriteTime.Date.ToString("yyyyMMdd"))
            record.SetString(5, fi.LastWriteTime.TimeOfDay.ToString)
            record.SetString(6, fi.LastAccessTime.Date.ToString("yyyyMMdd"))
            record.SetString(7, fi.LastAccessTime.TimeOfDay.ToString)
            record.SetString(8, fi.Attributes.ToString)
            ' Send the record to the client
            SqlContext.Pipe.Send(record)
        End If
    End Sub
End Class

推荐答案

您可以在代码中使用命令化
请点击以下链接

C#.NET中完整的模拟演示[^] [ ^ ]

you can use impersination in you code
follow the below link

A Complete Impersonation Demo in C#.NET[^][^]

这篇关于CLR/SQL:在CLR中实现模拟以进行文件访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！