create or replace package body JR_ROLE is

/****************************************************************************************
Revision History  (most recent on top!)
When          Who                            Construct
  Revision    What
------------------------------------------------------------------------------------------
$REVISION_HISTORY$
18-jul-2000   ReposDev
              enable load via RAU
16-jan-2001   Sandra Muller
  3.0         Added backup/restore function and procedures
15-jan-2001   Lucas Jellema (iDevCoE)
  2.1         added update_role
20-dec-2000   Sandra Muller (iDevCoE)
  2.0         ability to revoke privileges granted to the grantee through the
              role-to-be-revoked (if they are not granted through another role as well)
25-oct-2000   Lucas Jellema (iDevCoE)
  1.3         script in specification comments for restoring roles after reconcile/upgrade
              + script to export/import roles and role-access-privileges to/from
              temporary tables
24-oct-2000   Lucas Jellema (iDevCoE)
  1.2         OR_PRIVS and AND_PRIVS: public functions
              procedure check_upd_users: corrected system privilege name: MANAGE_USER
              instead of MANAGE_USERS
23-oct-2000   Lucas Jellema (iDevCoE)
  1.1         set error description when an exception is raised (in revoke_role)
              grant role with all options to creator of role (in create_role)
12-oct-2000   Lucas Jellema (iDevCoE)
  1.0         initial creation (along with iDevCoE Bulletin TB.OR.05)

****************************************************************************************/
/* 3.2 Added g_enforce_role_admin switch; used for Revoke role with reconcile
       to bypass the privilege checks when granting/revoking/reconciling roles
       to and through the dummy users
*/

--
-- private constants
--
REVISION_LABEL        constant varchar2(30) := '$x.y::3.3 $'      ;
PACKAGE_NAME          constant varchar2(30) := 'JR_ROLE';
NO_PRIVS_STRING       constant varchar2(20) := jr_acc_rights.ar_to_str
                                               ( jr_acc_rights.no_access_rights
                                               );
-- new constants used in procedure revoke_role
DUMMY_USER_REVOKED_ROLE constant varchar2(30) := '@JR_ROLE_DUMMY_A';
DUMMY_USER_OTHER_ROLES  constant varchar2(30) := '@JR_ROLE_DUMMY_B';
--
-- private types
--

type t_string_tbl is
table of varchar2(30)
index by binary_integer
;

--
-- private variables
--

g_role_tbl             t_string_tbl; -- collection of roles that have been examined
g_error_description    varchar2(32700);
g_cut_off              boolean;
g_enforce_role_admin   boolean:= true;  -- this boolean indicates whether or not when granting role privileges
  -- the procedure should check if the current user actually has the Grant Role privilege on the role
  -- (required to grant all roles to dummy role for revoke_with_reconcile)

-- 2.0 made it a package wide cursor
cursor c_granted_roles
(      b_grantee in varchar2
)      is
select rle.username         role_name
,      acc.object_reference role_irid
from   sdw_users            rle
,      sdw_access_rights    acc
where  acc.grantee_reference = b_grantee
and    acc.object_reference  = rle.irid
;

--
-- private and public program units
--


procedure add_line
( p_message in varchar2
) is
begin
  if not g_cut_off
  then
    g_error_description := g_error_description
    ||p_message
    ||chr(13)||chr(10)
    ;
  end if;
exception
  when others
  then
    if length( g_error_description) + length(p_message) > 31000
    then
      g_error_description:= (ROB_msg.getMsg(Rob_msg.MSG220_JR_ERRORDESC, substr( g_error_description,1,31000), '', '', ''));
      g_cut_off:= true;
    else
      raise;
    end if;
end;

procedure set_error_description
( p_message in varchar2
) is
begin
  g_cut_off:= false;
  add_line( p_message);
end;

procedure reset_error_description
is
begin
  g_cut_off := false;
  g_error_description:= '';
end;

function get_error_description
return varchar2
is
begin
 return g_error_description;
end;

function ifThenElse
( p_if   in boolean
, p_then in varchar2
, p_else in varchar2 default null
) return varchar2
is
begin
  if p_if
  then
    return p_then;
  else
    return p_else;
  end if;
end; -- ifThenElse


function get_wa_name
( p_wa_irid in number
) return varchar2
is
 cursor c_wa( b_wa_id in number)
 is
 select wa.name
 from   i$sdd_workareas wa
 where  wa.irid = b_wa_id
 ;
 r_wa c_wa%rowtype;
begin
 open c_wa( b_wa_id => p_wa_irid);
 fetch c_wa
 into  r_wa
 ;
 close c_wa;
 return r_wa.name;
end;

function get_cfg_name
( p_cfg_irid in number
, p_format   in varchar2 default 'NAME' -- values: NAME, VLABEL
) return varchar2
is
 cursor c_cfg( b_cfg_irid in number)
 is
 select cfg.name
 ,      ov.vlabel
 from   i$sdd_configurations cfg
 ,      i$sdd_object_versions ov
 where  cfg.irid = b_cfg_irid
 and    ov.irid  = cfg.irid
 and
        ( ov.state = 'N' -- unversioned
          or
          exists (select 'x'
                  from   i$sdd_version_associations  va
                  where  ov.ivid = va.from_ivid
                  and    va.from_ivid = va.to_ivid
                  and    va.edge_kind ='T' -- TIP version
                  )
        )
 ;
 r_cfg c_cfg%rowtype;
begin
 if p_cfg_irid is null
 then
   return '';
 end if;
 open c_cfg( b_cfg_irid => p_cfg_irid);
 fetch c_cfg
 into  r_cfg
 ;
 close c_cfg;
 return r_cfg.name
      ||ifThenElse
        ( p_format = 'VLABEL'
        , ' {'||r_cfg.vlabel||'}'
        )
 ;
end; -- get_cfg_name

function get_object_type
( p_irid in number
) return varchar2
is
   cursor c_type( b_irid in number)
     is
     select acc.object_type
     from   sdw_access_rights acc
     where  acc.object_reference = b_irid
     ;
  l_object_type varchar2(10);
begin
  open  c_type( b_irid => p_irid);
  fetch c_type
  into  l_object_type;
  close c_type;
  return l_object_type;
end; -- get_object_type


function get_object_label
( p_irid in number
, p_object_type in varchar2 default null
) return varchar2
is
  l_object_type varchar2(6):= nvl( p_object_type, get_object_type( p_irid));
  l_object_label varchar2(2000);
begin
  l_object_label:= ifThenElse
                    ( l_object_type = 'WA'
                    , get_wa_name(p_wa_irid => p_irid)
                    , ifThenElse
                      ( l_object_type ='CFG'
                      , get_cfg_name
                        ( p_cfg_irid => p_irid
                        )
                      , jr_name.get_path
                        ( id => p_irid
                        , format => 'NAME'
                        )  -- Folder
                      )
                    );
  return l_object_label;
end; -- get_object_label

function table_exists
( p_table_name in varchar2
)
return boolean
is
   cursor c_table_exists
   (      b_table_name in varchar2
   )      is
   select 1
   from   user_tables
   where  table_name = b_table_name;
   l_dummy number(1);
begin
   open c_table_exists(upper(p_table_name));
   fetch c_table_exists into l_dummy;
   if c_table_exists%found
   then
      close c_table_exists;
      return true;
   else
      close c_table_exists;
      return false;
   end if;
end table_exists;

procedure add_to_role_tbl
( p_role_name in varchar2
) is
begin
  g_role_tbl( g_role_tbl.count + 1) := p_role_name;
end; -- add_to_folder_tbl

function in_role_tbl
( p_role_name in varchar2
) return boolean
is
begin
  for i in 1..g_role_tbl.count loop
    if g_role_tbl(i) = p_role_name
    then
      return true;
    end if;
  end loop;
  return false;
end; -- in_role_tbl

procedure init_role_tbl
is
begin
  g_role_tbl.delete;
end; -- init_role_tbl

------------------------------------
--COPIED FROM JR_SYS_PRIVS
--PRIVATE HELPER FUNCTION
--Check that the current user can
--update user info. i.e., has the
--manage user privilege, or is the
--repository owner.
-------------------------------------
PROCEDURE check_upd_users
IS
 repos_owner VARCHAR2(30);
 curr_user   VARCHAR2(30):=USER;
BEGIN
 repos_owner:=jr_sys_privs.get_repos_owner;
 IF (curr_user=repos_owner)
    OR
    jr_sys_privs.has_privilege(curr_user,'MANAGE_USER')
 THEN
   null;
 else
   set_error_description(ROB_msg.getMsg(Rob_msg.MSG221_JR_PRIVERROR, '', '', '', ''));
   RAISE jr_sys_privs.SYSTEM_PRIV_ERROR;
 END IF;
END;

function get_check_upd_users
return boolean
is
begin
  check_upd_users;
  return true;
exception
  when others
  then
    return false;
end; -- check_upd_users

-- 3.0 extracted from check_role_exists
function role_exists
( p_role_name      in varchar2
)
return boolean
is
   cursor c_rol
   (      b_role_name in varchar2
   )      is
   select 1
   from   sdw_users
   where  username = b_role_name
   ;
   l_dummy       number(1);
   l_role_exists boolean;
begin
   open c_rol(p_role_name);
   fetch c_rol into l_dummy;
   l_role_exists := c_rol%found;
   close c_rol;
   return l_role_exists;
end role_exists;

procedure check_role_exists
( p_role_name      in varchar2
, p_reverse_check  in boolean default false
)
-- new in 2.0:
-- raise an exception if the given role does not exist
is
   l_role_exists boolean;
begin
   l_role_exists := role_exists(p_role_name);
   if not p_reverse_check and not l_role_exists
   then
      set_error_description(ROB_msg.getMsg(Rob_msg.MSG222_JR_ROLENOTEXIST, p_role_name, '', '', ''));
      raise e_role_does_not_exist;
   elsif p_reverse_check and l_role_exists
   then
      set_error_description(ROB_msg.getMsg(Rob_msg.MSG223_JR_ROLEEXIST, p_role_name, '', '', ''));
      raise e_role_already_exists;
   end if;
end check_role_exists;


-- copied from JR_ACC_RIGHTS
-----------------------------------------------------------------------
-- Private helper function.
-- Performs a logical AND on two lists of access privileges
-----------------------------------------------------------------------
function AND_PRIVS
-- 2.0 changed in/out parameters to in-parameters
-- otherwise a combination of AND and NOT in one statement is not possible
(first  in sdd_acc_priv_list
,second in sdd_acc_priv_list
)
return sdd_acc_priv_list
is
 -- 2.0 introduced local first/second,
 -- necessary because p_first/second can not be out parameters anymore
 -- (otherwise you can not call it with an expression instead of a variable)
 l_first  sdd_acc_priv_list := first;
 l_second sdd_acc_priv_list := second;
 l_result sdd_acc_priv_list:=sdd_acc_priv_list();
 l_temp   sdd_acc_priv_list:=sdd_acc_priv_list();
 i number;
 j number;
begin
 i := l_first.FIRST;
 j := l_second.FIRST;

 --If one of the lists is empty, then pass
 --back empty list
 if i is null then
  return sdd_acc_priv_list();
 elsif j is null then
  return sdd_acc_priv_list();
 end if;

 --if the priv lists are of different lengths, then
 --ensure that the first is the longest.
 if l_first.LAST < l_second.LAST then
  l_temp:=l_first;
  l_first:=l_second;
  l_second:=l_temp;
 end if;

 while i is not null loop
  l_result.extend;
  if j is null then
   --If we've reached the end of the second list,
   --rest of result is all 'N'
   l_result(l_result.last):='N';
  else
   if ((l_first(i) = 'Y') AND (l_second(j) = 'Y')) then
    l_result(l_result.last):='Y';
   else
    l_result(l_result.last):='N';
   end if;
  end if;

  i:=l_first.next(i);
  if j is not null then
   j:=l_second.next(j);
  end if;
 end loop;

 return l_result;
end and_privs;

-- copied from JR_ACC_RIGHTS
-- this function takes two sets of access privileges and determines
-- the logical OR (addition) of the two
function OR_PRIVS
(first  in out sdd_acc_priv_list
,second in out sdd_acc_priv_list
) return sdd_acc_priv_list
is
 result sdd_acc_priv_list:=sdd_acc_priv_list();
 temp   sdd_acc_priv_list:=sdd_acc_priv_list();
 i number;
 j number;
begin
 i := first.FIRST;
 j := second.FIRST;

 --If one of the lists is empty, then result is
 --simply the other list...
 if i is null then
  return second;
 elsif j is null then
  return first;
 end if;

 --if the priv lists are of different lengths, then
 --ensure that the first is the longest.
 if first.LAST < second.LAST then
  temp:=first;
  first:=second;
  second:=temp;
 end if;

 while i is not null loop
  result.extend;
  if j is null then
   --If we've reached the end of the second list,
   --rest of result is just the remainder of the first list
   result(result.last):=first(i);
  else
   --Result is logical OR of first and second lists
   if ((first(i) = 'Y') or (second(j) = 'Y')) then
    result(result.last):='Y';
   else
    result(result.last):='N';
   end if;
  end if;

  i:=first.next(i);
  if j is not null then
   j:=second.next(j);
  end if;
 end loop;

 return result;
end or_privs;


function NOT_PRIVS
( p_priv_list  in sdd_acc_priv_list
)
return sdd_acc_priv_list
-- 2.0 new function
-- based on jr_acc_rights.and_privs
-- if priv=Y make it N, and if priv=N make it Y
is
   l_result_list sdd_acc_priv_list := sdd_acc_priv_list();
   i             number;
begin
   i := p_priv_list.FIRST;

   -- If the list is empty, then pass back empty list
   if i is null
   then
      return sdd_acc_priv_list();
   end if;

   -- loop over privileges and reverse them
   while i is not null
   loop
      l_result_list.extend;
      if (p_priv_list(i) = 'Y')
      then
         l_result_list(l_result_list.last) := 'N';
      else
         l_result_list(l_result_list.last) := 'Y';
      end if;

      i := p_priv_list.next(i);
   end loop;

   return l_result_list;
end not_privs;


