I found it interesting that Oracle Apps
also didn't use the database's concept of user for the login to their ERP apps. For the benefits package that I wrote, I also used login and roles tables to define the access limits. I did, however, use stored procedures to enforce the read and write acess limits.
It doesn't really matter where or how you enforce these access rights, but they have to be enforced somewhere. The two most common methods are to either enforce them via stored procedures (or views or triggers), or to create a third tier to enforce them. Using stored procedures to filter all transactions provides the database with it's own defense mechanism. It means that no matter what kind of apps or programs are written to interface with the data, the database has a built in protection mechanism. Essentially it is an environment where the database is set up in an almost adversarial relationship with the users (both software developers and end users in this case). If you make the database just a repository that does not enforce the business rules, it means that you have to enforce them in all the applications that are written for the database (also taking care of killing connections from such sources as Excel and Access, as they will take down a database in nothing flat).
The other reason for using stored procedures has to do with transactional processing. Many of the transactions in a multiuser environment need to be done very fast - you don't want users stepping over each other. Stored Procs may be a pain to write and maintain, but their proximity to the database means that they can be used to write very fine grained transactions.
Of course, the downside is that filtering everything through Stored Procs also becomes a bottleneck for development. Many time you need new functionality, you have to rewrite the attached applications, as well as the SP's. In addition, the three most widely used procedural languages (PL/SQL, T-SQL and whatever it is they call the DB2 languae) are throwbacks to the early days of algol. Stuff you take for granted in most other languages is just not there. Not to mention that once you use a particular vendors procedural language, you've just significantly narrowed your market (at the exclusion of all other db vendor offerings).