REM FILE	: ckrepini.sql
REM	DATE	: 30/8/96
REM	Usage	: Initialise Repository Users and Access Rights
REM           This file is submitted by the RAU through RM

DECLARE
  users_count integer;              -- check that no >1 repository owner
  too_many_owners EXCEPTION;

  prev_rm_owner varchar2(30):=NULL; -- name of repository manager owner
                                    -- req'd if repository relocated by 
                                    -- import
  debug  boolean := FALSE;          -- change this to true to track process
  --VARRAY to hold access privs
  acc_privs sdd_acc_priv_list:=sdd_acc_priv_list(); 
  
  -- BUG 1249743 Need to grant on some Objects EXPLICITLY for Migration by a subordinate (see bug)
  PROCEDURE  DO_EXPLICIT (username IN VARCHAR2) IS   
     grant_sql VARCHAR2(1000);
	 rm_supports_designer VARCHAR2(1); 
     csr       NUMBER;
  BEGIN
  csr:=DBMS_SQL.OPEN_CURSOR;

  -- Removed un-initialised grant_sql statement #1324149
  
  --Need to grant access to types EXPLICITLY
  grant_sql:='GRANT EXECUTE ON SDD_ACC_PRIV_LIST TO "'||username||'"';
  DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE); 
  
  grant_sql:='GRANT EXECUTE ON SDD_SYS_PRIV_LIST TO "'||username||'"';
  DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE); 
  
  -- BUG 1249743 Need to grant on some Objects EXPLICITLY for Migration by a subordinate (see bug)
  grant_sql:='GRANT SELECT ON SDD_DEPENDENCY_TYPES TO "'||username||'"';
  DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE); 
 
  grant_sql:='GRANT SELECT ON I$RM_ELEMENT_TYPES TO "'||username||'"';
  DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE); 
  
  grant_sql:='GRANT SELECT ON SDD_GLOBAL_NAMES TO "'||username||'"';
  DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE); 
  
  grant_sql:='GRANT EXECUTE ON JR_UTIL TO "'||username||'"';
  DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE); 
  
  grant_sql:='GRANT EXECUTE ON JR_SYSTEM_UTIL TO "'||username||'"';
  DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE); 
  
  SELECT rm.supports_designer 
    INTO rm_supports_designer
    FROM RM$REPOSITORIES rm;
  -- If the REPOSITORY supports DESIGNER Grant EXECUTE on CKMIGFUN
  IF rm_supports_designer = 'Y' THEN
   grant_sql:='GRANT EXECUTE ON CKMIGFUN TO "'||username||'"';
   DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE);
  END IF;
  
  --We need to create a private synonym on RM$REPOSITORIES so that RAU
  --can recognise Oracle users already assigned to a repository instance
  grant_sql:='CREATE SYNONYM "'||username||'".RM$REPOSITORIES '||
                        'FOR '||USER||'.RM$REPOSITORIES';
  DBMS_SQL.PARSE(csr,grant_sql,DBMS_SQL.NATIVE);
 
  DBMS_SQL.CLOSE_CURSOR(csr);
 EXCEPTION
  when OTHERS then
   if DBMS_SQL.IS_OPEN(csr) then
    DBMS_SQL.CLOSE_CURSOR(csr);
   end if;
   NULL;
  END DO_EXPLICIT;
   