-- overloaded varchar2 version of and_privs and or_privs,
-- needed for ODWA, because there are problems with using sdd_acc_priv_list
-- in another schema than the Repository owner
function AND_PRIVS
( p_first  in varchar2
, p_second in varchar2
) return varchar2
is
 result sdd_acc_priv_list:=sdd_acc_priv_list();
 temp1  sdd_acc_priv_list:=sdd_acc_priv_list();
 temp2  sdd_acc_priv_list:=sdd_acc_priv_list();
begin
 temp1:= jr_acc_rights.str_to_ar( p_first);
 temp2:= jr_acc_rights.str_to_ar( p_second);
 result:= and_privs( first=> temp1, second => temp2);
 return jr_acc_rights.ar_to_str( result);
end and_privs;
function OR_PRIVS
( p_first  in varchar2
, p_second in varchar2
) return varchar2
is
 result sdd_acc_priv_list:=sdd_acc_priv_list();
 temp1  sdd_acc_priv_list:=sdd_acc_priv_list();
 temp2  sdd_acc_priv_list:=sdd_acc_priv_list();
begin
 temp1:= jr_acc_rights.str_to_ar( p_first);
 temp2:= jr_acc_rights.str_to_ar( p_second);
 result:= or_privs( first=> temp1, second => temp2);
 return jr_acc_rights.ar_to_str( result);
end or_privs;


-- this function returns the string that is derived from the input string
-- by taking uppercase, translating spaces to underscores and limiting the length to 30 characters
function proper
( p_string in varchar2
) return varchar2
is
begin
  return upper( translate( substr( p_string, 1, 30), ' ', '_'));
end; -- proper

-- this function returns the irid of a Role given its Role Name
function get_role_irid
( p_role_name in varchar2
) return number
is
  cursor c_usr
         ( b_role_name in varchar2
         ) is
    select usr.irid
    from   sdw_users usr
    where  usr.username = p_role_name
    ;
  l_role_irid number(38);
begin
  open c_usr( b_role_name => proper( p_role_name));
  fetch c_usr
  into  l_role_irid
  ;
  close c_usr;
  return l_role_irid;
end; -- get_role_irid

-- this function returns the creator of a Role given its Role Name
function get_role_creator
( p_role_name in varchar2
) return varchar2
is
  cursor c_usr
         ( b_role_name in varchar2
         ) is
    select usr.created_by
    from   sdw_users usr
    where  usr.username = b_role_name
    ;
  l_role_creator varchar2(30);
begin
  open c_usr( b_role_name => proper( p_role_name));
  fetch c_usr
  into  l_role_creator
  ;
  close c_usr;
  return l_role_creator;
end; -- get_role_creator

function get_object_owner
( p_irid in number
) return varchar2
is
  cursor c_usr
         ( b_obj_irid in number
         ) is
    select acc.grantee_reference
    from   sdw_access_rights acc
    where  acc.object_reference = b_obj_irid
    and    acc.grantee_reference = acc.grantor_reference
    ;
  l_obj_owner varchar2(30);
begin
  open c_usr( b_obj_irid => p_irid);
  fetch c_usr
  into  l_obj_owner
  ;
  close c_usr;
  return l_obj_owner;
end; -- get_object_owner

function get_role_owner
( p_role_name in varchar2
) return varchar2
is
  cursor c_usr
         ( b_role_name in varchar2
         ) is
    select acc.grantee_reference
    from   sdw_users usr
    ,      sdw_access_rights acc
    where  usr.username = b_role_name
    and    acc.object_reference = usr.irid
    and    acc.grantee_reference = acc.grantor_reference
    ;
  l_role_owner varchar2(30);
begin
  open c_usr( b_role_name => proper( p_role_name));
  fetch c_usr
  into  l_role_owner
  ;
  close c_usr;
  return l_role_owner;
end; -- get_role_owner

-- this function returns the type - R(ole) or U(ser) of a grantee
function get_grantee_type
( p_grantee in varchar2
) return varchar2
is
  cursor c_usr
         ( b_grantee in varchar2
         ) is
    select decode
           ( usr.user_type
           , 0, 'R'
           , 1, 'U'
           , 'U'
           )
    from   sdw_users usr
    where  usr.username = b_grantee
    ;
  l_grantee_type varchar2(1);
begin
  open c_usr( b_grantee => p_grantee);
  fetch c_usr
  into  l_grantee_type
  ;
  close c_usr;
  return l_grantee_type;
end; -- get_grantee_type

-- Create a new repository role
-- the role name is converted by this function to 30 characters, uppercase and no spaces (spaces are translated to underscores)
-- new roles can only be created by:
-- * the repository owner
-- * a repository user with Manage_Users privilege
-- As soon has the role has been created, you can start granting privileges to it in the RON,
-- in the same way you grant privileges on Workareas, Folders and Configurations to Users
function create_role
( p_role_name     in varchar2                       -- max length = 30
, p_description   in varchar2          default null -- max length = 240
, p_system_privs  IN sdd_sys_priv_list default jr_sys_privs.str_to_sysprv( 'NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN')
) return number
is
 l_role_irid number(38):= Jr_Util.get_new_irid;
 l_role_name varchar2(30):= proper( p_role_name);
begin
  reset_error_description;

  check_upd_users;  -- require users to have the manage_users privilege before they can cerate roles!
  INSERT INTO SDW_USERS
  ( IRID
  , IVID
  , user_type
  , USERNAME
  , FULL_USER_NAME
  , DESCRIPTION
  , SYSTEM_PRIVS
  )
  VALUES
  ( l_role_irid
  , 1
  , 0  -- user_type = 0 indicates a Role
  , l_role_name
  , p_role_name
  , p_description
  , p_system_privs
  );
  grant_role
  ( p_role_to_grant     => l_role_name
  , p_grantee           => user
  , p_grant_option      => true
  , p_delete_option     => true
  , p_reconcile_option  => true
  , p_reconcile         => true
  );
  return l_role_irid;
end; -- create_role


procedure update_role
( p_role_name      in varchar2                 -- max length = 30
-- 3.0 possibility to rename role
, p_new_role_name  in varchar2    default null -- max length = 30
, p_full_role_name in varchar2    default null -- max length = 240
, p_description    in varchar2    default null -- max length = 240
)
is
   l_role_name     varchar2(30) := proper(p_role_name);
   l_new_role_name varchar2(30) := proper(p_new_role_name);
begin
   reset_error_description;
   -- require users to have the manage_users privilege
   check_upd_users;
   -- 3.0 new check
   check_role_exists(l_role_name);
   -- 3.0 possibility to rename role
   if l_new_role_name is not null
      and
      l_new_role_name <> l_role_name
   then
      -- check new role name is not used yet
      check_role_exists
      ( p_role_name      => l_new_role_name
      , p_reverse_check  => true
      );
      -- rename all references to this role name in sdw_access_rights
      update sdw_access_rights
      set    grantee_reference = l_new_role_name
      where  grantee_reference = l_role_name;
      -- rename the role in sdw_users
      update sdw_users
      set    username = l_new_role_name
      where  username = l_role_name;
   end if;
   -- 3.0 possibility that p_full_role_name is null
   if p_full_role_name is not null
   then
      update SDW_USERS
      set    full_user_name = substr(p_full_role_name, 1, 240)
      where  username = l_role_name
      ;
   end if;
   -- 3.0 possibility that p_description is null
   if p_description is not null
   then
      update SDW_USERS
      set    description = substr(p_description, 1, 240)
      where  username = l_role_name
      ;
   end if;
