Query goal: | Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
SELECT tp.grantee, tp.table_schema, tp.table_name, array_agg(tp.privilege_type::TEXT ORDER BY tp.privilege_type) AS privileges, Count(*) AS number_of_privileges FROM information_schema.table_privileges AS tp INNER JOIN information_schema.tables AS t ON t.table_schema=tp.table_schema AND t.table_name=tp.table_name WHERE t.table_type='BASE TABLE' AND tp.table_schema <>ALL (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND grantee <> ALL (SELECT tableowner FROM pg_catalog.pg_tables AS pt WHERE pt.schemaname=tp.table_schema AND pt.tablename=tp.table_name) GROUP BY tp.grantee, tp.table_schema, tp.table_name ORDER BY Count(*) DESC, grantee, table_schema, table_name; |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Security | Queries of this category provide information about the security measures. |
Reference |
---|
https://en.wikipedia.org/wiki/Principle_of_least_privilege |
https://www.postgresql.org/docs/current/ddl-priv.html |