Carrying Security Intent from the Database to GraphQL

In my previous post about the TiDB-GraphQL project, I covered the idea of treating the database schema as a design surface rather than an implementation detail. Security is one area where that idea becomes concrete.

How Database Access Is Commonly Handled

Many modern applications use a shared-credential database access model, where user identity is resolved at the application boundary (with mechanisms like OIDC and JWTs). The application then connects using a single database identity, through a connection pool, to the database.

In this model authorization is enforced in application code, middleware, or policy layers. By the time a query reaches the database, the database itself typically has no awareness of the end user. All user context has already been resolved elsewhere.

This model works well. It scales, fits managed database offerings, and keeps operational complexity of the database low. The tradeoff is that the database, the store of your application’s data, becomes largely passive from a security perspective.

Earlier Experiences with Database-Enforced Security

Earlier in my career, I worked on systems where security was handled differently. Rigorous access control at the database layer was required by our customers. In this model the users identity was explicitly used to connect with the database, and permissions were enforced directly through grants and schema design. In short, the database determined what each user could access, and those rules applied consistently regardless of how the data was reached.

That exact model does not translate cleanly to modern, cloud-native systems. Per-user database connections do not scale well, and they complicate the ability to use tools like connection pools. However, the underlying idea stuck with me. The database does not have to be a passive participant in security.

Preserving Security Intent

When access rules are enforced at the database layer, they become part of the schema’s intent. Tables, views, roles, and grants together describe not just structure, but who is allowed to see what.

One of my rules of thumb is that security controls should ideally be applied at the right level, and with the right granularity. In the case of the data held in a database, ensuring that the access to the data is enforced close to the data drives makes it much easier to manage that control. Without it, similar checks have to be reimplemented in multiple places, and the database no longer reflects the full set of assumptions about data access.

For this project, I was interested in seeing how we could enable access-control that is applied at the database level, to be surfaced up to the application itself. The goal is to ensure database-level access intent is preserved without abandoning modern authentication patterns, shared connection pools, or compromising the user experience?

Applying This in TiDB-GraphQL

TiDB-GraphQL supports two models for managing data access.

First, a shared-credential database access model can be used out of the box. This is a familiar pattern, and is easy to get up and running with.

The second approach is using TiDB’s Role Based Access Control to manage the access to the database. To deliver this, it integrates with modern identity mechanisms (like OIDC and JWTs), and it continues to rely on pooled database connections. What changes is how authenticated identity is carried from the application to the database.

With the RBAC integrated model, authorized users are mapped to database roles, and all the queries and mutations execute within that role context by switching roles on pooled connections. This means the database’s existing RBAC model is used to authorize data-level access, while the application remains responsible for authentication.

In practice, this means:

  • Identity is handled using standard authentication mechanisms
  • Database connections remain pooled and shared
  • Authorization is enforced using database roles
  • GraphQL reflects what the database permits, rather than redefining those rules again

A High-Level Architecture

At a high level, the flow looks like this:

  1. A user authenticates (using OIDC or a similar mechanism)
  2. TiDB-GraphQL validates the bearer token and loads claim data
  3. TiDB-GraphQL obtains DB connection from the DB pool and switches the connection to that role with SET ROLE
  4. Resolvers execute SQL under that role. TiDB enforces table/column access.

In this second model, the database enforces access directly, and the API surfaces the results. You can read more about this approach in the TiDB-GraphQL project’s authentication architecture doc page.

Some Tradeoffs

This approach introduces its own constraints. Role management requires care. Schema design and RBAC need to be treated as first-class concerns. Some authorization logic moves closer to the data layer, which may be unfamiliar for teams used to handling everything in application code.

For many applications, traditional a shared-credential approach will remain the suitable choice. However, for systems where data-level security matters, and where the database already encodes meaningful access boundaries, this approach offers an interesting alternative.

Scheduled Tasks – YARA Post #8

Today I’ll share just a short snippet that I used to look for some specific scheduled tasks on a Windows system. Luckily windows creates XML files that are located somewhere like the C:\Windows\System32\Tasks folder. These files contain an XML representation of the scheduled tasks, and it is this that I am scanning with YARA.

Here’s a quick example of the rule:

// Detects the scheduled task on a Windows machine that runs Ransim
rule RansimTaskDetect : RansomwareScheduledTask {
    meta:
        author = "Ben Meadowcroft (@BenMeadowcroft)"
    strings:
        // Microsoft XML Task files are UTF-16 encoded so using wide strings
        $ransim = "ransim.ps1 -mode encrypt</Arguments>" ascii wide nocase
        $task1  = "<Task" ascii wide
        $task2  = "xmlns=\"http://schemas.microsoft.com/windows/2004/02/mit/task\">" ascii wide

    condition:
        all of them
}

