### ------------------ HEADER FILE BEGIN ------------------ ###

## Script to fill the tables with default roles and permissions
## for Jyve
## Currently tested with MySQL only.
## If you have access to Oracle, I'd appreciate if you could add script
## for it.
##
## date created:  21. Jan. 2000
## last modified: 26. Apr. 2000 
## author:       Bernie Hoeneisen, ETH Zurich <bhoeneis@ee.ethz.ch>
##


## Clean up all old Role Permissions

# Delete all existing Visitor - Role assignments
delete from VisitorRole ;

# Delete all existing Roles
delete from UserRole ;

# Delete all existing Permissions
delete from Permission ;

# Delete all existing Role - Permissions assignments
delete from RolePermission ;


## Create Roles (or Groups)

# Role with all Permissions
insert into UserRole (ROLENAME) values ("root");

# Role with Permission to change Roles, Permissions and 
# other user information in the database
insert into UserRole (ROLENAME) values ("user_admin");

# Role with all Permissions from Jyve, but not from
# Turbine (add_/modify_user/group/permissions) 
insert into UserRole (ROLENAME) values ("admin");

# Role which can be assigned more Permsissions manually
# (e.g. add, modify, ... Permissions)
insert into UserRole (ROLENAME) values ("trusted_user");

# Default role for users with a vadid email address
# (e.g. add only Permission)
insert into UserRole (ROLENAME) values ("authenticated_user");

# Role with no special Permission (can only see the entries)
insert into UserRole (ROLENAME) values ("guest");


## Assign all confirmed Users the role of an 'authenticated_user'
## Do not assign, if password_value is emtpy or NULL or
## if loginid = 'Anonymous Guest'.
insert into VisitorRole ( VISITORID, ROLEID ) select Visitor.VISITORID, UserRole.RoleID from Visitor, UserRole where Visitor.confirm_value = 'CONFIRMED' AND Visitor.password_value != '' AND Visitor.password_value IS NOT NULL AND Visitor.loginid != "Anonymous Guest" AND UserRole.rolename = 'authenticated_user';

## Do assign the user 'Anonymous Guest' the role of a 'guest'
insert into VisitorRole ( VISITORID, ROLEID ) select Visitor.VISITORID, UserRole.RoleID from Visitor, UserRole where Visitor.loginid = "Anonymous Guest" AND UserRole.rolename = 'guest';


## add (turbine) user administration permissions
insert into Permission (PERMISSION) values ('view_user');
insert into Permission (PERMISSION) values ('add_user');
insert into Permission (PERMISSION) values ('modify_user');
insert into Permission (PERMISSION) values ('view_group');
insert into Permission (PERMISSION) values ('add_group');
insert into Permission (PERMISSION) values ('modify_group');
insert into Permission (PERMISSION) values ('view_permission');
insert into Permission (PERMISSION) values ('add_permission');
insert into Permission (PERMISSION) values ('modify_permission');
insert into Permission (PERMISSION) values ('view_role');
insert into Permission (PERMISSION) values ('add_role');
insert into Permission (PERMISSION) values ('modify_role');
 

## Assign (turbine) user administration permissions to the root role
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'view_user' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'add_user' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'modify_user' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'view_group' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'add_group' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'modify_group' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'view_permission' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'add_permission' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'modify_permission' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'view_role' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'add_role' and  UserRole.ROLENAME = 'root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'modify_role' and  UserRole.ROLENAME = 'root';


## Assign (turbine) user administration permissions to the user_admin role
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'view_user' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'add_user' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'modify_user' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'view_group' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'add_group' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'modify_group' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'view_permission' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'add_permission' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'modify_permission' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'view_role' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'add_role' and  UserRole.ROLENAME = 'user_admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION = 'modify_role' and  UserRole.ROLENAME = 'user_admin';

### ------------------ HEADER FILE END ------------------ ###

#root    : changeit! : root, user_admin
insert into Visitor (LOGINID, PASSWORD_VALUE, CONFIRM_VALUE, MODIFIED, CREATED ) values ('root', 'changeit!', 'CONFIRMED', null, null );
insert into VisitorRole ( VISITORID, ROLEID ) select Visitor.VISITORID, UserRole.RoleID from Visitor, UserRole where Visitor.loginid = 'root' AND UserRole.rolename = 'root';
insert into VisitorRole ( VISITORID, ROLEID ) select Visitor.VISITORID, UserRole.RoleID from Visitor, UserRole where Visitor.loginid = 'root' AND UserRole.rolename = 'user_admin';

#sheriff : changeit! : admin
insert into Visitor (LOGINID, PASSWORD_VALUE, CONFIRM_VALUE, MODIFIED, CREATED ) values ('sheriff', 'changeit!', 'CONFIRMED', null, null );
insert into VisitorRole ( VISITORID, ROLEID ) select Visitor.VISITORID, UserRole.RoleID from Visitor, UserRole where Visitor.loginid = 'sheriff' AND UserRole.rolename = 'admin';

#add_project:   root, admin, trusted_user
insert into Permission (PERMISSION) values ('add_project');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_project' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_project' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_project' and UserRole.ROLENAME='trusted_user';

#add_faq:       root, admin, trusted_user
insert into Permission (PERMISSION) values ('add_faq');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_faq' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_faq' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_faq' and UserRole.ROLENAME='trusted_user';

#add_topic:     root, admin, trusted_user
insert into Permission (PERMISSION) values ('add_topic');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_topic' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_topic' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_topic' and UserRole.ROLENAME='trusted_user';

