Manually add a Group + Responsibilty to a Role

Owned by Kathleen Avery (Deactivated)
Last updated: Feb 22, 2017

To be used when a user does not have access to add permissions via the GUI but can add to tables with SQL Developer.

Step-by-step guide

Assumptions:

  1. Role exists on KRIM_ROLE_T.
  2. Group exists on KRIM_GRP_T.

Procedure:

  1. Create an entry on KRIM_ROLE_MBR_T, using an existing role and group.

insert into kr_SUP.KRIM_ROLE_MBR_T

  (role_mbr_id,  

   ver_nbr,  

   obj_id,   

   role_id,

   MBR_ID,  

   MBR_typ_cd  

   )

 

  values (kr_SUP.KRIM_ROLE_MBR_ID_S.nextVal,

  1,

  sys_guid(),

  '28',  <------ This is an existing role

  '10042', <------ This is an existing group

  'G') <------ Indicates you are adding a group rather than a principal id

  ;

commit;

2. Capture the newly created ROLE_MBR_ID.

select role_mbr_id

  from kr_SUP.KRIM_ROLE_MBR_T

  where role_id = '28'

  and mbr_id = '10042'

  and mbr_typ_cd = 'G';

Note that 52741 is the newly created role_mbr_id.

3.  Insert entries into KRIM_ROLE_MBR_ATTR_DATA_T.  There will be one entry for each attr_defn_id.  The valid attr_defn_ids can be found in KRIM_ATTR_DEFN_T.

In this case I only used the following attr_defn_ids:

 

13documentTypeName
22chartOf AccountsCode
28accountingLineOverrrideCode

insert into kr_SUP.krim_role_mbr_attr_data_t

  (attr_data_id,

  obj_id,

  ver_nbr,

  role_mbr_id,

  kim_typ_id,

  kim_attr_defn_id,

  attr_val)

  values (kr_SUP.KRIM_ATTR_DATA_ID_S.nextVal,

  sys_guid(),

  1,

  '52741',  <----- newly created role_mbr_id

  '30',       

  '13',       <----- attr_defn_id for documentTypeName

  'JV');  <----- actual Document Type Name

commit;

 -------------------------------------------------------------------------------------------------------------------------

   insert into kr_SUP.krim_role_mbr_attr_data_t

  (attr_data_id,

  obj_id,

  ver_nbr,

  role_mbr_id,

  kim_typ_id,

  kim_attr_defn_id,

  attr_val)

  values (kr_SUP.KRIM_ATTR_DATA_ID_S.nextVal,

  sys_guid(),

  1,

  '52741', <----- newly created role_mbr_id

  '30',

  '22',      <----- attr_defn_id for chartOfAccountCode

  'UC');    <----- actual Chart Of Accounts Code

commit;

 -------------------------------------------------------------------------------------------------------------------------

   insert into kr_SUP.krim_role_mbr_attr_data_t

  (attr_data_id,

  obj_id,

  ver_nbr,

  role_mbr_id,

  kim_typ_id,

  kim_attr_defn_id,

  attr_val)

  values (kr_SUP.KRIM_ATTR_DATA_ID_S.nextVal,

  sys_guid(),

  1,

  '52741', <----- newly created role_mbr_id

  '30',

  '28',       <----- attr_defn_id for Accounting Line Override Code

  'NONE'); <----- actual value for Accounting Line Override

commit;

4. Insert entry into the KRIM_ROLS_RSP_ACTN_T.

insert into KR_SUP.KRIM_ROLE_RSP_ACTN_T

  (role_rsp_actn_id,

  obj_id,

  ver_nbr,

  actn_typ_cd,

  actn_plcy_cd,

  role_mbr_id,

  role_rsp_id,

  frc_actn)

  values (kr_SUP.KRIM_ROLE_RSP_ACTN_ID_S.nextVal,

  sys_guid(),

  1,

  'A', <----- Approve (the document)

  'F', <----- First (person to act on the document)

  '52741', <----- newly created role_mbr_id

  '*',

  'Y'); <----- Must be approved even if initiator is within the group

commit;

5. Review Role to validate that entries are as expected:


 

 

 

Â