Find and Drop a user across multiple database servers

If you ever wished to find a user and drop them from multiple servers. Here is a one-liner:

Get-DbaRegServer -SqlInstance SQLSERVER01 -Group 'Production' | Get-DbaDbUser -User User1 | Remove-DbaDbUser -Force
Get-DbaRegServer -SqlInstance SQLSERVER01 -Group 'Production' | Get-DbaLogin -Login User1 | Remove-DbaLogin

The first line of the code remove user from all the databases. The second line drops the login from each SQL instance.

This is especially useful when you need to drop a user when they leave the company.

MySQL Classes And Tutorials

These are online, self-guided ways for developers wishing to gain more RDBMS understanding (or have a refresher course) in the two most ubiquitous platforms — MySQL and PostgreSQL.  I add and remove from this list from time to time as material becomes outdated or better bits are found.

Disclaimer:  I didn’t have a hand in creating any of these, but have recommended them in one format or another for several years.

All links open in a new tab.

TUTORIALS

MySQL for Developers
https://www.mysqltutorial.org/

MySQL in 7 Days
https://www.guru99.com/mysql-tutorial.html

Learn MySQL via TutorialsPoint (quite good, complete)
https://www.tutorialspoint.com/mysql/index.htm

Simple SQL Overview (beginners, from W3Schools)
https://www.w3schools.com/sql/

PostgreSQL Tutorial (also has you install it on your platform of choice)
https://www.postgresqltutorial.com/

MAINTENANCE TIPS

15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips
https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/

VIDEO TRAINING

MySQL Tutorial for Beginners [Full Course] (Programming with Mosh)
https://www.youtube.com/watch?v=7S_tz1z_5bA – (3h 10m)

MySQL Tutorial For Beginners (Intellipaat)
https://www.youtube.com/watch?v=WmGgxTpGs_8 – (2h 5m)

Learn PostgreSQL Tutorial – Full Course for Beginners
https://www.youtube.com/watch?v=qw–VYLpxG4 – (4h 20m)

Postgres for Beginners (Simplilearn)
https://www.youtube.com/watch?v=eMIxuk0nOkU – (1h 30m)

Setting up an SSH tunnel

This howto page will provide instructions on how to reach services running inside a firewall from outside of the network by using the Putty SSH Client and SSH Port Tunneling.

Requirements

Download Putty.exe.

Port Tunneling

Launch Putty. Different categories will be listed on the left side, click on Connection > SSH > Tunnels.

Under Add new forwarded port:, enter the following information:
Source port: [port on local machine]
Destination: [hostname of remote machine]:[port on remote machine]
Click Add.

It would look like this if I wanted to forward port 80 on the CCIS webserver to 8080 on my local machine:

Clicking Add will add it to the list of forwarded ports:

Connecting

After setting up the port tunnel, select Session from the category list on the left side.
Enter login.ccs.neu.edu in the Host Name (or IP Address) field and click the Open button at the bottom right.

Select Yes if prompted with this window:

Use your CCIS username and password when prompted to login and your port tunnel will be setup.

Utilizing the Port Tunnel

Now that the port is tunneled, you can connect to it using localhost:[port forwarded] where [port forwarded] is the local port you chose earlier.

In our previous example we forwarded port 80 on http://www.ccs.neu.edu to localhost:8080. We can now open up a web browser and browse to localhost:8080 to see it:

MSSQL Over an SSH Tunnel

The steps are practically the same as tunneling any other service, except the port you will tunnel is 1433. When connecting from MSSQL Management Studio, the connection host will be 127.0.0.1,[port you forwarded] . Notice the comma between the ip and the port number, this is very important. The following screens will show the proper setup:

And there you have it, you should now be able to SSH Tunnel to any service inside a firewall.

Tomcat 5/6/7/8 – How To Install An SSL Certificate

Tomcat–not my favorite.

General Steps

  1. Create a Key and Certificate Request
  2. Issue the Certificate from your favorite Registrar
  3. Merge the Certificate into a Tomcat File

Create a Key and Certificate Request

On your favorite Linux or Windows box, make sure you have OpenSSL.

I am making a directory called /home/keystore.  Seems fitting.

So:

mkdir /home/keystore

First we need a Private Key.  This is yours and yours alone.

