Writer account preparation

Copied here for convenience:

-- G. McCance May 2006

create or replace package fts_writer_account
authid current_user is

-- PUBLIC methods

procedure make_grants(writer_account in varchar);

procedure make_synonyms(owner_account in varchar);

end fts_writer_account;
/

and

-- G. McCance May 2006

create or replace package body fts_writer_account
is

-- PRIVATE methods

procedure check_not_account
  (username in varchar)
as
begin
  if user = username THEN
     dbms_output.put_line('Both the other account and the account you are running from are the same!');
     dbms_output.put_line('You are running this from the wrong account!');
     RAISE_APPLICATION_ERROR(-20101, 'You are running this from the wrong account!');
  end if;
end check_not_account;


-- PUBLIC methods

procedure make_grants
  (writer_account in varchar)
as
 writer_account_u varchar(255);
 grt varchar(1000);
begin
  dbms_output.put_line('Making grants...');
  writer_account_u := UPPER(writer_account);
  check_not_account(writer_account_u);
  for obj in ( select * from user_objects where generated = 'N' and object_type in ('TABLE', 'SEQUENCE', 'PACKAGE') ) loop
     if obj.object_type = 'TABLE' then
        grt := 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || obj.object_name || ' TO ' || writer_account_u;
     elsif obj.object_type = 'SEQUENCE' then
        grt := 'GRANT SELECT ON ' || obj.object_name || ' TO ' || writer_account_u;
     elsif obj.object_type = 'PACKAGE' then
        grt := 'GRANT EXECUTE ON ' || obj.object_name || ' TO ' || writer_account_u;
     end if;
     dbms_output.put_line('Running: ' || grt);
     execute immediate(grt);
  end loop;
end make_grants;

procedure make_synonyms
  (owner_account in varchar)
as
  owner_account_u varchar(255);
  syn varchar(1000);
begin
  owner_account_u := UPPER(owner_account);
  check_not_account(owner_account_u);
  for obj in ( select distinct table_name from all_tab_privs_recd where owner = owner_account_u ) loop
     syn := 'CREATE OR REPLACE SYNONYM ' || obj.table_name || ' FOR ' || owner_account_u || '.' || obj.table_name;
     dbms_output.put_line('Running: ' || syn);
     execute immediate(syn);
  end loop;
end make_synonyms;


end fts_writer_account;
/