How can I create Synonyms for all tables on the AutoSys database for the user AAI is using to connect to it?
Article ID: 270685
Updated On:
Products
Issue/Introduction
It is highly recommend ensuring that the user defined in the scheduler definition for connecting to the AutoSys database in AAI, has synonyms and is granted SELECT access to all tables in AutoSys when Autosys is using an Oracle database.
This should be verified:
Before adding an Autosys scheduler to AAI.
This should be checked again after any upgrade to Autosys or AAI.
If you do not have the syonyms and/or GRANT SELCT privledges added to the database user that AAI is using to connect to Autosys, you may see:
- Errors adding a new Autosys scheduler to Autosys
- Errors when the daily AAI Converter process runs to download new job definitions
- Errors when manually trying to update a scheduler to update job definitions.
In the jaws.log and/or hibernate.log there with be a message during the Converter run or manual download:
ORA-00942: table or view does not exist
For the daily converter run, you can search for messages like below and the ORA error should be seen within the full stack trace:
[ConverterServiceControl] Running converter for scheduler <Scheduler Name>
[ConverterServiceControl] Error occurred while converting scheduler
For a manual download you can search for a message below which should show the line before the error:
INFO [AdminOperationQueuePolicy] task finished: DOWNLOAD_SCHEDULER
ERROR [SchedulersService] caught unexpected Exception on update now Scheduler
For each of these there may be a corresponding log message in the hibernate.log with the error below:
ORA-00942: table or view does not exist
This document will provide sample queries and script to help you create these public synonyms and synonyms for a specific user that AAI is using to connect to the Autosys scheduler
As always, work with your AutoSys DBA to create these synonyms and "grant select" privileges for your environment.
Environment
AAI and AutoSys
Resolution
Below there are samples of finding and creating synonyms for either all users by creating PUBLIC synonyms or for the specific user that AAI is using to connect to the Autosys database.
All of these queries should be reviewed by your DBA and run with a user that has DB Admin privileges.
- PUBLIC SYNONYMS - To create synonyms for all users by creating PUBLIC synonyms for all tables in the Autosys schema follow the steps below:
(All examples below are assuming the Autosys schema is owned by the AEDBADMIN user, if you have a different user pleace replace AEDBADMIN user.)
1. To get a list of all tables in the Autosys schema that do not have PUBLIC synonyms, and to get the individual SQL statements to create those PUBLIC synonyms, run the SQL query below against the Autosys database. The output will give you SQL statements to create the PUBLIC synonyms for all of these tables:
--Get list of tables missing a PUBLIC synonym and provide the SQL to add it
SELECT 'CREATE OR REPLACE PUBLIC SYNONYM "' || a.table_name || '" FOR "' || a.owner || '"."' || a.table_name || '";'
FROM all_tables a
WHERE a.owner = 'AEDBADMIN'
AND NOT EXISTS (
SELECT 1
FROM all_synonyms s
WHERE s.owner = 'PUBLIC'
AND s.synonym_name = a.table_name
AND s.table_owner = a.owner
AND s.table_name = a.table_name
);
2. If you then wish to use one SQL script to create the missing PUBLIC synonyms for all of these tables, you can run the script below:
BEGIN
FOR r IN (SELECT owner, table_name FROM all_tables WHERE owner = 'AEDBADMIN') LOOP
EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM "' || r.table_name || '" FOR "' || r.owner || '"."' || r.table_name || '"';
END LOOP;
END;
3. You can run the script in step 1 and it should return no results if all tables now have PUBLIC synonyms
4. Another way 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' ;
5. Note the table names in step 2 as you may also need to issue "GRANT SELECT" statements to each table to ensure the user can run SELECT queries for all tables:
For example:
GRANT SELECT ON <DB_ADMIN_ID>.<synonym_name> TO <JAWS_USER_ID>;
- PRIVATE SYNONYMS - To create synonyms only for the specific user that AAI is using to connect to the Autosys database you can use the sample scripts below:
1. Just like the SQL to find all missing PUBLIC synonyms you can run the query below to find all tables that do not have a synonym for a specific user. In the example below AEDBADMIN is the schema owner and AAIUSER is the sample user that AAI is using to connect to the Autosys DB.
This query will give you the CREATE OR REPLACE SQL statement for any tables missing a synonym for the user that can be run manually:
--Get list of tables for a user missing a synonym and provide the SQL to add it
define aedbadmin = 'AEDBADMIN';
define dbuser = 'AAIUSER';
SELECT 'CREATE or REPLACE SYNONYM ' || '&&dbuser' || '.' || a.table_name || ' for ' || a.owner || '.' || a.table_name || ';'
FROM all_tables a
WHERE a.owner = UPPER('&&aedbadmin') -- Filter for tables owned by the specified schema
AND NOT EXISTS (
SELECT 1
FROM all_synonyms s
WHERE s.synonym_name = a.table_name
AND s.table_owner = a.owner
AND s.owner = UPPER('&&dbuser')
);
2. If you wish to use one SQL script to create all of the missing synonyms for one user you can use the SQL script below, just update AAIUSER with the user name that is being used by AAI to connect to the Autosys database:
--Add all missing synonyms for a user
define aedbadmin = 'AEDBADMIN';
define dbuser = 'AAIUSER';
begin
for r in (select owner, table_name from all_tables where owner = '&&aedbadmin')
loop
execute immediate 'create or replace 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;
3. You can run the script in step 1 and it should return no results if all tables now have synonyms for the user specified:
4. Another way to verify the public synonyms are created you can use the select statement below:
select * from all_synonyms where owner ='AAIUSER' and table_owner = 'AEDBADMIN' ;
5. Note the table names in step 2 as you may also need to issue "GRANT SELECT" statements to each table to ensure the user can run SELECT queries for all tables:
For example:
GRANT SELECT ON <DB_ADMIN_ID>.<synonym_name> TO <JAWS_USER_ID>;
6. Below is a sample script to find the "GRANT SELECT" statements for all tables for a user, again replace AAIUSER with your user:
DEFINE aedbadmin = 'AEDBADMIN';
DEFINE dbuser = 'AAIUSER';
SELECT 'GRANT SELECT ON "' || a.owner || '"."' || a.table_name || '" TO ' || UPPER('&&dbuser') || ';' AS grant_statement
FROM all_tables a
WHERE a.owner = UPPER('&&aedbadmin') -- Filter for tables owned by AEDBADMIN
AND NOT EXISTS (
SELECT 1
FROM all_tab_privs p
WHERE p.grantee = UPPER('&&dbuser')
AND p.table_schema = a.owner
AND p.table_name = a.table_name
AND p.privilege = 'SELECT'
);
- PRIVATE SYNONYMS AND CREATE USER - If you wish to create a DB user that AAI will use and create synonyms for each table in one step you can use the SQL script below as an example:
1. You will need to modify the sections highlighted in yellow with the user name you wish to created and their password.
Feedback
