SQL join Solaris RBAC files

I'm studying for Sun Certified Solaris Administrator II exam and now at RBAC. The study guide seems to say there are foreign key relationships among 4 files.
  1. /etc/user_attr
  2. /etc/security/auth_attr
  3. /etc/security/prof_attr
  4. /etc/security/exec_attr
Good. I was just getting tired of reading PDF so now is a good time to use keyboard and to know what the relationship really is.

I knew from Java CAPS experience that hsqldb can load text files as tables. Chapter 9. SQL Syntax. So, I use hsql for this experiment.

  1. Remove comment line from RBAC definition files. Count # of fields.

    $ grep '\^[A-z]'  /etc/security/prof_attr >/tmp/prof.txt 
    $ grep '\^[A-z]' /etc/security/exec_attr >/tmp/exec.txt
    $ head -1 /tmp/{prof,exec}.txt
    ==> /tmp/prof.txt <==
    All:::Execute any command as the user or role:help=RtAll.html
    ==> /tmp/exec.txt <==
    All:suser:cmd:::\*:


  2. Start SqlTool. I used hsqldb.jar from Java CAPS directory.

    $ java -jar /opt/caps513/logicalhost/is/lib/install/applications/eventmanagement/hsqldb.jar \\
    --inlineRc URL=jdbc:hsqldb:file:/tmp/hsqltest\\;shutdown=true,USER=sa
    sa's password:
    JDBC Connection established to a HSQL Database Engine v. 1.8.0 database as 'SA'.
    SqlTool v. 1.55. (SqlFile processor v. 1.135)
    Distribution is permitted under the terms of the HSQLDB license.
    (c) 2004-2005 Blaine Simpson and the HSQLDB Development Group.


  3. Create 2 tables and source from text files.

    sql> create text table prof_attr (p1 varchar);
    sql> alter table prof_attr add p2 varchar;
    sql> :s/2/3/;
    Executing:
    alter table prof_attr add p3 varchar
    sql> :s/3/4/;
    Executing:
    alter table prof_attr add p4 varchar
    sql> :s/4/5/;
    Executing:
    alter table prof_attr add p5 varchar
    sql> create text table exec_attr (e1 varchar);
    [... add columns e2-e7 ...]
    sql> set table prof_attr source "prof.txt;fs=:"
    sql> select count(\*) from prof_attr;
    46
    sql> \\-1
    RESTORED following command to buffer. Enter ":?" to see buffer commands:
    set table prof_attr source "prof.txt;fs=:"
    sql> :s/prof/exec/g;
    Executing:
    set table exec_attr source "exec.txt;fs=:"
    sql> select count(\*) from exec_attr;
    279
    sql> \\!wc /tmp/exec.txt
    279 674 16826 /tmp/exec.txt

  4. Join


    sql> select p4,e6 from prof_attr p,exec_attr e where p.p1=e.e1 and p5 not like '%auths%';

    P4 E6
    ------------------------------------------------------ --------------------------
    Execute any command as the user or role \*
    Reliably observe any/all contract events /usr/bin/ctwatch
    Cryptographic Framework Administration /usr/bin/kmfcfg
    Cryptographic Framework Administration /usr/sbin/cryptoadm
    Cryptographic Framework Administration /usr/sfw/bin/CA.pl
    Cryptographic Framework Administration /usr/sfw/bin/openssl
    Manage the DAT configuration /usr/sbin/datadm
    Manage the FTP server /usr/sbin/ftpaddhost
    [...]
    Zones Virtual Application Environment Administration /usr/sbin/zoneadm
    Zones Virtual Application Environment Administration /usr/sbin/zonecfg

    81 rows
    sql>


TODO:
  1. Do the same in MySQL
  2. Do the same in Python or Perl. Does it require add-on?
  3. Output the join result in HTML without redundant data. I doubt this is possible only by pure SQL.
    • Can hsql do "CONNECT BY" ?
    • Can hsql do pivot ?
  4. Does JDBC driver exist for compiled NIS maps?
  5. Compare SqlTool functionalities with SQL\*Plus
Comments:

Good point, RBAC data is somewhat hierarchical (authorization naming is, and then profiles can include other profiles). So hierarchical queries are necessary (or else you need triggers to maintain fully expanded entries on every insert/update/delete). That's obnoxious. The fact that most name services (read: all today) don't provide a way to do what you want means that profile expansion (complete with loop detection) needs to be implemented outside any sort of data language like SQL. (Well, SQLite has a notion of external tables, but it's probably not worth it for your purposes, and besides, SQLite doesn't support hierarchical queries.)

Posted by Nico on May 12, 2008 at 05:26 PM JST #

Thanks for your insight.

Posted by Katsumi INOUE on June 02, 2008 at 10:49 AM JST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today