Show Children Hide Children

Main FTS Pages
FtsRelease22
Install
Configuration
Administration
Procedures
Operations
Development
Previous FTSes
FtsRelease21
FtsRelease21
All FTS Pages
FtsWikiPages
Last Page Update
GavinMcCance
2007-06-15

Writer account preparation

This step is for the CERN Oracle RAC databases. See your DBA about your site's DB account setup.

Load the package into the schema owner account

Load the writer account package from sqlplus into the schema OWNER.

This is available from the current glite-data-transfer-scripts-0.1.7-1.noarch.rpm RPM.

sqlplus username/passwd@"connectstring" < /opt/glite/share/glite-data-transfer-scripts/plsql/fts_writer_account_pack.sql

sqlplus username/passwd@"connectstring" </opt/glite/share/glite-data-transfer-scripts/plsql/fts_writer_account_pack_body.sql

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

Make the grants in the owner account

Run this from sqlplus on the schema OWNER account. It will make the necessary grants to the specified writer account.

exec fts_writer_account.make_grants('lcg_fts_prod_w');

Make the synonyms in the writer account

Run this from sqlplus on the schema WRITER account. It will make the necessary synonyms to the schema objects in the specified owner account. Note you must prefix the package call with the name of the owner account (since this is who the package whose function your are calling belongs to).

exec lcg_fts_prod.fts_writer_account.make_synonyms('lcg_fts_prod');


Last edit: GavinMcCance on 2007-06-15 - 16:51
Number of topics: 1

Maintainer: GavinMcCance


Edit | Attach | Watch | Print version | History: r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r1 - 2007-06-15 - GavinMcCance
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LCG All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback