Month: June 2022

Show All Grants

Useful when migrating. Point at source DB instance, retrieve grants using this script, pipe through the Add a semicolon in PERL script (below), and voila, recreate grants on destination instance.

#!/bin/bash 
( 
 mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql 
) | while read user host 
do 
  echo "# $user @ $host"
  mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@'$host'"
done

Quickie to add a semicolon to end of non-commented lines (useful paired with the SHOW GRANTS bash script)

#!/usr/bin/perl --
open DATA, "$ARGV[0]";
while () {
  $liner=$_;
    if ("$liner" =~ /^#/) {
      print $liner;
    } else {
      chomp($liner);
      print "$liner" . ";n";
    }
 }
close DATA;

View group grants in Redshift

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;

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