-- Creating a database link to the database with connect string ashtmp with username system and password oracle
create database link rdbms_link connect to system identified by oracle using 'ashtmp';
set serveroutput on size 1000000
declare
cursor usernames is select upper(username) name from dba_users@rdbms_link;
cursor groups is select upper(role) role from dba_roles@rdbms_link;
cursor grantedroles is select upper(grantee) grantee, upper(granted_role) granted_role 
from dba_role_privs@rdbms_link;

cursor deleted is select subject_name from bism_subjects 
where subject_name!='BIBCAT' 
minus 
(select upper(username) from dba_users@rdbms_link
union
select upper(role) from dba_roles@rdbms_link);

cursor revokedroles is
select u.subject_name uname, g.subject_name gname from bism_groups, bism_subjects u, bism_subjects g where 
user_id = u.subject_id and group_id = g.subject_id
and user_id!=group_id
minus
select upper(grantee), upper(granted_role) 
from dba_role_privs@rdbms_link;

subjecttype bism_subjects.subject_type%type;
subname bism_subjects.subject_name%type;
userexists boolean := true;
groupexists boolean := true;
newguid bism_objects.object_id%type := null;
subid1 bism_subjects.subject_id%type;
subjecttype1 bism_subjects.subject_type%type;
subid2 bism_subjects.subject_id%type;
subjecttype2 bism_subjects.subject_type%type;
comboexists number :=0;
begin

-- loop for adding users to the Catalog from the RDBMS


for each in usernames loop

  if length(each.name)>64 then

    dbms_output.put_line(each.name||' can not be added to the Catalog because it has more than 64 chars');

  else

    begin
      userexists := true;
      -- checking user in the Catalog
      select SUBJECT_NAME,SUBJECT_TYPE into subname,subjecttype from bism_subjects where subject_name = each.name;
      -- the following condition should not happen but putting in an additional check
      if subname is null then
        userexists := false;
      end if;
    exception
      when no_data_found then
      userexists := false;
    end;

    if userexists = false then
      newguid := bism_utils.get_guid;
      insert into bism_subjects values (newguid,each.name,'u');
      insert into bism_groups values(newguid,newguid);
      -- give user some default privileges
      insert into bism_permissions values(newguid,'31',40);
 
      dbms_output.put_line('User with name '|| each.name||' has been added successfully');
    else
      if subjecttype = 'g' then
        dbms_output.put_line('A group already exists with name '|| each.name);
      else
        dbms_output.put_line('User '|| each.name ||' already exists');
      end if;
    end if;
  end if;
end loop;

-- loop for adding groups to the Catalog from the RDBMS

for eachgroup in groups loop

if length(eachgroup.role)>64 then

  dbms_output.put_line(eachgroup.role||' can not be added to the Catalog because it has more than 64 chars');

else

begin
  groupexists := true;
  -- checking group in the Catalog
  select SUBJECT_NAME,SUBJECT_TYPE into subname,subjecttype from bism_subjects where subject_name = eachgroup.role;
-- the following condition should not happen but putting in an additional check
if subname is null then
groupexists := false;
end if;
exception
when no_data_found then
groupexists := false;
end;

if groupexists = false then
newguid := bism_utils.get_guid;
insert into bism_subjects values (newguid,eachgroup.role,'g');
-- give user some default privileges
insert into bism_permissions values(newguid,'31',40);

dbms_output.put_line('Group with name '|| eachgroup.role||' has been added successfully');
else
if subjecttype = 'u' then
dbms_output.put_line('An user already exists with name '|| eachgroup.role);
else
dbms_output.put_line('Group '|| eachgroup.role ||' already exists');
end if;
end if;
end if;
end loop;


-- loop for adding users to groups within the Catalog from the RDBMS

for eachgrant in grantedroles loop


begin
comboexists:=1; 
select count(1) into comboexists from bism_groups where 
user_id =
(select subject_id from bism_subjects where subject_name = eachgrant.grantee and subject_type ='u')
and
group_id = 
(select subject_id  from bism_subjects where subject_name = eachgrant.granted_role and subject_type='g');
exception
when no_data_found then
comboexists := 0;
end;


begin

if comboexists = 0 then

  subid1:=null;
  subid2:=null;
  
  select subject_id,subject_type into subid1,subjecttype1 from bism_subjects where subject_name = eachgrant.grantee;
  select subject_id,subject_type into subid2,subjecttype2 from bism_subjects where subject_name = eachgrant.granted_role;
  -- only users can belong to groups
  if subjecttype1 <> 'u' then
    dbms_output.put_line(eachgrant.grantee||' is not a user. It can not be added to a group');
  elsif subjecttype2 <> 'g' then	
    dbms_output.put_line(eachgrant.granted_role||' is not a group. Users can not be added to it');
  else
	if subid1 is not null and subid2 is not null then
	  insert into bism_groups values(subid1,subid2);
      dbms_output.put_line('User '||eachgrant.grantee||' has been added to the group '|| eachgrant.granted_role);
	else
      dbms_output.put_line('Subject ids are null for the relationship between '||eachgrant.grantee||' and '||eachgrant.granted_role);
	end if;

  end if;

else

  dbms_output.put_line('The relationship between '||eachgrant.grantee||' and '||eachgrant.granted_role||' already exists');

end if;

exception
when no_data_found then
dbms_output.put_line('Either user '||eachgrant.grantee||' does not exist or group '||eachgrant.granted_role||' does not exist');
end;

end loop;

-- delete users and groups who are no longer in the RDBMS

for eachdeleted in deleted loop

delete bism_subjects where subject_name=eachdeleted.subject_name;
dbms_output.put_line('Deleted '||eachdeleted.subject_name);
end loop;

-- remove users from groups whose relationship no longer exists in the RDBMS

for eachrevoked in revokedroles loop

delete bism_groups where user_id = (select subject_id from bism_subjects where subject_name = eachrevoked.uname)
and group_id = (select subject_id from bism_subjects where subject_name = eachrevoked.gname);
dbms_output.put_line('Deleted relationship between user '||eachrevoked.uname||' and group '||eachrevoked.gname);
end loop;


end;
/
drop database link rdbms_link;