end; -- update_role

-- Delete a role; it will also delete
-- * all grants made of this role to other roles and users
-- * all grants (on roles, wa, folders, cfg) made to this role
-- note that privileges that roles or users have inherited from this role
-- (as part of a reconcile operation) are NOT revoked now that the role
-- is deleted
-- Roles can only be deleted by:
-- * the creator or the role
-- * the repository owner
-- * a user with Delete and Admin privileges on this role
procedure delete_role
( p_role_name     in varchar2
) is
   l_role_irid number(38):= get_role_irid( p_role_name => p_role_name);
   l_role_name varchar2(30):= proper( p_role_name);
begin
   reset_error_description;
   check_upd_users;  -- require users to have the manage_users privilege before they can delete roles!
   -- 3.0 new check
   check_role_exists(l_role_name);

  -- check the current user is
  -- either Repository Owner,
  -- or the creator (CREATED_BY) of the role
  -- or has ADMIN privileges (is that enough?)

  -- now remove all grants made of this role to users and roles from SDW_ACCESS_RIGHTS
   DELETE SDW_ACCESS_RIGHTS ar
   WHERE  ar.object_reference = l_role_irid
   ;

  -- now remove all privileges assigned to this role from SDW_ACCESS_RIGHTS
   DELETE SDW_ACCESS_RIGHTS ar
   WHERE  ar.grantee_reference = p_role_name
   ;

   -- now delete the Role from SDW_USERS
   delete SDW_USERS
   where  username = l_role_name
   ;
 end; -- delete_role

-- check whether p_grantee has been granted p_role_irid; if not directly,
-- go into each of the granted roles to find out whether they have been
-- granted (directly or indirectly through further nested roles) this role
function recursive_check
( p_role_irid in number
, p_grantee   in varchar2
) return boolean
is
  -- 2.0 own cursor needed here because of recursive calls,
  -- otherwise you get error ORA-06511: PL/SQL: cursor already open
  cursor c_granted_roles
  (b_grantee in varchar2)
  is
  select rle.username         role_name
  ,      acc.object_reference role_irid
  from   sdw_users            rle
  ,      sdw_access_rights    acc
  where  acc.grantee_reference = b_grantee
  and    acc.object_reference  = rle.irid
  ;
  l_found boolean;
begin
  if in_role_tbl( p_role_name => p_grantee)
  then
    return false;  -- already examined this grantee (role)
  else
    add_to_role_tbl( p_role_name => p_grantee);  -- do not investigate when next encountered
  end if;
  for r_granted_roles in c_granted_roles( b_grantee => p_grantee) loop
      if r_granted_roles.role_irid = p_role_irid
      then
        return true;
      else
        l_found:= recursive_check
                  ( p_role_irid => p_role_irid
                  , p_grantee   => r_granted_roles.role_name
                  );
        if l_found
        then
          return true;
        end if;
      end if;
  end loop; -- r_granted_roles
  return false;
end; -- recursive_check

-- function indicates whether the grantee p_grantee has been granted the role indicated by p_rolename
-- if p_recursive, then the function will also check whether this role is indirectly granted to the
-- user, through the nested role structure
function is_grantee_of_role
( p_grantee    in varchar2
, p_role_name  in varchar2
, p_recursive  in boolean default false
) return boolean
is
 l_role_name    varchar2(30)  := proper( p_role_name);
 l_role_irid    number(38)    := get_role_irid( p_role_name => l_role_name);
 l_grantee      varchar2(30)  := proper( p_grantee);


begin
  if  jr_acc_rights.has_access
      ( object_irid  => l_role_irid
      , grantee      => l_grantee
      , privilege    => 'SEL'
      )
  then
    return true;
  end if;
  -- now if p_recursive, we shall continue the search
  if not p_recursive
  then
    return false;
  end if;
  -- watch out for circular references turning into infinite loops!!
  -- add code to recursively check all granted roles
  init_role_tbl;
  return recursive_check
         ( p_role_irid => l_role_irid
         , p_grantee   => l_grantee
         );
end; -- is_grantee_of_role


-- Grants a role to another role or a user.
-- Can only be called by a user that
-- * is creator on the granted role
-- * has Admin privileges on the granted role
procedure grant_role
-- which role is to be granted
( p_role_to_grant     in varchar2
-- username or rolename of the grantee (who is the role granted to)
, p_grantee           in varchar2
-- is this grantee allowed to pass on the grant on this role
-- (translates to Admin privilege); only applicable when grantee is a user, not a role
, p_grant_option      in boolean   default false
-- is this grantee allowed to delete this role (translates to Del privilege);
-- only applicable when grantee is a user, not a role;
-- only granted if the grantee also gets ADMIN privileges
, p_delete_option     in boolean   default false
-- is this grantee allowed to reconcile this role,
-- which effectively would mean create real user privileges based on the role definition;
-- only applicable when grantee is a user, not a role
, p_reconcile_option  in boolean   default false
-- should the grantee immediately be granted all user privileges
-- that (recursively) derive from this role?; only applicable when grantee is a user;
, p_reconcile         in boolean   default false
-- should the existing rolegrant be updated (true)
-- or just extended (false) with the privileges indicated here
, p_override          in boolean   default false
)
is
 l_grant_irid   number(38)    := jr_util.get_new_irid;
 l_role_name    varchar2(30)  := proper( p_role_to_grant);
 l_role_irid    number(38)    := get_role_irid( p_role_name => l_role_name);
 l_grantee      varchar2(30)  := proper( p_grantee);
 l_grantee_type varchar2(1)   := get_grantee_type( p_grantee => p_grantee); -- R(ole) or U(ser)
 -- the access privileges list is sequenced as follows:
 -- 'DEL' , 'ADM', 'INS', 'SEL', 'UPD', 'VER', 'CMP', 'UPD_SPEC';
 l_new_acc_privs     sdd_acc_priv_list:=sdd_acc_priv_list( 'N','N','N','Y','N','N','N','N');
 l_grantee_acc_privs sdd_acc_priv_list:=sdd_acc_priv_list();
 l_summ_acc_privs    sdd_acc_priv_list:=sdd_acc_priv_list();
