How to avoid the "One Big Application User" model?
I'm writing a small web application in Java and my idea is to use database users as application users. Oracle recommends this approach in this document in case you never heard of it. Basically, the user will authenticate with their database credentials and I will forward these credentials to the dbms for the actual athentication process.
My problem is that every time I need to open a connection for the user I will need their credentials. I have come up with two ideas, but neither seems fully okay to me:
Are there other options I'm not thinking of? If not, which one of these seems the best?
The situation where you can map the application user to the database user permission-wise is quite rare in applications today, I would say. Look at this as more of a user-> role mapping.
To put it this way, in your application which is some kind of message board you will have a database with tables POSTS, USERS and SETTINGS.
Yet again you will have three user types, Users, Moderators and Administrators. You will probably have some kind of controller-level separation between all of these users, which means some separate code for the Users, Moderators and Administrators. The article states that you should not use one superuser database account for all purposes.
Consider this solution. For the Users group you should have one USER database user that only has WRITE privileges for the table POSTS. The controller for Moderators should connect to the database using a separate database user which has permissions to read and write over POSTS but without any permission over USERS and SETTINGS. And as you might suspect the Administrator panel should have its own user/role with almost all privileges.
You might also put another user, with only read permissions over the USERS table when you want to authenticate the registered users on your message board.
This is a fairly simple approach but this way you are guarding the database from possible bugs in your application logic that may leak requests that you do not want to destroy your database, or at least you can control the possible damage.
To sum it up then, this is a quite simple mechanism (simple enough for what you are building, I guess). This way you don't really get any vendor lock-in (what if you choose to go to MySQL one day or SQLite or something that does not work in the same way as Oracle) and you get more out of your controller-model decoupling.
As some commentators pointed out, you might even one day introduce a more sophisticated authentication approach so using the database integrated mechanism turns out as a migration issue.
What a nice distraction from grading database security papers!
Proponents of "one big application user" have a lot of bad company in developers, managers and administrators who don't want to bother to learn about the DBMS they are using. All these DBMSes have a wealth of auditing and security functionality that can be invaluable if you leverage database-level users. Conversely OBAU throws this away.
So I would use named, identified database users. However, I would rather secure each such named database user with LDAP authentication than database. (You can use an Oracle LDAP server but that's not the point.) I'd store the LDAP session credentials in the web session. Note we want the credentials to expire after some number of days or hours anyway!
Oracle also has implemented "lightweight" sessions (11gR2?) that you can keep open rather than pooling them. I was never completely sold on connection pooling - why are database connections the only resource that administrators never grow along a "Moore's Law" line?
You can continue to centralize your access by collecting synonyms and views into a single schema. You can also store the tables there and secure it with VPD/DBMS_RLS and it's always worked bulletproof for me in every way except my personal aesthetic sense.
Once you're connected, have your sessions ALTER SESSION SET CURRENT_SCHEMA = CENTRALIZED_OWNER so that your programmers don't have to prefix each database object with CENTRALIZED_OWNER.tablename.
Try to write your procedures (in Oracle) using AUTHID CURRENT_USER (or in other DBMSes' equivalents) to keep the privilege management under the actual end user.
When your developers scream for access to the tables, GRANT proxy access to them for the table-owner schemas in that installation. It's a fabulous feature.
Have a great day!
Andrew Wolfe
链接地址: http://www.djcxy.com/p/22240.html上一篇: 针对所有请求的自定义授权