The admonition (since the Oracle 6 days if my memory serves) for good DBAs is to never use the default roles when granting privileges to users in Oracle databases. Always grant users the specific privileges they require and never rely on ‘CONNECT’ or ‘RESOURCE’, because they generally grant more rights than you really want.
Oracle seems to have addressed this in 10g2, at least according to this this blog post and my observation. Now, granting the ‘CONNECT’ role is exactly the same as explicitly granting the ‘CREATE SESSION’ privilege and the ‘RESOURCE’ role has a more reasonable list of privileges.
I’d still be wary of them though, because through sheer laziness I granted these roles to a user in my development database and then got an ORA-01031 error when I tried to create a view. That’s right, the ‘RESOURCE’ role doesn’t (and as far as I can tell never did) contain the privilege to create a view. How strange.
So this advice would appear to still be valid - only grant those privileges that your users need and do it explicitly.
I gave my talk at OSDC yesterday so here are the fruits of my labours - the paper and slides. Enjoy.
As usual corrections and suggestions are very welcome.
Python 2.5 has been released today. Yay. I look forward to utilising some of its fine language features in my future code.
But, and you knew there was a gripe coming didn’t you? With the inclusion of the pysqlite module as part of the standard distribution there is a problem. It has been renamed from pysqlite2 to sqlite3. A move I agree with by the way, as it is clear from the name exactly what the module supports.
The problem is I’ve got quite a bit of code that uses the old name. Where my existing scripts say;
>>> from pysqlite2 import dbapi2 as sqlite
They now need to say;
>>> try:
... from pysqlite2 import dbapi2 as sqlite
... except ImportError: # for Python 2.5 and above
... from sqlite3 import dbapi2 as sqlite
>>> myDb = sqlite.connect('sqlitedatabasefile.db')
Or they would, if I hadn’t cunningly stolen an idea from SQLObject, which they borrowed from any number of other places, and written my own little connection function. So my scripts can carry on using;
>>> from utilities.dburi import get_connection
>>> myDb = get_connection('sqlite:/sqlitedatabasefile.db')
Fancy a look at the code? Oh, ok then, it’s called dburi.py and there is a text version of the file called dburi.py.txt. Patches are gratefully accepted.
Sometimes I think that the nice people at Oracle get the new information technology world. Sometimes I even think that they understand the web and it’s associated technologies. Then they do something stupid.
The latest crime? Publishing syndication feeds of various columns on the Oracle Technology Network but then putting the content on a site that is only available to registered users.
I subscribed to the feed for Steven Feuerstein’s PL/SQL questions and answers. It is, of course, a headline and excerpt only field - a particular bête noire of mine - so I clicked on an article headline in my feed reader expecting to be taken to the relevant article. But no, the information architects have decreed that you can’t casually view this premium content, you have to sign in to with an oracle.com account.
People, it’s just the web. It’s not like I’m trying to buy anything. Why should I have to sign in to a web site to read an article on the overhead of DBMS_OUTPUT?
The only conclusion I can reach is that they really don’t get this here web thing, and that makes me sad.
In the interests of completeness the article I was referring to is here (have your oracle.com user name and password handy) and Steve’s conclusion is largely correct. Although I would recommend using a package like Log 4 pl/sql rather than rolling your own (inevitably) halfcooked logging solution.
My proposal to talk at the 2006 Open Source Developers Conference has been accepted. All I’ve got to do now is write the paper.
Prompted by Mark and in an effort to keep up with Alan I’ve submitted a proposal for a presentation at OSDC 2006.
If they like my proposal I’ll be appearing in a lecture theatre in Melbourne in December talking about databases and Python. I’m being quite ambitious in scope for a thirty minute slot with discussion of the Python Database API, a side trip into JDBC and Perl’s DBI and then a discussion of database wrapper utilities, ORM implementations and patterns. I suspect that there will be some judicious editing before I’m finished, although I will try and keep all the topics in my conference paper.