begin
   reset_error_description;
  -- 3.0 new check
  check_role_exists(l_role_name);

  -- check p_grantee exists; also determine type (Role or User)
  if nvl(l_grantee_type,'X') not in ('R','U')
  then
    -- grantee is not a known user (3.0 set error description)
    set_error_description(ROB_msg.getMsg(Rob_msg.MSG224_JR_GRANTEEDNE, l_grantee, '', '', ''));
    raise jr_acc_rights.access_rights_error;
  end if; -- nvl(l_grantee_type,'X') not in ('R','U')
  if l_role_irid is null
  then
    -- does this happen only if the role does not exist?
    -- in that case it is already taken care of by check_role_exists
    raise jr_acc_rights.access_rights_error;
  end if;
  --Check user has Admin privileges on Role or is Creator
  if  jr_acc_rights.has_access
      ( object_irid  => l_role_irid
      , grantee      => USER
      , privilege    => 'ADM'
      )
      OR
      get_role_creator( l_role_name) = USER
      OR
      g_enforce_role_admin = false  -- for internal use with respect to DUMMY USER when revoking Role with Reconcile
  then
    null; -- is OK
  else
    -- 3.0 set error description
    set_error_description
    (ROB_msg.getMsg(Rob_msg.MSG225_JR_ADMINPRIVERROR, USER, l_role_name, '', ''));
    raise jr_acc_rights.access_rights_error;
  end if;
  if l_grantee_type = 'U' -- granting access to a user
  then
    if p_grant_option
    then
      l_new_acc_privs(2):= 'Y'; -- ADM privilege
      if p_delete_option
      then
        l_new_acc_privs(1):= 'Y'; -- DEL privilege
      end if; -- p_delete_option
    end if; -- p_grant_option
    if p_reconcile_option
    then
      l_new_acc_privs(7):= 'Y';  -- CMP privilege
    end if;
  end if;  -- p_grantee is a User (not a role)

  -- now check whether grantee already has privileges on this role;
  -- if that is the case, update the current entry in SDW_ACCESS_RIGHTS
  -- else insert a new row
   l_grantee_acc_privs:= jr_acc_rights.get_acc_rights
                         ( object_irid => l_role_irid
                         , grantee     => l_grantee
                         );
   -- if the user has no access privs at all on the object
   -- then insert a set of privs identical to the role privs
   -- else update the existing set to also include the role privs
   if jr_acc_rights.ar_to_str( l_grantee_acc_privs) = no_privs_string
   then
     insert into SDW_ACCESS_RIGHTS
     ( IRID
     , IVID
     , GRANTEE_REFERENCE
     , GRANTOR_REFERENCE
     , OBJECT_REFERENCE
     , OBJECT_TYPE
     , ACCESS_PRIVS
     )
     VALUES
     ( l_grant_irid
     , 1
     , l_grantee
     , USER
     , l_role_irid
     , 'WA' -- for now let us pretend a ROLE is a WA (Workarea);
            -- that allows us to grant Compile (= Reconcile) privs;
            -- the Check Constraint SDW_ACCESS_RIGHTS_CHECK1 forces us
            -- currently to use e\one of CEL, WA, CFG
     , l_new_acc_privs
     );
   else -- grantee already has some privileges on this role; now update
     if p_override
     then
        l_summ_acc_privs:= l_new_acc_privs;
     else
        l_summ_acc_privs:= OR_PRIVS
                           ( first  => l_new_acc_privs
                           , second => l_grantee_acc_privs
                          );
     end if;
     update sdw_access_rights
     set    access_privs = l_summ_acc_privs
     where  grantee_reference = l_grantee
     and    object_reference = l_role_irid
     ;
   end if; -- jr_acc_rights.ar_to_str( l_grantee_acc_privs) = no_privs_string

   -- possibly reconcile this role for this user
   if  l_grantee_type = 'U' -- granting access to a user
   and p_reconcile
   then
      -- recursively reconcile this role for this user
      reconcile_role
      ( p_role_name => l_role_name
      , p_grantee   => l_grantee
      , p_recursive => true
      );
   end if; -- grantee_type/reconcile
end; -- grant_role

-- 2.0 new procedure
procedure revoke_role_privs
( p_role_to_revoke    in varchar2
, p_grantee           in varchar2
, p_recursive         in boolean
)
-- should privileges granted to the grantee through this role be revoked
-- (if they are not granted through another role as well)?
is
   -- cursor to select all access rights of a user
   cursor   c_acc
   (        b_user_name in varchar2
   )        is
   select   acc.object_reference
   ,        acc.access_privs
   ,        acc.object_type
   from     sdw_access_rights     acc
   where    acc.grantee_reference = b_user_name
   ;
   -- cursor to select privileges of certain grantee/object combination
   cursor   c_privs
   (        b_grantee_reference in varchar2
   ,        b_object_reference  in number
   )        is
   select   acc.access_privs
   from     sdw_access_rights acc
   where    acc.grantee_reference = b_grantee_reference
   and      acc.object_reference = b_object_reference
   ;
   r_privs_revoked_role  c_privs%rowtype;
   r_privs_other_roles   c_privs%rowtype;
   l_privs_to_revoke     sdd_acc_priv_list;
   l_new_privs           sdd_acc_priv_list;
begin
   -- clean up the privileges of the dummy users, and the users themselves
   -- in case they are left over from previous operations
   delete sdw_access_rights
   where  grantee_reference
          in (DUMMY_USER_REVOKED_ROLE, DUMMY_USER_OTHER_ROLES)
   ;
   delete sdw_users
   where  username in (DUMMY_USER_REVOKED_ROLE, DUMMY_USER_OTHER_ROLES)
   ;
   g_enforce_role_admin:= false;
   -- 2.0 revoke actual user privs:
   -- create dummy users
   INSERT INTO SDW_USERS
   ( IRID
   , IVID
   , user_type
   , USERNAME
   , FULL_USER_NAME
   , DESCRIPTION
   , SYSTEM_PRIVS
   )
   VALUES
   ( Jr_Util.get_new_irid
   , 1
   , 1  -- user_type = 1 indicates a user
   , DUMMY_USER_REVOKED_ROLE
   , DUMMY_USER_REVOKED_ROLE
   , null
   , null
   );
   INSERT INTO SDW_USERS
   ( IRID
   , IVID
   , user_type
   , USERNAME
   , FULL_USER_NAME
   , DESCRIPTION
   , SYSTEM_PRIVS
   )
   VALUES
   ( Jr_Util.get_new_irid
   , 1
   , 1  -- user_type = 1 indicates a user
   , DUMMY_USER_OTHER_ROLES
   , DUMMY_USER_OTHER_ROLES
   , null
   , null
   );

   -- build list A of privs granted through the revoked role
   -- (if p_recursive, then recursively) (using dummy user)
   add_line(ROB_msg.getMsg(Rob_msg.MSG226_JR_DETERPRIV, p_grantee,p_role_to_revoke, '', ''));
   grant_role
   ( p_role_to_grant => p_role_to_revoke
   , p_grantee       => DUMMY_USER_REVOKED_ROLE
   , p_reconcile     => false -- here we cannot influence recursiveness
   );
   reconcile_role
   ( p_role_name     => p_role_to_revoke
   , p_grantee       => DUMMY_USER_REVOKED_ROLE
   , p_recursive     => p_recursive
   );
   -- build a list B of all privs granted through all other roles of p_grantee
   -- (the role to revoke is already removed from the grantee)
   add_line(ROB_msg.getMsg(Rob_msg.MSG227_JR_DETERPRIVANDROLE, p_grantee, '', '', ''));
   for r_granted_roles in c_granted_roles
                          ( b_grantee => p_grantee
                          )
   loop
      grant_role
      ( p_role_to_grant => r_granted_roles.role_name
      , p_grantee       => DUMMY_USER_OTHER_ROLES
      , p_reconcile     => true -- will always do recursive reconcile
      -- is correct, because all privs granted (recursively) through other roles
      -- must stay
      );
   end loop; -- granted roles
   -- now traverse all actual user privs of the grantee;
   -- any priv that occurs in list A and NOT in list B should be revoked
   for r_acc in c_acc(p_grantee)
   loop
      -- check if DUMMY_USER_REVOKED_ROLE (list A) contains row for this object
      open c_privs(DUMMY_USER_REVOKED_ROLE, r_acc.object_reference);
      fetch c_privs into r_privs_revoked_role;
      if c_privs%notfound
      then
         close c_privs;
         -- list A is empty, there are no privs belonging to the revoked role,
         -- so the object grants of the grantee can stay the same
      else
         close c_privs;
         -- list A contains row for this object
         -- check if DUMMY_USER_OTHER_ROLES (list B) contains row for this object
         open c_privs(DUMMY_USER_OTHER_ROLES, r_acc.object_reference);
         fetch c_privs into r_privs_other_roles;
         if c_privs%notfound
         then
            close c_privs;
            -- object is present in list B,
            -- it is not granted at all through other roles;
            -- revoke privileges that came through revoked role
            l_privs_to_revoke := r_privs_revoked_role.access_privs;
         else
            close c_privs;
            -- both list A and list B contain this object,
            -- keep privileges that are not granted through other roles:
            -- revoke what's in A but not in B
            l_privs_to_revoke := AND_privs
                                 ( r_privs_revoked_role.access_privs
                                 , NOT_privs(r_privs_other_roles.access_privs)
                                 );
         end if; -- list B contains this object
         -- change the user privs of this object:
         -- only leave the privs that are not in l_privs_to_revoke
         l_new_privs := AND_privs
                        ( r_acc.access_privs
                        , NOT_privs(l_privs_to_revoke)
                        );
         if jr_acc_rights.ar_to_str(l_new_privs)
         =  jr_acc_rights.ar_to_str(jr_acc_rights.no_access_rights)
         then
            -- no privileges at all left for this object,
            -- delete the row from sdw_access_rights
            add_line(ROB_msg.getMsg(Rob_msg.MSG228_JR_REMPRIV, get_object_label( r_acc.object_reference, r_acc.object_type), '', '', ''));
            delete sdw_access_rights
            where  grantee_reference = p_grantee
            and    object_reference = r_acc.object_reference
            ;
         elsif jr_acc_rights.ar_to_str(l_new_privs)
         <>    jr_acc_rights.ar_to_str(r_acc.access_privs)
         then
            -- change the acc. rights of the grantee for this object
            add_line(ROB_msg.getMsg(Rob_msg.MSG229_JR_REMSOMEPRIV, get_object_label( r_acc.object_reference, r_acc.object_type), '', '', ''));
            update sdw_access_rights
            set    access_privs = l_new_privs
            where  grantee_reference = p_grantee
            and    object_reference = r_acc.object_reference
            ;
         end if; -- l_new_privs
      end if; -- list A contains this object
   end loop; -- acc
   -- clean up the privileges of the dummy users, and the users themselves
   delete sdw_access_rights
   where  grantee_reference
          in (DUMMY_USER_REVOKED_ROLE, DUMMY_USER_OTHER_ROLES)
   ;
   delete sdw_users
   where  username in (DUMMY_USER_REVOKED_ROLE, DUMMY_USER_OTHER_ROLES)
   ;
   g_enforce_role_admin:= true;
