Home » RDBMS Server » Security » Unable to see data in base table where as data can be viewed in synonym. (Oracle 11.2.0.1, OS- Windows 2008)
Unable to see data in base table where as data can be viewed in synonym. [message #522372] Fri, 09 September 2011 00:53 Go to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
I have one schema argus_app where there is a table cfg_enterprise. There is a view v$cfg_enterprisewhich selects data from cfg_enterprise and there is apublic synonym cfg_enterprise for v$cfg_enterprise.

When I login to argus_app and select from cfg_enterprise there is no data where as i log in to sys and select I can see data.

SQL> conn /as sysdba
Connected.
SQL> select enterprise_name from cfg_enterprise;

ENTERPRISE_NAME
------------------------------------------------------------
TAKE

SQL> conn argus_app/argus_app
Connected.
SQL> select enterprise_name from cfg_enterprise;

no rows selected

SQL> conn /as sysdba
Connected.
SQL> select object_name,object_type,owner from dba_objects where object_name='CFG_ENTERPRISE';

OBJECT_NAME          OBJECT_TYPE         OWNER
--------------   ------------------ ---------------

CFG_ENTERPRISE       SYNONYM             PUBLIC

CFG_ENTERPRISE       TABLE               ARGUS_APP


SQL> select * from dba_synonyms where synonym_name='CFG_ENTERPRISE';

OWNER        SYNONYM_NAME     TABLE_OWNER      TABLE_NAME           DB_LINK
-------------------------------------------------------------------------------- 
PUBLIC     CFG_ENTERPRISE      ARGUS_APP     V$CFG_ENTERPRISE



Can some one point out what would be creating this problem.

Thanks
Ridhi
Re: Unable to see data in base table where as data can be viewed in synonym. [message #522373 is a reply to message #522372] Fri, 09 September 2011 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do not query the same table/view: one query V$CFG_ENTERPRISE and the other one CFG_ENTERPRISE.
We have not the definition of the view so can't say more.

Regards
Michel

[Updated on: Fri, 09 September 2011 01:06]

Report message to a moderator

Re: Unable to see data in base table where as data can be viewed in synonym. [message #522374 is a reply to message #522373] Fri, 09 September 2011 01:33 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
I have already mentioned v$cfg_enterprise is a view which selects data from cfg_enterprise

Defination for the view:

CREATE OR REPLACE FORCE VIEW "ARGUS_APP"."V$CFG_ENTERPRISE"
(
   "ENTERPRISE_NAME",
   "ENTERPRISE_ABBRV",
   "ACTIVE",
   "PREFIX",
   "FIRST_NAME",
   "MIDDLE_NAME",
   "LAST_NAME",
   "DEPARTMENT",
   "ADDRESS",
   "CITY",
   "STATE",
   "POSTAL_CODE",
   "COUNTRY",
   "EMAIL",
   "PHONE",
   "ALT_PHONE",
   "FAX",
   "NOTES",
   "BASE_ENTERPRISE_ID",
   "DELETED",
   "ENTERPRISE_ID"
)
AS
   SELECT "ENTERPRISE_NAME",
          "ENTERPRISE_ABBRV",
          "ACTIVE",
          "PREFIX",
          "FIRST_NAME",
          "MIDDLE_NAME",
          "LAST_NAME",
          "DEPARTMENT",
          "ADDRESS",
          "CITY",
          "STATE",
          "POSTAL_CODE",
          "COUNTRY",
          "EMAIL",
          "PHONE",
          "ALT_PHONE",
          "FAX",
          "NOTES",
          "BASE_ENTERPRISE_ID",
          "DELETED",
          "ENTERPRISE_ID"
     FROM ARGUS_APP.CFG_ENTERPRISE
    WHERE ACTIVE = 1 AND DELETED IS NULL;


[EDITED by LF: formatted code]

[Updated on: Fri, 09 September 2011 02:00] by Moderator

Report message to a moderator

Re: Unable to see data in base table where as data can be viewed in synonym. [message #522376 is a reply to message #522374] Fri, 09 September 2011 01:59 Go to previous messageGo to next message
Littlefoot
Messages: 21480
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SYS selected data from a public synonym (created for a view (which contains a WHERE condition, so it can be expected that view "contains" less data than a table) based on a table owned by ARGUS_APP).

ARGUS_APP - when running "SELECT * FROM CFG_ENTERPRISE" - selects data from a table (not a public synonym, as a table and a public synonym have the same name). It should return the whole table contents.

Now: if the owner doesn't see anything in a table and SYS sees *something* in a synonym created for a view based on a table ... hm, strange. If it was vice versa, I'd say that view's WHERE conditions prevents data to be seen. But the way it is now, no idea.

Re: Unable to see data in base table where as data can be viewed in synonym. [message #522394 is a reply to message #522376] Fri, 09 September 2011 04:09 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Is it any role or grant triggering this issue?
Re: Unable to see data in base table where as data can be viewed in synonym. [message #522404 is a reply to message #522394] Fri, 09 September 2011 04:42 Go to previous message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have already mentioned v$cfg_enterprise is a view which selects data from cfg_enterprise

Yes, but can we trust you as you don't know where does come the problem?
Many times people THINKS it is in a way when it is not.
Anyway, when we ask for something do not think, provide.

Quote:
Is it any role or grant triggering this issue?

Maybe some VPD policy.

Regards
Michel
Previous Topic: Hiding UnApproved Data to Users
Next Topic: User should be able to read all the objects of an user
Goto Forum:
  


Current Time: Sun Jun 16 09:35:35 CDT 2019