Oranew
asked on
Oracle synonym behavior
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.