BEGIN
  jr_context.disable(jr_context.CHECK_WORKAREA_CONTEXT);
  jr_context.disable(jr_context.NO_CHECKIN_UPDATE);
  jr_context.disable(jr_context.ACCESS_RIGHTS);

  update ck_installed_objects 
  set ci_created = 'N'
  where ci_stage='CKREPINI';

  -- Check there is only one repository owner.
  -- Stop and report error if >1. Get the current repository owner.
  select count(*)
    into   users_count
  from sdw_users
  where sdd_owner = 'Y'
  ;
  If users_count > 1 Then
    RAISE too_many_owners;
  End If;
  -- Get the current repository owner
  -- use MAX function here, so that NO_DATA_FOUND exception never fires  
  select max(username)
    into prev_rm_owner
  from   sdw_users
  where  sdd_owner = 'Y'
  ;
  If debug Then
    dbms_output.put_line(
      'Current/Previous Repository Owner : '||prev_rm_owner);     
  End If;

  select count(*)
    into users_count
    from sdw_users
   where username='PUBLIC';

  If users_count = 0 then
    -- Need to create a PUBLIC role
    insert into SDW_USERS
     (irid
     ,ivid
     ,user_type
     ,username
     ,full_user_name
     ,sdd_owner
     ,system_privs)
    values
     (jr_util.get_new_irid
     ,1
     ,0
     ,'PUBLIC'
     ,'PUBLIC'
     ,'N'
     ,sdd_sys_priv_list('N','N','N','N'
                       ,'N','N','N','N'
                       ,'N','N','N','N'
                       ,'N','N','N','N'
                       ,'N','N','N')
     );
  end if;

  If prev_rm_owner is null Then
    -- A new installation of a repository. Simply create
    -- a new Repository Owner/User i.e. won't currently exist
    insert into SDW_USERS
     (irid
     ,ivid
     ,user_type
     ,username
     ,full_user_name
     ,sdd_owner
     ,system_privs)
    values
     (jr_util.get_new_irid
     ,1
     ,1
     ,USER
     ,USER
     ,'Y'
     ,sdd_sys_priv_list('Y','Y','Y','Y'
                       ,'Y','Y','Y','Y'
                       ,'Y','Y','Y','Y'
                       ,'Y','Y','Y','Y'
                       ,'Y','Y','Y')
     );

    If debug and SQL%ROWCOUNT = 1 Then
      dbms_output.put_line(
        'Repository owner created' || user);     
    End If;

  Else
    -- This is either an upgrade, a restoration of a repository
    -- from an import or full reconcilliation of access rights. 
    -- In the case of restoration, it is possible that the 
    -- repository has been relocated to a new 'owner/user' 
    -- in which case this new user will inherit any access rights of 
    -- the previous owner together with its own.
    -- Note that relocating a repository to a new user, will completely
    -- remove the old owner from the set of registered users and all
    -- its access rights.


    -- On relocation, delete new user if it existed as a subordinate.
    -- Note that any access rights will subsequently be reinherited 
    -- when it is reregistered as the owner in following statements.
    -- Also delete any other ex repository users which are not 
    -- registered in the Oracle instance users catalog but keep
    -- the repository owner (if relocating this is updated to the
    -- current user in the following statement).

    -- Delete any repository users where there is no corresponding
    -- oracle user. Also delete any entry for the current user where
    -- they are a subordinate user. Don't delete PUBLIC!
    delete sdw_users
    where  username = user
      and  sdd_owner != 'Y'
       or  username in (
            select username
            from   sdw_users
            where  nvl(sdd_owner,'N') != 'Y'
              and  username!='PUBLIC'
           minus 
            select username
            from   all_users
           )
    ;
    If debug Then
      dbms_output.put_line(
         'Count of ex repository users deleted : ' || SQL%ROWCOUNT);     
    End If;

    -- Update the repository owners record. 
    -- This is only really necessary if repository was relocated
    -- but is done always just to keep the logic simple.
    update sdw_users
    set    username  = user
     ,     full_user_name   = user
    where  sdd_owner        = 'Y'
    ;


    -- The sdw_users data should now be consistent with ALL_USERS,
    -- and there will be one repository owner record which = the
    -- current user.

    -- Update the ownership property of any application systems 
    -- which have lost their owners via the previous steps, so
    -- that they are now owned by the repository owner.
    
        -- Containers:
        update i$sdd_folders
        set    owning_user = user
        where  owning_user in (
            select distinct grantee_reference 
            from   sdw_access_rights
           minus
            select username
            from   sdw_users
           )
    ;   
    If debug Then
      dbms_output.put_line(
       'Count of containers with updated ownership: ' ||SQL%ROWCOUNT);     
    End If;
    
        -- Workareas:
        update i$sdd_workareas
        set    owner = user
        where  owner in (
            select distinct grantee_reference 
            from   sdw_access_rights
           minus
            select username
            from   sdw_users
           )
    ;
    -- ********************************************************************
 
    If debug Then
      dbms_output.put_line(
       'Count of workareas with updated ownership: ' ||SQL%ROWCOUNT);     
    End If;

    -- Delete remaining access rights of ex repository users,
    -- except for rows for application system owners
    -- (ar_user_granted_by = ar_user_granted_to), noting that
    -- it is much quicker to update and set these for the 
    -- repository owner than to insert/create.
    -- Note condition for deleting AR to current Repos OWNER!
    delete sdw_access_rights
    where  grantor_reference != grantee_reference
      and 
           grantee_reference not in (
            select username
            from   sdw_users
           )
       or (      -- delete AR to current repos OWNER, see also next step
           grantor_reference not in (
            select username
            from   sdw_users
           )
           and grantee_reference = USER
          )
    ;
    If debug Then
      dbms_output.put_line(
        'Count of ex repository user access rights deleted : ' || SQL%ROWCOUNT);     
    End If;

    -- Update access rights granted by ex repository users so
    -- that they are consistent with the application owner property
    -- i.e. owned by the repository owner.
    update sdw_access_rights
    set    grantor_reference = user
     ,     grantee_reference = decode(grantor_reference
                                     ,grantee_reference, user
                                     ,grantee_reference)
    where  grantor_reference not in (
             select username
             from   sdw_users
           )
    ;        
    If debug Then
      dbms_output.put_line(
        'Count of ex repository user access rights updated : ' || SQL%ROWCOUNT);     
    End If;

    -- Always ensure that managers/owners of an application system
    -- have the minimum required privileges for it.
    for acc_rights in (select irid,access_privs
                         from sdw_access_rights
                        where grantor_reference=grantee_reference)
    loop
     acc_privs:=acc_rights.access_privs;
     
     if ((acc_privs(jr_acc_rights.get_acc_type('SEL'))='N') 
       or(acc_privs(jr_acc_rights.get_acc_type('ADM'))='N')) then
      acc_privs(jr_acc_rights.get_acc_type('SEL')):='Y';
      acc_privs(jr_acc_rights.get_acc_type('ADM')):='Y';

      update sdw_access_rights ar
      set    ar.access_privs=acc_privs
      where  ar.irid = acc_rights.irid;
     end if;
    end loop;
    
    If debug Then
      dbms_output.put_line(
        'Count of ex repository user preferences deleted : ' || SQL%ROWCOUNT);     
    End If;

    -- Reconcile the ownership property of application systems to the
    -- ownership criteria in access rights 
    -- (ar_usr_granted_by=ar_usr_granted_to).
    -- This is a recovery step for repositories which have got out of step
    -- when migrating from one schema to another.

    --Conainters:
    update i$sdd_folders ap
    set    ap.owning_user = (
                              select distinct xr.grantor_reference
                              from   sdw_access_rights  xr
                              where  ap.irid = xr.object_reference
                                and  xr.grantee_reference=xr.grantor_reference
                                 )
    where  ap.irid in (
            select distinct xr.object_reference
            from   sdw_access_rights xr
            where  xr.grantee_reference=xr.grantor_reference
              and  ap.owning_user!=xr.grantee_reference
           )
    ; 
  
    If debug Then
      dbms_output.put_line(
       'Count of application systems with reconciled ownership:' ||SQL%ROWCOUNT);     
    End If;
 
    --Workareas:
    update i$sdd_workareas wa
    set    wa.owner = (
                        select distinct xr.grantor_reference
                        from   sdw_access_rights  xr
                        where  wa.irid = xr.object_reference
                          and  xr.grantee_reference=xr.grantor_reference
                                 )
    where  wa.irid in (
            select distinct xr.object_reference
            from   sdw_access_rights xr
            where  xr.grantee_reference=xr.grantor_reference
              and  wa.owner!=xr.grantee_reference
           )
    ; 
  
    If debug Then
      dbms_output.put_line(
       'Count of workareas with reconciled ownership:' ||SQL%ROWCOUNT);     
    End If;

  End If;

  -- if we have got this far evereything should hacve worked correctly 
  -- update ck_installed_objects to reflect this
 
  update ck_installed_objects 
  set ci_created = 'Y'
  where ci_stage='CKREPINI';
  
  commit;

  jr_context.enable(jr_context.CHECK_WORKAREA_CONTEXT);
  jr_context.enable(jr_context.NO_CHECKIN_UPDATE);
  jr_context.enable(jr_context.ACCESS_RIGHTS);
  
  -- BUG 1249743 Need to grant on some Objects EXPLICITLY for Migration by a subordinate (see bug)
  dbms_output.enable(1000000);
  FOR this_user in (select username 
                    from   sdw_users 
       			    where  nvl(sdd_owner, 'N') = 'N' 
  					and user_type = 1 
  					and nvl(STATUS,'X') != 'D' )
  LOOP
     dbms_output.put_line('Performing Explicit Grants for '||this_user.username);
     DO_EXPLICIT (this_user.username);
  END LOOP;

  EXCEPTION
    -- something has gone wrong, update ck_installed objects to reflect this
    When others then
	  jr_context.enable(jr_context.CHECK_WORKAREA_CONTEXT);
	  jr_context.enable(jr_context.NO_CHECKIN_UPDATE);
	  jr_context.enable(jr_context.ACCESS_RIGHTS);
      raise;
END;
/