end revoke_role_privs;

-- Revokes a role from another role or a user.
-- Can only be called by a user that
-- * has Admin privileges on the granted role
procedure revoke_role
( p_role_to_revoke    in varchar2  -- which role is to be revoked
, p_grantee           in varchar2  -- username or rolename of the grantee
  -- (who is the role curtently granted to and to be revoked from)
, p_revoke_privs      in boolean   default false
  -- should privileges granted to the grantee through this role be revoked
  -- (if they are not granted through another role as well)?;
  -- only applicable if the p_grantee is a user, not a role
, p_recursive         in boolean   default false
  -- should privileges of the grantee acquired through the nested roles
  -- of the revoked role be revoked
  -- (if they are not granted through another role as well)?;
  -- only applicable when p_revoke_privs = true
) is
   l_role_name           varchar2(30) := proper(p_role_to_revoke);
   l_role_irid           number(38)   := get_role_irid(p_role_name => l_role_name);
   l_grantee             varchar2(30) := proper(p_grantee);
   -- R(ole) or U(ser)
   l_grantee_type        varchar2(1)  := get_grantee_type(p_grantee => p_grantee);
begin
   reset_error_description;
   -- 3.0 new check
   check_role_exists(l_role_name);

   -- check p_grantee exists; also determine type (Role or User)
   if nvl(l_grantee_type,'X') not in ('R','U')
   then
      -- grantee is not a known user
      set_error_description(ROB_msg.getMsg(Rob_msg.MSG230_JR_NOTUSERORROLE, p_grantee, '', '', ''));
      raise jr_acc_rights.access_rights_error;
   end if; -- nvl(l_grantee_type,'X') not in ('R','U')
   if l_role_irid is null
   then
      set_error_description(ROB_msg.getMsg(Rob_msg.MSG231_JR_NOTROLE, p_role_to_revoke, '', '', ''));
      raise jr_acc_rights.access_rights_error;
   end if;
   -- Check user has Admin privileges on Role
   if  jr_acc_rights.has_access
       ( object_irid  => l_role_irid
       , grantee      => USER
       , privilege    => 'ADM'
       )
       or
       g_enforce_role_admin = false -- internal use when revoking role with reconcile
   then
      null; -- is OK
   else
      set_error_description
      ( ROB_msg.getMsg(Rob_msg.MSG232_JR_NOADMINPRIV, user, l_role_name, '', '')
      );
      raise jr_acc_rights.access_rights_error;
   end if;

   -- Revoke the role itself (not its derived acc. rights yet) from the grantee
   delete sdw_access_rights
   where  grantee_reference = l_grantee
   and    object_reference  = l_role_irid
   ;
   add_line(ROB_msg.getMsg(Rob_msg.MSG233_JR_REMROLE, p_role_to_revoke, l_grantee, '', ''));
   if l_grantee_type = 'U' -- revoking the role from a user
   and p_revoke_privs
   then
      -- 2.0 revoke the privileges that were granted through the role
      -- unless they are also granted through other roles
      add_line(ROB_msg.getMsg(Rob_msg.MSG234_JR_REVOKEPRIV, l_grantee, l_role_name, l_grantee, ''));
      revoke_role_privs
      ( p_role_to_revoke => l_role_name
      , p_grantee        => l_grantee
      , p_recursive      => p_recursive
      );
   end if; -- p_grantee is a User (not a role) and p_revoke_privs
end; -- revoke_role


procedure propagate_role_privs_to_user
( p_role_name in varchar2
, p_grantee   in varchar2
, p_recursive in boolean
) is
   l_grant_irid     number(38);
   l_acc_privs      sdd_acc_priv_list:=sdd_acc_priv_list();
   l_role_acc_privs sdd_acc_priv_list:=sdd_acc_priv_list();
   l_user_acc_privs sdd_acc_priv_list:=sdd_acc_priv_list();
   cursor   c_acc
   (        b_role_name in varchar2
   )        is
   select   acc.object_reference
   ,        acc.access_privs
   ,        acc.object_type
   from     sdw_access_rights     acc
   where    acc.grantee_reference = b_role_name
   and      ( object_type <> 'WA'   -- exclude Role to Role grants
            or
              exists ( select 'x'
                       from   i$sdd_workareas
                       where  irid = acc.object_reference
                     )
            )
   ;
   -- $%2.0$ own cursor needed here because of recursive calls,
   -- otherwise you get error ORA-06511: PL/SQL: cursor already open
   cursor c_granted_roles
   (b_grantee in varchar2)
   is
   select rle.username         role_name
   ,      acc.object_reference role_irid
   from   sdw_users            rle
   ,      sdw_access_rights    acc
   where  acc.grantee_reference = b_grantee
   and    acc.object_reference  = rle.irid
   ;
