Note to myself: do not forget iptables on AWS!

Last week I spent a few hours struggling against an Oracle database connectivity problem on a RedHat machine on AWS that almost made me jump from the window.

The database seemed OK but I was not able to remote connect to it.

To start with, the usual verifications:

 - Are Oracle env variables correctly configured?

 env | grep -i ora 

 - Is the DB up and running?

 ps -ef | grep -i pmon 

 - Are the listeners up and running?

 lsnrctl status
 netstat -ln

 - Can I connect to the DB?

 sqlplus <user>/<passwd>@<host>:<port>/<service>

Everything seemed fine but I was still not able to remote connect to the database. I could connect locally but not remotely.

So I checked my tnsnames.ora. I've had this problem before, the listener which listens only on localhost (127.0.0.1). That was not the problema now, though. The listener was correctly configured on tnsnames.ora.

Then I turn my attention to AWS security groups. Could it be it was blocking my incoming connections? I checked everything and it looked fine. But I still couldn't connect remotely. I changed a few permissions. No luck. Then I completely open the machine to the world. Still no luck.

I was almost filling a bug report on AWS security groups when I decided to look for local firewalls. Bingo! Iptables was the culprit. Problem solved simply flushing all iptables rules:

 sudo iptables -F

To make it persistent:

 sudo service iptables save

Lesson learned: even though it seems completely illogical to me to have iptables blocking connections on a AWS machine (which is already behind the security groups rules) some people do it and there are even some AMIs which come bundled that way. So, always check iptables (and/or other local firewalls) if your security groups rules seem correct but you can't connect to your instance!

Unlock Oracle DB account

To unlock a locked Oracle DB account:

1) Find the locked account:

  select username,account_status from dba_users where account_status like '%LOCK%';

2) Unlock the user:

  alter user <USER> account unlock;

In my case the server was a development database and locked accounts were just an unnecessary hassle since I don't need any security on this database. So, to avoid the account being locked again in the future I changed some parameters of the DEFAULT profile (the profile used by this user in my database):

1) Check the profile used by the user:

  select profile from dba_users where user=<USER>;

2) Change the parameters related to password expiration and failed login attempts:

 
 alter profile <PROFILE> limit FAILED_LOGIN_ATTEMPTS unlimited;

 alter profile <PROFILE> limit PASSWORD_LIFE_TIME unlimited;

 alter profile <PROFILE> limit PASSWORD_REUSE_TIME unlimited;

 alter profile <PROFILE> limit PASSWORD_REUSE_MAX unlimited;

 alter profile <PROFILE> limit PASSWORD_LOCK_TIME unlimited;

 alter profile <PROFILE> limit PASSWORD_GRACE_TIME unlimited;

Obs1: You must have ALTER PROFILE system privilege to change profile resource limits. To modify password limits and protection, you must have ALTER PROFILE and ALTER USER system privileges.

Obs2: Don't do this in your production server or any server where data loss is not acceptable! In such cases, follow Oracle security practices.

Good news, everyone! (aka articles/posts worth reading according to me)

Sometimes it seems impossible to find useful, creative or entertaining information in the huge pile of trash that the Internet has become. But there are plenty of good articles/posts out there. I've decided to post here some of the ones that caught my attention lately and I've decided to call this column "Good news, everyone!", in honor of professor Farnsworth :) (Futurama, in case you didn't get the reference).

Deep Learning in the Stock Market

https://medium.com/@TalPerry/deep-learning-the-stock-market-df853d139e02

A Mathematician's Secret: We're Not All Geniuses

https://www.bloomberg.com/view/articles/2017-06-07/a-mathematician-s-secret-we-re-not-all-geniuses

How to read and understand a scientific paper: a guide for non-scientists

http://blogs.lse.ac.uk/impactofsocialsciences/2016/05/09/how-to-read-and-understand-a-scientific-paper-a-guide-for-non-scientists/

How long should peer review take?

https://scientistseessquirrel.wordpress.com/2015/08/31/how-long-should-peer-review-take/

The Limits of the CAP Theorem

https://www.cockroachlabs.com/blog/limits-of-the-cap-theorem/

GitHub Secrets

https://github.com/blog/967-github-secrets

What papers should everyone read?

https://cstheory.stackexchange.com/questions/1168/what-papers-should-everyone-read

Learn to Read Code

https://kylesletten.com/2017/06/26/learn-to-read-code.html