Couple Audit Queries

[Brandin]

Hello,

Here is a couple audit queries I have written over the past few months to keep an eye on your Database:

Page does not exist but permission_page_matches does

Code:

SELECT * FROM permission_page_matches WHERE page_id NOT IN (SELECT id FROM pages)

DELETE query:

Code:

DELETE FROM permission_page_matches WHERE page_id NOT IN (SELECT id FROM pages)

User does not exist but items still in

Code:

$table

Create a table (mine is called

Code:

crontables

), I also have an "active" column defining if the table is to be checked, and the

Code:

crontype

as I have multiple crons, you can call this UID (and use it for this audit and the next) or RDU (and use it for this audit only)
Run query to pull crontables:

Code:

SELECT * FROM crontables WHERE active = 1 AND crontype='uid'

Then run the RDU query:
<pre>

Code:

SELECT *
                    FROM  $crontable
                    WHERE  $crontable.user_id NOT IN (SELECT users.id FROM users)

</pre>



(you will have to do the conversion to PDO if you wish to use this)
DELETE query:

Code:

DELETE FROM $crontable WHERE $crontable.user_id NOT IN (SELECT users.id FROM users)

(you will have to convert to PDO if you wish to use this)

Missing User ID (make your user_id columns!)
Setup your crontables like above, and run the crontable query for

Code:

uid

Query:
<pre>

Code:

SELECT *
                    FROM  $crontable
                    WHERE  user_id IN ('',0)
                    OR  user_id IS NULL

</pre>

DELETE query:

Code:

DELETE FROM $crontable WHERE user_id IN ('',0) OR user_id IS NULL

(convert to PDO if you wish to use)

Duplicate Page Permissions
If you are like me, and assign certain pages only to 1 permission (and then assign each type of permission, e.g. user manager, system manager, user) then you can use this to find duplicate entries.

Code:

SELECT *FROM permission_page_matches GROUP BY page_id HAVING COUNT(*) > 1

Another modification I made to control this duplicate page permissions audit was to change the admin_page.php and the admin_permission.php pages so you can only assign one permission per page, see the changes below:

Code:

admin_page.php

Add your Count Query:

Code:

$countQ = $db->query("SELECT id, permission_id FROM permission_page_matches WHERE page_id = ? ",array($pageId));

Code:

$countCountQ = $countQ->count();

Change your Add Access panel (Around line 258):
https://hastebin.com/kolewiwovu.js
This will change the checkboxes under add to radios, so you can only select one, and once you add it, it will not allow you to add more, either outputting the maximum 1 is reached, or you have reached an illegal amount (greater than 1, less than 0 soemhow lol)

Code:

admin_permission.php

Change your Add Access To This Level paragraph around line 294 to:
https://hastebin.com/girezojedo.xml

This will disallow adding of any private pages that have a count of at least one in

Code:

permission_page_matches

I will add more audits as I make them!

I hope this helps some of you keep an eye on your database and UserSpice system!