Query Example

Here is a (greatly simplified) example of how one might answer a typical question: "What programs write to /etc/passwd, and why?"

Assume that we have tables named "file" and "action", created by SQL commands as found in Create Example.

The file table contains information on individual files, as described in File Schema:

    file_id | path_name       | ...
    -------------------------------
          1 | /etc/passwd     | ...
          2 | /usr/bin/chfn   | ...
          3 | /usr/bin/chsh   | ...
          4 | /usr/bin/passwd | ...
          5 | /usr/sbin/vipw  | ...
    ...

The action table contains information on actions that can take place by one file (i.e., by_id) and to another (i.e., to_id):

  by_id | action_type | on_id | purpose                  | ...
  ------------------------------------------------------------
      2 | writes      |     1 | change "full name", etc. | ...
      3 | writes      |     1 | change login shell       | ...
      4 | writes      |     1 | change password          | ...
      5 | writes      |     1 | edit password file       | ...
  ...

Here is a corresponding SQL query:

    SELECT file.path_name, action.purpose
    WHERE  action.by_id         = file.file_id
    AND     action.action_type  = 'writes'
    AND     action.on_id        =
       (SELECT file_id from file,
        WHERE  path_name = '/etc/passwd');

The output table might look something like:

    file.path_name  | action.purpose
    ------------------------------------------
    /usr/bin/chfn   | change "full name", etc.
    /usr/bin/chsh   | change login shell
    /usr/bin/passwd | change password
    /usr/sbin/vipw  | edit password file
    ...

Obviously, a browser could format this information as desired, as well as bringing in links to appropriate man pages, etc.

-- Main.RichMorin - 16 Jun 2003
Topic revision: r5 - 08 Jun 2003, WikiGuest
This site is powered by Foswiki Copyright © by the contributing authors. All material on this wiki is the property of the contributing authors.
Foswiki version v2.1.6, Release Foswiki-2.1.6, Plugin API version 2.4
Ideas, requests, problems regarding CFCL Wiki? Send us email