Yes I understand that what I am trying to do is "broken" from a Oracle perspective. The specific issue I am running into is that my application environment is only allowed to connect to the DB as one user/schema. I want to use dbicdump on other schemas however, and its not just like a couple. Its going to be over 50. So creating a trigger in the DB to switch my schema on login is not really feasible, Id like to be able to automate it more easily. Passing a schema= to the connect string seemed like the best option.
On Mon, Nov 12, 2012 at 4:03 PM, Furst, Carl <carl.fu...@mlb.com> wrote: > Switching schemas in Oracle makes no sense, because schemas are tied to > the user. In fact you don't login to oracle with a username but with a > schema name. Schema and username are the same things. It is not like MySQL > where you have Databases that denote these object groups and are separate > from users. > > However, this is not something that is confined. If you track which > schemas you want to access, and have the proper grants issued.. You can > always access any object with the following notion: > > schema_name.object_name > > This is irregardless of which schema you are logged into. However, the > proper grants need to be issued and can only be issued if you are logged > into the schema that owns those objects. > > For example.. > > Table A is in Schema X and Table B is in schema Y.. X wants to access B > and Y wants to access A.. > > So you have to login as Y and > > GRANT SELECT,UPDATE,INSERT,DELETE ON B TO X; > > Then login as X and > > GRANT SELECT,UPDATE,INSERT,DELETE ON A TO Y; > > Then if you are logged in as X you can do > > SELECT * FROM Y.B; > > And logged in as Y > > SELECT * FROM X.A; > > Hope that helps, > Carl Furst > > > > > > > On 11/12/12 3:32 PM, "John Scoles" <byter...@hotmail.com> wrote: > >> >> >>---------------------------------------- >>> Date: Mon, 12 Nov 2012 19:04:56 +0000 >>> From: martin.ev...@easysoft.com >>> To: dbi-users@perl.org >>> CC: kevin.k...@gmail.com >>> Subject: Fwd: DBD::Oracle Schema different than User question >>> >>> Hi Kevin, >>> >>> I've forwarded your email on to the dbi-users list. See >>> http://dbi.perl.org and look at the support page. Sorry for top posting >>> but my email client is having some sort of fit with your email. I don't >>> have any issue with any well formed patch to set the schema but I'll >>> wait to see what others say as personally I've never had to change it. >>> >>> Martin >>> >>> >>> -------- Original Message -------- >>> Subject: DBD::Oracle Schema different than User question >>> Date: Mon, 12 Nov 2012 13:04:05 -0500 >>> From: Kevin L. Kane <kevin.k...@gmail.com> >>> To: Tim Bunce <t...@cpan.org>, Yanick Champoux <yan...@cpan.org>, >>> "Martin J. Evans" <mjev...@cpan.org> >>> >>> Hi all, >>> I am running into a problem and was planning on modifying my local >>> DBD::Oracle to add support for a "schema=<mySchema>" construct in the >>> connect string. Specifically, I want to connect as user X but set >>> current_schema to Y. >> >>Sounds like someone tried to creata a MySQL type DB schema on Oracle >>again;) >> >>Anyway if you want to log in wiht 1 generic user then change the 'schema' >> just issue >>an sql something like this >> >>exec sql execute immediate ALTER SESSION SET CURRENT_SCHEM=sss >> >> >> >>> Another solution i've toyed with is having a >>> trigger that switches my schema when I log in but I need to do this >>> for a lot of different schemas and I will always be the same user. It >>> seems weird to me that support for this isn't included in DBD::Oracle >>> currently. >>> >>> If I do this in a sane way are you at all interested in the patch? >> >>IF if you come up with a patch we will have a look at it. >> >>> Am I just missing something and this functionality is already there? Or >>> should this functionality not exist in the first place and why? >>> >> >>I don't think it should be there because what oracle thinks a schema is >>different thatn other DB?? >>A schema to an Oracle DB is the set of all tables and other objects owned >>by a user account if I am not mistaken >>not the grouping of all the table under an app or alike >> >>Anyway off the top of head I do not think there is a way with OCI to >>change the shcema without loging in again as there >> is only OCISession begin OCIlogin2 neither take any shcema params??? >> >> >>Cheers >>John >>> Thanks, >>> Kevin >>> -- >>> Kevin L. Kane >>> kevin.kane at gmail.com >>> >>> >>> >>> >>> >>> > > > > > > > ********************************************************** > > MLB.com: Where Baseball is Always On > -- Kevin L. Kane kevin.kane at gmail.com
