Having the application using only stored procedures certainly gives somewhat better security, since you don't have to grant direct access to the tables. And if all you want to keep out are people who are too smart for their own good it works.

However, unless you encode all security checks into the stored procedures, and you only grant access to the top-level procedures, there may still be a lot that a rogue user could do with the stored procedures alone.