example jdbcRealm configured in shiro.ini

shiro.ini

[main]
ds = com.mysql.jdbc.Driver
ds.serverName = localhost
ds.user = user
ds.password = password
ds.databaseName = db_name
 
jdbcRealm = org.apache.shiro.realm.jdbc.JdbcRealm
jdbcRealm.dataSource = $ds
jdbcRealm.permissionsLookupEnabled = true
jdbcRealm.authenticationQuery = "SELECT password FROM users WHERE user_name = ?"
jdbcRealm.userRolesQuery = "SELECT role_name FROM user_rolesWHERE user_name = ?"
jdbcRealm.permissionsQuery = "SELECT permission FROM roles_permissions WHERE role_name = ?"

authc = org.apache.shiro.web.filter.authc.PassThruAuthenticationFilter
authc.loginUrl = /login/

[urls]
/login/ = authc 

be aware of that there should be a space between users_roles and WHERE in line 12

It's also worth noting that the " " surrounding the queries causes the string to be interpreted as a literal string and thus the ? gets lost for the prepared statements in the default JdbcRealm implementation. It will throw an error about index being out of range if you try to set any of the positions in the query (e.g. setString(1, "foo") will throw an error that 1 is out of range because there are 0 columns)

@ilkelma +1

I'd like to use MS SQL Server instead of MySQL. So what should I change for "ds = com.mysql.jdbc.Driver"? Thank you.

I have an issue where the connection to the oracle db never disconnects until i kill the java app.
have not been able to release the connection..any one has an idea?
here is my shiro.ini

ds = com.jolbox.bonecp.BoneCPDataSource
ds.driverClass = oracle.jdbc.driver.OracleDriver
ds.jdbcUrl = jdbc:oracle:thin:@localhost:1521/xe
#ds.connectionTimeoutInMs = 30000
ds.username = hr
ds.password = hr123
ds.connectionTestStatement = select 1 from dual
ds.idleMaxAgeInMinutes = 4

jdbcRealm = org.apache.shiro.realm.jdbc.JdbcRealm
jdbcRealm.dataSource = $ds
jdbcRealm.authenticationQuery = select password  from USERS where USERNAME = ?
securityManager.realms = $jdbcRealm

any idea? thank you.

fixed it by using ojdbc data source pool.

Is there a way to give encrypted value in ds.password = hr123? The plain text is a security concern. @gavinHuang @yanko @zhanzhan

Hey @suchitgupta01,
good question, I have not had the need for it, so I dont know but a quick search got me these, hope it helps!

secure-database-password-in-shiro-ini
shiro-command-line-hasher