begin
   -- first of all. grant the privs of this role to the grantee
   if in_role_tbl( p_role_name => p_role_name)
   then
      null;  -- already examined/granted/propagated this role
   else
      add_to_role_tbl( p_role_name => p_role_name);
      -- do not process when next encountered
      for r_acc in c_acc( b_role_name => p_role_name) loop
         l_user_acc_privs:= jr_acc_rights.get_acc_rights
                            ( object_irid => r_acc.object_reference
                            , grantee     => p_grantee
                            );
         -- if the user has no access privs at all on the object
         -- then insert a set of privs identical to the role privs
         -- else update the existing set to also include the role privs
         if jr_acc_rights.ar_to_str( l_user_acc_privs) = no_privs_string
         then
            add_line( ROB_msg.getMsg(Rob_msg.MSG235_JR_CREATEPRIV, '', '', '', '')||get_object_label( r_acc.object_reference, r_acc.object_type));
            l_grant_irid := jr_util.get_new_irid;
            insert into SDW_ACCESS_RIGHTS
            ( IRID
            , IVID
            , GRANTEE_REFERENCE
            , GRANTOR_REFERENCE
            , OBJECT_REFERENCE
            , OBJECT_TYPE
            , ACCESS_PRIVS)
            VALUES
            ( l_grant_irid
            , 1
            , p_grantee
            , USER
            , r_acc.object_reference
            , r_acc.object_type
            , r_acc.access_privs
            );
         else
            l_role_acc_privs:=  r_acc.access_privs;
            l_acc_privs:= OR_PRIVS
                          ( first  => l_role_acc_privs
                          , second => l_user_acc_privs
                          );
            add_line( ROB_msg.getMsg(Rob_msg.MSG236_JR_UPDATEPRIV, '', '', '', '')||get_object_label( r_acc.object_reference, r_acc.object_type));
            update sdw_access_rights
            set    access_privs = l_acc_privs
            where  grantee_reference = p_grantee
            and    object_reference = r_acc.object_reference
            ;
         end if; -- l_user_acc_privs = jr_acc_rights.no_access_rights
      end loop; -- r_acc

      -- now if p_recursive, we should investigate all nested roles of p_role_name
      -- and grant the associated privileges to the grantee
      if p_recursive
      then
         add_line(ROB_msg.getMsg(Rob_msg.MSG237_JR_RECONCILEROLE, p_grantee, p_role_name, '', ''));
         for r_granted_roles in c_granted_roles( b_grantee => p_role_name) loop
            propagate_role_privs_to_user
            ( p_role_name => r_granted_roles.role_name
            , p_grantee   => p_grantee
            , p_recursive => p_recursive
            );
         end loop; -- r_granted_roles
      end if; -- p_recursive = true
   end if; -- not in role table already
end; -- propagate_role_privs_to_user


-- in so far they do not have them already, grant all object privileges granted to role p_role_nanme
-- to p_grantee (if p_grantee is not null) or
-- all users who have been granted this role, either directly or indirectly through nested roles
-- if p_recursive = true, then do not just reconcile role p_role_name but also all of its nested roles
procedure reconcile_role
( p_role_name in varchar2
, p_grantee   in varchar2 default null
, p_recursive in boolean  default false  -- also reconcile all nested roles
)
is
   l_role_name      varchar2(30):= proper( p_role_name);
   l_role_irid    number(38)    := get_role_irid( p_role_name => l_role_name);
   cursor   c_usr
   (        b_grantee in varchar2
   )        is
   select   usr.username
   from     sdw_users usr
   where    usr.user_type = 1  -- only care about USERS, not ROLES
   and      usr.username  = nvl( b_grantee, usr.username)
   ;
begin
   reset_error_description;
   -- 3.0 new check
   check_role_exists(l_role_name);
   -- first check whether the invoker (USER) has the privilege to reconcile this role
   -- Check user has Admin privileges on Role
   if  jr_acc_rights.has_access
       ( object_irid  => l_role_irid
       , grantee      => USER
       , privilege    => 'CMP'
       )
       or
       g_enforce_role_admin = false
   then
      null; -- is OK
   else
      -- 3.0 set error description
      set_error_description
      (ROB_msg.getMsg(Rob_msg.MSG238_JR_NORECONPRIV, USER, l_role_name, '', '')
      );
      raise jr_acc_rights.access_rights_error;
   end if;

   -- it seems more performant to loop over all users,
   -- determine of a user is a grantee of the role
   -- and then loop over all access privileges for this role;
   -- it probably makes it easier to plug in the recursive structure
   for r_usr in c_usr
                ( b_grantee => p_grantee
                ) loop
      if jr_role.is_grantee_of_role
         ( p_grantee    => r_usr.username
         , p_role_name  => l_role_name
         , p_recursive  => true  -- either directly or indirectly a grantee
         )
      then
        add_line( ROB_msg.getMsg(Rob_msg.MSG239_JR_RECONCILE, r_usr.username, '', '', ''));
        init_role_tbl;
        propagate_role_privs_to_user
        ( p_role_name => l_role_name
        , p_grantee   => r_usr.username
        , p_recursive => p_recursive
        );
      end if; -- user is a grantee of the role
   end loop; -- r_usr
end; -- reconcile_role


-- Ensure that user p_user_name has at least all the privileges that he is entitled to
-- through any role he has directly or indirectly (nested) (been granted).
-- If p_revoke is false, no privileges are removed for the user; the procedure
-- will only extend the current set of user privileges.
-- If p_revoke is true, then the procedure will build set of user privileges from scratch, only based on roles
-- the user will have no privileges besides the ones granted to him through any of the roles
-- he has (directly or indirectly)
-- There is one exception: the procedure will not remove any privileges on an object that the user is the
-- current owner of.
-- This procedure can only be invoked by a user with the Manage_users system privilege.
-- WARNING: if p_revoke = true AND if a user has been granted privileges completely outside
-- the scope of the project that you are responsible for
-- he will lose privileges on those objects outside of the project scope if they have not been granted through roles!
procedure reconcile_user
( p_user_name in varchar2
, p_revoke    in boolean  default false  -- build set of user privileges from scratch, only based on roles
) is
  l_user_name      varchar2(30):= proper( p_user_name);
  l_grantee_type varchar2(1)   := get_grantee_type( p_grantee => p_user_name); -- R(ole) or U(ser)
