Posts Tagged ‘TABLE’

Oracle: Get database, tables and instances access permissions

Thursday, January 30th, 2014

Oracle user get permissions of table database and instances access
If you are logged in to Oracle SQL server with sqlplus and you're not sure to which Database, Tables, Object instances you have permissions to below 2 queries will be of use:

SQL> SELECT DISTINCT OWNER, OBJECT_NAME  FROM ALL_OBJECTS  WHERE OBJECT_TYPE = 'TABLE';

Query lists all queries in a Oracle table schema. Alternavite shorter way to do the query is via:

SQL> SELECT table_name FROM user_tables;

SQL> SELECT * FROM TAB;

Shows your own schema's all tables and views.

Other oracle useful query is the Oracle equivalent of MySQL SHOW TABLES;

SQL> SELECT table_name FROM user_tables;

It will also output info only for logged in user credentials, if you're logged in as oracle database administrator (DBA role) account and you would like to check what Instances are owned by any user lets say user GEORGI query should be;

SQL> SELECT DISTINCT OWNER, OBJECT_NAME  FROM ALL_OBJECTS  WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'GEORGI;

Other way to do it is via:

SQL> SELECT Table_Name from All_Tables WHERE OWNER = 'YOURSCHEMA';


How a monastery celebrates a brotherhood monk name day – the feast of (Saint Sergii from Radonezh) in Bulgarian Orthodox Church

Thursday, July 5th, 2012

Saint Sergii of Radonezh Orthodox icon

Now it is my 4th day being in Pomorie Monastery with Kimba. The monastic life is not so boring as I thought back in the days 🙂
Yesterday I was drived to the Kamenar pub, by one of the workers here in the monastery (with the monastic car an old Ford);
We went to the restaurant in Kamenar village with the blessing of the abbot has blessed that we go and enjoy ourselves for few hours there with a group of tourists staying for (1 or 2) weeks in the monastery.

The pub was quite a news for me as it was a nice looking place with a lounge and swimming pool, this seemed quite unusual for especially since it was located in a village with 200 or 300 hundred of people living 🙂
The village pub had even a billiard table, the coin price there was on the shocking 25 stotinki (0.13) euro cents! 🙂
In the pub came an orthodox priest dressed like a casual person and started singing some traditional old Bulgarian songs (typical for singing in the different regions in BG). The guy was really talented and his memory seem to be very strong, since he was able to sing by heart about 10 songs in a row !

Besides that the priest voice was very beautiful. When I later had a talk with the guy it appeared, he learned professional singing before he became an Orthodox priest some years ago.
What really shocked me when the priest started singing in Italian Luciano Pavaroti, he sang it so well so you can hardly find out if it is not really the real authentic Luciano voice 🙂

This priest and the overall people in the pub had a great fun, as the place and people in were quite spirited; Actually I felt in a while like being in Emil Kosturica's movie 🙂 🙂 🙂

Being over with yesterday I will say few words on my monastic experience today ….
The Church bell rang early in the morning to summit the monks for the early morning prayer followed by a Holy Liturgy served. The Holy Liturgy today served was a co-memoration of the feast of Saint Sergii of Radonezh.

Cause it is weekly day here there were not too many people in the monastic Church. The monastery monks were there,few other people and some Russian pilgrim woman. By the way I'm more and more being convinced that many of the Russian people are very pious oriented and have strong faith in God than us bulgarians. Sadly it appears (from my observations so far) Russians generally are richer in faith.
It is sad that Russian Orthodox Christianity is stronger than ours in Bulgarian, especially when we take the fact historically Russians have received the Orthodox Chrisitian faith and language from us Bulgarians …

One of the key figure (brothers) monks Father Sergii is having a name day here, so due to that and because it is not a fasting day today (Thursday), the traditional monastic dinner was bigger and more rich than usual. Some youghurt mixed with cucumbers (Tarator) as we call it in Bulgaria was served with a little salad a few pieces of Banica and even little meat balls (for people eating meat).
The Abbot of the monastery was not served meat as I heard from the cook lady he is completely abstaying from meat since about 10 years already; some of the monk brother didn't eat meat as well.
The dinner started about 12:20 in the usual dinner room. For another day the food in the monastery was tasting super-delicious.
It is rather interesting that the food here is more delicious than the food we usually eat at home; Probably the food is so delicious because large portion of the food on the table is monastic-grown and besides that a food blessing prayer is said everytime before eat time. Earlier times I've heard many others who visited and eat food in monasteries that the monastic food is tastier and more delicious than we ordinary people have on our tables, however I was sceptical until I experienced it myself, these days.
Cause the food is so tasteful, I consume here usually twice more food than I usually eat at home 🙂