The scheduled tasks runs a short PowerShell script that simulates some basic ransomware behavior, and this rule just matches the XML file for that task. This file is encoded in UTF-16, so the $task1 and $task2 strings simply reference some strings with the wide that are a part of the common strings found within the XML file (the start of the <Task element, and the XML namespace used to define the schema), the ascii wide modifiers searches for the string in both ascii and wide (double byte) form. The remaining string just looks for the invocation of the script as an argument to the task, and ignores the case used.

If I was looking for the presence of a task on live systems then I of course have other tools I could use, such as schtasks query. However, as I am often operating on the backups of a system being able to use this file based approach can be very helpful as it doesn’t rely on the availability of the primary system when I want to identify whether a scheduled task was present at some historical point in time.

Analyzing ZIP (OOXML) Files with YARA (part 3) – YARA Post #7

My prior posts about examining ZIP archives have covered matching the file names within a ZIP archive, as well as matching the pre-compression CRC values of the files within the archive. In this blog I am going to reference an interesting example of parsing the OOXML format used by modern Microsoft Office products. This office format essentially is a ZIP archive that contains certain files within it (describing the office document).

Aaron Stephens at Mandiant wrote a blog called “Detecting Embedded Content in OOXML Documents“. In that blog Aaron shared a few different techniques used to detect and cluster Microsoft Office documents. One of these examples was detecting a specific PNG file embedded within documents, the image was using to guide the user towards enabling macros. The presence of the image in this phishing doc could be used to indicate a clustering of these attacks.

Given the image files CRC, size, and that it was a png file, the author was able to create a YARA rule that would match if this image file was located within the OOXML document (essentially a ZIP archive). This rule approached the ZIP file a little differently than we have in my prior couple of blogs. The author skips looking for the ZIP file entry and references the CRC ($crc) and uncompressed file size ($ufs) hex strings directly to narrow down the match. They also checked if the file name field entry ended with the ".png" extension.

rule png_397ba1d0601558dfe34cd5aafaedd18e {
    meta:
        author = "Aaron Stephens <[email protected]>"
        description = "PNG in OOXML document."

    strings:
        $crc = {f8158b40}
        $ext = ".png"
        $ufs = {b42c0000}

    condition:
        $ufs at @crc[1] + 8 and $ext at @crc[1] + uint16(@crc[1] + 12) + 16 - 4
}

In this example the condition is using the @crc[1] as the base from which the offsets are calculated, unlike our prior examples where the offsets were based from the start of the local file header. The use of the at operator tests for the presence of the other strings at a specific offset (to the CRC value in this case).

An alternative approach to consider is using the wildcard character ? in the hex string, this allows us to match on the CRC and uncompressed file size fields together while skipping over the 4 bytes used to store the compressed file size field. Then validating that the four letter .png extension is at the end of the file name field.

rule png_alt {
    strings:
        $crc_ufs = {f8158b40 ???????? b42c0000}
        $ext = ".png"

    condition:
        $ext at @crc_ufs[1] + uint16(@crc_ufs[1] + 12) + 16 - 4
}

Analyzing ZIP Files with YARA (part 2) – YARA Post #6

In my first exploration of analyzing ZIP files with YARA I covered how we could create a YARA rule that matched on specific file names within the ZIP archive, in this post we’ll cover a few other fields that may be of interest.

One interesting example is looking for encrypted ZIP archives, here’s the Twitter post from Tyler McLellan that I read that showed how to do this with YARA:

Check if ZIP Archive is Encrypted with YARA – from @tylabs

This snippet is first checks if the file starts with a local file header record, uint16be(0) == 0x504B, it then tests whether the first bit in the “general purpose bit flag” is set by performing a bitwise and against the flag value (to get the value of the first bit) and seeing if that is set uint16(6) & 0x1 == 0x1. This first bit indicates whether the file is encrypted or not:

4.4.4 general purpose bit flag: (2 bytes)

Bit 0: If set, indicates that the file is encrypted.

section 4.4.4 of the .ZIP File Format Specification

Another interesting field in the ZIP Archive’s header is the CRC32 of the file. This is stored at offset 14 and is 4 bytes long. If you are looking for specific files then this can help narrow it down. It should be noted that the CRC is not a cryptographic calculation, but as it is stored in the header it is very simple to check for.

