locked
Oracle Client in ASP.Net connecting two different schemas RRS feed

  • Question

  • User462755366 posted

    Hi

    In my asp.net application im using Oracle client. In the connection string, we specify the 3 basic attributes: data source, User Id, and Password. We are using connection pooling. Our data resides in 2 different Oracle schemas. Most of the requests are for data from both Schema1 and Schema2.

    Is it possible that the connections are implicitly tied to a schema1 or schema2…?

    Thanks

    Sreenath

    Wednesday, February 9, 2011 12:45 PM

Answers

  • User269602965 posted

    I would think about this:

    SCHEMA1 is for data

    SCHEMA2 is for data

    SCHEMA12_USER is a user account referenced by the ASP.NET connection string,

        revoke all default privileges for SCHEMA12_USER from the account creation.

    Then Create a ROLE, SCHEMA12_VIEWER.

    Grant SCHEMA12_VIEWER all the specific privileges needed, like GRANT SELECT ON SCHEMA1.TABLE1 to SCHEMA12_VIEWER,

    and GRANT SELECT, UPDATE, INSERT on SCHEMA2.TABLE1 to SCHEMA12_VIEWER.

    Finally, GRANT SCHEMA12_VIEWER TO SCHEMA12_USER, giving the user account all the same privileges needed to access

    both data schemas from one connection account and pool. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 9, 2011 11:42 PM

All replies

  • User269602965 posted

    I would elevate that highly technical question to the oracle ODP.NET forum

    where Oracle engineers visit.

    http://forums.oracle.com/forums/forum.jspa?forumID=146 

    Wednesday, February 9, 2011 1:00 PM
  • User269602965 posted

    I would think about this:

    SCHEMA1 is for data

    SCHEMA2 is for data

    SCHEMA12_USER is a user account referenced by the ASP.NET connection string,

        revoke all default privileges for SCHEMA12_USER from the account creation.

    Then Create a ROLE, SCHEMA12_VIEWER.

    Grant SCHEMA12_VIEWER all the specific privileges needed, like GRANT SELECT ON SCHEMA1.TABLE1 to SCHEMA12_VIEWER,

    and GRANT SELECT, UPDATE, INSERT on SCHEMA2.TABLE1 to SCHEMA12_VIEWER.

    Finally, GRANT SCHEMA12_VIEWER TO SCHEMA12_USER, giving the user account all the same privileges needed to access

    both data schemas from one connection account and pool. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 9, 2011 11:42 PM