I recommend anyone who didn't eat a meal in a monastery (yet) to try this at least once in a lifetime ;;;

How to fix “ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start”

Saturday, May 12th, 2012

After migrating databases data from FreeBSD MySQL 5.0.83 server to a Debian Squeeze Linux MySQL version 5.1.61, below is a mysql –version issued on both the FreeBSD and the Debian servers

freebsd# mysql --version
mysql Ver 14.12 Distrib 5.0.83, for portbld-freebsd7.2 (i386) using 5.2

debian:~# mysql --version
mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i486) using readline 6.1

The data SQL dump from the FreeBSD server was dumped with following command arguments:

freebsd# mysqldump --opt --allow-keywords --add-drop-table --all-databases -u root -p > complete_db_dump.sql

Then I used sftp to transfer complete_db_dump.sql dump to the a brand new installed latest Debian Squeeze 6.0.2. The Debian server was installed using a "clean Debian install" without graphical environment with CD downloaded from debian.org's site.

On the Debian machine I imported the dump with command:

debian:~# mysq -u root -p < complete_db_dump.sql

Right After the dump was imported I re-started SQL server which was previously installed with:

debian:~# apt-get install mysql-server
The error I got after restarting the mysql server:

debian:~# #/etc/init.d/mysql restart

was:

ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted

This error cost me a lot of nerves and searching in google to solve. It took me like half an hour of serious googling ,until I finally found the FIX!!!:

debian:~# mysql_upgrade -u root -h localhost -p --verbose --force
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost'
bible.holy_bible OK
bible.holybible OK
bible.quotes_meta OK

Afterwards finally I had to restart the mysql server once again in order to finally get rid of the shitty:

ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted error!

debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

This solved the insane Column count of mysql.proc is wrong. Expected 20, found 16 once and for all!

Before I came with this fix I tried all kind of forum suggested fixes like:

debian:~# mysql_upgrade -u root -p
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.61, use --force if you still need to run mysql_upgrade

debian:~# mysql_upgrade -p
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.61, use --force if you still need to run mysql_upgrade

And few more, none of them worked the only one that worked was:

debian:~# #mysql_upgrade -u root -h localhost -p --verbose --force

I have to say big thanks to Mats Lindth wonderful blog post which provided me with the solution.

It seems, since Oracle bought the Community edition of MySQL thinks with this database server are getting more and more messy and backwards incompatible day by day.
Lately, I'm experiencing too much hassles with MySQL version incompitabilities. Maybe I should think for migrating permanently to Postgre …

By the way the ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. is most probably caused of some kind of password hashing incompitability between the password hashing between the BSD and Debian SQL versions, as mysql -u root -p < dump.sql, does override default stored user passwords in the mysql database tables… Such password, hashing issues were common in prior MySQL 4 to MySQL 5 migrations I've done, however since MySQL 5+ is already storing its password strings encrypted with md5 encryption I wonder why on earth this mess happens ….
 

How to solve “Incorrect key file for table ‘/tmp/#sql_9315.MYI’; try to repair it” mysql start up error

Saturday, April 28th, 2012

When a server hard disk scape gets filled its common that Apache returns empty (no content) pages…
This just happened in one server I administer. To restore the normal server operation I freed some space by deleting old obsolete backups.
Actually the whole reasons for this mess was an enormous backup files, which on the last monthly backup overfilled the disk empty space.

Though, I freed about 400GB of space on the the root filesystem and on a first glimpse the system had plenty of free hard drive space, still restarting the MySQL server refused to start up properly and spit error:

Incorrect key file for table '/tmp/#sql_9315.MYI'; try to repair it" mysql start up error

Besides that there have been corrupted (crashed) tables, which reported next to above error.
Checking in /tmp/#sql_9315.MYI, I couldn't see any MYI – (MyISAM) format file. A quick google look up revealed that this error is caused by not enough disk space. This was puzzling as I can see both /var and / partitions had plenty of space so this shouldn't be a problem. Also manally creating the file /tmp/#sql_9315.MYI with:

server:~# touch /tmp/#sql_9315.MYI

Didn't help it, though the file created fine. Anyways a bit of a closer examination I've noticed a /tmp filesystem mounted besides with the other file system mounts ????
You can guess my great amazement to find this 1 Megabyte only /tmp filesystem hanging on the server mounted on the server.

