Re: .NET, connection pooling and security .
We are going the OID way because of these problems. Anyway here is a wild idea: Tell the web guys to use the user userid (he probably logged to the web application) with a standard password that is common to all of them and is supplied by the web application, the user does not see it. If you have an information security guy, teach him how to add users and grant the application user role. The schema owner password need to be a closely held secret of the dba group. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 5:49 AM I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: .NET, connection pooling and security .
Hi Jared, The users don't have to authenticate in the application because they've been setup in active directory. It may be similar to SAP, except we don't have the SAP developers in-house making production changes without telling anyone. That's why I wan't to lock it down. In the past the developers had full access to dev/qa/prod. I've removed full access to qa and prod. qa is the clean room before prod and prod is for application sql/dml only - not tweaking. They're looking for other alternative accesses. I've turned on auditing and have sent out emails to their mana-jerks when I see that they've accessed production with one of these user ids, but they don't see any problems and say it's all water under the bridge. I trust one or two of the developers to do some of the stuff (in dev first). They know the data better than I do, but not all developers are created equal... I seen some delete and update statements sent to me to run that are missing the where clauses... those people do stuff without telling me and then make a big stink about Oracle mysteriously losing data. I don't have the time to keep playing detective. I guess I should feel glad that this is the standard :) thanks. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:14 PM Steve, I'm not a web developer either, but I do know that this is a very common method of handling the database connections. Many 2 tier apps work this way as well. SAP for example. Unless you have influence on the architecture and can present a convincing argument, you best learn how to work with it. You don't give any details about the app either. Are users required to authenticate? If not, what would be the point of requiring db accounts for them? The number of users is important as well. Imagine a web app that services 250k users. Do you really want that many users in the data dictionary? Would you want the DDL overhead of creating/administering that many users? I'm considering some extremes, because there were no details provided. HTH Jared On Sat, 2003-11-29 at 19:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
Re: .NET, connection pooling and security .
I like the idea of roles and that's what I'm trying to get at, but the app determines the user's authority prior to connecting to the database by looking at a key in active directory. The database connections have to connect as the highest level user possible, which is the application admin. I don't like that because it seems like a security hole. Like I said, I'm not real comfortable with Web security and may be making something out of nothing. thanks. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:44 PM Well ... in general it's the apps that manage the system security, and the DB users are there to prevent the app users from doing damage, but in general these two work in unison. I have not seen any decent ways of having the DB administer users without there being a serious overhead, in terms of administration duties, for the DBA (which is what Jared mentioned). I say that, given the information you provide, sticking with the two types of roles (owner and user) is the most adequate way. Why would you want to change this anyway? My 3.14159 pence worth. -Original Message- Jared Still Sent: Saturday, November 29, 2003 9:15 PM To: Multiple recipients of list ORACLE-L Steve, I'm not a web developer either, but I do know that this is a very common method of handling the database connections. Many 2 tier apps work this way as well. SAP for example. Unless you have influence on the architecture and can present a convincing argument, you best learn how to work with it. You don't give any details about the app either. Are users required to authenticate? If not, what would be the point of requiring db accounts for them? The number of users is important as well. Imagine a web app that services 250k users. Do you really want that many users in the data dictionary? Would you want the DDL overhead of creating/administering that many users? I'm considering some extremes, because there were no details provided. HTH Jared On Sat, 2003-11-29 at 19:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: nelson flores
Re: .NET, connection pooling and security .
I'm going to start looking at OID. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 4:49 AM We are going the OID way because of these problems. Anyway here is a wild idea: Tell the web guys to use the user userid (he probably logged to the web application) with a standard password that is common to all of them and is supplied by the web application, the user does not see it. If you have an information security guy, teach him how to add users and grant the application user role. The schema owner password need to be a closely held secret of the dba group. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 5:49 AM I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: .NET, connection pooling and security .
I like the idea of setting the client info. The consensus on the other stuff is that's just the way it is. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:34 PM On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: .NET, connection pooling and security .
Multi-Org in Oracle Applications works (well) with this client info setting and views having where clauses on client info. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 01, 2003 6:19 AM I like the idea of setting the client info. The consensus on the other stuff is that's just the way it is. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:34 PM On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the
Re: .NET, connection pooling and security .
What is Multi-Org? Sounds like a brand of kitchen utensils? On 2003.12.01 00:39, Tanel Poder wrote: Multi-Org in Oracle Applications works (well) with this client info setting and views having where clauses on client info. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 01, 2003 6:19 AM I like the idea of setting the client info. The consensus on the other stuff is that's just the way it is. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:34 PM On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of
Re: .NET, connection pooling and security .
Technically it's a means for doing row-level security in Oracle Apps (in functional side there's of course more). It's just a bunch of views on base tables. All base tables have org_id column in them and the views include a clause where they compare rows org_id to organization id taken from sessions client info. And Forms applications populate the client info during logon. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 01, 2003 7:54 AM What is Multi-Org? Sounds like a brand of kitchen utensils? On 2003.12.01 00:39, Tanel Poder wrote: Multi-Org in Oracle Applications works (well) with this client info setting and views having where clauses on client info. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 01, 2003 6:19 AM I like the idea of setting the client info. The consensus on the other stuff is that's just the way it is. thanks, steve - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 11:34 PM On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include
Re: .NET, connection pooling and security .
Steve, I'm not a web developer either, but I do know that this is a very common method of handling the database connections. Many 2 tier apps work this way as well. SAP for example. Unless you have influence on the architecture and can present a convincing argument, you best learn how to work with it. You don't give any details about the app either. Are users required to authenticate? If not, what would be the point of requiring db accounts for them? The number of users is important as well. Imagine a web app that services 250k users. Do you really want that many users in the data dictionary? Would you want the DDL overhead of creating/administering that many users? I'm considering some extremes, because there were no details provided. HTH Jared On Sat, 2003-11-29 at 19:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: .NET, connection pooling and security .
On 2003.11.29 22:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve Steve, I am not a .NOT user or admirer but I think that all security should be in one place because then it is non-conflicting and more easily controlled. If the business decision is made that this place is LDAP, then you don't have much choice. For the sake of the DBA staff, you can adopt a standard mandating that every application should call DBMS_APPLICATION_INFO.SET_CLIENT_INFO immediately after it connects to the database. Client info information is visible from V$SESSION so you can use alternative means of determining sid and serial#. What does seem as an objectionable practice is granting admin authority through LDAP. Only DBA should have DBA role and nobody else. Hopefully, this admin role granted through the active directory does not mean DBA, but only application admin. Application admins are helpful people who know the application and administer certain parts of it. They can take the burden of mundane tasks like granting revoking roles as well as creating users away from the DBA and have him working on more important tasks like helping developers, documenting best practices, planning disaster recovery, setting standards, planning upgrades and tuning buffer cache hit ratio. In other words, everything seems to be hunky dory except the posibiliity that the DBA role is granted away lightheartedy. You are a DBA and as a DBA, you took the oath of enforcing the first DBA commandment which reads: Thou shalt not have other DBAs but me. No ifs, no buts, no active directories here. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: .NET, connection pooling and security .
Well ... in general it's the apps that manage the system security, and the DB users are there to prevent the app users from doing damage, but in general these two work in unison. I have not seen any decent ways of having the DB administer users without there being a serious overhead, in terms of administration duties, for the DBA (which is what Jared mentioned). I say that, given the information you provide, sticking with the two types of roles (owner and user) is the most adequate way. Why would you want to change this anyway? My 3.14159 pence worth. -Original Message- Jared Still Sent: Saturday, November 29, 2003 9:15 PM To: Multiple recipients of list ORACLE-L Steve, I'm not a web developer either, but I do know that this is a very common method of handling the database connections. Many 2 tier apps work this way as well. SAP for example. Unless you have influence on the architecture and can present a convincing argument, you best learn how to work with it. You don't give any details about the app either. Are users required to authenticate? If not, what would be the point of requiring db accounts for them? The number of users is important as well. Imagine a web app that services 250k users. Do you really want that many users in the data dictionary? Would you want the DDL overhead of creating/administering that many users? I'm considering some extremes, because there were no details provided. HTH Jared On Sat, 2003-11-29 at 19:49, Steve Perry wrote: I hope somebody on the list can help me out with this. All of our 3-tier apps are architected with a schema owner (owns all objects used by an application) and application user (no create privs, but it does have full dml privs to the schema owner objects). On the web side, connection pooling is setup with 10 connections logged in (all as the application user). When users connect, the application reads some active directory keys that tell if the user is a reader, dml user or admin user (all privs). I don't feel the application should be managing security and I'd like to take that responsibility away. The 10 identical connections logged into the database bothers me too. I'd like to make it work similar to our 2-tier apps where we use roles, assign them to a user and they connect individually. We don't have OID setup and I imagine that would solve this. Short of that, is there any other way to work around having the 10 identical connections logging in and having the application maintaining security? Is there another way of assigning the security? I don't have any web development experience and I thought I'd check here first to see how others deal with this. I hope somebody else has worked this out at their shop. I'm not sure if the answers will change, but it's an all M$ shop, except for Oracle. Any help would be appreciated. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Perry INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: nelson flores INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).