Custom Data Access Object with Metadata libraries

[AmySwinford]

Our platform architects would like to abstract the databases from the systems consuming the data (including SAS) using data access object design patterns. We have almost 700 libraries defined in SAS metadata through SAS access products pulling TB of data daily. To maintain security we don't allow direct connect statements for SQL passthru, only metadata libraries. How can I work with this new abstraction layer and allow my users to continue to utilize metadata libraries and SAS clients such as EG? Do I need to find a good java developer and write a driver layer in between SAS Access to JDBC and the data access object? We are in SAS 9.4M4, moving soon to M6, working on a grid.

[SASKiwi]

So what do you mean by "data access object design patterns"? How is this different from say EG metadata-defined libraries just listing tables in a database schema?

BTW, how do you block users from using SQL passthru? The only way I can think of doing this would be via token-based database connections and users not having individual database logins.

[AmySwinford]

A data access object is an abstraction layer that sits between applications and the database behind them. This allows developers to use data and remain agnostic to the database structure and code. Theoretically this allows us to put anything in place for data storage, even replacing RDBMS with NoSQL. This is very different from the way we currently have our metadata libraries defined because the database structure is exposed in the tables of the library in EG. Typically our users will either use a Query task or write some code using a libname with the meta engine and SQL code to get to the data. 

You are correct, we don't completely "block" our users from SQL passthru, but they don't have individual logins and we don't share the credentials of the service account. A savvy user could figure out how to set up SQL passthru, but the savvy users aren't the core problem we are trying to solve for.  

[SASKiwi]

OK. that is a bit clearer now. As far as I'm aware metadata-defined libraries require real database connections to external databases as underneath they use SAS/ACCESS products. Your Custom Data Access Object would therefore need to work in a real external database that SAS can connect to. I don't know how this would work or how it differs from say a database view that might simplify and standardize your data accesses.