Message-Id: <25929.337898@fatcity.com> From: "Ryan" Date: Tue, 15 Jul 2003 18:11:19 -0400 Subject: Re: security without using different usernames This is a multi-part message in MIME format. ------=_NextPart_000_10B4_01C34AFC.7D829630 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: upgrade to AIX 5management has handled it. namely my manager and the = answer is no. Partitioning cant be done. we are ingesting data to this = application via transportable tablespaces. So each schema has to be = self-contained.=20 ----- Original Message -----=20 From: Goulet, Dick=20 To: Multiple recipients of list ORACLE-L=20 Sent: Tuesday, July 15, 2003 6:59 PM Subject: RE: security without using different usernames Ryan, What would be much better is to create the single schema and = partition the tables so that each customer's data lands into it's own = partition. As for this other group, make some friends. It's a lot = easier to get your problems and concerns addressed if the people your = talking to are on a friendly basis with you. You can also bring up the = problems of scaling to your management in terms of dollars needed for = additional servers, memory, hard disk, and software. For some reason = that is something pointy headed managers seem to understand, especially = when you start talking about Oracle licenses at $40K per CPU. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA=20 -----Original Message----- From: Ryan [mailto:rgaffuri@cox.net] Sent: Tuesday, July 15, 2003 6:29 PM To: Multiple recipients of list ORACLE-L Subject: security without using different usernames I know this is terrible design, but the GUI was created by a software = engineering group that is seperate from the database group. Its not = scalable. So Im trying to come up with a more scalable method. I have no = power to change their gui. It rides on the database. I have to live with = it. This is not a high enough transaction database to warrant seperate = instances.=20 We have a variety of customers. Each of them has their own versions of = data. However, the schema is exactly the same. These tables can get = huge, so we dont want to throw them all into the same schema. Right now, due to the fact that the GUI has a series of logins that = are the same across clients, each client has its own instance. This isnt = very scalable as we get more business. We have to create another = instance and ingest data to it.=20 Id like to find a way to get all the clients in the same instance with = just different schemas and tablespaces. One thing I may have control = over would be to slightly rename the executable. If you check v$session, = in a client-server application the name of the product connecting to the = database is recording. I can handle security based off of that.=20 My question is what would be the best way? Cant do synonyms for this = since its the same login. I think I saw somewhere that there is a = session based 'set' command where you can say use this schema. I think = it was on asktom and in reference to a question about public synonyms. I = cant find it. Anyone know it?=20 Also is it viable to base a context off of what is in v$sesion with a = logon trigger? How would I 'redirect' all queries to a specific schema? To stress, I cant change the application. Different group with = different skillsets. Any suggestions?=20 ------=_NextPart_000_10B4_01C34AFC.7D829630 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: upgrade to AIX 5
management has handled it. namely my = manager and=20 the answer is no. Partitioning cant be done. we are ingesting data = to this=20 application via transportable tablespaces. So each schema has to be=20 self-contained.
----- Original Message -----
From:=20 Goulet, = Dick=20
To: Multiple recipients of list = ORACLE-L=20
Sent: Tuesday, July 15, 2003 = 6:59=20 PM
Subject: RE: security without = using=20 different usernames

Ryan,
 
    What would be much better is to create the = single schema=20 and partition the tables so that each customer's data lands into it's = own=20 partition.  As for this other group, make some friends.  = It's a lot=20 easier to get your problems and concerns addressed if the people your = talking=20 to are on a friendly basis with you.  You can also bring up the = problems=20 of scaling to your management in terms of dollars needed for = additional=20 servers, memory, hard disk, and software.  For some reason that = is=20 something pointy headed managers seem to understand, especially when = you start=20 talking about Oracle licenses at $40K per CPU.
 

Dick Goulet
Senior Oracle DBA
Oracle Certified = 8i DBA=20

-----Original Message-----
From: Ryan=20 [mailto:rgaffuri@cox.net]
Sent: Tuesday, July 15, 2003 6:29=20 PM
To: Multiple recipients of list = ORACLE-L
Subject:=20 security without using different usernames

I know this is terrible design, but = the GUI was=20 created by a software engineering group that is seperate from the = database=20 group. Its not scalable. So Im trying to come up with a more scalable = method.=20 I have no power to change their gui. It rides on the database. I have = to live=20 with it. This is not a high enough transaction database to warrant = seperate=20 instances.
 
We have a variety of customers. Each = of them has=20 their own versions of data. However, the schema is exactly the same. = These=20 tables can get huge, so we dont want to throw them all into the same=20 schema.
 
Right now, due to the fact that the = GUI has a=20 series of logins that are the same across clients, each client has its = own=20 instance. This isnt very scalable as we get more business. We have to = create=20 another instance and ingest data to it.
 
Id like to find a way to get all the = clients in=20 the same instance with just different schemas and tablespaces. One = thing I may=20 have control over would be to slightly rename the executable. If you = check=20 v$session, in a client-server application the name of the product = connecting=20 to the database is recording. I can handle security based off of that. =
 
My question is what would be the best = way? Cant=20 do synonyms for this since its the same login. I think I saw somewhere = that=20 there is a session based 'set' command where you can say use this = schema. I=20 think it was on asktom and in reference to a question about public = synonyms. I=20 cant find it. Anyone know it?
 
Also is it viable to base a context = off of what=20 is in v$sesion with a logon trigger? How would I 'redirect' all = queries to a=20 specific schema?
 
To stress, I cant change the =