4

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:

  1. Store the jdbc connection object in the user session so I don't need to open a new one every time the user has to execute a query. This has its obvious downsides and here is a good discussion on the subject.
  2. Store the user credentials in the session. This way I could use connection pooling but storing a password in session is a security risk. Either way, this seems better than option 1, and I could encrypt the password to make the whole thing a bit "less insecure".

Are there other options I'm not thinking of? If not, which one of these seems the best?

Community
  • 1
  • 1
lufte
  • 1,283
  • 1
  • 11
  • 21
  • 2
    Keep in mind that Oracle is a hugely database oriented company, so of course they will recommend that the database (preferably Oracle) handle a lot of the functionality. – Kayaman Apr 23 '14 at 19:02
  • I know of a large enterprise product (IBM Maximo, in case it matters) that used this approach in its earlier versions. They switched to the "one big application user" in the later versions. Not sure what the reasons are behind it, but it might have to do with an option to use LDAP authentication in the application. So in other words: if you would like an option to add other authentication options to your web application later on, than using one user for DB might still be a good approach (haven't read your links on the reasons to go the other way, though). – neuromouse Apr 23 '14 at 19:06
  • 2
    And a really good point by Kayaman. And not only that - it is important to consider that Oracle's licences are sold on user basis. So each user accessing the DB = more $ for Oracle. so while there might be good reasons to use this approach, do consider this. – neuromouse Apr 23 '14 at 19:13
  • Just curious: do you consider this for a public web application? Isn't the licensing of the application users an issue here (neuromouse now posted this already)? My gut feeling is that I would only use this model for a data-centric in-house solution. Nonetheless Oracle has a feature called "Proxy Authentication", maybe this helps (I don't know any details)? → http://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvprxy.htm – mdo Apr 23 '14 at 19:21
  • Thanks for all the feedback. I linked the Oracle article but I am actually using Postgresql in this project. The application is just going to be like a dashboard screen that shows (and constantly updates) statistics gathered from a local database. It's not public, it's just for "the office", so I'm not really worried about scalability and portability. – lufte Apr 23 '14 at 19:32

2 Answers2

2

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.

Aleksandar Stojadinovic
  • 4,851
  • 1
  • 34
  • 56
  • Although this is a perfectly good approach (way better than the 2 options I proposed), it's not really a way of implementing what I want to do here. My idea is to delegate all the authentication work to the dbms. I'll wait for an answer that gives me a better way of implementing my idea (if there's any) but I will accept your answer if none comes up. – lufte Apr 24 '14 at 13:44
  • Again, I say, authentication is not your problem. Authorization is your problem and the database itself has only a limited vision of what that means. cf http://stackoverflow.com/questions/6556522/authentication-versus-authorization – Bob Dalgleish Apr 27 '14 at 00:54
  • There is not much difference between THREE Big Application Users and One Big Application Users. – Andrew Wolfe Apr 27 '14 at 16:07
  • Aleksandar, why do you say "situation where you can map the application user to the database user permission-wise is quite rare in applications today." I know I keep seeing application coders say this but what are the real issues? – Andrew Wolfe Apr 27 '14 at 16:53
  • Authentication/authorization is not often a check-password-and-let-them-through business, and there is usually quite an application logic that needs to be altered depending on the roles. Very often you have exceptional situations that are easier to be modeled as some kind of separate layer. What I'm trying to say is that A&A in applications like we are talking about is more on the controller level and application access level. I do not thing building your security model based on a database security engine (which is the model level) and then trying to propagate it through the app. – Aleksandar Stojadinovic Apr 28 '14 at 07:08
1

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

Andrew Wolfe
  • 2,020
  • 19
  • 25
  • Oracle is extremely restricted in the number and kind of authentication methods it supports. Since the intent of "one DB user" per user is actually authorization, this is a big problem. – Bob Dalgleish Apr 26 '14 at 17:45
  • Which sort of additional authentication methods would you want to see? There is a lot of flexibility in creating CONTEXT along with LDAP access, after logon triggers. But perhaps you're thinking of something else? – Andrew Wolfe Apr 27 '14 at 00:01
  • OpenID, Facebook, Kerberos, NTLM, etc, authentication. Oracle confounds authentication with authorization. It is also the case that rights and abilities need to be visible at the user interface, so that you never present views or abilities that are not permissible to the current user. This is because there is a lot of difference between being able to view all records versus those in your department, etc. Database permissions are only a subset of the permissions that inform an application. – Bob Dalgleish Apr 27 '14 at 00:47
  • @Bob, your information is incorrect. So if you down voted me, I'd appreciate you removing it. Oracle supports OpenID since at least 11.2, Kerberos since 9.2, and both of these have sections in the Oracle Advanced Security Guide. NTLM is supported as well. Facebook authentication is not part of the database but is available through OAM. – Andrew Wolfe Apr 27 '14 at 15:42
  • Also, Javier doesn't WANT "one DB user". Note database views can authorize UPDATE as well as select access distinguishing "all records versus those in your department," especially with INSTEAD OF triggers. DBMS_RLS covers remaining gaps. And in terms of 'conflating authentication with authorization,' table-level GRANT is an ANSI SQL problem, not an Oracle problem; also, Oracle has superseded GRANT CONNECT for creating users for many years. You need to know current information to make such broad criticisms! – Andrew Wolfe Apr 27 '14 at 16:04
  • (I'll remove the down vote but it says you have to edit the posting first -_^) Somehow all of those database permissions have to be turned back into business rules that affect how the UI displays (or not) its views. Since many business rules don't have analogs in the DB permission world, you need to maintain BR in more than one place. – Bob Dalgleish Apr 28 '14 at 00:02
  • You've found an important point. Luckily, there are even more dictionary views that allow the session to find out which columns are NOT NULL, which it can update and which it can't, and where foreign keys refer, etc. However I have not the slightest idea whether postgresql supports any of these! – Andrew Wolfe Apr 29 '14 at 00:42
  • This is very Oracle related, but I see you are on the line of saving the user credentials (LDAP session credentials in your case) instead of keeping the connection object saved in the session. From your answer; I understand that with LDAP I could store a temporary credential in the web session instead of the real database credential, but I really don't want to add more technologies to what it's supposed to be a quite small and simple app. – lufte May 02 '14 at 00:16