begin
  reset_error_description;
  check_upd_users;  -- require users to have the manage_users privilege before they can cerate roles!
  -- check p_user_name exists and is of type User
  if nvl(l_grantee_type,'X') <> 'U'
  then
    -- grantee is not a known user
    set_error_description(ROB_msg.getMsg(Rob_msg.MSG240_JR_NOUSER, l_user_name, '', '', ''));
    raise jr_acc_rights.access_rights_error;
  end if; -- nvl(l_grantee_type,'X') <> 'U'

  -- now if p_revoke = true
  -- remove all the access privileges on objects that p_user_name is not the owner of
  if p_revoke
  then
    delete sdw_access_rights acc
    where  grantee_reference = p_user_name
    and    grantee_reference <> grantor_reference -- check if user is NOT owner of object
    and    not exists ( select 'x'   -- do not need Role Grants!
                        from   sdw_users usr
                        where  usr.irid = acc.object_reference
                      )
    ;
  end if; -- p_revoke = true
  -- now loop over all roles that have been granted to the user and reconcile every role
  -- for the user (recursively)
  for r_granted_roles in c_granted_roles
               ( b_grantee => l_user_name
               ) loop
      add_line( ROB_msg.getMsg(Rob_msg.MSG239_JR_RECONCILE, r_granted_roles.role_name, '', '', ''));
      init_role_tbl;
      propagate_role_privs_to_user
      ( p_role_name => r_granted_roles.role_name
      , p_grantee   => l_user_name
      , p_recursive => true
      );
  end loop; -- r_acc
end; -- reconcile_user


--
-- new in 3.0: backup/restore program units
--

function backup_exists
return boolean
-- Returns true if there is at least one role in the backup tables
is
   l_role_count    number;
   l_backup_exists boolean;
begin
   -- first check if table jr_role exists
   if table_exists('JR_ROLES')
   then
      -- check if it contains one or more roles
      execute immediate 'select count(*) from jr_roles'
      into l_role_count;
      l_backup_exists := l_role_count > 0;
   else
      l_backup_exists := false;
   end if; -- table_exists
   return l_backup_exists;
end backup_exists;

procedure backup
-- Overwrites content of backup tables with new backup of roles / role access rights
-- If tables do not exist yet, it creates them
is
begin
   reset_error_description;
   -- drop the backup tables if they exist
   if table_exists('JR_ROLES')
   then
      execute immediate 'drop table jr_roles';
   end if;
   if table_exists('JR_ROLE_ACCESS_RIGHTS')
   then
      execute immediate 'drop table jr_role_access_rights';
   end if;

   -- create the backup tables as a select from the sdw tables
   execute immediate 'create table jr_roles'
                     ||' as select *'
                     ||' from      sdw_users'
                     ||' where     user_type = 0'
                     ||' and       username <> ''PUBLIC'''
                     ;
   execute immediate 'create table jr_role_access_rights'
                     ||' as select *'
                     ||' from      sdw_access_rights'
                     ||' where     grantee_reference in'
                     ||'             (select username from jr_roles)'
                     ||'       or  object_reference  in'
                     ||'             (select irid     from jr_roles)'
                     ;
end backup;

procedure delete_all_roles
-- Removes all traces of non-proprietary roles from the Repository,
-- so that support is guaranteed.
is
begin
   reset_error_description;

   -- require users to have the manage_users privilege
   check_upd_users;
   -- first delete the role access rights
   delete from sdw_access_rights
   where  grantee_reference in
             (select username
              from   sdw_users
              where  user_type = 0
              and    username <> 'PUBLIC'
             )
      or  object_reference  in
             (select irid
              from   sdw_users
              where  user_type = 0
              and    username <> 'PUBLIC'
             )
   ;
   -- then delete the roles themselves
   delete from sdw_users
   where  user_type = 0
   and    username <> 'PUBLIC'
   ;
   -- commit;
end delete_all_roles;

procedure restore
-- Restores roles / role access rights from backup tables to Repository
-- If role already exists (must be with same irid, otherwise error) it is left alone.
-- If access rights record already exists for same object and grantee,
-- extra privileges might be added (existing privileges are never removed).
is
   type cursor_type is  ref cursor;
   c_rol                cursor_type;
   r_rol                sdw_users%rowtype; -- same record structure as jr_roles
   c_rac                cursor_type;
   r_rac                sdw_access_rights%rowtype; -- same record structure as jr_role_access_rights
   l_existing_acc_privs sdd_acc_priv_list := sdd_acc_priv_list();
   l_sum_acc_privs      sdd_acc_priv_list := sdd_acc_priv_list();
begin
   reset_error_description;
   if not backup_exists
   then
      set_error_description(ROB_msg.getMsg(Rob_msg.MSG241_JR_NOBACKUP, '', '', '', ''));
   else
      -- require users to have the manage_users privilege
      check_upd_users;
      -- first restore the roles
      -- need (native) dynamic sql because this package has to compile
      -- even if jr_roles does not exist (yet)
      open c_rol for 'select * from jr_roles';
      loop
         fetch c_rol into r_rol;
         exit when c_rol%notfound;
         -- check if this role name already exists in sdw_users
         if role_exists(r_rol.username)
         then
            -- check if irid is the same
            if get_role_irid(r_rol.username) = r_rol.irid
            then
               null; -- role already exists with correct irid
            else
               set_error_description
               ( ROB_msg.getMsg(Rob_msg.MSG242_JR_REWDID, r_rol.username, '', '', '')
               );
               rollback;
               raise e_role_different_irid;
            end if;
         else
            -- create role
            INSERT INTO SDW_USERS
            ( IRID
            , IVID
            , user_type
            , USERNAME
            , FULL_USER_NAME
            , DESCRIPTION
            , SYSTEM_PRIVS
            )
            VALUES
            ( r_rol.irid
            , r_rol.ivid
            , r_rol.user_type
            , r_rol.username
            , r_rol.full_user_name
            , r_rol.description
            , r_rol.system_privs
            );
         end if;
      end loop; -- select * from jr_roles
      -- then restore the access rights
      open c_rac for 'select * from jr_role_access_rights';
      loop
         fetch c_rac into r_rac;
         exit when c_rac%notfound;
         -- check if this role access right record
         -- already exists in sdw_access_rights
         l_existing_acc_privs:= jr_acc_rights.get_acc_rights
                                ( object_irid => r_rac.object_reference
                                , grantee     => r_rac.grantee_reference
                                );
         -- if the user has no access privs at all on the object
         -- then insert a set of privs identical to the role privs
         -- else update the existing set to also include the role privs
         if jr_acc_rights.ar_to_str(l_existing_acc_privs) = no_privs_string
         then
             -- create access right record
            insert into SDW_ACCESS_RIGHTS
            ( IRID
            , IVID
            , GRANTEE_REFERENCE
            , GRANTOR_REFERENCE
            , OBJECT_REFERENCE
            , OBJECT_TYPE
            , ACCESS_PRIVS
            )
            VALUES
            ( r_rac.irid
            , r_rac.ivid
            , r_rac.grantee_reference
            , r_rac.grantor_reference
            , r_rac.object_reference
            , r_rac.object_type
            , r_rac.access_privs
            );
         else
            -- only extend the privileges, do not revoke anything
            l_sum_acc_privs:= OR_PRIVS
                              ( first  => r_rac.access_privs
                              , second => l_existing_acc_privs
                              );
            update sdw_access_rights
            set    access_privs = l_sum_acc_privs
            where  grantee_reference = r_rac.grantee_reference
            and    object_reference = r_rac.object_reference
            ;
         end if; -- existing acc rights
      end loop; -- select * from jr_role_access_rights
      -- commit;
   end if; -- backup exists
end restore;


end JR_ROLE;
/