Fwd: DBD::Oracle Schema different than User question

[Martin J. Evans]

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...@gmail.com>
To: Tim Bunce <ti...@cpan.org>, Yanick Champoux <yan...@cpan.org>,
"Martin J. Evans" <mje...@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. 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? Am
I just missing something and this functionality is already there? Or
should this functionality not exist in the first place and why?

Thanks,
Kevin
--
Kevin L. Kane
kevin.kane at gmail.com

[Matthew Musgrove]

I've never had a need to have it set in the connect string because our
logon trigger sets it for us based on a variety of criteria.

execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = SOMESCHEMA';



Matt


On Mon, Nov 12, 2012 at 1:04 PM, Martin J. Evans
<martin...@easysoft.com>wrote:

[John Scoles]

----------------------------------------
> Date: Mon, 12 Nov 2012 19:04:56 +0000
> From: martin...@easysoft.com
> To: dbi-...@perl.org
> CC: kevin...@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...@gmail.com>
> To: Tim Bunce <ti...@cpan.org>, Yanick Champoux <yan...@cpan.org>,
> "Martin J. Evans" <mje...@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

[Furst, Carl]

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

**********************************************************

MLB.com: Where Baseball is Always On

[Martin J. Evans]

Forwarded to dbi-users.

-------- Original Message --------
Subject: DBD::Oracle Schema different than User question
Date: Mon, 12 Nov 2012 13:04:05 -0500
From: Kevin L. Kane <kevin...@gmail.com>
To: Tim Bunce <ti...@cpan.org>, Yanick Champoux <yan...@cpan.org>,
"Martin J. Evans" <mje...@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. 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? Am

I just missing something and this functionality is already there? Or
should this functionality not exist in the first place and why?

[Kevin L. Kane]

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.

[Charles Jardine]

On 12/11/12 21:27, Kevin L. Kane wrote:
> 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.

You can change the default schema at any time after connecting to an Oracle
database by:

$dbh->do("alter session set current_schema=$schema");

This changes the default schema used in subsequent DML (Data Manipulation
Language) statements which use partially qualified object names.

I can't see why changing this setting would have any effect on the behaviour
of DBIx::Class::Schema::Loader, since this module will obtain all its data
from the Data Dictionary, in schema SYS. You do not get the description
of a schema by querying objects in the schema itself.

If dbicdump does not provide a way of specifying a schema name different
from
the user name, it needs fixing.

--
Charles Jardine - Computing Service, University of Cambridge
cj...@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679