aspose file tools*
The moose likes JDBC and the fly likes How to change user credentials when accessing DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to change user credentials when accessing DB" Watch "How to change user credentials when accessing DB" New topic
Author

How to change user credentials when accessing DB

Chris Johnston
Ranch Hand

Joined: Dec 13, 2004
Posts: 85
Hello,

The project that I am working on, one thing we are looking at is the ability to use the same user credentials (username, password) on both the application side and the DB side. This would mean that when someone logs in using a specific username/password, we would turn around and use those credentials when connecting to the RDBMS.

If we are using straight JDBC, this seems like a fairly simple thing to accomplish. All we would need to do is, when we create the connection, simply pass in the username and password that the user used to login. This would then connect to the DB using that user's credentials and enforce whatever DB security there was to enforce.

My real question is how to do this with c3p0, Hibernate, and data sources within an app server such as JBoss or WebLogic? Are there ways of programatically changing the username/password that is used to make database connections?


www.fuzzylizard.com
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

What you're describing is generally not what's done in practice. Database user accounts are very different from application user accounts. You can mimic the system so that it appears they are the same, but you would rarely ever want them to be the same.

The primary reasons are:
  • You never want the database password exposed in such a way. It would be a huge security risk to link them.
  • In many DBMS's user accounts are associated with different database schemas so you actually see different things depending on your user
  • Database accounts are severely limited in terms of user control versus what you can programmatically do with application login/passwords

  • In short, management of such a system would be difficult. A better solution is to build your database schema such that you have a security mechanism in place, possibly through an object access table, then have the system pass the user account id in the query that determines what the user has the ability to access.


    My Blog: Down Home Country Coding with Scott Selikoff
    Chris Johnston
    Ranch Hand

    Joined: Dec 13, 2004
    Posts: 85
    Originally posted by Scott Selikoff:
    A better solution is to build your database schema such that you have a security mechanism in place, possibly through an object access table, then have the system pass the user account id in the query that determines what the user has the ability to access.


    That all makes sense. Can you elaborate more on your solution or provide a good tutorial for it?


  • In many DBMS's user accounts are associated with different database schemas so you actually see different things depending on your user


  • This is exactly what we want though. We want to be able to use the user credentials to do row/column based filtering of the data that the user can see. Even though a user may be able to see a particular table, that doesn't mean they can see all the data in that table. They will be able to see certain rows, but not others. We were hoping that, by being able to use the same user credentials when accessing the database, to have the DB handle this level of security.
    Scott Selikoff
    Saloon Keeper

    Joined: Oct 23, 2005
    Posts: 3704
        
        5

    Database credentials cannot be used to filter data within a table, only the table itself. There are some ways around this using views but they create major security wholes in the process since the data isn't truly safe.
    Chris Johnston
    Ranch Hand

    Joined: Dec 13, 2004
    Posts: 85
    Hmm... I was under the impression that Oracle was able to do row based filtering of data. How would using views introduce holes in the security model and leave some data unsecured?

    Do you know of any best practices papers that relate to handing database/application security?
    Joel McNary
    Bartender

    Joined: Aug 20, 2001
    Posts: 1817

    As Scott has said, this is generally not a good practice.

    The main problem with using Database logons in place of application logins is that this gives the user access to the database outside of your application. This introduces security issues right here.

    Second, there's maintainibility issues. If you have hundreds of users, then you have to have views for each user. DBAs (who ususlly handle database security) would not be thrilled about this -- either because of the number of views involved or because they would be the ones having to create them everytime a new user was created.

    Third, it makes it more difficult to implement standard application security features, like forcing a user to change their password after x number of days, ensuring that a user's password meets certain formatting criteria (e.g., min of 8 characters, at least one capital letter and one digit, etc.), allowing the user to change their password, making sure that they havn't used the same password within the past 180 days, etc.

    Now, Oracle may have the features that you are talking about (Virtual Private Databases may be what you are thinking of...) but I wouldn't try using things like that without consulting with an Oracle expert, which I am not.

    I've worked on applications that used database-level authentication, and I've found that it is so much easier to manage it through the application.

    That said, I do not know about how views can introduce holes (perhaps Scott can provide the counter-examples).


    Piscis Babelis est parvus, flavus, et hiridicus, et est probabiliter insolitissima raritas in toto mundo.
    Chris Johnston
    Ranch Hand

    Joined: Dec 13, 2004
    Posts: 85
    Once again, all of that makes sense. However, I still have a few more questions.

  • If implementing an application frontend to database access is a best practice, how would you give a user, or group of users, direct SQL access to a DB?
  • As for the user explosion problem, would groups and generic user credentials help with this problem?
  • So if understand what is being said, it is better to implement row, or object, based security in the application layer instead of trying to handle it in the DB? If this is the case, then why does Oracle have such stringent security?
  • So would the best solution be to use an LDAP server so both the application and the DB (Oracle) use the same security credentials?
  • Finally, is there anyway to extend Oracle's level based security into a Java application?


  • Thank you for the help so far. I am new to both Java and Oracle/DB security. We are trying to create an application with many forms of database access and trying to create a unified security model for it. We were thinking that if we could just use what is in Oracle, then we would not have to worry about it in the application beyond basic authentication.
    [ July 19, 2006: Message edited by: Chris Johnston ]
    Joel McNary
    Bartender

    Joined: Aug 20, 2001
    Posts: 1817


    If implementing an application frontend to database access is a best practice, how would you give a user, or group of users, direct SQL access to a DB?

    In general, you don't. Giving this type of access to a user is generally considered a bad idea. I'm not saying that it's a bad idea in your case (I don't have enough information to tell that), but I tend to assume that this is a bad idea and then have to be convinced that, in this case, it is really a good idea.


    As for the user explosion problem, would groups and generic user credentials help with this problem?

    Yes, it probably would.


    So if understand what is being said, it is better to implement row, or object, based security in the application layer instead of trying to handle it in the DB? If this is the case, then why does Oracle have such stringent security?

    Oracle has the stringent security because it is an enterprise-level database that can be used (and mis-used) for all sorts of purposes. When I'm telling you, "this is generally not a good practice," I am speaking in generalities. Your application may be one of those where it is, in fact, a good idea to use the database's security model instead of your own. However, the vast majority of applications out there are not good candidates for this.


    So would the best solution be to use an LDAP server so both the application and the DB (Oracle) use the same security credentials?

    I'm note sure that that is necessary. We use LDAP to ensure that the user's network and application credentials are the same; the Oracle connection is maintained by the application and is independent of the LDAP.


    Finally, is there anyway to extend Oracle's level based security into a Java application?

    That I'm not sure about. I'm sure you could query the SYS tables in Oracle to get this information, but I've never tried this.
    Scott Selikoff
    Saloon Keeper

    Joined: Oct 23, 2005
    Posts: 3704
        
        5

    I think what you might be missing is that all of these ideas are possible. It is definitely possible to setup a database system such as you described, but doing so would be like watching someone cook a steak with a single match, its kind of painful to watch and you'd be lucky if you could get anything good out of it.

    In the case of what has been said thus far, Oracle is complicated, its technicians pretty well paid. If you had a team of Oracle/Programming experts they could pull it off. The problem is that the people usually managing users/access are not database engineers or even programmers, therefore it would be pretty unlikely they'd be able to maintain the system long term. Further, suppose you had a client that didn't want to use Oracle anymore. Creating a new system would require a completely new product.

    Ultimately, its not the security, maintability, or scalibility that really will hurt the most. What's going to be painful is figuring out ways to implement complex business rules (security systems can be very specific to the application) in your application. You'd have to muddle through a lot of headaches to get it to work. Database systems severely lack the functionality to pull offer a good user-based system.

    My suggestion is to focus more on your database design and figure out what objects even require security. For example, a table listing zip codes would not likely need to be secured, only objects related to orders tend to fall into that category. Good luck!
    [ July 19, 2006: Message edited by: Scott Selikoff ]
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: How to change user credentials when accessing DB