We help IT Professionals succeed at work.

Oracle synonym behavior

Oranew
Oranew asked
on
I have granted the alter privileges to a a user on a table but can not able to alter it without owner.table_name. The synonyms are already created and he can able to select it. Here are the steps. Can you please point out what is missing here?

SQLPLUS>show user
USER is "TEST"
SQLPLUS>desc ps_test_1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 FF                                                 VARCHAR2(20)

SQLPLUS>select * from ps_test_1;

         A FF
---------- --------------------
       100

SQLPLUS>alter table ps_test_1 add xyz varchar2(20);
alter table ps_test_1 add xyz varchar2(20)
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQLPLUS>alter table sysadm.ps_test_1 add xyz varchar2(20);

Table altered.

The table is getting altered when using the <OWNER.TABLE_NAME > but not just with the table name. I can select it and the public synonym is there on this table.
Experts , What is missing here ?
Comment
Watch Question

Senior Database Administrator
Commented:
Check the documentation for this one:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

You can't use synonyms with ALTER statements.  The only DDL statements where synonyms are allowed to be used are  AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT.

ALTER is not a valid DDL statement for synonyms.