Shiro内置Realm之JdbcRealm

Shiro内置Realm

• IniRealm
• JdbcRealm

JdbcRealm

JdbcRealm的方式访问数据库，通过与数据库的连接，验证相应的登录用户与授权。

使用JdbcRealm默认的数据库sql语句

users表 user_role表
roles_permissions表

JdbcRealm中定义的sql语句

/**
     * The default query used to retrieve account data for the user.
     */
    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.
     */![myroles_permissions.jpg](https://img.haomeiwen.com/i10949367/2d590ef8ed90a55c.jpg?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

    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 = ?";

使用自定义的sql语句

myusersb表
myuser_roles表
myroles_permissions表

测试用例

package com.jarworker.test;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.mgt.DefaultSecurityManager;
import org.apache.shiro.realm.jdbc.JdbcRealm;
import org.apache.shiro.subject.Subject;
import org.junit.Before;
import org.junit.Test;
/**
 * 数据库名shrio
 * 使用JdbcRealm默认的三张表`users`{id,username,password},`user_roles`{id,username,role_name},`roles_permissions`{id,role_name,permission}
 *
 * 自定义的三张表`myusers`{id,username,password},`myuser_roles`{id,username,role_name},`myroles_permissions`{id,role_name,permission}
 *
 */
public class JdbcRealmTest {
    JdbcRealm jdbcRealm;

    /**
     * 初始化数据源
     * 设置JdbcRealm的数据源
     * @throws Exception
     */
    @Before
    public void addIniRealmTestUser() throws Exception {
        DruidDataSource dataSource=new DruidDataSource();
        {
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setUrl("jdbc:mysql://localhost:3306/shiro");
            dataSource.setUsername("root");
            dataSource.setPassword("root");
        }
        jdbcRealm=new JdbcRealm();
        jdbcRealm.setDataSource(dataSource);
        jdbcRealm.setPermissionsLookupEnabled(true);//默认为false,必须设置为true才能进行角色的授权
    }

    /**
     * 使用JdbcRealm默认的sql语句进行认证
     * @throws Exception
     */
    @Test
    public void testJdbcRealmAuthenticator() throws Exception {
        //构建DefaultSecurityManager 环境
        DefaultSecurityManager defaultSecurityManager = new DefaultSecurityManager();
        defaultSecurityManager.setRealm(jdbcRealm);
        //主体提交认证请求
        SecurityUtils.setSecurityManager(defaultSecurityManager);
        Subject subject = SecurityUtils.getSubject();
        UsernamePasswordToken token = new UsernamePasswordToken("jarworker","123");
        subject.login(token);
        System.out.println("是否认证："+subject.isAuthenticated());
    }

    /**
     * 自定义sql语句进行认证
     * @throws Exception
     */
    @Test
    public void testJdbcRealmAuthenticatorBySQL() throws Exception {
        //构建DefaultSecurityManager 环境
        DefaultSecurityManager defaultSecurityManager = new DefaultSecurityManager();
        defaultSecurityManager.setRealm(jdbcRealm);
        String usersSql="SELECT `password` FROM myusers WHERE username=?";
        jdbcRealm.setAuthenticationQuery(usersSql);
        //主体提交认证请求
        SecurityUtils.setSecurityManager(defaultSecurityManager);
        Subject subject = SecurityUtils.getSubject();
        UsernamePasswordToken token = new UsernamePasswordToken("jarworker1","123");
        subject.login(token);
        System.out.println("是否认证："+subject.isAuthenticated());
    }

    /**
     * 使用JdbcRealm默认的sql语句进行授权
     * @throws Exception
     */
    @Test
    public void testJdbcRealmAuthorizer() throws Exception {
        //构建DefaultSecurityManager 环境
        DefaultSecurityManager defaultSecurityManager = new DefaultSecurityManager();
        defaultSecurityManager.setRealm(jdbcRealm);
        //主体提交认证请求
        SecurityUtils.setSecurityManager(defaultSecurityManager);
        Subject subject = SecurityUtils.getSubject();
        UsernamePasswordToken token = new UsernamePasswordToken("jarjar","1234");
        subject.login(token);
        System.out.println("是否认证："+subject.isAuthenticated());
        subject.checkRoles("tourist");
        //必须设置JdbcRealm为jdbcRealm.setPermissionsLookupEnabled(true)
        subject.checkPermission("user:query");//是否拥有查询的权限
    }

    /**
     *自定义sql语句进行授权
     * @throws Exception
     */
    @Test
    public void testJdbcRealmAuthorizerBySQL() throws Exception {
        //构建DefaultSecurityManager 环境
        DefaultSecurityManager defaultSecurityManager = new DefaultSecurityManager();
        defaultSecurityManager.setRealm(jdbcRealm);
        String usersSql="SELECT `password` FROM myusers WHERE username=?";
        jdbcRealm.setAuthenticationQuery(usersSql);
        String rolesSql="select role_name from myuser_roles where username = ?";
        jdbcRealm.setUserRolesQuery(rolesSql);
        String permissionsSql="select permission from myroles_permissions where role_name = ?";
        jdbcRealm.setPermissionsQuery(permissionsSql);
        //主体提交认证请求
        SecurityUtils.setSecurityManager(defaultSecurityManager);
        Subject subject = SecurityUtils.getSubject();
        UsernamePasswordToken token = new UsernamePasswordToken("jarworker1","123");
        subject.login(token);
        System.out.println("是否认证："+subject.isAuthenticated());
        subject.checkRoles("admin");
        //必须设置JdbcRealm为jdbcRealm.setPermissionsLookupEnabled(true)
        subject.checkPermission("user:update");//是否拥有删除的权限
    }
}