Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: November 02, 2016 - 6:32 am UTC

Last updated: November 04, 2016 - 2:42 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,
I aasked below question to my friend
Why oracle created views for all system tables?
and he said to protect the base tables (i.e hiding name by creating view), iam not satisfied with that question because by plsql developer we can know the view source code,so his thoght is wrong.So would you tell me the reason for creating views.

Thank you.

and Connor said...

(My opinion - not suggesting this is why we did it)

There is a difference between what you need for a dictionary to *work* versus what makes it nicely readable.

For example, internally, to us, a user is just a number (you can see it on sys.user$). But that's not helpful or you - you want to see user *name*.

Same with objects, tablespaces, roles, etc etc... The "application" that is the Oracle data dictionary only needs to work with surrogate keys etc...but the "user" (ie, DBA's etc wanting to see the dictionary) want more useful information. So we have views to expose that data more cleanly.

And that of course (like any application) is useful when we want to change the underlying structures yet insulate the "outside world" from those changes. We just alter the view to present the same information but from the new structures. Its good practice for *your* applications as well

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

santhoshreddy podduturi, November 03, 2016 - 8:06 am UTC

Hi Conor,
Please dont think that i am gonna waste your time to read my story.Acutally i am a oracle begineer also new to data base technologies.My intension is to know the logic behind the DataBases working.I want to have knowledge on oracle like you,so iam exploring for logics and startegies behind the oracle data base working but every one saying different logics different strategies.

For example i asked the question for reason behind the creating synonyms views for system tables.all people around me said to protect the tables from ddl commands like drop table alter table.but iam not satisfied with that answer because we can know the source code of views and synonyms by so many tools.you explained the reason iam satisfied with that,but still i have the doubt really,Can we protect them(system tables of dictionary views) by using views and synonyms?

Connor McDonald
November 04, 2016 - 2:42 am UTC

"Can we protect them(system tables of dictionary views) by using views and synonyms? "

Yes, because no-one logs on to the SYS schema (or shouldnt be) in normal operations. Thus you will see that the base tables not accessible to anyone, but the views on top of them *are* accessible (via grants).

So views are a means of encapsulating information, and grants on those views, provide a nice security layer on top of that

santhoshreddy podduturi, November 04, 2016 - 3:50 am UTC

Thank you So much Conor