Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
CREATE VIEW `user_attributes` AS select `u`.`user_identifier` AS `user_identifier`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.1`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.7'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.7`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.6'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.6`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.9'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.9`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.13'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.13`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.16'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.16`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.3'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.3`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.13'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.13`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.16.840.1.113730.3.1.241'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.16.840.1.113730.3.1.241`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.23'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.23`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.42'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.42`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.7'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.7`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.250.1.57'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.250.1.57`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.3'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.3`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.41'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.41`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.10'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.10`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.11'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.11`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.16'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.16`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.17'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.17`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.4'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.4`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.8'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.8`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.9'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.9`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.20'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.20`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.12'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.12`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.1`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.5.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.5.1.1` from (((`attr` `a` left join `a_type` `atype` on((`a`.`a_id` = `atype`.`a_id`))) left join `user` `u` on((`a`.`u_id` = `u`.`uid`))) left join `vo` `v` on((`a`.`v_id` = `v`.`vid`))) order by `atype`.`a_oid`

Implementation of AA with SimpleSAMLphp (plan)

We can start out with a stock simpleSAMLphp. Currently (20 June, 2016) this is version 1.14.4. : https://simplesamlphp.org/

...

Then ssp has to be configured to use the newly created module in config/config.php

Code Block

   authproc.aa = array(
       ...
       '60' => array(
            'class' => 'comanage:Comanage',
            'nameId_attribute_name' =>  'subject_nameid', // look at the aa authsource config
            'comanage_db_host' =>          '()',
			'comanage_db_port' =>          '()',
			'comanage_db_dbname' =>          '()',
			'comanage_db_user' =>          '()',
			'comanage_db_password' =>          '()'
       ),


Implementation of AA with SimpleSAMLphp v0.1 (based on plan above)

The database scheme in use is as follows:

Table and view definitions:

Code Block
CREATE TABLE `attr` (
  `attr_values_id` int(11) NOT NULL,
  `vo_id` int(11) NOT NULL,
  `u_id` int(11) NOT NULL,
  `a_id` int(11) NOT NULL,
  `a_val` varchar(2048) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `attribute_defs` (
  `a_id` int(11) NOT NULL,
  `a_name` varchar(256) DEFAULT NULL,
  `a_oid` varchar(256) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sp` (
  `sp_id` int(11) NOT NULL,
  `entity_id` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `spvo` (
  `spvo_id` int(11) NOT NULL,
  `sp_id` int(11) NOT NULL,
  `vo_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `u_id` int(11) NOT NULL,
  `u_identifier` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `vo` (
  `vo_id` int(11) NOT NULL,
  `vo_identifier` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The view that makes it possible to query everything by one SELECT statement

Code Block
DROP TABLE IF EXISTS `attributeValuesView`;
CREATE ALGORITHM=UNDEFINED DEFINER=`vopaas`@`localhost` SQL SECURITY DEFINER VIEW `attributeValuesView`  AS  select `attribute_defs`.`a_name` AS `attrName`,`attribute_defs`.`a_oid` AS `oid`,`attr`.`a_val` AS `value`,`user`.`u_identifier` AS `userNameId`,`sp`.`entity_id` AS `spEntityId`,`vo`.`vo_identifier` AS `vo` from (((((`attr` join `attribute_defs` on((`attr`.`a_id` = `attribute_defs`.`a_id`))) join `vo` on((`attr`.`vo_id` = `vo`.`vo_id`))) join `user` on((`attr`.`u_id` = `user`.`u_id`))) join `spvo` on((`vo`.`vo_id` = `spvo`.`vo_id`))) join `sp` on((`spvo`.`sp_id` = `sp`.`sp_id`))) ;

Indexes and auto_increment id declarations:

Code Block
ALTER TABLE `attr`
  ADD PRIMARY KEY (`attr_values_id`),
  ADD KEY `v_id` (`vo_id`,`u_id`);

ALTER TABLE `attribute_defs`
  ADD PRIMARY KEY (`a_id`);

ALTER TABLE `sp`
  ADD PRIMARY KEY (`sp_id`),
  ADD KEY `entity_id` (`entity_id`);

ALTER TABLE `spvo`
  ADD PRIMARY KEY (`spvo_id`);

ALTER TABLE `user`
  ADD PRIMARY KEY (`u_id`),
  ADD KEY `u_identifier` (`u_identifier`);

ALTER TABLE `vo`
  ADD PRIMARY KEY (`vo_id`);

ALTER TABLE `attr`
  MODIFY `attr_values_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
ALTER TABLE `sp`
  MODIFY `sp_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `spvo`
  MODIFY `spvo_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `user`
  MODIFY `u_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `vo`
  MODIFY `vo_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

How it is selected:

Code Block
SELECT oid,value FROM attributeValuesView WHERE userNameId=? AND spEntityId=?

GitLab link to the php code: https://dev.niif.hu/vopaas/vopaas-ssp-module

Ansible script for deployment:

Code Block
---
# Deploys the vopaas simplesamlphp module that is handling the database connection to vopaas database
# requires a simplesamlphp installation + niif simplesamlphp aa module
- hosts: vopaas-aa

  tasks:
  - name: download vopaas-module files
    unarchive: src=vopaas-ssp-module.tar.gz dest={{ssp_path}}/modules

  - name: enable vopaas-module in configuration
    blockinfile:
      dest: "{{ssp_path}}/config/config.php"
      # this line is chosen because it is before the authproc.idp segment
      insertafter: "shib13.signresponse"
      block: |
            'authproc.aa' => array(
                '60' => array(
                     'class' => 'vopaas:Vopaas',
                     'nameId_attribute_name' =>  'eduPersonPrincipalName',
                     'vopaas_db_host' => '{{vopaas_db_host}}',
                     'vopaas_db_port' => '{{vopaas_db_port}}',
                     'vopaas_db_dbname' => '{{vopaas_db_dbname}}',
                     'vopaas_db_user' => '{{vopaas_db_user}}',
                     'vopaas_db_password' => '{{vopaas_db_password}}'
                ),
                90 => 'core:LanguageAdaptor',
            ),