SwiftUI中通过书签URL恢复数据库访问时遇权限拒绝问题

通过NSOpenPanel获取文件权限，保存SQLite文件的安全作用域书签后，恢复URL并启动安全访问，能成功连接数据库，但执行db.prepare()查询时出现access denied (code:23)错误。

书签保存代码 #

private func checkForSQLiteFiles(at containerURL: URL, appName: String) {
    do {
            viewModel.connect(strConnect: selectedSQLiteFile!.path)
            viewModel.fetchAllTables()
            
            // Save selected SQLite file to UserDefaults
            UserDefaults.standard.set(selectedSQLiteFile, forKey: "selectedSQLiteFile")
            saveBookmark(for: selectedSQLiteFile!, key: "SQLiteBookmark")
        }
    } catch {
        errorMessages.append("Error")
    }
}


func saveBookmark(for url: URL, key: String) {
    do {
        let bookmarkData = try url.bookmarkData(options: .withSecurityScope, includingResourceValuesForKeys: nil, relativeTo: nil)
        UserDefaults.standard.set(bookmarkData, forKey: key)
    } catch {
        print("Failed to create bookmark for \(url.path): \(error)")
    }
}

书签恢复与数据库操作代码 #

private func openExistingProject() {
    if let sqliteURL = resolveBookmark(for: "SQLiteBookmark") {
        if sqliteURL.startAccessingSecurityScopedResource() {
            // Check if file is readable and exists
            if FileManager.default.isReadableFile(atPath: sqliteURL.path) {
                print("SQLite file is readable at path: \(sqliteURL.path)")
                
                // Proceed to connect and load entities
                viewModel.connect(strConnect: sqliteURL.path)
                viewModel.fetchAllTables()
            } else {
                print("Failed to access the SQLite file at \(sqliteURL.path)")
            }
        } else {
            print("Failed to access the security-scoped resource for \(sqliteURL.path)")
        }
        
        defer { sqliteURL.stopAccessingSecurityScopedResource() }
    } else {
        print("No valid bookmark for SQLite file")
    }
}

func resolveBookmark(for key: String) -> URL? {
    if let bookmarkData = UserDefaults.standard.data(forKey: key) {
        var isStale = false
        do {
            let url = try URL(resolvingBookmarkData: bookmarkData, options: .withSecurityScope, relativeTo: nil, bookmarkDataIsStale: &isStale)
            if isStale {
                print("Bookmark data is stale for \(url.path)")
            }
            return url
        } catch {
            print("Failed to resolve bookmark for key \(key): \(error)")
        }
    }
    return nil
}

SQLite管理类代码 #

class SQLiteEntityManager: ObservableObject {
    @Published var isConnected: Bool = false
    private var db: Connection?
    
    func connect(strConnect: String) {
        // Define the path to the SQLite database file
        let dbPath = strConnect
        
        // Create a connection to the database
        do {
            db = try Connection(dbPath)
            isConnected = true
        } catch {
            print("Unable to open database. Error: \(error)")
        }
    }
    
    func fetchAllTables() -> [String] {
        guard let db = db else {
            print("Database is not connected")
            return []
        }
        
        do {
            let tables = try db.prepare("SELECT name FROM sqlite_master WHERE type='table'")
            return tables.map { "\($0[0]!)" }
        } catch {
            print("Error fetching tables: \(error)")
            return []
        }
    }
}

解决方法 #

1. 确保安全作用域覆盖所有数据库操作 #

当前代码中defer { sqliteURL.stopAccessingSecurityScopedResource() }会在openExistingProject函数结束时立即停止访问，但fetchAllTables的执行可能延迟到安全权限失效后。需确保数据库查询在安全访问生命周期内完成：

private func openExistingProject() {
    if let sqliteURL = resolveBookmark(for: "SQLiteBookmark") {
        guard sqliteURL.startAccessingSecurityScopedResource() else {
            print("Failed to access the security-scoped resource for \(sqliteURL.path)")
            return
        }
        
        // 延迟停止访问，确保同步查询完成
        defer { 
            DispatchQueue.main.asyncAfter(deadline: .now() + 0.1) {
                sqliteURL.stopAccessingSecurityScopedResource()
            }
        }
        
        let fileManager = FileManager.default
        if fileManager.isReadableFile(atPath: sqliteURL.path) {
            print("SQLite file is readable at path: \(sqliteURL.path)")
            viewModel.connect(strConnect: sqliteURL.path)
            // 同步获取结果，确保在安全访问期间完成
            let tables = viewModel.fetchAllTables()
            // 处理查询结果
        } else {
            print("Failed to access the SQLite file at \(sqliteURL.path)")
        }
    } else {
        print("No valid bookmark for SQLite file")
    }
}

2. 直接传递URL而非路径字符串 #

避免将URL转为字符串传递，优先用URL对象（若SQLite库支持），确保安全权限正确传递：

// 修改SQLiteEntityManager的connect方法
func connect(url: URL) {
    do {
        // 若SQLite库支持直接传入URL则优先使用，否则用path
        db = try Connection(url.path)
        isConnected = true
    } catch {
        print("Unable to open database. Error: \(error)")
    }
}

// 调用时直接传sqliteURL
viewModel.connect(url: sqliteURL)

3. 检查文件读写权限 #

错误码23常和文件权限相关，SQLite即使只读数据库也可能需要临时文件权限，需同时检查可读可写：

if fileManager.isReadableFile(atPath: sqliteURL.path) && fileManager.isWritableFile(atPath: sqliteURL.path) {
    // 执行数据库操作
}

4. 刷新过期的书签数据 #

若书签标记为isStale，需重新保存书签以更新权限：

func resolveBookmark(for key: String) -> URL? {
    if let bookmarkData = UserDefaults.standard.data(forKey: key) {
        var isStale = false
        do {
            let url = try URL(resolvingBookmarkData: bookmarkData, options: .withSecurityScope, relativeTo: nil, bookmarkDataIsStale: &isStale)
            if isStale {
                print("Bookmark data is stale, re-saving...")
                try saveBookmark(for: url, key: key)
            }
            return url
        } catch {
            print("Failed to resolve bookmark for key \(key): \(error)")
        }
    }
    return nil
}

内容的提问来源于stack exchange，提问作者Alex Filatov