I didn't mounted this 1 Megabyte filesystem, so it was either an intruder or some kind of "weird" bug…
I digged in Googling to see, if I can find more on the error and found actually the whole mess with this 1 mb mounted /tmp partition is caused by, just recently introduced Debian init script /etc/init.d/mountoverflowtmp.
It seems this script was introduced in Debian newer releases. mountoverflowtmp is some kind of emergency script, which is triggered in case if the root filesystem/ space gets filled.
The script has only two options:

# /etc/init.d/mountoverflowtmp
Usage: mountoverflowtmp [start|stop]

Once started what it does it remounts the /tmp to be 1 megabyte in size and stops its execution like it never run. Well maybe, the developers had something in mind with introducing this script I will not argue. What I should complain though is the script design is completely broken. Once the script gets "activated" and does its job. This 1MB mount stays like this, even if hard disk space is freed on the root partition – / ….

Hence to cope with this unhandy situation, once I had freed disk space on the root partition for some reason mountoverflowtmp stop option was not working,
So I had to initiate "hard" unmount:

server:~# mount -l /tmp

Also as I had a bunch of crashed tables and to fix them, also issued on each of the broken tables reported on /etc/init.d/mysql start start-up.

server:~# mysql -u root -p
mysql> use Database_Name;
mysql> repair table Table_Name extended;
....

Then to finally solve the stupid Incorrect key file for table '/tmp/#sql_XXYYZZ33444.MYI'; try to repair it error, I had to restart once again the SQL server:

Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
root@server:/etc/init.d#

Tadadadadam!, SQL now loads and works back as before!

Resolving “nf_conntrack: table full, dropping packet.” flood message in dmesg Linux kernel log

Wednesday, March 28th, 2012

nf_conntrack_table_full_dropping_packet
On many busy servers, you might encounter in /var/log/syslog or dmesg kernel log messages like

nf_conntrack: table full, dropping packet

to appear repeatingly:

[1737157.057528] nf_conntrack: table full, dropping packet.
[1737157.160357] nf_conntrack: table full, dropping packet.
[1737157.260534] nf_conntrack: table full, dropping packet.
[1737157.361837] nf_conntrack: table full, dropping packet.
[1737157.462305] nf_conntrack: table full, dropping packet.
[1737157.564270] nf_conntrack: table full, dropping packet.
[1737157.666836] nf_conntrack: table full, dropping packet.
[1737157.767348] nf_conntrack: table full, dropping packet.
[1737157.868338] nf_conntrack: table full, dropping packet.
[1737157.969828] nf_conntrack: table full, dropping packet.
[1737157.969928] nf_conntrack: table full, dropping packet
[1737157.989828] nf_conntrack: table full, dropping packet
[1737162.214084] __ratelimit: 83 callbacks suppressed

There are two type of servers, I've encountered this message on:

1. Xen OpenVZ / VPS (Virtual Private Servers)
2. ISPs – Internet Providers with heavy traffic NAT network routers
 

I. What is the meaning of nf_conntrack: table full dropping packet error message

In short, this message is received because the nf_conntrack kernel maximum number assigned value gets reached.
The common reason for that is a heavy traffic passing by the server or very often a DoS or DDoS (Distributed Denial of Service) attack. Sometimes encountering the err is a result of a bad server planning (incorrect data about expected traffic load by a company/companeis) or simply a sys admin error…

– Checking the current maximum nf_conntrack value assigned on host:

linux:~# cat /proc/sys/net/ipv4/netfilter/ip_conntrack_max
65536

– Alternative way to check the current kernel values for nf_conntrack is through:

linux:~# /sbin/sysctl -a|grep -i nf_conntrack_max
error: permission denied on key 'net.ipv4.route.flush'
net.netfilter.nf_conntrack_max = 65536
error: permission denied on key 'net.ipv6.route.flush'
net.nf_conntrack_max = 65536

– Check the current sysctl nf_conntrack active connections

To check present connection tracking opened on a system:

:

linux:~# /sbin/sysctl net.netfilter.nf_conntrack_count
net.netfilter.nf_conntrack_count = 12742

The shown connections are assigned dynamicly on each new succesful TCP / IP NAT-ted connection. Btw, on a systems that work normally without the dmesg log being flooded with the message, the output of lsmod is:

linux:~# /sbin/lsmod | egrep 'ip_tables|conntrack'
ip_tables 9899 1 iptable_filter
x_tables 14175 1 ip_tables

