Coffeehouse Thread

17 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

DB credentials

Back to Forum: Coffeehouse
  • User profile image
    spivonious

    I'm just trying to get a sense of how the average DB connections are made. Do you normally use one DB user for an app, or do you have a DB account for each user of the app?

  • User profile image
    ManipUni

    One for the application.

     

    Depressingly the typical user is likely called "root" and is used for ALL of the applications.

  • User profile image
    blowdart

    It depends on the app. Normally it's one user, with any security checks being done by a gatekeeper layer that talks to the repository, which then passes the user's login name through for auditing. This is more scalable as you then get a greater potential for connection pooling, as the connection strings are always the same.

     

    However sometimes you need auditing at the database, for ease of administration, in which case it's pass-through authentication, assuming you configuration delegation correctly.

  • User profile image
    davewill

    Ideally you want both.  Some environments will force your hand for one or the other.

  • User profile image
    JoshRoss

    Are you talking about an internal app or a public app?  If it is an internal app, and you are using a domain, are all your users members of the domain?

     

    Another silly question, is the database local or hosted?

  • User profile image
    spivonious

    JoshRoss said:

    Are you talking about an internal app or a public app?  If it is an internal app, and you are using a domain, are all your users members of the domain?

     

    Another silly question, is the database local or hosted?

    The question was just in general. At my company it's all internal apps. We're on a domain, but Oracle is running on a Unix server, so no luck using the Windows credentials. Our apps are mostly all two-tier (i.e. app installed on the client, connects to Oracle DB directly) so there wouldn't be any gain from connection pooling.

     

    I asked because it seems that most of the wizards in VS assume that user info is not important.

  • User profile image
    Maddus Mattus

    spivonious said:
    JoshRoss said:
    *snip*

    The question was just in general. At my company it's all internal apps. We're on a domain, but Oracle is running on a Unix server, so no luck using the Windows credentials. Our apps are mostly all two-tier (i.e. app installed on the client, connects to Oracle DB directly) so there wouldn't be any gain from connection pooling.

     

    I asked because it seems that most of the wizards in VS assume that user info is not important.

    On oracle use oracle login's if you cant use windows.

     

    Per user different login, so you can track who is hacking your db.

     

    Or store credentials encrypted and do some sort of certificate thing.

     

    You need to prevent your users from being able to connect to the database without the application.

  • User profile image
    figuerres

    spivonious said:
    JoshRoss said:
    *snip*

    The question was just in general. At my company it's all internal apps. We're on a domain, but Oracle is running on a Unix server, so no luck using the Windows credentials. Our apps are mostly all two-tier (i.e. app installed on the client, connects to Oracle DB directly) so there wouldn't be any gain from connection pooling.

     

    I asked because it seems that most of the wizards in VS assume that user info is not important.

    VS takes the view that in most cases a developer will have a test server to work with but the end user will connect to a different server.

     

    most of my stuff is using a layer of web services to connect to the end user. so i can and do benefit from connection pooling.

    also even with an oracle db i would favor bulding the app to have a client that knows zero about the actual database connection.

    if you make the client talk to a mid-layer then if you want to make a change to the database end you can make the mid-layer handle the changes so the client does not need to be updated. in my case a client update means touching 200 to 400 clients part on winCE and part normal desktops... we like to not update them unless we must.

     

     

  • User profile image
    spivonious

    Maddus Mattus said:
    spivonious said:
    *snip*

    On oracle use oracle login's if you cant use windows.

     

    Per user different login, so you can track who is hacking your db.

     

    Or store credentials encrypted and do some sort of certificate thing.

     

    You need to prevent your users from being able to connect to the database without the application.

    "You need to prevent your users from being able to connect to the database without the application."

     

    Why? They can't do anything directly in the DB that they couldn't do from the app.

     

    figuerres - one of the developers that is no longer here setup a "OneClick"-esque updating system with our main VB6 client, so updates are downloaded and installed automatically. Pretty neat actually. I definitely see the advantages of a 3+-tier architecture, but for our needs the 2-tier is working fine. All that adding another tier would do is add another server, unless I'm missing something. The app.config would make sense too, but the client has a server choice. End users don't have a logon to the development server, and the production server is chosen by default. Not the prettiest solution, but it works.

     

    Anyway, I have no power to change these things, so back to the topic at hand! Smiley

     

  • User profile image
    ManipUni

    spivonious said:
    Maddus Mattus said:
    *snip*

    "You need to prevent your users from being able to connect to the database without the application."

     

    Why? They can't do anything directly in the DB that they couldn't do from the app.

     

    figuerres - one of the developers that is no longer here setup a "OneClick"-esque updating system with our main VB6 client, so updates are downloaded and installed automatically. Pretty neat actually. I definitely see the advantages of a 3+-tier architecture, but for our needs the 2-tier is working fine. All that adding another tier would do is add another server, unless I'm missing something. The app.config would make sense too, but the client has a server choice. End users don't have a logon to the development server, and the production server is chosen by default. Not the prettiest solution, but it works.

     

    Anyway, I have no power to change these things, so back to the topic at hand! Smiley

     

    It might work, I'd run FAILED_LOGIN_ATTEMPTS 5 just to be sure and audit regularly. I'd still want to sit there and see if a user really can "only do what the application can do."

  • User profile image
    spivonious

    ManipUni said:
    spivonious said:
    *snip*

    It might work, I'd run FAILED_LOGIN_ATTEMPTS 5 just to be sure and audit regularly. I'd still want to sit there and see if a user really can "only do what the application can do."

    The way we do things, the user has no permissions at all. They are assigned one or more database roles, which are only enabled when the "SET ROLE ALL" command is run in the DB session. They have no permissions to grant roles, so no extra permissions can be attained once connected. The application runs this command when connecting to the DB.

     

    This brings up another question. How would this be handled normally? Hopefully not with client-side logic.

  • User profile image
    figuerres

    spivonious said:
    Maddus Mattus said:
    *snip*

    "You need to prevent your users from being able to connect to the database without the application."

     

    Why? They can't do anything directly in the DB that they couldn't do from the app.

     

    figuerres - one of the developers that is no longer here setup a "OneClick"-esque updating system with our main VB6 client, so updates are downloaded and installed automatically. Pretty neat actually. I definitely see the advantages of a 3+-tier architecture, but for our needs the 2-tier is working fine. All that adding another tier would do is add another server, unless I'm missing something. The app.config would make sense too, but the client has a server choice. End users don't have a logon to the development server, and the production server is chosen by default. Not the prettiest solution, but it works.

     

    Anyway, I have no power to change these things, so back to the topic at hand! Smiley

     

    you can run it on one or as many as you need.

    that also has an effect when you need to scale up.

     

    if you need to handle more users with database connections the sql server has to alloc more ram for each connection - try tripling the number of user connections and watch the server choke when it gets ram-hungry.

     

    put if the connections are done by a service layer you can have say 3 servers each making say 10 connections each and handle say 200 users per server (or more)

     

    so that 1 user != 1 sql connection.

     

    when small you can have one server with both tiers.

     

    if you use DNS to connect the client to the server then you can add mid-servers and let DNS sort them out to any number of mid-servers.

     

    Database roles wise you should have  a database Schema that maps what tables / views / procs etc... are availibale.

    you then put the users login on that Schema

    I have not done Oracle for a long time so i can't say eactly how to do that....

    SET ROLE ALL sounds like they are getting full rights to the database ?? if so then thats not good at all.

    *if* that's what it does.

    do you have an actual DBA there ? someone with certified ORACLE DBA training ?

     

  • User profile image
    Maddus Mattus

    spivonious said:
    Maddus Mattus said:
    *snip*

    "You need to prevent your users from being able to connect to the database without the application."

     

    Why? They can't do anything directly in the DB that they couldn't do from the app.

     

    figuerres - one of the developers that is no longer here setup a "OneClick"-esque updating system with our main VB6 client, so updates are downloaded and installed automatically. Pretty neat actually. I definitely see the advantages of a 3+-tier architecture, but for our needs the 2-tier is working fine. All that adding another tier would do is add another server, unless I'm missing something. The app.config would make sense too, but the client has a server choice. End users don't have a logon to the development server, and the production server is chosen by default. Not the prettiest solution, but it works.

     

    Anyway, I have no power to change these things, so back to the topic at hand! Smiley

     

    Then ditch the application and just give them a login.

  • User profile image
    Dr Herbie

    spivonious said:
    Maddus Mattus said:
    *snip*

    "You need to prevent your users from being able to connect to the database without the application."

     

    Why? They can't do anything directly in the DB that they couldn't do from the app.

     

    figuerres - one of the developers that is no longer here setup a "OneClick"-esque updating system with our main VB6 client, so updates are downloaded and installed automatically. Pretty neat actually. I definitely see the advantages of a 3+-tier architecture, but for our needs the 2-tier is working fine. All that adding another tier would do is add another server, unless I'm missing something. The app.config would make sense too, but the client has a server choice. End users don't have a logon to the development server, and the production server is chosen by default. Not the prettiest solution, but it works.

     

    Anyway, I have no power to change these things, so back to the topic at hand! Smiley

     

    We use a 3-tier system (client app, remoting server, and DB).  The remoting server connects to the DB with a single login. Our framework marks record edits with the application-level login details of the user for auditing.

    I did consider adding the option of per-user logins, but I don't actually think anyone will need or want it.

     

    We regard the database as belonging to our customers so they have full, unfettered access; it is their data after all.  They know that if they screw with the schema or the lookup data they might well break the application and that will be their own damn fault.  We make sure they have a good backup process in place. We have an hourly rate for manual database repairs.  This doesn't mean that accidents don't happen, but it deters casual meddling.

     

    At least one of our customers has decades of data in their database that their sales teams mine for info. All of our customers are free to create their own reports outside of our application's reporting system.  Like I said, it's their data, not ours. 

     

    Herbie

     

     

     

  • User profile image
    spivonious

    figuerres said:
    spivonious said:
    *snip*

    you can run it on one or as many as you need.

    that also has an effect when you need to scale up.

     

    if you need to handle more users with database connections the sql server has to alloc more ram for each connection - try tripling the number of user connections and watch the server choke when it gets ram-hungry.

     

    put if the connections are done by a service layer you can have say 3 servers each making say 10 connections each and handle say 200 users per server (or more)

     

    so that 1 user != 1 sql connection.

     

    when small you can have one server with both tiers.

     

    if you use DNS to connect the client to the server then you can add mid-servers and let DNS sort them out to any number of mid-servers.

     

    Database roles wise you should have  a database Schema that maps what tables / views / procs etc... are availibale.

    you then put the users login on that Schema

    I have not done Oracle for a long time so i can't say eactly how to do that....

    SET ROLE ALL sounds like they are getting full rights to the database ?? if so then thats not good at all.

    *if* that's what it does.

    do you have an actual DBA there ? someone with certified ORACLE DBA training ?

     

    Oracle splits user privileges into two groups, default and session. When a user logs on they automatically get the default privileges. Running set role all gives them session privileges. There's no way for them to get privileges that aren't assigned to them so it's a fairly good system.

     

    Anyway, I didn't set it up, and it's been that way for 10-15 years now, so I doubt they'll want to change it Smiley

     

    Maddus - just give them a login? The same could be said for pretty much any LOB app. Teach the users some SQL and fire the developers.

  • User profile image
    Maddus Mattus

    spivonious said:
    figuerres said:
    *snip*

    Oracle splits user privileges into two groups, default and session. When a user logs on they automatically get the default privileges. Running set role all gives them session privileges. There's no way for them to get privileges that aren't assigned to them so it's a fairly good system.

     

    Anyway, I didn't set it up, and it's been that way for 10-15 years now, so I doubt they'll want to change it Smiley

     

    Maddus - just give them a login? The same could be said for pretty much any LOB app. Teach the users some SQL and fire the developers.

    The added value for your application is the context the data is used in. Your application contains the business rules. That's why you should shield your users from the database. They have no business there.

  • User profile image
    Matthew van Eerde

    There is no "in general" answer for this question.  The answer depends entirely on the specifics of the situation.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.