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.
AAI and AutoSys
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' ;
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.