On servers which are encountering nf_conntrack: table full, dropping packet error, you can see, when issuing lsmod, extra modules related to nf_conntrack are shown as loaded:

linux:~# /sbin/lsmod | egrep 'ip_tables|conntrack'
nf_conntrack_ipv4 10346 3 iptable_nat,nf_nat
nf_conntrack 60975 4 ipt_MASQUERADE,iptable_nat,nf_nat,nf_conntrack_ipv4
nf_defrag_ipv4 1073 1 nf_conntrack_ipv4
ip_tables 9899 2 iptable_nat,iptable_filter
x_tables 14175 3 ipt_MASQUERADE,iptable_nat,ip_tables

 

II. Remove completely nf_conntrack support if it is not really necessery

It is a good practice to limit or try to omit completely use of any iptables NAT rules to prevent yourself from ending with flooding your kernel log with the messages and respectively stop your system from dropping connections.

Another option is to completely remove any modules related to nf_conntrack, iptables_nat and nf_nat.
To remove nf_conntrack support from the Linux kernel, if for instance the system is not used for Network Address Translation use:

/sbin/rmmod iptable_nat
/sbin/rmmod ipt_MASQUERADE
/sbin/rmmod rmmod nf_nat
/sbin/rmmod rmmod nf_conntrack_ipv4
/sbin/rmmod nf_conntrack
/sbin/rmmod nf_defrag_ipv4

Once the modules are removed, be sure to not use iptables -t nat .. rules. Even attempt to list, if there are any NAT related rules with iptables -t nat -L -n will force the kernel to load the nf_conntrack modules again.

Btw nf_conntrack: table full, dropping packet. message is observable across all GNU / Linux distributions, so this is not some kind of local distribution bug or Linux kernel (distro) customization.
 

III. Fixing the nf_conntrack … dropping packets error

– One temporary, fix if you need to keep your iptables NAT rules is:

linux:~# sysctl -w net.netfilter.nf_conntrack_max=131072

I say temporary, because raising the nf_conntrack_max doesn't guarantee, things will get smoothly from now on.
However on many not so heavily traffic loaded servers just raising the net.netfilter.nf_conntrack_max=131072 to a high enough value will be enough to resolve the hassle.

– Increasing the size of nf_conntrack hash-table

The Hash table hashsize value, which stores lists of conntrack-entries should be increased propertionally, whenever net.netfilter.nf_conntrack_max is raised.

linux:~# echo 32768 > /sys/module/nf_conntrack/parameters/hashsize
The rule to calculate the right value to set is:
hashsize = nf_conntrack_max / 4

– To permanently store the made changes ;a) put into /etc/sysctl.conf:

linux:~# echo 'net.netfilter.nf_conntrack_count = 131072' >> /etc/sysctl.conf
linux:~# /sbin/sysct -p

b) put in /etc/rc.local (before the exit 0 line):

echo 32768 > /sys/module/nf_conntrack/parameters/hashsize

Note: Be careful with this variable, according to my experience raising it to too high value (especially on XEN patched kernels) could freeze the system.
Also raising the value to a too high number can freeze a regular Linux server running on old hardware.

– For the diagnosis of nf_conntrack stuff there is ;

/proc/sys/net/netfilter kernel memory stored directory. There you can find some values dynamically stored which gives info concerning nf_conntrack operations in "real time":

linux:~# cd /proc/sys/net/netfilter
linux:/proc/sys/net/netfilter# ls -al nf_log/

total 0
dr-xr-xr-x 0 root root 0 Mar 23 23:02 ./
dr-xr-xr-x 0 root root 0 Mar 23 23:02 ../
-rw-r--r-- 1 root root 0 Mar 23 23:02 0
-rw-r--r-- 1 root root 0 Mar 23 23:02 1
-rw-r--r-- 1 root root 0 Mar 23 23:02 10
-rw-r--r-- 1 root root 0 Mar 23 23:02 11
-rw-r--r-- 1 root root 0 Mar 23 23:02 12
-rw-r--r-- 1 root root 0 Mar 23 23:02 2
-rw-r--r-- 1 root root 0 Mar 23 23:02 3
-rw-r--r-- 1 root root 0 Mar 23 23:02 4
-rw-r--r-- 1 root root 0 Mar 23 23:02 5
-rw-r--r-- 1 root root 0 Mar 23 23:02 6
-rw-r--r-- 1 root root 0 Mar 23 23:02 7
-rw-r--r-- 1 root root 0 Mar 23 23:02 8
-rw-r--r-- 1 root root 0 Mar 23 23:02 9

 

