JBoss5 security based on MySQL

Create database called jboss_db_security wity the follow structure:

-- Generated by MySQLWorkbench SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; CREATE SCHEMA IF NOT EXISTS `jboss_db_security` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `jboss_db_security`; -- ----------------------------------------------------- -- Table `jboss_db_security`.`usuario` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `jboss_db_security`.`usuario` ( `id` INT NOT NULL AUTO_INCREMENT , `user` VARCHAR(45) NOT NULL , `pass` VARCHAR(45) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `jboss_db_security`.`role` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `jboss_db_security`.`role` ( `id` INT NOT NULL AUTO_INCREMENT , `role` VARCHAR(45) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `jboss_db_security`.`usuario_role` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `jboss_db_security`.`usuario_role` ( `usuario_id` INT NOT NULL , `role_id` INT NOT NULL , PRIMARY KEY (`usuario_id`, `role_id`) , INDEX `fk_usuario_role_usuario` (`usuario_id` ASC) , INDEX `fk_usuario_role_role` (`role_id` ASC) , CONSTRAINT `fk_usuario_role_usuario` FOREIGN KEY (`usuario_id` ) REFERENCES `jboss_db_security`.`usuario` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_usuario_role_role` FOREIGN KEY (`role_id` ) REFERENCES `jboss_db_security`.`role` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION); USE `jboss_db_security`; -- ----------------------------------------------------- -- Data for table `jboss_db_security`.`usuario` -- ----------------------------------------------------- SET AUTOCOMMIT=0; INSERT INTO `usuario` (`id`, `user`, `pass`) VALUES (1, 'user1', 'pass1'); INSERT INTO `usuario` (`id`, `user`, `pass`) VALUES (2, 'user2', 'pass2'); COMMIT; -- ----------------------------------------------------- -- Data for table `jboss_db_security`.`role` -- ----------------------------------------------------- SET AUTOCOMMIT=0; INSERT INTO `role` (`id`, `role`) VALUES (1, 'administrador'); INSERT INTO `role` (`id`, `role`) VALUES (2, 'superusuario'); INSERT INTO `role` (`id`, `role`) VALUES (3, 'convidado'); COMMIT; -- ----------------------------------------------------- -- Data for table `jboss_db_security`.`usuario_role` -- ----------------------------------------------------- SET AUTOCOMMIT=0; INSERT INTO `usuario_role` (`usuario_id`, `role_id`) VALUES (1, 1); INSERT INTO `usuario_role` (`usuario_id`, `role_id`) VALUES (1, 2); INSERT INTO `usuario_role` (`usuario_id`, `role_id`) VALUES (2, 3); COMMIT; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Drop mysql-connector-java-5.1.6-bin.jar on folder: ${JBOSS_HOME}\server\default\lib\
Where ${JBOSS_HOME} is your JBoss root folder

Create file ${JBOSS_HOME}\server\default\deploy\mysql-ds.xml
With the following contents

<?xml version="1.0" encoding="UTF-8"?> <datasources> <local-tx-datasource> <jndi-name>MySqlDS</jndi-name> <connection-url>jdbc:mysql://127.0.0.1:3306/jboss_db_security</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>root</user-name> <password>fiap</password> <valid-connection-checker-class-name> org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker </valid-connection-checker-class-name> <metadata> <type-mapping>mySQL</type-mapping> </metadata> </local-tx-datasource> </datasources>

Testing the JDBC connection
Create a new web project Eg: test_jdbc and create the file index.jsp
With the following contents:

<%@page contentType="text/html" import="java.util.*,javax.naming.*,javax.sql.DataSource,java.sql.*"%> <% DataSource ds = null; Connection con = null; PreparedStatement pr = null; InitialContext ic; try { ic = new InitialContext(); ds = (DataSource) ic.lookup("java:/MySqlDS"); con = ds.getConnection(); pr = con.prepareStatement("SELECT USER, PASS FROM USUARIO"); ResultSet rs = pr.executeQuery(); while (rs.next()) { out.println("<br> " + rs.getString("USER") + " | " + rs.getString("PASS")); } rs.close(); pr.close(); } catch (Exception e) { out.println("Exception thrown " + e); } finally { if (con != null) { con.close(); } } %>

Deploy it on JBoss and open on your browser: http://localhost:8080/test_jdbc

You should get:
user1 | user1
user2 | pass2

Edit the file: ${JBOSS_HOME}\server\default\conf\login-config.xml and add the following node:

<application-policy name="test-policy"> <authentication> <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required"> <module-option name="dsJndiName">java:/MySqlDS</module-option> <module-option name="principalsQuery"> SELECT pass FROM usuario WHERE user = ? </module-option> <module-option name="rolesQuery"> SELECT r.role, 'Roles' FROM usuario_role ur INNER JOIN role r ON ur.role_id = r.id INNER JOIN usuario u ON ur.usuario_id = u.id WHERE u.user = ? </module-option> </login-module> </authentication> </application-policy>

References:

http://www.jboss.org/community/wiki/DatabaseServerLoginModule
JBoss5_Installation_And_Getting_Started_Guide.pdf

Multi browser instant preview with Notepad++ and Autohotkey

Every serious web designer should have as main concern that your page shall be seen exactly the same way on all main browsers and follow strictly the w3c's xhtml rules. But...Why?

An xhtml valid code is rendered a bit faster than a unclosed, unested, unmatched tag. This forces the browser a guess working job, try rendering the page correcly. In a mobile device with limited processing power, it becomes a bottleneck or even corrupt all layout that intended to be seen. Unfortunatelly only a low figure percentage of sites follow that rules.

I could'n leaving unmentioned the IE as a little hell for web designers due it's several rendering css bugs, problems with java script, ajax that do not follow w3c recommendations, lack of support for css rounded corners, svg that not implemented yet and god knows what else... In my projects I have two works, first I do a page that works on firefox, chrome and safari after that, as a second job, I have a hard time tweaking the css rules and rewrite javascript. All extra wasted time and effort just to get the page be presented (almost) the same way on IE.

In the other (good) side we have a neat combination: Firefox + developing aid plugins. With them, now things that most developers were afraid of, like debugging Java Script and ajax calls now became a much easier and pleasant job thanks a jobsaver plugin called Firebug. It is probably the best thing happened on client-side web developing in years. Now I can write a thousand-line javascript code with much more confidence and knowing that future improvements won't be a torture. It's not an overstatement saying that I wouldn't leave as much processing in the client side in my projects if this tool didn't exist. Probably I'd leave all processing, fields cheking, business rules and rendering on server's side with performance and response-time costs for the whole application. Not to mention the real time css editing with instant graphic feedback, loading time benchmark for each http call and several other improvements. My favorite plugin list in order of relevance are: Firebug, Html Validator, Xdebug Helper, YSlow, MeasureIt, FireSizer, UrlParams. Other general use plugins are: FireGestures, NoScript, VideoDownloader, QuickRestart, Show MyIP, Regular Expressions Tester, ServerSpy.

One of the most annoing thing in a WYSIWYG editor, is that the page never is rendered de same way on a real browser like is shown on a preview window. Besides, the generated code is fat, with several redundant style rules and identation not respected. I just use them for an aid to create complex tables or when I forget the sintax of this or that tag. After, the html generated is paste in a text-editor where I have the precise control over css rules and can change, for instance, a id label in html and css in just one command, instead of navigating on menus and sub-menus on a visual application. Is a matter of personal taste. With a text editor, you know what you doing.

The major drawback on editing in a text editor is losing the preview feature. Would be great if it could be like "Matrix movie" where you looking at the code, should see "blondes and brunettes". Well, I only see the code! For that reason a preview feature is a highly desired feature, but how to join the "best of both worlds"? I needed a solution where I change something in the code and instantly that be presented on all oppened browsers at once.

After some researching, the response for that problem was a desktop automation tool called AutoHotKey. With a little effort of programming a simple script, you can, with 2-key press do that trick. In the code below the key combination is [Win] + [\] with can easly be replaced for any other arrangement or even triggered by a single key.

How to use: Just paste the code below in a text file, rename it for .ahk extension and drag and drop it on an Autohotkey shortcut. Open the same html file on notepad++ and one or more browsers. Just change something, type [Win] + [\] and see what happens.

Tips: The strings MozillaUIWindowClass, etc were got from the active window with Window Spy application bundled inside Autohotkey. OutputDebug messages can be viewed with DebugView.

;############################################################################### ; Save all unsaved documents, refresh all opened browsers and return to Notepad++ ; This code can be freely modified and redistributed ; Marcelo Gennari - Version 090808 ;############################################################################### #\:: ;conf vars varTextEditor = Notepad++ varBrowsers = MozillaUIWindowClass,Chrome_XPFrame,IEFrame,OpWindow,{1C03B488-D53B-4a81-97F8-754559640193} ; Firefox Chrome IE Opera Safari ;Salve all varTextEditorClass = ahk_class %varTextEditor% If WinActive(varTextEditorClass) { Send ^+s ;Ctrl + Shift + S = Save all } else { MsgBox, 0, Ops!, You must be on %varTextEditor% to get this script running, 5 } ;Refresh all opened browsers Loop, parse, varBrowsers, `, { varClasseBrowser = ahk_class %A_LoopField% if WinExist(varClasseBrowser) { OutputDebug, Refresh browser %A_LoopField% WinActivate, %varClasseBrowser% Send {F5} } else { OutputDebug, The browser %A_LoopField% is not running } } ;Return to Notepad++ WinActivate, %varTextEditorClass% return

Java connecting to MSSQL (Sql Server) thru JDBC

Download the JDBC Driver http://sourceforge.net/projects/jtds/

My choice was FreeTds. Aside fron being open source, the linux version has proved work pretty fine on an production enviroment so I can't find any other reason to avoid keeping using it in Java.
If you want more reasonings to choose it, download the zip file, open the documentation inside it. In the index.html go to the item "Why use jTDS?" There a lot more compelling reasons.

Start your project

It's assumed you already done you "Hello World" on eclipse IDE and already know now to add jar file on your project

Create an /lib folder on your project root folder and copy the file jtds-x.x.x.jar on it. Add to your build path

After preparing the environment, let's code:

package <yourPackage>; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class ConnTest { private static Connection cn = null; public static void main(String[] args) { try { // connect database Class.forName("net.sourceforge.jtds.jdbc.Driver"); cn = DriverManager .getConnection( "jdbc:jtds:sqlserver://<serverAddr>:1433/<database>;tds=8.0;lastupdatecount=true", "<username>", "<password>"); // prepare the query String sql = "<suaQuery>"; Statement stmt = cn.createStatement(); ResultSet rs = stmt.executeQuery(sql); // Print some registries while (rs.next()) { System.out.println(rs.getString(1)); } // close all and release db resources stmt.close(); rs.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { try { cn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

The code above is the shortest I could find. In a real project this would be separated in classes, DAOs according to best practices to guarantee reuse and maintanibility
The goal is set up an application running in the shortest time

Aspell plugin on Notepad++ portable - How To

I prefer use the portable version of Notepad++ so everything was aimed to keep that still running on an external HD.

Its assumed you already have Notepad++ with Spell-Checker plugin installed.

Download and Install the aspell full installer on http://aspell.net/win32/

Download and Install the desired dictionary (English for this example)

Test if aspell is working:
Open Command shell
type WIN + R then CMD on the input box
Go to aspell folder
> CD c:\program files\aspell\bin (or another if is your case)
Execute some aspell command
> aspell soundslike
> table
you should get
table TBL (TBL is the "sounds like" return of aspell)
Close the command shell

Do Notepad++ recognize aspell:
On Notepad++ installation dir, create the "aspell" folder and copy contents of installation folder on it. Your directory tree should be like that:

    Notepad++InstallFolder
      \aspell
      \bin
        aspell-15.dll
        aspell.exe
        pspell-15.dll
        word-list-compress.exe
      \data
        ASCII.dat
        dvorak.kbd
        en.dat
        en_phonet.dat
        iso8859-1.dat
        iso8859-10.dat
        iso8859-13.dat
        iso8859-14.dat
        iso8859-15.dat
        iso8859-2.dat
        iso8859-3.dat
        iso8859-4.dat
        iso8859-5.dat
        iso8859-6.dat
        iso8859-7.dat
        iso8859-8.dat
        iso8859-9.dat
        koi8-f.dat
        koi8-r.dat
        koi8-u.dat
        standard.kbd
        viscii.dat
      \dict
        american-w-accents.alias
        american.alias
        british-w-accents.alias
        british.alias
        canadian-w-accents.alias
        canadian.alias
        en-only.rws
        en.multi
        english.alias
        en_CA-only.rws
        en_CA-w-accents.multi
        en_CA.multi
        en_GB-only.rws
        en_GB-w-accents.multi
        en_GB.multi
        en_US-only.rws
        en_US-w-accents.multi
        en_US.multi  
      \plugins
        SpellChecker.dll
      \Config
        SpellChecker.ini
    

Edit the SpellChecker.ini file and change the path parameter to:
Relative Path=aspell\bin

Do NOT try to run the plugin yet!
UNINSTALL the original aspell otherwise the plugin will try reference it instead the Notepad++InstallFolder.

So..after everything done you should get everthing working!

To install additional modules:
Heres de tip: Use Winrar to extract files from .exe file.
After that, just copy the contents of \data and \dict folders and restart Notepad++