Hello,
You can, in fact, take the approach you discussed below (meaning that you can separate tables into folders as you require for logical organization and security purposes).
You most likely already understand this, but as was mentioned below, folder permissions in Metadata only secure the table metadata, not the underlying physical data. You still need to properly secure data at either the OS level (for SAS datasets) or RDBMS level. You could access the data through the Metadata Libname Engine, which honors the Read, Write, Update and Delete permissions listed in metadata. However, there is no way to force users to access the data with this method. If they have permission to the underlying physical data (file system permissions or DBMS password), they can always get to the data by issuing a regular libname statement rather than MLE. So, whether you can use MLE depends on your application, the way you configure security and the other access your users may have to the data.
There are some other approaches you can take in 9.2 with the introduction of Token Based Authentication, where you can have all Workspace Server sessions for a particular application run as a privileged user, which might provide a solution. If this is of interest, I could post some more on this topic.
Thanks,
Tim Stearn