IV. Decreasing other nf_conntrack NAT time-out values to prevent server against DoS attacks

Generally, the default value for nf_conntrack_* time-outs are (unnecessery) large.
Therefore, for large flows of traffic even if you increase nf_conntrack_max, still shorty you can get a nf_conntrack overflow table resulting in dropping server connections. To make this not happen, check and decrease the other nf_conntrack timeout connection tracking values:

linux:~# sysctl -a | grep conntrack | grep timeout
net.netfilter.nf_conntrack_generic_timeout = 600
net.netfilter.nf_conntrack_tcp_timeout_syn_sent = 120
net.netfilter.nf_conntrack_tcp_timeout_syn_recv = 60
net.netfilter.nf_conntrack_tcp_timeout_established = 432000
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
net.netfilter.nf_conntrack_tcp_timeout_last_ack = 30
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_close = 10
net.netfilter.nf_conntrack_tcp_timeout_max_retrans = 300
net.netfilter.nf_conntrack_tcp_timeout_unacknowledged = 300
net.netfilter.nf_conntrack_udp_timeout = 30
net.netfilter.nf_conntrack_udp_timeout_stream = 180
net.netfilter.nf_conntrack_icmp_timeout = 30
net.netfilter.nf_conntrack_events_retry_timeout = 15
net.ipv4.netfilter.ip_conntrack_generic_timeout = 600
net.ipv4.netfilter.ip_conntrack_tcp_timeout_syn_sent = 120
net.ipv4.netfilter.ip_conntrack_tcp_timeout_syn_sent2 = 120
net.ipv4.netfilter.ip_conntrack_tcp_timeout_syn_recv = 60
net.ipv4.netfilter.ip_conntrack_tcp_timeout_established = 432000
net.ipv4.netfilter.ip_conntrack_tcp_timeout_fin_wait = 120
net.ipv4.netfilter.ip_conntrack_tcp_timeout_close_wait = 60
net.ipv4.netfilter.ip_conntrack_tcp_timeout_last_ack = 30
net.ipv4.netfilter.ip_conntrack_tcp_timeout_time_wait = 120
net.ipv4.netfilter.ip_conntrack_tcp_timeout_close = 10
net.ipv4.netfilter.ip_conntrack_tcp_timeout_max_retrans = 300
net.ipv4.netfilter.ip_conntrack_udp_timeout = 30
net.ipv4.netfilter.ip_conntrack_udp_timeout_stream = 180
net.ipv4.netfilter.ip_conntrack_icmp_timeout = 30

All the timeouts are in seconds. net.netfilter.nf_conntrack_generic_timeout as you see is quite high – 600 secs = (10 minutes).
This kind of value means any NAT-ted connection not responding can stay hanging for 10 minutes!

The value net.netfilter.nf_conntrack_tcp_timeout_established = 432000 is quite high too (5 days!)
If this values, are not lowered the server will be an easy target for anyone who would like to flood it with excessive connections, once this happens the server will quick reach even the raised up value for net.nf_conntrack_max and the initial connection dropping will re-occur again …

With all said, to prevent the server from malicious users, situated behind the NAT plaguing you with Denial of Service attacks:

Lower net.ipv4.netfilter.ip_conntrack_generic_timeout to 60 – 120 seconds and net.ipv4.netfilter.ip_conntrack_tcp_timeout_established to stmh. like 54000

linux:~# sysctl -w net.ipv4.netfilter.ip_conntrack_generic_timeout = 120
linux:~# sysctl -w net.ipv4.netfilter.ip_conntrack_tcp_timeout_established = 54000

This timeout should work fine on the router without creating interruptions for regular NAT users. After changing the values and monitoring for at least few days make the changes permanent by adding them to /etc/sysctl.conf

linux:~# echo 'net.ipv4.netfilter.ip_conntrack_generic_timeout = 120' >> /etc/sysctl.conf
linux:~# echo 'net.ipv4.netfilter.ip_conntrack_tcp_timeout_established = 54000' >> /etc/sysctl.conf

How to add a new MySQL user to have INSERT,UPDATE,DELETE permissions to a Database

Tuesday, October 25th, 2011

I needed to add a newly created MySQL user with no access to any database with no special permissions (user is created from phpmyadmin) with some permissions to a specific database which is used for the operation of a website, here are the MySQL CLI client commands I issued to make it work:

# mysql -u root -p
mysql> GRANT ALL ON Sql_User_DB.* TO Sql_User@localhost;
mysql> FLUSH PRIVILEGES;

Where in the Example Sql_User_DB is my example database to which the user is granted access and my sample user is Sql_User .
Note that without FLUSH PRIVILEGES; new privileges might not be active. 

To test further if all is fine login with Sql_User and try to list database tables.

$ mysql -u Sql_User -p
password:
mysql> USE Sql_User_DB;
mysql> SHOW TABLES;
...

Cracking zip protected password files on GNU/Linux and FreeBSD

Wednesday, October 5th, 2011

Its not very common, but sometimes it happens you have to crack some downloaded file from thepiratebay.com or some other big torrent tracker. An example scenario would be downloading a huge words dictionary (a rainbow table) dictionary etc., which was protected by the author with a password and zipped.

Fortunately Mark Lehmann developed a software called fcrackzip which is capable of brute forcing zip protected file passwords straight on UNIX like operating systems (GNU/Linux, FreeBSD).

fcrackzip is available from package repositories on Debian and Ubuntu Linuces to install via apt:

linux:~# apt-get install frackzip
...

fcrackzip is also available on FreeBSD via the ports tree and can be installed with:

freebsd# cd /usr/ports/security/fcrackzip
freebsd# make install cleam

On Debian it’s worthy to have a quick look on the README file:

linux:~# cat /usr/share/doc/fcrackzip/READMESee fcrackzip.txt (which is derived from the manpage), or fcrackzip.html

There is a web page with more information at
http://lehmann.home.ml.org/fcrackzip.html or
http://www.goof.com/pcg/marc/fcrackzip.html

A sample password-protected .zip file is included as “noradi.zip”. It’s
password has 6 lower case characters, and fcrackzip will find it (and a
number of false positives) with

fcrackzip -b -c a -p aaaaaa ./noradi.zip

which will take between one and thirty minutes on typical machines.

To find out which of these passwords is the right one either try them out
or use the –use-unzip option.

Marc

Cracking the noradi.zip password protected sample file on my dual core 1.8 ghz box with 2gb, it took 30 seconds.

linux:~# time fcrackzip -u -b -c a -p aaaaaa noradi.zip

PASSWORD FOUND!!!!: pw == noradi

real 0m29.627s
user 0m29.530s
sys 0m0.064s

Of course the sample set password for noradi.zip is pretty trivial and with more complex passwords, sometimes cracking the password can take up to 30 minutes or an hour and it all depends on the specific case, but at least now we the free software users have a new tool in the growing arsenal of free software programs 😉

Here are the options passed on to the above fcrackzip command:

-uTry to decompress with the detected possible archive passwords using unzip (This is necessery to precisely find the archive password, otherwise it will just print out a number of possible matching archive passwords and you have to try each of the passwords one by one. Note that this option depends on a working unzip version installed.)

-c ainclude all charsets to be tried with the generated passwords

-bSelect brute force mode – Tries all possible combinations of letters specified

-p aaaaaainit-password string (Look up for a password between the password length 6 characters long)

FCrackZip is partly written in assembler and thus is generally works fast, to reduce the CPU load fcrackzip will put on the processor its also capable of using external words dictionary file by passing it the option:

-DThe file should be in a format one word per line and be preliminary alphabetically sorted with let’s say sort

Also fcrackzip supports parallel file brute force, for example if you have 10 zip files protected with passwords it can paralelly try to brute force the pwds.

As of time of writting frackzip reached version 1.0 and seems to be pretty stable. Happy cracking.
Just to make sure fcrackzip’s source is not lost somewhere in the line in the long future to come, I’ve created a fcrackzip download mirror here

How to make NAT enable hosts in a local network to access the internet, create port forwarding to local IPs behind the router using iptables

Tuesday, August 23rd, 2011

I’m bulding new iptables firewall on one Linux server. The Debian GNU/Linux is required to act as firewall do Network Adress Translation for a small network of office PCs as well as forward some of the inbound ports to hosts from the local network located behind the router.

The local network besides the router had an IP addressing in the class C network e.g. (192.168.1.1-255)

First I procceded and enabled the Network Address Translation via the Linux kernel variable:

linux:~# sysctl -w net.ipv4.ip_forward=1
linux:~# echo 'net.ipv4.ip_forward=1' >> /etc/sysctl.conf

Initially I even forgot to switch on the net.ipv4.ip_forward to 1 (by default this value is set to 0) – GNU/Linux’s default network behaviour is not predetermined to act as network router.
However, since I haven’t configured Network Address Translation for quite some time it completely slipped my mind!

