ORA-04043 and ORA-00980

This example was tested on Oracle 9. First create a table:

 

SQL> create table table1 (col1 number)

  2  /

 

Table created.

 

SQL>

 

Then create a synonym pointing to that table:

 

SQL> create synonym synonym1 for table1

  2  /

 

Synonym created.

 

SQL> desc synonym1

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                NUMBER

 

SQL>

 

Rename the underlying the table:

 

SQL> rename table1 to table2

  2  /

 

Table renamed.

 

SQL>

 

Now, if you describe the table via the synonym, you get an ORA-04043, which I have shown elsewhere:

 

SQL> desc synonym1

ERROR:

ORA-04043: object "ORACLE"."TABLE1" does not exist

 

SQL>

 

But, if you select from the table via the synonym, you get an ORA-00980:

 

SQL> select * from synonym1

  2  /

select * from synonym1

              *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

 

SQL>

 

Depending on the circumstances, you could fix this as follows:

(1)  Rename the table back again, if it was renamed by mistake in the first place.

(2)  Drop and recreate the synonym so that it points to the new table name.

Stored Procedures and Roles (Example 2)

Go back to example 1 

A colleague showed me another example of Oracle not recognising grants made to roles when creating stored procedures. I'm including it as it is slightly easier to follow than the first example. It was tested on an Oracle 9 database. First create a test user:

SQL> conn / as sysdba
Connected.
SQL> create user andrew identified by reid
  2  default tablespace user_data
  3  quota unlimited on user_data
  4  /

User created.

SQL> grant create session to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user and try to create a table. This will fail as the user does not have the create table privilege:

SQL> conn andrew/reid
Connected.
SQL> create table test_table(one_col number)
  2  /
create table test_table(one_col number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> 

Create a role with the create table privilege and grant it to the test user:

SQL> conn / as sysdba
Connected.
SQL> create role table_creator
  2  /

Role created.

SQL> grant create table to table_creator
  2  /

Grant succeeded.

SQL> grant table_creator to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user using SQL*Plus and show that this new role allows him to create a table:

SQL> conn andrew/reid
Connected.
SQL> create table test_table(one_col number)
  2  /

Table created.

SQL> drop table test_table
  2  /

Table dropped.

SQL>

Allow the test user to create stored code:

SQL> conn / as sysdba
Connected.
SQL> grant create procedure to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user and create a procedure to recreate the same table. Run the procedure. It will fail as stored code ignores grant statements passed via roles:

SQL> conn andrew/reid
Connected.
SQL> create procedure role_tester
  2  is
  3  begin
  4  execute immediate
  5  ('create table test_table(one_col number)');
  6  end;
  7  /

Procedure created.

SQL> exec role_tester;
BEGIN role_tester; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ANDREW.ROLE_TESTER", line 4
ORA-06512: at line 1

SQL> desc test_table
ERROR:
ORA-04043: object test_table does not exist

SQL>

Now grant the create table privilege directly to the user:

SQL> conn / as sysdba
Connected.
SQL> grant create table to andrew
  2  /

Grant succeeded.

SQL>

Connect as the test user. Run the procedure again. This time it will work:

SQL> conn andrew/reid
Connected.
SQL> exec role_tester;

PL/SQL procedure successfully completed.

SQL> desc test_table
Name                    Null?    Type
----------------------- -------- ----------------
ONE_COL                          NUMBER

SQL>

Synonyms

Tested on an Oracle 11 database.

You can use a synonym to create an alias for an object e.g. a table or view:

 

SQL> create synonym a for b

  2  /

 

Synonym created.

 

SQL> create public synonym x for y

  2  /

 

Synonym created.

SQL>
  

If the object does not exist, you do not get an error until you try to access it:

SQL> desc a

ERROR:

ORA-04043: object "SYSTEM"."B" does not exist

 

SQL> desc x

ERROR:

ORA-04043: object "SYSTEM"."Y" does not exist

 

SQL>

And you can even create a synonym for an object which belongs to a user who does not exist:

SQL> create synonym c for d.e;

 

Synonym created.

 

SQL> select count(*) from dba_users

  2  where username = 'D';

 

  COUNT(*)

----------

         0

 

1 row selected.

 

SQL> desc c

ERROR:

ORA-04043: object "D"."E" does not exist

 

SQL>