rule matchFileCRC {
   strings:
        $zip_header = {50 4B 03 04}
        $crc1 = {D8 39 6B 82}
        $crc2 = {B1 81 05 76}
        $crc3 = {C2 4A 02 94}
   condition:
        // iterate over local file zip headers
        for any i in (1..#zip_header):
        (
            // match any of the CRC32 values
            for any of ($crc*):
            (
                $ at @zip_header[i]+14
            )
        )
}

The line for any of ($crc*) checks all of the defined CRC values and the line $ at @zip_header[i]+14 causes the rule to be matched if any of the CRCs we are looking for are included in the ZIP header starting at offset 14.

Learning from Others – YARA Post #5

Taking a quick break from my Zip Archive adventures, one thing I’d be remiss not to mention is the community sharing that happens around YARA. As well as the specific YARA rules that people share, there are also a lot of insights into how to use YARA, how to craft or generate rules, and lots of other creative uses of the tool.

One example of this is the activity around #100DaysofYARA on Twitter last year that was kicked off by Greg Lesnewich. Looking through many of the tweets mentioning this Hashtag will certainly show some interesting possibilities in using YARA. I’d recommend following that hashtag on Twitter and Mastodon, seeing what comes up on January 1st 2023, and sharing your own experiments!

Reading ZIP (JAR) Files with YARA (part 1) – YARA Post #4

ZIP files have a well defined structure, this makes it possible to use YARA to match certain characteristics of files stored within the ZIP file. For example file name information is stored in both local file headers and in the central directory file headers within the Archive. Wikipedia has a decent write-up on the ZIP file format structure.

I first started experimenting with this to examine the contents of Java archives (JAR files, that are essentially ZIP archives) for specific files (in this case bundled log4j jars). To do this I first defined the marker for the local file header $zip_header = {50 4B 03 04} in the strings section, followed by the files I was interested in locating in the zip. See rule below:

rule vuln_log4j_jar_name_injar : log4j_vulnerable {
    strings:
        $zip_header = {50 4B 03 04}
        $a00 = "log4j-core-2.0-alpha1.jar"
        $a01 = "log4j-core-2.0-alpha2.jar"
        // …
        $a41 = "log4j-core-2.14.0.jar"
        $a42 = "log4j-core-2.14.1.jar"
            
    condition:
        // iterate over local file zip headers
        for any i in (1..#zip_header):
        (
            // match any of the file names
            for any of ($a*):
            (
                $ in (@zip_header[i]+30..@zip_header[i]+30+uint16(@zip_header[i]+26))
            )
        )
}

In the condition section we introduced some new capabilities. We iterate over the string matches for the $zip_header string. The variable #zip_header (note the #) gives us the count of the matches, for any i in (1..#zip_header):(…) iterates over the matches (populating i for each match), while the @zip_header[i] syntax (note the @) lets us reference the offset in the file for each match.

From the ZIP format specification we know that the file name is at offset 30 from the start of the local file header. The length of the file name is stored in the two bytes at offset 26. Given this information we can read the length of the file name using uint16(@zip_header[i]+26), and then read the file name beginning at offset 30 through to the offset specified in the file name length field and compare this to the file names we are looking for referenced by $a*.

In part 2 I’ll dig into some other interesting things we can look for in the zip headers.

XProtect & YARA – YARA Post #3

In yesterday’s post I covered an example of a YARA rule from AT&T’s Alien Labs used to detect payloads used by BlackCat Ransomware. Today I’ll take a quick look at one of the YARA rules used by Apple in XProtect to help protect macOS devices.

Apple’s XProtect uses YARA rules to deliver “signature-based detection and removal of malware” as described in their security guide Protecting against malware in macOS.

On my version of macOS these signatures are located in /Library/Apple/System/Library/CoreServices/XProtect.bundle/Contents/Resources/XProtect.yara . In this file Apple uses a technique called private rules to define reusable YARA rules that can be references in the conditions of other rules.

For example, one of these rules identifies Portable Executables:

private rule PE
{
    meta:
        description = "private rule to match PE binaries"

    condition:
        uint16(0) == 0x5a4d and uint32(uint32(0x3C)) == 0x4550
}

This rule is a little different from the condition uses in yesterday’s BlackCat rule that just used uint16(0) == 0x5A4D to identify the executable. Here the rule Apple are using does a couple of lookups to identify that this is a PE file using specific offsets defined in the PE file format specification:

After the MS-DOS stub, at the file offset specified at offset 0x3c, is a 4-byte signature that identifies the file as a PE format image file. This signature is “PE\0\0” (the letters “P” and “E” followed by two null bytes).

PE Format

If you are interested in seeing how Apple identifies MACH-0 files (executable format used by macOS and iOS) they also have a private rule to do that as well:

private rule Macho
{
    meta:
        description = "private rule to match Mach-O binaries"
    condition:
        uint32(0) == 0xfeedface or uint32(0) == 0xcefaedfe or uint32(0) == 0xfeedfacf or uint32(0) == 0xcffaedfe or uint32(0) == 0xcafebabe or uint32(0) == 0xbebafeca

}

These private rules can be used within the condition sections of the public rules that XProtect also uses. For example in the XProtect_MACOS_51f7dde rule we can see that the rules condition first references the Macho private rule, an upper bound on file size, and then the presence of multiple strings (that were defined using the Hexadecimal format in the strings section above):

rule XProtect_MACOS_51f7dde
{
    meta:
        description = "MACOS.51f7dde"
    strings:

        $a = { 63 6F 6D 2E 72 65 66 6F 67 2E 76 69 65 77 65 72 }
        $b = { 53 6D 6F 6B 65 43 6F 6E 74 72 6F 6C 6C 65 72 }
        $c1 = { 75 70 64 61 74 65 53 6D 6F 6B 65 53 74 61 74 75 73 }
        $c2 = { 70 61 75 73 65 53 6D 6F 6B 65 3A }
        $c3 = { 72 65 73 75 6D 65 53 6D 6F 6B 65 3A }
        $c4 = { 73 74 6F 70 53 6D 6F 6B 65 3A }
    condition:
        Macho and filesize < 2MB and all of them
}

This shows how commonly used elements of a rule can be defined separately and then composed with other conditions to simplify the resulting rules.

YARA BlackCat Payload Example – YARA Post #2

In my first post in this series, Day 1 of the 12 Days of YARA, I shared some resources that can help you to get started with YARA. YARA is a rules based language that allows for pattern matching against a file (or a processes memory). This is a useful tool that can describe a malware sample in a way can match on both a specific sample and (if properly generalized) on similar samples. A generalized rule may continue to match on related samples even if they change slightly from version to version, this is in contrast to cryptographic hash based approaches (SHA1, SHA256, MD5) to detection where minor changes in the malware create an entirely different hash value.

YARA rules are quite simple in terms of structure. The rule typically consists of the strings that will match file contents, and boolean conditions that determine whether the rule is matched or not. There’s a good overview of how to write a YARA rule in the documentation that it is worth reading if you are unfamiliar with how the rules are structured.

When I learn something I like to learn by reading the documentation as well as examples published by others. This helps me get a better sense of the idiomatic use of a language. Luckily there are lots of examples published that you can use to see different approaches people have taken.

In this post I’ll reference one of the YARA rules provided by AT&T Alien Labs in their post about BlackCat ransomware earlier this year:

rule BlackCat : WindowsMalware {
   meta:
      author = "AlienLabs"
      description = "Detects BlackCat payloads."
      SHA256 = "6660d0e87a142ab1bde4521d9c6f5e148490b05a57c71122e28280b35452e896"
    strings:
        $rust = "/rust/" ascii wide
        $a0 = "vssadmin.exe Delete Shadows /all /quietshadow" ascii
        $a1 = "bcdedit /set {default}bcdedit /set {default} recoveryenabled No" ascii wide
        $a2 = "Services\\LanmanServer\\Parameters /v MaxMpxCt /d 65535" ascii wide
        $a3 = ".onion/?access-key=${ACCESS_KEY}" ascii wide
        $b0 = "config_id" ascii
        $b1 = "public_key" ascii
        $b2 = "extension" ascii
        $b3 = "note_file_name" ascii
        $b4 = "enable_esxi_vm_kill" ascii
        $b5 = "enable_esxi_vm_snapshot_kill" ascii
    condition:
        uint16(0) == 0x5A4D and filesize < 5MB and $rust and 2 of ($a*) and 3 of ($b*)

}

Taking a few moments to examine this a few things begin to pop out.

First the meta section provides information about the origin of the rule, in this case information about the authorship, description, and the hash of the Windows BlackCat payload. While the hash in the metadata is not used in the rule itself, it is useful to be able to look up more information about the file on other sources (like VirusTotal).

Second, in the strings section the author has grouped related sets of strings together with the same prefix ($a,$b), when we look in the condition section we can see that YARA allows the use of wildcards to identify these different sets of strings and apply different conditional logic to them.

Third, in the condition section we can see how the author uses both the strings defined earlier, as well as some other conditional statements:

  • uint16(0) == 0x5A4D. The uint16(0) reads the unsigned 16 bit integer at the start of the file (offset 0) and compares it with 0x5A4D which is the magic number indicating this is an executable file on Windows. YARA does include a PE module that allows for fine-grained inspection of attributes of a portable executable files, but looking for specific markers at a known offset (as in this case) may be more efficient from an execution perspective if the rule doesn’t need that level of granularity.
  • filesize < 5MB. Here the authors include a filesize limit, this will help optimise the processing of very large files (above the 5MB specified) so that the scan can concentrate on the right set of files.
  • $rust and 2 of ($a*) and 3 of ($b*). Here the author uses the strings defined earlier. These sets of strings relate to characteristics that are less likely to change over time. For example, commands to delete shadow copies are a common characteristics of ransomware, so detecting strings related to that operation are less likely to change than strings related to non-core aspects of the malware.

Stay tuned for Day 3 tomorrow!