Anyways next the actual iptables rule which makes NAT possible I used is:

linux:~# /sbin/iptables -t nat -A POSTROUTING -s 192.168.1.0/24 ! -d 192.168.1.0/24 -j SNAT --to-source xxx.xxx.xxx.xxx

Whether xxx.xxx.xxx.xxx is the External IP address assigned to the router on eth0

With this very simple rules now Network the local network is capable of accessing the Internet withotu problem.

It’s a good time to say that still many system administrators, still erroneously use MASQUERADE rules instead of SNAT .
IP MASQUERADING is an ancestry from ipchains and these days should be completely abandonded, especially where no often change of primary IP address to access the internet is made.
For dial-ups or other kind of networking, where the IP addresses are often changed still IP MASQUERADING might be a good idea though.

My next goal was to make the Linux router to do port forwarding of Traffic which arrives on port 80 to a IIS server assigned with a local IP address of 192.168.1.5
I did the webserver (port 80), port forwarding from IP xxx.xxx.xxx.xxx to 192.168.1.5 with the iptables rule:

linux:~# /sbin/iptables -t nat -A PREROUTING -d xxx.xxx.xxx.xxx/32 -p tcp -m tcp --dport 80 -j DNAT --to-destination 192.168.1.5:80

There was a requirement to do port forwarding for a Windows remote Desktop running on standard port 3389 from the router to the internal Windows IP address running the IIS webserver, however the company required me to only allow access to the rdesktop 3389 port to certain real IP addresses.
Initially I thought about using the above PREROUTING rule which makes the port redirection to the IIS server and only change port 80 to port 3389 , and then use filter table INPUT chain rules like:

/sbin/iptables -A INPUT -s xx1.xx2.xx3.xx4,1xx,2xx,3xx,4xx,xxx.xxx.xxx.xxx -p tcp -m tcp --dport 3389 -j ACCEPT/sbin/iptables -A INPUT -p tcp -m tcp --dport 3389 -j REJECT --reject-with icmp-port-unreachable
32

However this did not work out, so I decided to give a try to do the same within the filter table using the FORWARD chain, like so:

FORWARD/sbin/iptables -A FORWARD -p tcp -m tcp -s xx1.xx2.xx3.xx4,1xx,2xx,3xx,4xx,xxx.xxx.xxx.xxx -p tcp -m tcp --dport 3389 -j ACCEPT
/sbin/iptables -A FORWARD -p tcp -m tcp --dport 3389 -j REJECT --reject-with icmp-port-unreachable

Adding this rules did not added any filtering to the forwarded remote desktop port. I suspected that somehow probably my above PREROUTING nat rules are read before any other rules and therefore automatically allows any IP address to port fortward traffic.
I’ve checked the iptables documentation and it seems my guess was partially right.

When some kind of network traffic enters the iptables firewall it first goes through the PREROUTING channel and then the traffic flows in a certain order.
iptables packet flow diagram

The iptables network packets flow is clearly seen in above’s diagram a thorough looks gives a very good idea on how packet is being processed by iptables

Finally as I couldn’t think about a good solution on how to only filter the port redirected traffic, which always firstly entered in the POSTROUTING chain, I’ve consulted with the guys in irc.freenode.net in #Netfilter.

I’m quite thanksful as a guy nicknamed Olipro has given me a pretty good picture on the port forwarding POSTROUTING problem and has provided me with a very logical easy and great fix.
He suggested that I only do port forwarding for certain IP addresses instead of allowing all IP addresses and then lookup for a way to allow only some of them and filter the rest.

The iptables rule to restrict the incoming traffic to the remote desktop forwarded port 3389 to few only allowed IP addresses looks like so:

linux:~# /sbin/iptables -t nat -A PREROUTING -d xxx.xxx.xxx.xxx/32 -s xx1.xx2.xx3.xx4,1xx,2xx,3xx,4xx,xxx.xxx.xxx.xxx -p tcp -m tcp –dport 3389 -j DNAT –to-destination 192.168.1.5:3389

Now the three sample IPs passed xx1.xx2.xx3.xx4,1xx,2xx,3xx,4xx,xxx.xxx.xxx.xxx has added to port forward traffic on 3389 to 192.168.1.5

By the way I did not know that newer versions of iptables support passing by multiple IP addresses to the –source or –destination IP. This is really great feature I’ve learned from the good guys from #Netfilter. However one should be careful when using the multiple IPs with -s or -d, it’s really important that the passed consequent IPs has no space between the , delimiter.

