I've more than one activity (inside the same Application) that needs to have access to the database. What's the best pattern to implement this ? Do I need a content provider even if all activities belong to the same application?
Which activity should have the responsibility for opening and closing the database ?
Not necessary. You just have to create a Content Provider if you want some external application to access your data.
Your two options are Content Provider or just using your own database abstraction layer. The content provider is a better way to go as pointed out, if you need other apps to share your data or if you need to hook into some other part of Android (like the Quick Search framework). It should not be tied into an Activity - should just be a separate class that you import and use.
The OReilly Android programming book has a chapter which illustrates both approaches, its a good read.