perspective. The specific issue I am running into is that my
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
Id like to be able to automate it more easily. Passing a schema= to
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-us...@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