Now that’s all my task is completed. All computerse inside the Network 192.168.1.1-255 on the Linux router freely can access the Internet, all IPs are also capable to access the IIS server located behind the NAT as well as only certain IPs are capable of accessing to the IIS remote desktop.
Hope the article helps somebody 😉

How to delete entries from routing table on Linux

Thursday, July 7th, 2011

Every now and then I had to tamper routing tables in Linux and every time I had to do it I forgot how I did it last time so finally I decided to put it on my blog and find how I can delete from Linux routing table easier

Deleting a record from a wrong routing table on Linux is a piece of cake basicly, here is an example:

linux:~# route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
192.168.0.1 0.0.0.0 255.255.255.0 U 0 0 0 eth0
169.254.0.0 0.0.0.0 255.255.0.0 U 0 0 0 eth0
0.0.0.0 192.168.0.1 0.0.0.0 UG 0 0 0 eth0

Let’s say this is the routing table and it’s incorrect as the routing through the node 169.254.0.0 should not be there.
Here is how the routing through 169.254.0.0 can be deleted:

linux:~# /sbin/route del -net 169.254.0.0 netmask 255.255.0.0

Now here is the difference after deletion:

linux:~# route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
192.168.0.1 0.0.0.0 255.255.255.0 U 0 0 0 eth0
0.0.0.0 192.168.0.1 0.0.0.0 UG 0 0 0 eth0

If you want to delete the default gateway just use 0.0.0.0, e.g. -net 0.0.0.0 .. in above cmd example.
This would delete default gateway record from routing table which by the way in routing tables is marked with the UG flag.
Cheers 😉

Great and Holy Friday / Friday of the Crucifix (Remembrance of the holy saving sufferings of the Lord Jesus Christ

Friday, April 22nd, 2011

Friday of the Crucifix of the Lord Christ

Today is the great and sad day for us Christians in which the Lord has been crucified.

It’s a day of a great spiritual sorrow for all the Orthodox Christian.
It’s also the day in which we do venerate the holy shroud (plashtenica) in which the Lord’s body has been wrapped, after it was removed from the life giving cross.

The priest takes the plashtenica and walks through the temple and afterwards we do venerate the holy plashtenica (The dead body of the saviour).

There is a local Orthodox Church tradition which I believe is very specific to the bulgarian orthodox Church.
The plashtenica is placed on a table, the holy gospel and the holy cross are placed by the priest over the plashtenica (the shroud), after which grouped in a line (first the children, then the man, followed by the woman) we the layman do kiss the holy gospel, the cross and the plashtenica showing evidently our love for Christ and his gospel and our respect for the Lord’s Great Cross sufferings. Further on the layman does crawl (under the table with the plashtenica).

child crawling under a table with plashtenica

plashtenica with the immaculate body of Christ

The crawling under the plashtenica in Bulgarian Orthodox Church is a very known tradition by bulgarian people.

The crawling under the table symbolically shows that we take participation in the Lord’s death.
As it’s written that we all who are in Christ are being death for the world after the Holy Baptism.

There are probably other reasons for which the Church has established the passing under of the holy plashtenica which I’m not aware of.

What is sad is that most people does not really understand the real symbolism behind the crossing below the plashtenica (crossing below the table).

Thus many people who know the feast of Great Friday do come to the church to cross below the plashtenica as an act of superstition, as they don’t really understand why they do it.
They simply interpret that crossing below the plashtenica would grant them “good health”, “a life success” or good fortune.

Many of those people who come to crawl under the table, are not a regular on other Church services (Holy Liturgies) and therefore completely miss even the basics of our Christian beliefs.

Many of those people who are not adept in faith, do come to the Church with the only goal “to crawl below the table” and leave the Church immediately after that …

It’s truly sad to see that especially when I know that we Bulgarians are Orthodox Christian nation.
An Orthodox Christian nation who is starting to forget Christianity …

Just to give you an idea on how people have left astray from Orthodox Christian faith I can tell you for sure that the regular Church goers who attend Holy Liturgies and have intermediate knowledge of Orthodox Christianity and Church order in Bulgaria are not more than 4% of all the Bulgarian population.

This means that probably no more than approximately 300 000 of Bulgarians are in a communion with our Bulgarian Orthodox Church and do regularly confess and take the sacraments.

Here is an Orthodox Singing of the core troparion for the day (in Greek):