Need help with a couple of MariaDB queries for a security audit

[kevinmillecam]

Hi folks; I need to generate two reports to get a better grip on my site’s security (your help is greatly appreciated):

1 - a list of my system’s roles and under each role, a list of permissions associated with that role.
2 - a list of my system’s users and under each user, a list of roles that user belongs to.

Here are the relevant tables:

MariaDB [xxxxx_prod]> describe role;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| rid    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(64)      | NO   | UNI |         |                |
| weight | int(11)          | NO   |     | 0       |                |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [xxxxx_prod]> describe role_permission;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| rid        | int(10) unsigned | NO   | PRI | NULL    |       |
| permission | varchar(128)     | NO   | PRI |         |       |
| module     | varchar(255)     | NO   |     |         |       |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)



MariaDB [xxxxx_prod]> describe users;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| uid              | int(10) unsigned | NO   | PRI | 0       |       |
| name             | varchar(60)      | NO   | UNI |         |       |
| pass             | varchar(128)     | NO   |     |         |       |
| mail             | varchar(254)     | YES  | MUL |         |       |
| theme            | varchar(255)     | NO   |     |         |       |
| signature        | varchar(255)     | NO   |     |         |       |
| signature_format | varchar(255)     | YES  |     | NULL    |       |
| created          | int(11)          | NO   | MUL | 0       |       |
| access           | int(11)          | NO   | MUL | 0       |       |
| login            | int(11)          | NO   |     | 0       |       |
| status           | tinyint(4)       | NO   |     | 0       |       |
| timezone         | varchar(32)      | YES  |     | NULL    |       |
| language         | varchar(12)      | NO   |     |         |       |
| picture          | int(11)          | NO   | MUL | 0       |       |
| init             | varchar(254)     | YES  |     |         |       |
| data             | longblob         | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

MariaDB [xxxxx_prod]> describe users_roles;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| uid   | int(10) unsigned | NO   | PRI | 0       |       |
| rid   | int(10) unsigned | NO   | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [xxxxx_prod]> describe role;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| rid    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(64)      | NO   | UNI |         |                |
| weight | int(11)          | NO   |     | 0       |                |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

[benanamen]

An actual SQL dump minus any sensitive data would be quite handy. I would suggest you PM it.

[kevinmillecam]

Thanks all; I was able to figure it out on my own. Here’s what I came up with:

SELECT r.rid, r.name AS 'role', u.uid, u.mail, DATE_FORMAT(FROM_UNIXTIME(u.access),'%b-%d-%Y') AS 'last visit'
FROM users_roles ur
LEFT JOIN users u
ON u.uid = ur.uid
LEFT JOIN role r
ON r.rid = ur.rid
WHERE r.rid NOT IN(1,2,93,283,621)
ORDER BY r.name, u.access DESC;

SELECT r.rid, r.name, rp.permission
FROM role_permission rp
LEFT JOIN role r
ON r.rid = rp.rid
WHERE r.rid != 23 # Exclude the administrators role.
ORDER BY r.name ASC, rp.permission ASC;

[benanamen]

That’s great. Nothing better in coding than the joy of figuring it out on your own. I bet you even learned something.