"Interesting post. I had a number of posts on my blog over the years about read only tables. Some of which linked to no longer existing HJR posts/articles. I won’t go into the details of methods of making “read-only” tables but I do have a couple of important points to make.
The first is the need for so-called read only tables, why? - these are often created to allow developers into production or for support people (slightly better but not much) to allow analyis of issues in the database against prod data. Your example above would not support this anyway as it involves dropping the original data. I don’t recommend access in these cases.
The second issue is that if you create a so called “read-only” users - lots of details here - /weblog/archives/00000166.htm. the big issue is that even if you create a user with just CREATE SESSION and maybe select privileges (or some ideas like above) on certain production data (tables) then because of the PUBLIC issue in 10gR2 they have 21,000 other privileges, in 11gR1 27,000 other privileges. In this sense there is no such thing as a read only user or indeed read only table (because of this). disallow access is the best option."
I liked Richard's post but wrote the comment because the major point i wanted to make is "WHY", OK, there are plenty of potential how's and Oracle are even helping in 11g but WHY, Why does anyone need read-only access? If we limit the question to production databases I am often told that developers dont have access to a particular production database that i am performing an Oracle database security audit on but I then find evidence that this is not the case and developers are indeed given access. When questioning the client, I often get a response of
"Ahhh, that account is for "" who is our super special employee / developer / tester who is able to fix every issue we have."
hmmmm... I often say. That means developers do have access to production and they often have sweeping access to system privileges, database objects and more. This implies a complete lack of change control and access controls. Even when this type of sweeping access is not there, often there is a token role or account (often shared amongst many people) who has "read-only" access to the customer schemas and data. There is the major issue I raised about that in 11g at least for a so-called "Read-only" user he also has approximately 27,000 other privileges because of grants to PUBLIC. This is the killer issue as because of this it is in fact not possible to create a read-only user.
The second issue around developer or indeed anyone else getting access to the production data is the fact that at least in most businesses in the UK (and othe countries) they should not have access to the data in the first place, i.e. in simple terms peoples data should be accessed and used only by those who need to use it. Bug fixing and problem resolution is a grey/sticky area, do they need to see it or not? - my view is always to try and resolve bugs out of production and not using production data. That is another thorny subject! - moving data from production to development and test systems without any obfuscation of any sort..
data protect
