Kylin使用数据库进行安全认证

Kylin官方提供的只有 ldap，testing，saml 三种认证方式。

ldap，搭建和使用比较麻烦，学习成本高。

smal，基于ldap。

testing，方式是基于内存来进行认证，在增加用户和修改用户都比较费劲。

扩展第四种方式进行认证，mysql，配置方式如下

步骤一，调整kylinSecurity.xml

    <beans profile="mysql"> <!-- 新增 mysql配置块 -->
        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
            <property name="username" value="kylin"/>
            <property name="password" value="123456"/>
            <property name="url" value="jdbc:mysql://hostname/kylin?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull"/>
        </bean>
        <bean id="kylinUserAuthProvider"
              class="org.apache.kylin.rest.security.KylinAuthenticationProvider">
            <constructor-arg>
                <bean class="org.springframework.security.authentication.dao.DaoAuthenticationProvider">
                    <property name="userDetailsService">
                        <bean class="org.springframework.security.provisioning.JdbcUserDetailsManager">
                            <property name="dataSource" ref="dataSource"/>
                            <property name="enableGroups" value="true"/>
                        </bean>
                    </property>
                    <property name="passwordEncoder" ref="passwordEncoder"/>
                </bean>
            </constructor-arg>
        </bean>
        <!-- user auth -->
        <bean id="passwordEncoder"
              class="org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder"/>
        <scr:authentication-manager alias="testingAuthenticationManager">
            <!-- do user ldap auth -->
            <scr:authentication-provider ref="kylinUserAuthProvider"></scr:authentication-provider>
        </scr:authentication-manager>
    </beans>

    <beans profile="testing,ldap,mysql"> <!-- 增加mysql -->
        <scr:http auto-config="true" use-expressions="true">
            <scr:csrf disabled="true"/>
            <scr:http-basic entry-point-ref="unauthorisedEntryPoint"/>
            <scr:intercept-url pattern="/api/user/authentication*/**" access="permitAll"/>
            <scr:intercept-url pattern="/api/query/runningQueries" access="hasRole('ROLE_ADMIN')"/>
            <scr:intercept-url pattern="/api/query/*/stop" access="hasRole('ROLE_ADMIN')"/>
            <scr:intercept-url pattern="/api/query*/**" access="isAuthenticated()"/>
            <scr:intercept-url pattern="/api/metadata*/**" access="isAuthenticated()"/>
            <scr:intercept-url pattern="/api/**/metrics" access="permitAll"/>
            <scr:intercept-url pattern="/api/cache*/**" access="permitAll"/>
            <scr:intercept-url pattern="/api/streaming_coordinator/**" access="permitAll" />
            <scr:intercept-url pattern="/api/service_discovery/state/is_active_job_node" access="permitAll"/>
            <scr:intercept-url pattern="/api/cubes/src/tables" access="hasAnyRole('ROLE_ANALYST')"/>
            <scr:intercept-url pattern="/api/cubes*/**" access="isAuthenticated()"/>
            <scr:intercept-url pattern="/api/models*/**" access="isAuthenticated()"/>
            <scr:intercept-url pattern="/api/streaming*/**" access="isAuthenticated()"/>
            <scr:intercept-url pattern="/api/job*/**" access="isAuthenticated()"/>
            <scr:intercept-url pattern="/api/admin/public_config" access="permitAll"/>
            <scr:intercept-url pattern="/api/projects" access="permitAll"/>
            <scr:intercept-url pattern="/api/admin*/**" access="hasRole('ROLE_ADMIN')"/>
            <scr:intercept-url pattern="/api/tables/**/snapshotLocalCache/**" access="permitAll"/>
            <scr:intercept-url pattern="/api/**" access="isAuthenticated()"/>
            <scr:form-login login-page="/login" />
            <scr:logout invalidate-session="true" delete-cookies="JSESSIONID" logout-url="/j_spring_security_logout" logout-success-url="/." />
            <scr:session-management session-fixation-protection="newSession"/>
        </scr:http>
    </beans>

步骤二：初始化数据库

找到部署包中的 dbSql/schema.sql 脚本，在数据库中执行

同步数据： 依次执行一下insert语句

-- 初始化user数据 -- 加密密码 kylin123456
INSERT INTO `users` VALUES ('admin', '$2a$10$A7JEISvM2GMDgg5fy4XrQOJQCkIPtlRKHBlMIaSV5Frmd0Tr/aFlG', '1');
INSERT INTO `users` VALUES ('modeler', '$2a$10$A7JEISvM2GMDgg5fy4XrQOJQCkIPtlRKHBlMIaSV5Frmd0Tr/aFlG', '1');
-- 初始化groups数据
INSERT INTO `groups` VALUES ('1', 'admin');
INSERT INTO `groups` VALUES ('2', 'modeler');
INSERT INTO `groups` VALUES ('3', 'analyst');
-- 初始化group_authorities数据
INSERT INTO `group_authorities` VALUES ('1', 'ROLE_ADMIN');
INSERT INTO `group_authorities` VALUES ('1', 'ROLE_MODELER');
INSERT INTO `group_authorities` VALUES ('1', 'ROLE_ANALYST');
INSERT INTO `group_authorities` VALUES ('2', 'ROLE_MODELER');
INSERT INTO `group_authorities` VALUES ('2', 'ROLE_ANALYST');
-- 初始化group user关系数据
INSERT INTO `group_members` VALUES ('1', 'admin', '1');
INSERT INTO `group_members` VALUES ('2', 'modeler', '2');

user密码初始化方法

import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;

/**
* @author zhangyw
* @date 2019/8/7 10:07
*/
public class GenUserPassword {

    public static void main(String[] args) {
        BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
        System.out.println(passwordEncoder.encode("kylin123456"));
        // $2a$10$A7JEISvM2GMDgg5fy4XrQOJQCkIPtlRKHBlMIaSV5Frmd0Tr/aFlG
    }
}

步骤三：下载mysql驱动包，添加到系统lib中，可放置在${KYLIN_HOME}/lib 或者 ${KYLIN_HOME}/tomcat/lib

步骤四：修改kylin.properties中的

kylin.security.profile=mysql

重启kylin即可，后续新增用户可直接在数据库中操作，或者单独开发个功能对kylin的权限进行管理

步骤一中的数据库相关配置，可以写在kylin.properties：

kylin.security.db.url=xxx
kylin.security.db.username=xxx
kylin.security.db.password=xxx

转载于:https://my.oschina.net/vill/blog/3084646