此产品的文档集力求使用非歧视性语言。在本文档集中,非歧视性语言是指不隐含针对年龄、残障、性别、种族身份、族群身份、性取向、社会经济地位和交叉性的歧视的语言。由于产品软件的用户界面中使用的硬编码语言、基于 RFP 文档使用的语言或引用的第三方产品使用的语言,文档中可能无法确保完全使用非歧视性语言。 深入了解思科如何使用包容性语言。
思科采用人工翻译与机器翻译相结合的方式将此文档翻译成不同语言,希望全球的用户都能通过各自的语言得到支持性的内容。 请注意:即使是最好的机器翻译,其准确度也不及专业翻译人员的水平。 Cisco Systems, Inc. 对于翻译的准确性不承担任何责任,并建议您总是参考英文原始文档(已提供链接)。
本文档介绍如何配置思科身份服务引擎(ISE) 2.2以与MySQL开放式数据库连接(ODBC)外部源集成。本文档适用于将MySQL用作ISE身份验证和授权的外部身份源的设置。
Cisco 建议您了解以下主题:
本文档中的信息基于以下软件和硬件版本:
本文档中的信息都是基于特定实验室环境中的设备编写的。本文档中使用的所有设备最初均采用原始(默认)配置。如果您使用的是真实网络,请确保您已经了解所有命令的潜在影响。
ISE 2.2支持多个ODBC外部源,其中一个是MySQL。您可以使用ODBC作为外部身份源来验证用户和终端是否与Active Directory (AD)类似。ODBC身份源可用于身份库序列以及访客和发起人身份验证。
以下是ISE 2.2支持的列表数据库引擎:
在此配置示例中,终端使用无线适配器以便与无线网络关联。WLC上的无线局域网(WLAN)配置为通过ISE对用户进行身份验证。在ISE上,MySQL配置为外部身份库。下图说明了所使用的网络拓扑:
提供的MySQL配置是一个示例。Do not treat is a Cisco建议。
更新您的系统:
sudo apt-get update sudo apt-get upgrade
安装MySQL(在安装过程中,系统将提示您输入根用户的密码):
sudo apt-get install mysql-server
要访问MySQL数据库,请执行以下操作:
mysql -u root -p
创建数据库:
mysql>
mysql> CREATE DATABASE demo_db;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use demo_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
创建数据库用户并授予其权限:
mysql>
mysql> CREATE USER 'cisco' IDENTIFIED BY 'cisco';
mysql> GRANT USAGE ON *.* TO 'cisco'@'%';
mysql> GRANT ALL PRIVILEGES ON `demo_db`.* TO 'cisco'@'%';
mysql> GRANT SELECT ON *.* TO 'cisco'@'%';
创建用户表:
mysql>
mysql> CREATE TABLE ´users´ (
-> `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `username` varchar(50) NOT NULL,
-> `password` varchar(50) NOT NULL,
-> PRIMARY KEY (`user_id`),
-> UNIQUE KEY `username_UNIQUE` (`username`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
创建用户并将其添加到表中:
mysql>
mysql> INSERT INTO users
-> (user_id, username, password)
-> VALUES
-> (1, "alice", "Krakow123");
Query OK, 1 row affected (0.00 sec)
可以类似地添加其他用户并列出表内容(与用户一样,添加MAC地址以进行MAB身份验证-密码可以保留为空):
mysql>
mysql> select * from users;
+---------+----------+-----------+
| user_id | username | password |
+---------+----------+-----------+
| 1 | alice | Krakow123 |
| 2 | bob | Krakow123 |
| 3 | oscar | Krakow123 |
+---------+----------+-----------+
创建组表:
mysql>
mysql> CREATE TABLE `groups` (
-> `group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `groupname` varchar(50) NOT NULL,
-> PRIMARY KEY (`group_id`),
-> UNIQUE KEY `groupname_UNIQUE` (`groupname`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
创建组并将它们添加到表中:
mysql>
mysql> INSERT INTO groups
-> (group_id, groupname)
-> VALUES
-> (1, "everyone");
Query OK, 1 row affected (0.00 sec)
可以类似地添加其他组,并列出表格的内容:
mysql>
mysql> select * from groups;
+----------+------------+
| group_id | groupname |
+----------+------------+
| 3 | contractor |
| 2 | employee |
| 1 | everyone |
+----------+------------+
为用户和组之间的映射创建表
mysql>
mysql> CREATE TABLE `user_group` (
-> `user_id` int(10) unsigned NOT NULL,
-> `group_id` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`user_id`,`group_id`),
-> KEY `group_id` (`group_id`),
-> CONSTRAINT `user_group_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
-> ON DELETE CASCADE,
-> CONSTRAINT `user_group_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups`
-> (`group_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
填写用户和组之间的映射表
mysql>
mysql> INSERT INTO user_group
-> (user_id, group_id)
-> VALUES
-> (1, 1);
Query OK, 1 row affected (0.00 sec)
可以类似地添加其他映射并列出表的内容:
mysql>
mysql> select * from user_group;
+---------+----------+
| user_id | group_id |
+---------+----------+
| 1 | 1 |
| 2 | 1 |
| 1 | 2 |
| 2 | 3 |
+---------+----------+
4 rows in set (0.00 sec)
您必须配置所需的存储过程,以便根据ODBC身份源对用户进行身份验证。根据身份验证协议,过程执行的任务会有所不同。 ISE支持对ODBC外部存储执行三种不同类型的凭证检查。您需要为每种检查类型配置单独的存储过程。 ISE使用输入参数调用相应的存储过程并接收输出。数据库可以返回记录集或一组命名参数以响应ODBC查询。
其中每个过程都应使用分隔符进行定义,以便MySQL接受查询语法:
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `ISEGroups`(username varchar(64), OUT result INT)
begin
CASE username
WHEN '*' THEN
select distinct groupname from groups;
ELSE
select groupname from user_group
inner join users ON users.user_id = user_group.user_id
inner join groups ON groups.group_id = user_group.group_id
where users.username = username;
END CASE;
SET result = 0;
end //
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `ISEAuthUserPlainReturnsRecordset`(username varchar(64), password varchar(255))
begin
IF EXISTS (select * from users where users.username = username and users.password = password ) THEN
select 0,11,'This is a very good user, give him all access','no error';
ELSE
select 3, 0, 'odbc','ODBC Authen Error';
END IF;
end //
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `ISEFetchPasswordReturnsRecordset`(username varchar(64))
begin
IF EXISTS (select * from users where users.username = username) THEN
select 0,11,'This is a very good user, give him all access','no error',password from users where users.username = username;
ELSE
select 3, 0, 'odbc','ODBC Authen Error';
END IF;
end //
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `ISEUserLookupReturnsRecordset`(username varchar(64))
begin
IF EXISTS (select * from users where users.username = username) THEN
select 0,11,'This is a very good user, give him all access','no error';
ELSE
select 3, 0, 'odbc','ODBC Authen Error';
END IF;
end //
使用以下信息将MySQL与Cisco ISE集成。导航到管理>身份管理>外部身份源> ODBC,然后添加新存储:
使用运行MySQL数据库的Ubuntu的IP地址作为以下主机名/IP地址。指定数据库类型(在这种情况下,使用MySQL),并插入之前创建的数据库名称和数据库用户凭据:
指定在MySQL中创建的过程的名称-您需要小心使用MAC地址格式(在本例中,它被更改为不同的格式):
完成后,返回Connection选项卡并测试连接:
从MySQL获取属性,单击属性选项卡:
按相同方式获取组:
将ISE配置为对MySQL数据库中的用户进行身份验证和授权。导航到策略>身份验证和策略>授权:
测试了两个身份验证流程:PEAP-MSCHAPv2和MAB。Alice是MySQL上的员工组的一部分,Bob是承包商组的一部分:
要在ISE上启用调试,请导航到管理>系统>记录>调试日志配置,选择PSN节点并将odbc-id-store 组件的日志级别更改为调试:
要检查的日志- prrt-server.log和prrt-management.log。您可以直接从ISE的CLI跟踪它们:
vchrenek-ise22-1/admin# show logging application prrt-management.log tail
在用户bob的身份验证期间,ISE必须获取纯文本密码,并且以下存储过程被使用ISEFetchPasswordReturnsRecordset:
2017-02-18 14:13:37,565 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Fetch Plain Text Password. Username=bob, SessionID=0a3e94660000090658a8487f
2017-02-18 14:13:37,566 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.CustomerLog -:::- Write customer log message: 24861
2017-02-18 14:13:37,567 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - get connection
2017-02-18 14:13:37,567 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - use existing connection
2017-02-18 14:13:37,568 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - connections in use: 1
2017-02-18 14:13:37,568 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Fetch plain text password
2017-02-18 14:13:37,568 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Prepare stored procedure call, procname=ISEFetchPasswordReturnsRecordset
2017-02-18 14:13:37,568 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Using recordset to obtain stored procedure result values
2017-02-18 14:13:37,568 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.CustomerLog -:::- Write customer log message: 24855
2017-02-18 14:13:37,568 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Text: {call ISEFetchPasswordReturnsRecordset(?)}
2017-02-18 14:13:37,568 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Setup stored procedure input parameters, username=bob
2017-02-18 14:13:37,568 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Execute stored procedure call
2017-02-18 14:13:37,571 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Process stored procedure results
2017-02-18 14:13:37,571 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Obtain stored procedure results from recordset
2017-02-18 14:13:37,571 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Received result recordset, number of columns=5
2017-02-18 14:13:37,571 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Results successfully parsed from recordset
2017-02-18 14:13:37,572 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - release connection
2017-02-18 14:13:37,572 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - connections in use: 0
2017-02-18 14:13:37,572 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- Call to ODBC DB succeeded
2017-02-18 14:13:37,572 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.OdbcAuthResult -:::- Authentication result: code=0, Conection succeeded=false, odbcDbErrorString=no error, odbcStoredProcedureCustomerErrorString=null, accountInfo=This is a very good user, give him all access, group=11
因为ISE必须检查ODBC组分配,所以它必须检索组:
2017-02-18 14:13:37,572 DEBUG [Thread-493][] cisco.cpm.odbcidstore.impl.CustomerLog -:::- Write customer log message: 24862
2017-02-18 14:13:37,728 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user groups. Username=bob, SessionID=0a3e94660000090658a8487f
2017-02-18 14:13:37,728 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Fetch user groups. Username=bob, SessionID=0a3e94660000090658a8487f
2017-02-18 14:13:37,728 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.CustomerLog -:::- Write customer log message: 24869
2017-02-18 14:13:37,729 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - get connection
2017-02-18 14:13:37,729 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - use existing connection
2017-02-18 14:13:37,729 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - connections in use: 1
2017-02-18 14:13:37,729 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Fetch user groups
2017-02-18 14:13:37,729 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Prepare stored procedure call, procname=ISEGroups
2017-02-18 14:13:37,729 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Text: {call ISEGroups(?,?)}
2017-02-18 14:13:37,733 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Setup stored procedure input parameters, username=bob
2017-02-18 14:13:37,733 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Execute stored procedure call
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Process stored procedure results
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Received result recordset, total number of columns=1
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- According to column number expect multiple rows (vertical attributes/groups retured result)
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Fetched data: ExternalGroup=everyone
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Fetched data: ExternalGroup=contractor
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Results successfully parsed from recordset
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Result code indicates success
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - release connection
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - connections in use: 0
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- Call to ODBC DB succeeded
2017-02-18 14:13:37,740 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.CustomerLog -:::- Write customer log message: 24870
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user groups. Got groups...
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user groups. Got groups(0) = everyone
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user groups. Setting Internal groups(0) = everyone
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user groups. Got groups(1) = contractor
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user groups. Setting Internal groups(1) = contractor
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user groups. Username=bob, ExternalGroups=[everyone, contractor]
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Fetch user attributes. Username=bob, SessionID=0a3e94660000090658a8487f
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.CustomerLog -:::- Write customer log message: 24872
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - get connection
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - use existing connection
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - connections in use: 1
这同样适用于以下属性:
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Fetch user attributes
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Prepare stored procedure call, procname=ISEAttrsH
2017-02-18 14:13:37,741 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Text: {call ISEAttrsH(?,?)}
2017-02-18 14:13:37,745 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Setup stored procedure input parameters, username=bob
2017-02-18 14:13:37,746 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Execute stored procedure call
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Process stored procedure results
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Received result recordset, total number of columns=3
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- According to column number expect multiple columns (hotizontal attributes/groups retured result)
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Fetched data: eye_color=green
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Fetched data: floor=1
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Fetched data: is_certified=true
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Results successfully parsed from recordset
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnection -:::- Result code indicates success
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - release connection
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcConnectionPool -:::- OdbcConnectionPool - connections in use: 0
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- Call to ODBC DB succeeded
2017-02-18 14:13:37,749 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.CustomerLog -:::- Write customer log message: 24873
2017-02-18 14:13:37,750 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user attrs. Username=bob, Setting myODBC.eye_color to green
2017-02-18 14:13:37,750 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user attrs. Username=bob, Setting myODBC.floor to 1
2017-02-18 14:13:37,750 DEBUG [Thread-259][] cisco.cpm.odbcidstore.impl.OdbcIdStore -:::- ODBC ID Store Operation: Get all user attrs. Username=bob, Setting myODBC.is_certified to true
版本 | 发布日期 | 备注 |
---|---|---|
1.0 |
01-Apr-2017 |
初始版本 |