CoGe/iPlant authentication update

Background

CoGe's backend is being updated to forward all authentication requests to iPlant's authentication system. This is to provide the following features:

• enable individual user accounts
• use iPlant's infrastructure to create and manage those accounts
• permit users to move seamlessly between iPlant and CoGe resources
• permit users to send to and retrieve data from their iPlant data store

Additional goals of having better management of individual user accounts are:

• user have fine-grain control of data
  • users can have private genomes and annotations
  • users can share genomes and annotations with other users
• users can save custom preferences for CoGe's tools
• users can save analyses
• users can save sets of genomic features
• users can save sets of genomes

Updates

Several new tables are required in the database to accommodate fine-grain data permissions for user accounts:

drop table user_group_data_connector;
drop table user_group;
drop table user_group_connector;
drop table role;
drop table role_permission_connector;
drop table permission;

create table user_group_data_connector (
      user_group_dataset_connector_id  int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      dataset_group_id  int(11) NULL,
      dataset_id   int(11) NULL,
      group_id    int(11) NULL
);

create table user_group (
      user_group_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name varchar(255),
      description longtext,
      role_id int(11) NOT NULL
);

create table user_group_connector (
      user_group_connector_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      user_id int(11) NOT NULL,
      user_group_id int(11) NOT NULL
);

create table role (
      role_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name varchar(255) NOT NULL,
      description longtext
);

create table role_permission_connector (
      role_permission_connector_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      role_id int(11) NOT NULL,
      permission_id int(11) NOT NULL

);
 
create table permission (
      permission_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name varchar(255) NOT NULL,
      description longtext
);