yesteday I saw a post by Jeffrey McDonald on his blog titled "OCSG+XE" and noted that he suggested a tip; to create the user OCSG40 and to then grant ALL PRIVILEGES to it. I responded with a comment as follows:
"Hi Jeffrey,
Thanks for your post. I would like to make a comment about the example code that grants all privileges to the user OCSG40. This is not a good tip nowadays. Granting ALL PRIVILEGES is actually slightly worse than granting DBA as these privileges are not turned off in definer rights code, they are always on. Also even SYS does not have all privileges in 11g anymore, as it has lost EXEMPT ACCESS POLICY and EXEMPT IDENTITY POLICY.
I always recommend that the minimum privileges necessary are granted for each role and user and these must be designed for purpose.
cheers
Pete"
Then Jeffrey updated his post to first suggest that another password instead of OCSG40 could be used and also to grant the privileges CREATE SESSION and CREATE TABLE. This is quite a difference to ALL PRIVILEGES.
I wanted to make the point about this here, its not a dig at Jeffrey but quite the opposite, he responded well and changed what is afterall a blog post not documentation, anyone installing should read the docs not rely on a blog post (actually its worrying how many people do rely on Google and not official documentation nowadays). My experience is that the world is getting better, there are still apps out there where the schema owner is a DBA or has ALL PRIVILEGES granted or even SYSDBA, there are even new apps that I see written and deployed now that still do this. The key change for me is that customers of mine who have written applications are becoming more savy and are willing to listen and make changes. I have dealt with some pretty big vendors over the last few years and its becoming much easier to get them to reduce privileges. This is good and should be applauded BUT we are no where near yet. Most sirtes still have far far too excessive privileges but the tide is turning.
One thing i mentioned above and should elucidate on is that its often possible to reduce the privileges to almost nothing. In our example of OCSG40 (I don't know the app in details so this could be wrong buit the general sentiment is not) has CREATE TABLE. If this application does not generate tables dynamically then it doesnt need this privilege during run time use. It needs it for install and perhaps upgrades but not for run-time. we can remove it and add it back when needed. A lot of my clients are now doing this. Even if it does need it at run-time sometimes its better to grant that privilege to another schema and expose that privilege through a PL/SQL API, that way use of the privilege is better controlled. Another way may be to retsrict use via DDL triggers. We need to get clever with the granting of privileges.
One final point that must be made is that Oracle itself is not improving fast enough, the products are still open by default and the number of privileges in use after install and before customers create anything is extremely excessive and also worringly we see a lot of duplication.
