in clns-acuity-va-security/security/src/main/java/com/acuity/va/security/acl/dao/AclRepositoryImpl.java [189:356]
List<String> listUsersByPermissionMask(
@Param("aclClassName") String aclClassName,
@Param("permissionsAsRoleMask") Integer permissionsAsRoleMask);
/**
* List all the acls in the database
*/
@Select("SELECT * FROM acl_object_identity")
List<Map<String, Object>> listAcls();
/**
* High-level information for the number of users/group, mask and granted for a drug p/clinical study/dataset.
* <p/>
* acl_class.class = com....DrugProgramme is DrugProgramme and acl_entry.mask in as list of all the possible AcuityCumulativePermissionsAsRoles masks
* ie ( != 522240 (DEVELOPER).
*
* @param aclClassName either com....DrugProgramme Clinical Study from the acl_class
* @param aclObjectIdentityId internal acl id of the drug programme/clinical study/dataset
* @return list of SidWithPermissionMaskAndGranted
*/
@Select("SELECT distinct "
+ " acl_sid.sid AS sid, acl_sid.principal as isuser, "
+ " acl_entry.mask AS permissionMask, "
+ " acl_entry.granting AS granted "
+ " FROM acl_object_identity "
+ " JOIN acl_class ON acl_class.id = acl_object_identity.object_id_class "
+ " JOIN acl_entry ON acl_entry.acl_object_identity = acl_object_identity.id "
+ " JOIN acl_sid ON acl_sid.id = acl_entry.SID "
+ " WHERE acl_class.class = #{aclClassName} "
+ " AND acl_object_identity.object_id_identity = #{aclObjectIdentityId} "
+ " AND acl_entry.mask != 522240")
List<SidWithPermissionMaskAndGranted> getAcuityObjectIdentitySidsWithPermissionMaskAndGranted(
@Param("aclClassName") String aclClassName,
@Param("aclObjectIdentityId") Long aclObjectIdentityId);
/**
* Gets the acl name from the acl id and acl classname
*/
@Select("SELECT ao.name "
+ " FROM acl_object_identity aoi "
+ " JOIN acl_object ao ON ao.acl_object_identity_id = aoi.id "
+ " JOIN acl_class ac ON ac.id = aoi.object_id_class "
+ " WHERE aoi.object_id_identity = #{aclObjectIdentity} AND ac.class = #{aclClassName}")
String getAclName(@Param("aclClassName") String aclClassName, @Param("aclObjectIdentity") Long aclObjectIdentity);
/**
* List all the enabled remote acl locations, ie for acuity and detect
*/
@Select("SELECT name, base_url AS baseUrl, enabled "
+ " FROM acl_remote "
+ " WHERE enabled = true")
List<AclRemoteLocation> listAllRemoteAclsLocations();
/**
* List all the enabled remote acl locations, ie for acuity and detect
*/
@Select("SELECT name, base_url AS baseUrl, enabled "
+ " FROM acl_remote "
+ " WHERE name = 'detect'")
List<AclRemoteLocation> listDetectRemoteAclsLocations();
/**
* Insert a new aclObject
*/
@Insert("INSERT INTO acl_object "
+ " (name, code, acl_object_identity_id, lockdown, parent_drug_programme, parent_clinical_study, parent_clinical_study_code, acl_type)"
+ " VALUES (#{name,jdbcType=VARCHAR}, #{code,jdbcType=VARCHAR}, #{aclObjectIdentityPkId,jdbcType=NUMERIC}, #{lockdown}, "
+ " #{parentDrugProgramme,jdbcType=VARCHAR}, #{parentClinicalStudyName,jdbcType=VARCHAR}, #{parentClinicalStudyCode,jdbcType=VARCHAR},"
+ " #{aclType,jdbcType=VARCHAR})")
int insertAclObject(AclObject aclObject);
/**
* Updates an aclObject.
*
* No updates on DP. DS updates both CS updates on name, parentClinicalStudyName will always be null
*/
@Update("UPDATE acl_object "
+ " SET name = #{name,jdbcType=VARCHAR}, parent_clinical_study = #{parentClinicalStudyName,jdbcType=VARCHAR} "
+ " WHERE id = #{id}")
int updateAclObject(AclObject aclObject);
/**
* Sets lockdown for an aclObject
*/
@Update("UPDATE acl_object "
+ " SET lockdown = #{lockdownStatus}"
+ " WHERE id = #{id}")
int setLockdown(@Param("id") Long id, @Param("lockdownStatus") boolean lockdownStatus);
/**
* Gets the lockdown for an aclObject
*/
@Select("SELECT lockdown "
+ " FROM acl_object "
+ " WHERE id = #{id}")
boolean isLockdown(@Param("id") Long id);
/**
* Checks if the aclObject is already in the db
*/
@Select("SELECT ao.id "
+ " FROM acl_object_identity aoi "
+ " JOIN acl_object ao ON ao.acl_object_identity_id = aoi.id "
+ " JOIN acl_class ac ON ac.id = aoi.object_id_class "
+ " WHERE aoi.object_id_identity = #{aclObjectIdentity} AND ac.class = #{aclClassName}")
Long findAclObjectId(@Param("aclClassName") String aclClassName, @Param("aclObjectIdentity") Long aclObjectIdentity);
// TODO need to add study information
@Select(" SELECT aoi.object_id_identity AS id, ac.class AS type, ao.name AS name, "
+ " ao.module_type AS moduleType, ao.parent_drug_programme AS drugProgramme, ao.parent_clinical_study AS study "
+ " FROM acl_object_identity aoi"
+ " JOIN acl_object ao ON ao.acl_object_identity_id = aoi.id "
+ " JOIN acl_class ac ON ac.id = aoi.object_id_class "
+ " WHERE ac.class = 'com.acuity.va.security.acl.domain.AcuityDataset'"
+ " AND or ac.class = 'com.acuity.va.security.acl.domain.DetectDataset'"
+ " ORDER BY id")
List<DrugStudyDataset> listDrugsStudiesInstances();
@Select(" SELECT acl_sid.sid FROM acl_object_identity "
+ "JOIN acl_object on acl_object.acl_object_identity_id = acl_object_identity.id "
+ "JOIN acl_class on acl_object_identity.object_id_class = acl_class.id "
+ "JOIN acl_entry on acl_entry.acl_object_identity = acl_object_identity.id "
+ "JOIN acl_sid on acl_sid.id = acl_entry.sid "
+ "WHERE lockdown = true AND principal = false "
+ "AND class = 'com.acuity.va.security.acl.domain.DetectDataset' "
+ "AND acl_sid.sid != 'DEVELOPMENT_TEAM'")
List<String> listGroupsInLockdown();
@Select(" SELECT acl_sid.sid AS sid, name AS ds_name FROM acl_object_identity "
+ "JOIN acl_object on acl_object.acl_object_identity_id = acl_object_identity.id "
+ "JOIN acl_class on acl_object_identity.object_id_class = acl_class.id "
+ "JOIN acl_entry on acl_entry.acl_object_identity = acl_object_identity.id "
+ "JOIN acl_sid on acl_sid.id = acl_entry.sid "
+ "WHERE lockdown = true AND principal = false "
+ "AND class = 'com.acuity.va.security.acl.domain.DetectDataset' "
+ "AND acl_sid.sid != 'DEVELOPMENT_TEAM'")
@ResultMap("com.acuity.va.security.acl.dao.AclRepositoryImpl.listGroupsAndTheirDatasetsInLockdown")
List<GroupWithItsLockdownDatasets> listGroupsAndTheirDatasetsInLockdown();
@Select(" SELECT object_id_identity FROM acl_object_identity "
+ " JOIN acl_object ON acl_object.acl_object_identity_id = acl_object_identity.id "
+ " JOIN acl_class ON acl_object_identity.object_id_class = acl_class.id "
+ " WHERE acl_type = 'DS' AND class = 'com.acuity.va.security.acl.domain.AcuityDataset' "
+ " AND parent_clinical_study_code = #{studyId}")
Long findVasecurityIdFromStudyId(@Param("studyId") String studyId);
/**
* Get acl object identity id
*/
@Select("SELECT aoi.id "
+ "FROM acl_object_identity aoi "
+ "JOIN acl_class ac ON ac.id = aoi.object_id_class "
+ "WHERE aoi.object_id_identity = #{aclObjectIdentity} AND ac.class = #{aclClassName}")
Long findAclObjectIdentityId(@Param("aclClassName") String aclClassName,
@Param("aclObjectIdentity") Long aclObjectIdentity);
/**
* Remove the entries from the db
*/
@Delete({"<script>",
"DELETE ",
"FROM acl_entry ae ",
"WHERE ae.acl_object_identity IN ",
"<foreach item='object' index='index' collection='aclObjectIdentityIds'",
"open='(' separator=', ' close=')'>",
"#{object}",
"</foreach>",
"</script>"})