Category: Uncategorized

Add Extra IPs To A Server

1. Determine what existing range files exist:

# cd /etc/sysconfig/network-scripts/

# ls ifcfg-eth1-range*

You will see at least one file, possibly several. Find the highest number following the “range” and add one to it. This will be the new range number.

For example, if you see ifcfg-eth1-range0 and ifcfg-eth1-range1, your new range number will be “3”.

2. Determine the next available interface number (clone number).

# ifconfig | grep eth1

You will see a list of interfaces that looks like this:

eth1 Link encap:Ethernet HWaddr 00:08:74:A3:29:70
eth1:0 Link encap:Ethernet HWaddr 00:08:74:A3:29:70
eth1:1 Link encap:Ethernet HWaddr 00:08:74:A3:29:70
.
.
.
eth1:8 Link encap:Ethernet HWaddr 00:08:74:A3:29:70

Find the highest number after the “eth1:”. Add one to it and this your new clone number. In this case it would be 9.

3. create a range file for the new range number. (for this example, we will use range3)

# vi ifcfg-eth1-range3

4. Write the following lines to the range file. (replace the dummy ip information with your desired ip range and the CLONENUM_START value with the one calculated above)

IPADDR_START='123.0.0.1'
IPADDR_END='123.0.0.10'
CLONENUM_START='9'

5. Write and quit the range file, and restart your network.

# /etc/init.d/network restart

6. Your new ips should now be visible by running:

# ifconfig

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';