3 Manage access to specimen aliases

In this chapter you will learn to:

Authorize specimen alias access
Revoke specimen alias access for a user

About specimen aliases and permissions

A laboratory that receives a specimen for processing may assign a barcode and use it for specimen identification purposes. In the Cohort Data Model (CDM) schema, these barcode identifiers are tracked as specimen aliases in addition to the primary lab specimen identifier (SPECIMEN_NUMBER/SPECIMEN_VENDOR_NUMBER).

The permissions to see specimen aliases are calculated based on the service provider (lab) that issued the alias and study or patient group that includes the specimen donor (patient or subject).

Authorize specimen alias access

For each user who needs access to specimen aliases, specify:

Either a specific service provider (such as a lab) or all providers
A specific study or patient group
All studies or all patient groups

If a user needs access to specimen aliases from more than one provider but not all, or to specimen aliases used in more than one study or patient group but not all, run this procedure once for each combination required:

On the database server, log in to SQL*Plus as CDM.
Run stored procedure VPD_UTIL.GRANT_SVCPRV_USER, entering values as follows:
- The user's user name.
- Service provider scope. Set one of the following parameters:
  - I_ISSNG_SVCPRV_ID. To limit access to aliases used by a single service provider, enter the service provider's ID.
  - I_ANY_ISSNG_SVCPRVS. To allow access to aliases created by any service provides, set this parameter s to 1.
- Subject and/or patient scope. Set one of the following parameters, or set one study parameter and one patient group parameter:
  - I_STUDY_NAME. To allow access to aliases used in a single study, enter the name of the study.
  - I_ANY_STUDY. To allow access to aliases used in all studies, set this parameter to 1.
  - I_PT_GROUP_NAME. To allow access to aliases used for a single patient group, enter the name of the patient group.
  - I_ANY_PT_GROUP. To allow access to aliases used in all patient groups, set this parameter to 1.
- (Optional) An expiration date for the user assignment. On this date the privileges given to the user will be automatically revoked. The date can be formatted in any valid date-type expression. For example:
```
 sysdate+x_days 
date '2020-12-31'
trunc(sysdate +1217
to_date('2020-12-31','YYYY-MM-DD')
```

Example 1: Access to a single provider's aliases for a single patient group

Authorize a user to access aliases for a specific service provider in the context of a single patient group.

begin
 vpd_util.grant_svcprv_user (
  i_user_name =>' LABUSER1',
  i_pt_group_name =>'GROUP_1',
  i_issng_svcprv_id =>'SVCPRV1',
  i_expiration_date =>sysdate+100
) ;
end;
/

Example 2: Access to a single provider's aliases for a single study

Authorize a user to access aliases for a specific service provider in the context of a single study.

begin
 vpd_util.grant_svcprv_user (
  i_user_name =>' LABUSER2',
  i_study_name =>'STUDY3',
  i_issng_svcprv_id =>'18_SVCPRV_NBR',
  i_expiration_date =>sysdate+100
) ;
end;
/

Example 3: Access to any provider's aliases for any study or patient group

Authorize a user to access any specimen alias in the context of any patient group or study.

begin
 vpd_util.grant_svcprv_user (
  i_user_name =>' LABSUPEVISOR1',
  i_any_study =>1,
  i_any_pt_group =>1,
  i_any_issng_svcprvs =>1,
  i_expiration_date =>sysdate+100
) ;
end;
/

Revoke specimen alias access for a user

Note:

To revoke specimen alias access from a user, use numeric identifiers (row_wids) of the specific study or patient group.

On the database server, log in to SQL*Plus as CDM.
Run stored procedure VPD_UTILrevoke_svcprv_user, entering values as follows:
- The user's user name.
- Service provider scope. Set one of the following parameters:
  - I_ISSNG_SVCPRV_ID. To revoke access from aliases used by a single service provider, enter the service provider's ID.
  - I_ANY_ISSNG_SVCPRVS. To revoke access from aliases created by any service provides, set this parameter s to 1.
- Subject and/or patient scope. Set one of the following parameters, or set one study parameter and one patient group parameter:
  - I_STUDY_NAME. To revoke access from aliases used in a single study, enter the name of the study.
  - I_ANY_STUDY. To revoke access from aliases used in all studies, set this parameter to 1.
  - I_PT_GROUP_NAME. To revoke access from aliases used for a single patient group, enter the name of the patient group.
  - I_ANY_PT_GROUP. To revoke access from aliases used in all patient groups, set this parameter to 1.
    
    Note:
    For the purpose of revoking access, the parameters for any study, any pt group, and any provider do not include any separately granted named study, patient, or provider scope. You must revoke these separately if needed.

Example 1: Revoke access to one study/provider combination

Revoke access to study_wid=99 and Service Provider (row_wid=105) from user TSTUSER1.

begin
vpd_util.revoke_svcprv_user (
    i_user_name =>'TSTUSER1',
  i_study_wid =>99,
  i_issng_svcprv_wid =>105
) ;
end;
/

Example 2: Revoke access to one patient group/provider combination

Revoke access to patient_group_wid=10 and Service Provider (row_wid=105) from user TSTUSER2.

begin
vpd_util.revoke_svcprv_user (
    i_user_name =>'TSTUSER2',
i_pt_group_wid =>10,
  i_issng_svcprv_wid =>105
) ;
end;
/

Example 3: Revoke access to any study, patient group, and provider granted using parameter I_ANY_STUDY, I_ANY_PT_GROUP, and I_ANY_ISSNG_SVCPRVS

Revoke access from a user to any study or patient group or service provider that was granted through an ”any” parameter.

begin
vpd_util.revoke_svcprv_user (
    i_user_name =>'TSTUSER3',
  i_any_study =>1,
  i_any_pt_group =>1,
  i_any_issng_svcprvs =>1
) ;
end;
/