How can I create Synonyms for all tables on the AutoSys database for the user AAI is using to connect to it?

Products

• Automation Analytics & Intelligence
• Automation Analytics & Intelligence

Issue/Introduction

It is now recommend ensuring that the user defined in the scheduler definition for connecting to the AutoSys database in AAI, has read access to all tables in AutoSys.

 

When using an Oracle database, the user may require to have public synonyms created for all tables.

 

This document will provide sample queries and script to help you create these public synonyms.

 

As always, work with your AutoSys DBA to create these synonyms for your environment.

Environment

AAI and AutoSys

Resolution

1. Using SQL Developer or SQL*Plus you can run the script below, making sure the replace the highlighted user with the user who owns the tables, usually it will be AEDBADMIN.

If you currently do not have any public synonyms created, you can use this script to create them automatically. However if it detects any public synonyms already created the the script will exit.

begin
  for r in (select owner, table_name from all_tables where owner = 'AEDBADMIN')
  loop
    execute immediate 'create public synonym ' || r.table_name || ' for ' || r.owner || '.' || r.table_name;
  end loop;
end;

 

2. If the script exits or you know you already have some public synonyms created, you can use the SQL below to generate the SQL for the synonym on each table:

SELECT 'CREATE PUBLIC SYNONYM "' || a.table_name || '" FOR "' || a.owner || '"."' || a.table_name || '";'
FROM   all_tables a
WHERE  NOT EXISTS (SELECT s.synonym_name
                   FROM   all_synonyms s
                   WHERE  s.synonym_name = a.table_name
                   AND    s.table_owner  = a.owner)
AND    a.owner = 'AEDBADMIN';

 

3. To verify the public synonyms are created you can use the select statement below:

select * from all_synonyms where owner ='PUBLIC' and table_owner = 'AEDBADMIN' ;

Additional Information

The above process creates public synonyms, if you wish to create synonyms for a specific user you can use a script like below.

You will need to modify the sections highlighted in yellow with the user name you wish to created and their password.

// Provide the credentials of the AutoSys database user 

// for the AAI integration and creation of Oracle synonyms.

define aedbadmin = 'AEDBADMIN';

define dbuser = 'AAI';

define passwd = 'password';

create user &&dbuser identified by &&passwd;

grant connect, create session to &&dbuser;

begin

  for r in (select owner, table_name from all_tables where owner = '&&aedbadmin')

  loop

    execute immediate 'create synonym ' || '&&dbuser' || '.' || r.table_name || ' for ' || r.owner || '.' || r.table_name;

    execute immediate 'grant select on ' || r.owner || '.' || r.table_name || ' to ' || '&&dbuser' ;

  end loop;

end;

 

 

If you have an existing database user and do not wish to create a new one, you can remove the two lines below:

 

create user &&dbuser identified by &&passwd;

grant connect, create session to &&dbuser;