Apache Shiro Multi-tenancy with JDBC MySQL



0. GOALS


- Provide user authentication and authorization for multi-tenant scenarios

- Provide easy-to-maintain data storage with only one database

- Provide domain-differentiated, credential-based authentication

- Provide domain-differentiated, role-based, service-specific authorization;




1. QuickStart with Apache Shiro


- English Documentation

- Chinese Documentation

- Example Applications

- Architecture:


Shiro Architecture



2. Environment Prerequisites


Apache Maven 3.3.9 (bb52d8502b132ec0a5a3f4c09453c07478323dc5; 2015-11-10T17:41:47+01:00)
Maven home: /Library/Maven
Java version: 1.7.0_79, vendor: Oracle Corporation
Java home: /Library/Java/JavaVirtualMachines/jdk1.7.0_79.jdk/Contents/Home/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "mac os x", version: "10.11.2", arch: "x86_64", family: "mac"




3. Apache Shiro Maven Dependency


        <dependency>
            <groupId>org.apache.shiro</groupId>
            <artifactId>shiro-core</artifactId>
            <version>1.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.6.1</version>
            <scope>test</scope>
        </dependency>




4. MySQL QuickStart


1. Install MySQL on Mac

2. 修改Root密码

3. W3Schools SQL Tutorial

4. W3School中文SQL教程

5. RUNOOB MySQL教程




5. Create Tables


1.User Table:
user table


2.Domain_User Table:
domain user table

3.User_Role Table:
user role table

4.Permission_Domain_Role Table:
permission domain role table


6. JDBC Setup and Code Customization


1.Maven Dependency:

<dependency>  
    <groupId>mysql</groupId>  
    <artifactId>mysql-connector-java</artifactId>  
    <version>5.1.25</version>  
</dependency>  
<dependency>  
    <groupId>com.alibaba</groupId>  
    <artifactId>druid</artifactId>  
    <version>0.2.23</version>  
</dependency> 



2.Rewrite getPermissions method in jdbcRealm

protected Set<String> getPermissions(Connection conn, String username) throws SQLException {
    PreparedStatement ps = null;
    Set<String> permissions = new LinkedHashSet<String>();
    try {
        ps = conn.prepareStatement(permissionsQuery);

        ps.setString(1, username);

        ResultSet rs = null;

        try {
            // Execute query
            rs = ps.executeQuery();

            // Loop over results and add each returned role to a set
            while (rs.next()) {

                String permissionString = rs.getString(1);

                // Add the permission to the set of permissions
                permissions.add(permissionString);
            }
        } finally {
            JdbcUtils.closeResultSet(rs);
        }
    } finally {
        JdbcUtils.closeStatement(ps);
    }

    return permissions;
}


3.Add getUserDomain method in jdbcRealm

    public Set<String> getUserDomain(Connection conn, String username){

        PreparedStatement ps = null;
        Set<String> domains = new LinkedHashSet<>();
        try {
            ps = conn.prepareStatement(userDomainQuery);
            ps.setString(1, username);
            ResultSet rs = null;

            try {
                rs = ps.executeQuery();
                while (rs.next()) {
                    String domainID = rs.getString(1);
                    domains.add(domainID);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.closeResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeStatement(ps);
        }
        return domains;

    }


4.Rewrite doGetAuthenticationInfo method in jdbcRealm

protected AuthenticationInfo doGetAuthenticationInfo(AuthenticationToken token) throws AuthenticationException {

        VTNAuthNToken upToken = (VTNAuthNToken) token;
        String username = upToken.getUsername();
        String domainID = Integer.toString(upToken.getDomainId());
        // Null username is invalid
        if (username == null) {
            throw new AccountException("Null usernames are not allowed by this realm.");
        }

        Connection conn = null;
        SimpleAuthenticationInfo info = null;
        try {
            conn = dataSource.getConnection();
            Set<String> domains = getUserDomain(conn, username);
            if(!(domains.contains(domainID))){
                throw new AuthenticationException("Domain not found");
            }

            String password = null;
            String salt = null;
            switch (saltStyle) {
                case NO_SALT:
                    password = getPasswordForUser(conn, username)[0];
                    break;
                case CRYPT:
                    // TODO: separate password and hash from getPasswordForUser[0]
                    throw new ConfigurationException("Not implemented yet");
                    //break;
                case COLUMN:
                    String[] queryResults = getPasswordForUser(conn, username);
                    password = queryResults[0];
                    salt = queryResults[1];
                    break;
                case EXTERNAL:
                    password = getPasswordForUser(conn, username)[0];
                    salt = getSaltForUser(username);
            }

            if (password == null) {
                throw new UnknownAccountException("No account found for user [" + username + "]");
            }

            info = new SimpleAuthenticationInfo(username, password.toCharArray(), getName());

            if (salt != null) {
                info.setCredentialsSalt(ByteSource.Util.bytes(salt));
            }

        } catch (SQLException e) {
            final String message = "There was a SQL error while authenticating user [" + username + "]";
            if (log.isErrorEnabled()) {
                log.error(message, e);
            }

            // Rethrow any SQL errors as an authentication exception
            throw new AuthenticationException(message, e);
        } finally {
            JdbcUtils.closeConnection(conn);
        }

        return info;
    }




7. Setup Shiro.ini Configuration File


[main]
#authenticator
authenticator=aaa.authn.VTNAuthenticator   #(Customized)
authenticationStrategy=org.apache.shiro.authc.pam.AtLeastOneSuccessfulStrategy
authenticator.authenticationStrategy=$authenticationStrategy
securityManager.authenticator=$authenticator

#authorizer
authorizer=aaa.authz.VTNAuthorizer         #(Customized)
permissionResolver=org.apache.shiro.authz.permission.WildcardPermissionResolver
authorizer.permissionResolver=$permissionResolver
securityManager.authorizer=$authorizer

#Realm
jdbcRealm= aaa.realms.MySQLRealm           #(Customized)
dataSource=com.alibaba.druid.pool.DruidDataSource
dataSource.driverClassName=com.mysql.jdbc.Driver
dataSource.url=jdbc:mysql://localhost:3306/vtn
dataSource.username=root
jdbcRealm.dataSource=$dataSource
securityManager.realms=$jdbcRealm
jdbcRealm.permissionsLookupEnabled = true

#SQL Queries
jdbcRealm.authenticationQuery = SELECT password FROM user WHERE user_name = ?

jdbcRealm.userRolesQuery = SELECT role_id FROM user_role left join user using(user_id) WHERE user_name = ?

jdbcRealm.permissionsQuery = SELECT distinct permission_id FROM perm_domain_role left join domain_user using(domain_id) left join user using(user_id) WHERE (domain_id, role_id) IN ( SELECT domain_id, role_id From user left join user_role using(user_id) left join domain_user using(user_id) WHERE user_name = ?)


8. Tips about SQL permissionsQuery in INI file:



1. Query with same parameter at two places:

SELECT T.P

FROM (SELECT distinct permission_id as P, role_id AS R 
      FROM perm_domain_role 
           left join domain_user using(domain_id) 
           left join user using(user_id) 
      WHERE user_name= ?) AS T 

WHERE T.R 
  IN (SELECT role_id 
      FROM user_role 
           left join user using(user_id) 
      WHERE user_name = ?)


2.Optimization with only one parameter:

SELECT DISTINCT permission_id

FROM perm_domain_role 
     left join domain_user using(domain_id) 
     left join user using(user_id) 

WHERE (domain_id, role_id)
 IN (SELECT domain_id, role_id 
     FROM user 
          left join user_role using(user_id) 
          left join domain_user using(user_id) 
     WHERE user_name = ?)



9. Result:


Finally, you can easily test that each user has different services authorized by Shiro according to its tenant domain and its role.

for (VTNAuthNToken token: userTokenList) {
      Mappable userRequest = new MappableMsg(null,null,token);
      for (String service: servList){
            userRequest.setServID(service);
            if(IShiro.getInstance().isAuthorized(userRequest)){
                  String entry = "Domain "+token.getDomainId()+": "+token.getUsername()+": "+service;
                  authZResult.add(entry);
            }
      }
}
for(String i: authZResult){
      System.out.println(i);
}


Output:

Domain 1: admin: vtn:topo:create
Domain 1: admin: vtn:topo:read
Domain 1: admin: vtn:topo:update
Domain 1: admin: vtn:topo:delete
Domain 1: admin: system:vtn:create
Domain 1: admin: system:vtn:update
Domain 1: admin: system:vtn:delete
Domain 1: admin: serv:firewall:create
Domain 1: admin: serv:firewall:read
Domain 1: admin: serv:firewall:update
Domain 1: admin: serv:firewall:delete
Domain 1: boss: system:vtn:read
Domain 2: tenant1: vtn:topo:create
Domain 2: tenant1: vtn:topo:read
Domain 2: tenant1: vtn:topo:update
Domain 2: tenant1: vtn:topo:delete
Domain 2: tenant1: serv:firewall:create
Domain 2: tenant1: serv:firewall:read
Domain 2: tenant1: serv:firewall:update
Domain 2: tenant1: serv:firewall:delete
Domain 2: guest1: vtn:topo:read
Domain 2: guest1: serv:firewall:read
Domain 3: tenant2: vtn:topo:create
Domain 3: tenant2: vtn:topo:read
Domain 3: tenant2: vtn:topo:update
Domain 3: tenant2: vtn:topo:delete
Domain 3: guest2: vtn:topo:read


photo-zzz.png

Hao JiangJustin Time

We only live once, and time just goes by.

  • R&D Engineer @H3C
  • Hangzhou, China

  • Facebook
    微博
    Linkedin
    Github
    Email
    Instagram
    ResearchGate
    知乎
    OSChina