{"id":259,"date":"2010-08-11T19:04:46","date_gmt":"2010-08-11T23:04:46","guid":{"rendered":"http:\/\/www.xavignu.com\/?p=259"},"modified":"2010-09-25T06:05:15","modified_gmt":"2010-09-25T10:05:15","slug":"tomcat-mysql-realm","status":"publish","type":"post","link":"https:\/\/www.xavignu.com\/?p=259","title":{"rendered":"Tomcat MySQL realm"},"content":{"rendered":"

Let’s get started with another Tomcat<\/a> chapter in this blog. Tomcat comes by default with the UserDatabaseRealm<\/a> which is really crappy because it reads user info from tomcat-users.xml<\/a> file and a Tomcat restart is needed for the changes to take effect. This is not appropriate for a production environment so we are going to authenticate against a MySQL<\/a> database instead of the tomcat-users.xml file.
\n
\nWe should already have a MySQL database running. Now we create the authority database.<\/p>\n

\r\nCREATE DATABASE authority;\r\nUSE authority;\r\nCREATE TABLE users (\r\n\tuser_name varchar(20) NOT NULL PRIMARY KEY,\r\n\tpassword varchar(32) NOT NULL\r\n);\r\nCREATE TABLE roles (\r\n\trole_name varchar(20) NOT NULL PRIMARY KEY\r\n);\r\nCREATE TABLE users_roles (\r\n\tuser_name varchar(20) NOT NULL,\r\n\trole_name varchar(20) NOT NULL,\r\n\tPRIMARY KEY (user_name, role_name),\r\n\tCONSTRAINT tomcat_users_roles_foreign_key_1 FOREIGN KEY (user_name) REFERENCES users (user_name),\r\n\tCONSTRAINT tomcat_users_roles_foreign_key_2 FOREIGN KEY (role_name) REFERENCES roles (role_name)\r\n);\r\nINSERT INTO users (user_name, password) VALUES ('manager', 'manager');\r\nINSERT INTO users (user_name, password) VALUES ('tomcat', 'tomcat');\r\nINSERT INTO roles (role_name) VALUES ('role1');\r\nINSERT INTO roles (role_name) VALUES ('manager');\r\nINSERT INTO users_roles (user_name, role_name) VALUES ('tomcat', 'tomcat');\r\nINSERT INTO users_roles (user_name, role_name) VALUES ('manager', 'manager');\r\nCOMMIT;\r\n<\/pre>\n
Copy previous SQL code to a file named tomcat.sql. Now we create the authority database running the following.<\/p>\n
\r\n[17:55:42] xavi@NewYork:\/tmp $ mysql -u root -p < tomcat.sql \r\nEnter password: \r\n[17:59:23] xavi@NewYork:\/tmp $\r\n<\/pre>\n
Now we check the newly created database. Log in to database and check the fields we just entered.<\/p>\n
\r\nmysql> use authority;\r\nReading table information for completion of table and column names\r\nYou can turn off this feature to get a quicker startup with -A\r\n\r\nDatabase changed\r\nmysql> show tables;\r\n+---------------------+\r\n| Tables_in_authority |\r\n+---------------------+\r\n| roles               |\r\n| users               |\r\n| users_roles         |\r\n+---------------------+\r\n3 rows in set (0.00 sec)\r\n\r\nmysql> SELECT * FROM roles;\r\n+-----------+\r\n| role_name |\r\n+-----------+\r\n| manager   |\r\n| role1     |\r\n+-----------+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql> SELECT * FROM users;\r\n+-----------+----------+\r\n| user_name | password |\r\n+-----------+----------+\r\n| manager   | manager  |\r\n| tomcat    | tomcat   |\r\n+-----------+----------+\r\n2 rows in set (0.01 sec)\r\n\r\nmysql> SELECT * FROM users_roles;\r\n+-----------+-----------+\r\n| user_name | role_name |\r\n+-----------+-----------+\r\n| manager   | manager   |\r\n| tomcat    | tomcat    |\r\n+-----------+-----------+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql>\r\n<\/pre>\n
So now we have to create a user to run queries on the authority database.<\/p>\n
\r\n [18:26:08] xavi@NewYork:\/tmp $ mysql -u root -p\r\nEnter password: \r\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\r\nYour MySQL connection id is 62\r\nServer version: 5.1.49-1 (Debian)\r\n\r\nCopyright (c) 2000, 2010, Oracle and\/or its affiliates. All rights reserved.\r\nThis software comes with ABSOLUTELY NO WARRANTY. This is free software,\r\nand you are welcome to modify and redistribute it under the GPL v2 license\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> use mysql;\r\nReading table information for completion of table and column names\r\nYou can turn off this feature to get a quicker startup with -A\r\n\r\nDatabase changed\r\nmysql> CREATE USER 'realm_user'@'localhost' IDENTIFIED BY 'password';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> GRANT SELECT ON authority.* TO realm_user@localhost;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> \r\n<\/pre>\n
Lets download the driver that will connect Tomcat to the MySQL database. Download the driver from the following URL<\/a>. Unzip the driver and drop the .jar file in the $CATALINA_HOME\/common\/lib directory.
\nNow we need to modify server.xml to authenticate against the database and not against tomcat-users.xml file. Comment tomcat-users.xml entry and add the following.<\/p>\n
\r\n<Realm className=\"org.apache.catalina.realm.JDBCRealm\" debug=\"99\"\r\n      driverName=\"org.gjt.mm.mysql.Driver\"\r\n   connectionURL=\"jdbc:mysql:\/\/localhost\/authority?user=realm_user&amp;password=password\"\r\n       userTable=\"users\" userNameCol=\"user_name\" userCredCol=\"user_pass\"\r\n   userRoleTable=\"user_roles\" roleNameCol=\"role_name\"\/>\r\n<\/pre>\n
Now restart tomcat and you should be good to go.<\/p>\n","protected":false},"excerpt":{"rendered":"
Let’s get started with another Tomcat chapter in this blog. Tomcat comes by default with the UserDatabaseRealm which is really crappy because it reads user info from tomcat-users.xml file and a Tomcat restart is needed for the changes to take effect. This is not appropriate for a production environment so we are going to authenticate […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[3],"tags":[20,22,6,23,49,70],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/pTQgt-4b","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts\/259"}],"collection":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=259"}],"version-history":[{"count":0,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=\/wp\/v2\/posts\/259\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xavignu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}