--
-- Revision History
-- Label      Date         Who                  What
------------------------------------------------------------------------------------------
--            18-jul-2002  ReposDev             Changes to enable for RAU load
------------------------------------------------------------------------------------------



create or replace package JR_ROLE is

  -- Author  : LJELLEMA
  -- Created : 11-10-2000 09:35:00
  -- Purpose : Repository Role Management
  -- Revision History : see start of package body

--
-- public exceptions
--
e_role_does_not_exist   exception;
e_role_already_exists   exception;
e_role_different_irid   exception;
e_backup_does_not_exist exception;
  
function get_error_description
return varchar2
;
 
-- The following two functions were copied from jr_acc_rights and made public
function OR_PRIVS
(first  in out sdd_acc_priv_list 
,second in out sdd_acc_priv_list
) return sdd_acc_priv_list 
;
function AND_PRIVS
(first  in sdd_acc_priv_list
,second in sdd_acc_priv_list
) return sdd_acc_priv_list
;

-- 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 OR_PRIVS
( p_first  in varchar2
, p_second in varchar2
) return varchar2
;  
function AND_PRIVS
( p_first  in varchar2
, p_second in varchar2
) return varchar2
;  

-- this function returns the irid of a Role given its Role Name
function get_role_irid
( p_role_name in varchar2
) return number
;

function get_grantee_type
( p_grantee in varchar2
) return varchar2
;

-- this function returns the creator of a Role given its Role Name
function get_role_creator
( p_role_name in varchar2
) return varchar2
;
function get_object_owner
( p_irid in number
) return varchar2
;

function get_role_owner
( p_role_name in varchar2
) return varchar2
;


-- Create a new role (a new row in the SDW_USERS table)
-- The name of the role is 30 characters or shorter; it will be converted to UpperCase
-- and any spaces are replaced with underscores
-- The role name must be unique across the entire repository.
-- The function returns the IRID value of the new role (corresponds with the SDW_USERS.irid column)
-- Can only be called by a user with the Maintain Users Repository system privilege
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
;

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
) ;

-- 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
);

-- 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
( p_role_to_grant     in varchar2  -- which role is to be granted
, p_grantee           in varchar2  -- username or rolename of the grantee (who is the role granted to)
, p_grant_option      in boolean   default false -- 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_delete_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_reconcile_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         in boolean   default false -- should the grantee immediately be granted all user privileges that derive from this role?; only applicable when grantee is a user; 
, p_override          in boolean   default false -- should the existig rolegrant be updated (true) or just extended (false) with the privileges indicated here
);

-- 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
);


-- 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
;

PROCEDURE check_upd_users
;

function get_check_upd_users
return boolean
;
-- 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
-- Should be called by a user who has the Reconcile (CMP) privilege on this role
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
);

-- 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
);

--
-- backup/restore program units
--

function backup_exists return boolean;
-- Returns true if there is at least one role in the backup tables

procedure backup;
-- Overwrites content of backup tables with new backup of roles / role access rights
-- If tables do not exist yet, it creates them

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

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 priviliges might be added (existing privileges are never removed).

/* some additional queries to find out more details about the Roles and Privileges

query to list all Repository Roles 

select rle.username       rolename
,      rle.full_user_name full_rolename
,      rle.description    description
,      rle.created_by     creator
from   sdw_users          rle
where  rle.user_type = 0
order
by     1

query to find out which Roles are Granted to a certain user:

select rle.username rolename
,      substr( jr_acc_rights.ar_to_str( acc.access_privs), 2,1) grant_option
,      substr( jr_acc_rights.ar_to_str( acc.access_privs), 7,1) reconcile_option
from   sdw_access_rights acc
,      sdw_users         rle
where  acc.grantee_reference = <username>
and    acc.object_reference  = rle.irid
order
by     1

query to find out to which Users and Roles a certain role has been granted:

select decode
       ( grt.user_type
       , 0 , 'Role'
       , 1 , 'User'
       )            grantee_type
,      grt.username grantee
,      substr( jr_acc_rights.ar_to_str( acc.access_privs), 2,1) grant_option
,      substr( jr_acc_rights.ar_to_str( acc.access_privs), 7,1) reconcile_option
from   sdw_access_rights acc
,      sdw_users         grt
,      sdw_users         rle
where  acc.grantee_reference = grt.username
and    acc.object_reference  = rle.irid
and    rle.username          = <rolename>
order
by     1, 2


-- the following statement can help retrieve Roles in SDW_ACCESS_RIGHTS once they have
inadvertently be removed by Full Reconcile or Upgrade of the Repository

declare
  cursor c_rle 
  is
  select distinct 
         grantee_reference 
  from   sdw_access_rights acc
  where not exists 
        (select 'x' 
         from sdw_users usr
         where usr.username = acc.grantee_reference
         )
         ;
  l_irid number(38);
begin
  for r_rle in c_rle loop
    l_irid:= jr_role.create_role(p_role_name => r_rle.grantee_reference);
  end loop;  -- r_rle
end; 
*/     

end JR_ROLE;
/