I saw a post by Richard Foote a few days ago and made a comment on the blog entry and also made a note to chat about it here. Richards post is titled "Read-Only Table Before 11g (A Day In The Life)
" and is quite an interesting post related to an attempt to create a read only table in a database. This is something I have spoken about here a number of times in the past and I have also spoken about the creation of read only users (in the same area as read only tables). There is a link shortly in my comment below that takes you to most of my own past posts, here is the comment, repeated here:
"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 - http://www.petefinnigan.com/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..