So, the private key is critical.  It’s your unique identifier for this SSL cert.

Next, we need to generate the request to send to GoDaddy, InstantSSL, etc

Now, the command:

You are going to be prompted for all of the details as follows. For Wildcard, use *.mydomain.com. For other hosts, just use the hostname. ie mydomain.com (you will get www automatically)


Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:Texas
Locality Name (eg, city) [Default City]:Tim
Organization Name (eg, company) [Default Company Ltd]:My Domain Inc
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:*.mydomain.com
Email Address []:support@mydomain.com

Press Enter on the Extra fields, no password needed.

Ok, once finished, take your CSR and submit to your provider. Once you submit, you wait and then you will get your certificate. You may have to check email to approve it.

Now the Easy Part!

Merge the Certificate into a Tomcat File

If you are a GoDaddy Customer, you will get two files. Other providers might send you on a wild goose chase for the Bundle file.

6e00664a60ac4578.crt  - This is the Actual Certificate
gd_bundle-g2-g1.crt   - This is your Bundle file with all the certificate chain data from GoDaddy

For simplicity and understanding, let’s rename the file:

mv 6e00664a60ac4578.crt mydomain.crt

Now, let’s make the Tomcat keystore container

openssl pkcs12 -export -chain -CAfile gd_bundle-g2-g1.crt -in mydomain.crt -inkey private.key -out keystore.tomcat -name tomcat -passout pass:changeit

Ok, you have everything you need. Now, setup Tomcat.

Installing the Certificate in Tomcat

Let’s copy the file to our tomcat installation configuration directory.  My tomcat was in /usr/local/tomcat5

cp keystore.tomcat /usr/local/tomcat5/conf

Now, we need to enable SSL.  So, we need to edit the server-wide server.xml file.  Find the section like this:

<!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->
<!--
<Connector port="8443"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" disableUploadTimeout="true"
acceptCount="100" debug="0" scheme="https" secure="true"
clientAuth="false" sslProtocol="TLS" />
-->

Replace it.  Mine looks like this:


<!-- Define a SSL Coyote HTTP/1.1 Connector on port 8443 -->
<Connector port="443"
maxHttpHeaderSize="8192" maxThreads="250" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" disableUploadTimeout="true"
acceptCount="100" debug="0" scheme="https" secure="true" SSLEnabled="true"
clientAuth="false" sslProtocol="TLS" keyAlias="tomcat"
keystoreFile="/usr/local/tomcat5/conf/keystore.tomcat"
keystorePass="changeit"
keystoreType="PKCS12" />

Lastly, find any other references to port 8443 in the server.xml file and replace them with just 443.

Now, restart Tomcat and enjoy your newly functioning wildcard certificate.

I hope this bridges the gaps on some of the other articles out there.

View privileged objects in Redshift

SELECT
   namespace AS schemaname,
   item AS object,
   pu.groname AS groupname,
   DECODE(
      charindex (
         'r',
         split_part (
            split_part (
               array_to_string (relacl, '|'),
               pu.groname,
               2
            ),
            '/',
            1
         )
      ),
      0,
      0,
      1
   ) AS
   SELECT
      ,
      DECODE(
         charindex (
            'w',
            split_part (
               split_part (
                  array_to_string (relacl, '|'),
                  pu.groname,
                  2
               ),
               '/',
               1
            )
         ),
         0,
         0,
         1
      ) AS
      UPDATE
         ,
         DECODE(
            charindex (
               'a',
               split_part (
                  split_part (
                     array_to_string (relacl, '|'),
                     pu.groname,
                     2
                  ),
                  '/',
                  1
               )
            ),
            0,
            0,
            1
         ) AS
         INSERT,
         DECODE(
            charindex (
               'd',
               split_part (
                  split_part (
                     array_to_string (relacl, '|'),
                     pu.groname,
                     2
                  ),
                  '/',
                  1
               )
            ),
            0,
            0,
            1
         ) AS
         DELETE
         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 item = 'fa_analystbyregion'
               AND nsp.nspname NOT IN (
                  'pg_catalog',
                  'pg_toast',
                  'information_schema'
               ))
            JOIN pg_group pu
               ON array_to_string (relacl, '|') LIKE '%' || pu.groname || '%';