#add_question:  root, admin, trusted_user
insert into Permission (PERMISSION) values ('add_question');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_question' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_question' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_question' and UserRole.ROLENAME='trusted_user';

#add_answer:    root, admin, trusted_user
insert into Permission (PERMISSION) values ('add_answer');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_answer' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_answer' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_answer' and UserRole.ROLENAME='trusted_user';

#add_unreleased_project:   root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('add_unreleased_project');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_project' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_project' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_project' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_project' and UserRole.ROLENAME='authenticated_user';

#add_unreleased_faq:       root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('add_unreleased_faq');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_faq' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_faq' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_faq' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_faq' and UserRole.ROLENAME='authenticated_user';

#add_unreleased_topic:     root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('add_unreleased_topic');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_topic' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_topic' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_topic' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_topic' and UserRole.ROLENAME='authenticated_user';

#add_unreleased_question:  root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('add_unreleased_question');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_question' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_question' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_question' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_question' and UserRole.ROLENAME='authenticated_user';

#add_unreleased_answer:    root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('add_unreleased_answer');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_answer' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_answer' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_answer' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='add_unreleased_answer' and UserRole.ROLENAME='authenticated_user';

#modify_project:   root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_project');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_project' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_project' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_project' and UserRole.ROLENAME='trusted_user';

#modify_faq:       root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_faq');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_faq' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_faq' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_faq' and UserRole.ROLENAME='trusted_user';

#modify_topic:     root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_topic');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_topic' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_topic' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_topic' and UserRole.ROLENAME='trusted_user';

#modify_question:  root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_question');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_question' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_question' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_question' and UserRole.ROLENAME='trusted_user';

#modify_answer:    root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_answer');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_answer' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_answer' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_answer' and UserRole.ROLENAME='trusted_user';

#modify_own_project:   root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_own_project');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_project' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_project' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_project' and UserRole.ROLENAME='trusted_user';

#modify_own_faq:       root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_own_faq');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_faq' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_faq' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_faq' and UserRole.ROLENAME='trusted_user';

#modify_own_topic:     root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_own_topic');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_topic' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_topic' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_topic' and UserRole.ROLENAME='trusted_user';

#modify_own_question:  root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_own_question');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_question' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_question' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_question' and UserRole.ROLENAME='trusted_user';

#modify_own_answer:    root, admin, trusted_user
insert into Permission (PERMISSION) values ('modify_own_answer');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_answer' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_answer' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_answer' and UserRole.ROLENAME='trusted_user';

#modify_own_unreleased_project:   root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('modify_own_unreleased_project');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_project' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_project' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_project' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_project' and UserRole.ROLENAME='authenticated_user';

#modify_own_unreleased_faq:       root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('modify_own_unreleased_faq');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_faq' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_faq' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_faq' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_faq' and UserRole.ROLENAME='authenticated_user';

#modify_own_unreleased_topic:     root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('modify_own_unreleased_topic');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_topic' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_topic' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_topic' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_topic' and UserRole.ROLENAME='authenticated_user';

#modify_own_unreleased_question:  root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('modify_own_unreleased_question');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_question' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_question' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_question' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_question' and UserRole.ROLENAME='authenticated_user';

#modify_own_unreleased_answer:    root, admin, trusted_user, authenticated_user
insert into Permission (PERMISSION) values ('modify_own_unreleased_answer');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_answer' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_answer' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_answer' and UserRole.ROLENAME='trusted_user';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='modify_own_unreleased_answer' and UserRole.ROLENAME='authenticated_user';

#see_unreleased_project:   root, admin, trusted_user
insert into Permission (PERMISSION) values ('see_unreleased_project');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_project' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_project' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_project' and UserRole.ROLENAME='trusted_user';

#see_unreleased_faq:       root, admin, trusted_user
insert into Permission (PERMISSION) values ('see_unreleased_faq');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_faq' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_faq' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_faq' and UserRole.ROLENAME='trusted_user';

#see_unreleased_topic:     root, admin, trusted_user
insert into Permission (PERMISSION) values ('see_unreleased_topic');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_topic' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_topic' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_topic' and UserRole.ROLENAME='trusted_user';

#see_unreleased_question:  root, admin, trusted_user
insert into Permission (PERMISSION) values ('see_unreleased_question');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_question' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_question' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_question' and UserRole.ROLENAME='trusted_user';

#see_unreleased_answer:    root, admin, trusted_user
insert into Permission (PERMISSION) values ('see_unreleased_answer');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_answer' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_answer' and UserRole.ROLENAME='admin';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='see_unreleased_answer' and UserRole.ROLENAME='trusted_user';

#release_project:   root, admin
insert into Permission (PERMISSION) values ('release_project');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_project' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_project' and UserRole.ROLENAME='admin';

#release_faq:       root, admin
insert into Permission (PERMISSION) values ('release_faq');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_faq' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_faq' and UserRole.ROLENAME='admin';

#release_topic:     root, admin
insert into Permission (PERMISSION) values ('release_topic');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_topic' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_topic' and UserRole.ROLENAME='admin';

#release_question:  root, admin
insert into Permission (PERMISSION) values ('release_question');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_question' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_question' and UserRole.ROLENAME='admin';

#release_answer:    root, admin
insert into Permission (PERMISSION) values ('release_answer');
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_answer' and UserRole.ROLENAME='root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID, Permission.PERMISSIONID from UserRole, Permission where Permission.PERMISSION='release_answer' and UserRole.ROLENAME='admin';
