select relacl ,
'grant ' || substring(
case when charindex('r',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',select ' else '' end
||case when charindex('w',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',update ' else '' end
||case when charindex('a',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',insert ' else '' end
||case when charindex('d',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',delete ' else '' end
||case when charindex('R',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',rule ' else '' end
||case when charindex('x',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',references ' else '' end
||case when charindex('t',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',trigger ' else '' end
||case when charindex('X',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',execute ' else '' end
||case when charindex('U',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end
||case when charindex('C',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end
||case when charindex('T',split_part(split_part(array_to_string(relacl, '|'),pu.groname,2 ) ,'/',1)) > 0 then ',temporary ' else '' end
, 2,10000)
|| ' on '||namespace||'.'||item ||' to "'||pu.groname||'";' as grantsql
from
(SELECT
use.usename as subject,
nsp.nspname as namespace,
c.relname as item,
c.relkind as type,
use2.usename as owner,
c.relacl
FROM
pg_user use
cross join pg_class c
left join pg_namespace nsp on (c.relnamespace = nsp.oid)
left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
c.relowner = use.usesysid
and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY
subject, namespace, item
) join pg_group pu on array_to_string(relacl, '|') like '%'||pu.groname||'%'
where relacl is not null
and pu.groname='job_analyst'
order by 2;
Author: Vinay
Various Queries For Showing User Permissions To Objects
SELECT
u.usename,
s.schemaname,
has_schema_privilege (u.usename, s.schemaname, 'create') AS user_has_select_permission,
has_schema_privilege (u.usename, s.schemaname, 'usage') AS user_has_usage_permission
FROM
pg_user u
CROSS JOIN
(SELECT DISTINCT
schemaname
FROM
pg_tables) s
WHERE u.usename = 'tim'
AND s.schemaname = 'dbo';
SELECT
u.usename,
t.schemaname || '.' || t.tablename,
has_table_privilege (u.usename, t.tablename, 'select') AS user_has_select_permission,
has_table_privilege (u.usename, t.tablename, 'insert') AS user_has_insert_permission,
has_table_privilege (u.usename, t.tablename, 'update') AS user_has_update_permission,
has_table_privilege (u.usename, t.tablename, 'delete') AS user_has_delete_permission,
has_table_privilege (
u.usename,
t.tablename,
'references'
) AS user_has_references_permission
FROM
pg_user u
CROSS JOIN pg_tables t
WHERE u.usename = 'tim' -- AND t.tablename = 'myTableName'
AND t.schemaname = 'dbo';
SELECT
u.usename,
t.schemaname || '.' || t.tablename,
has_table_privilege (u.usename, t.tablename, 'select') AS user_has_select_permission,
has_table_privilege (u.usename, t.tablename, 'insert') AS user_has_insert_permission,
has_table_privilege (u.usename, t.tablename, 'update') AS user_has_update_permission,
has_table_privilege (u.usename, t.tablename, 'delete') AS user_has_delete_permission,
has_table_privilege (
u.usename,
t.tablename,
'references'
) AS user_has_references_permission
FROM
pg_user u
CROSS JOIN pg_tables t
WHERE -- u.usename = 'tim'
-- AND t.tablename = 'myTableName'
user_has_insert_permission = 't'
AND t.schemaname = 'dbo';
Detecting And Killing Duplicate Queries
Problem statement: A rogue application process is sending multiple copies of exactly the same query to the database so you end up with a ton of them
DBA charge: (a) write a query that checks for these, and (b) for my own purposes, a quick way to kill the duplicate offenders.
-- output just the thread IDs of the duplicates, keeping the oldest version intact
SELECT
DISTINCT(p2.id)
FROM
information_schema.processlist p1
INNER JOIN information_schema.processlist p2
ON p2.info = p1.info
AND p2.id > p1.id
AND p1.time > 300
AND p1.command IS NOT NULL;
-- returns actual executable KILL statements for the duplicates, suitable
-- for a mysql -N -B command line
SELECT DISTINCT
(CONCAT('KILL ', p2.id, ';')) AS killstatements
FROM
information_schema.processlist p1
INNER JOIN information_schema.processlist p2
ON p2.info = p1.info
AND p2.id > p1.id
AND p1.time > 300
AND p1.command IS NOT NULL;
Error In Executing Mysqldump For 8.0
In attempting to use the mysqldump utility provided with MySQL 8.x against a 5.7 server, you may receive the following error message:
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')
This is due to the INFORMATION_SCHEMA in 8.0 having more columns to support several new features in 8.x. This error can be silenced by adding the following parameter to the mysqldump command line:
--column-statistics=0
Mysqldump Syntax
Here is the standard mysqldump syntax I always use. This dumps routines and triggers, does so in the most expeditious manner possible, and adds DROP xxxxx statements before all CREATE statements, just in case. It also adds the FORCE parameter in case it runs into issues with views. The “xxxx” at the end can be either one schema or a space-delimited list of schemata.
mysqldump --add-drop-database --add-drop-table --triggers --routines --single-transaction --opt --quick --dump-date --force --databases xxxx > dumpfile.sql
Production dump timing:
[mysqladm@dbvrp83383:/mysbackupdd] time mysqldump --add-drop-database --add-drop-table --triggers --routines --single-transaction --opt --quick --dump-date --force --databases sms > sms.sql
real 5m47.223s
user 3m23.495s
sys 0m19.139s
In dumping from a 8.x server via a lower version mysqldump, add…
--column-statistics=0
…to avoid those odd stats errors.