Table schema for Shiro JdbcRealm Authorization?

Question

I would like my app to use Apache Shiro for both authentication and authorization. I would like to start simple and have a relational database be the data source for both of these. This means I need to use the JdbcRealm.

After reading the docs, it is not clear to me what table schema is required for any "user tables", including any tables that link users to their permissions/roles.

So I ask: using Shiro's JdbcRealm, how/where do I link user's to their respective permissions/roles?

It can't be in the shiro.ini file because that is a static config file and wouldn't be feasible for connecting to a JDBC data source where users info is stored.

Answer 1

The JdbcRealm doesn't depend on a specific table schema: it uses some default queries which you can override (either by subclassing or by means of specific setter methods) in order to adapt it to your needs.

As stated in the javadoc, you can use the default queries as a base for building your own schema. Having a look at the source, you may start creating three basic tables: users, user_roles and roles_permissions.

protected static final String DEFAULT_AUTHENTICATION_QUERY = "select password from users where username = ?";

/**
* The default query used to retrieve account data for the user when {@link #saltStyle} is COLUMN.
*/
protected static final String DEFAULT_SALTED_AUTHENTICATION_QUERY = "select password, password_salt from users where username = ?";

/**
* The default query used to retrieve the roles that apply to a user.
*/
protected static final String DEFAULT_USER_ROLES_QUERY = "select role_name from user_roles where username = ?";

/**
* The default query used to retrieve permissions that apply to a particular role.
*/
protected static final String DEFAULT_PERMISSIONS_QUERY = "select permission from roles_permissions where role_name = ?";