Re: security without using different usernames

From: <JApplewhite_at_austin.isd.tenet.edu>
Date: Tue, 15 Jul 2003 17:32:01 -0500
Message-Id: <25929.337903@fatcity.com>

This is a multipart message in MIME format. --=_alternative 007BC2B786256D64_=
Content-Type: text/plain; charset="us-ascii"

Ryan,

Segment Exchange is a very easy, instantaneous, method of moving data back and forth between standalone tables and partitioned tables. Transportable Tablespaces can "ingest" the data into your DB, but then it can be comfortably "digested" into partitions via Segment Exchange.

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite_at_austin.isd.tenet.edu

"Ryan" <rgaffuri_at_cox.net>
Sent by: ml-errors_at_fatcity.com
07/15/2003 06:09 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: security without using different usernames


management 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. ----- Original Message -----
From: Goulet, Dick
To: Multiple recipients of list ORACLE-L 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
-----Original Message-----
From: Ryan [mailto:rgaffuri_at_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.

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.

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.

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?

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?

--=_alternative 007BC2B786256D64_=
Content-Type: text/html; charset="us-ascii"

 Ryan,
 
 Segment Exchange is a very easy, instantaneous, method of moving data back and forth between standalone tables and partitioned tables.  Transportable Tablespaces can "ingest" the data into your DB, but then it can be comfortably "digested" into partitions via Segment Exchange. 
 

Jack C. Applewhite 
Database Administrator 
Austin Independent School District 
Austin, Texas 
512.414.9715 (wk) 
512.935.5929 (pager) 
JApplewhite_at_austin.isd.tenet.edu 

 
 
 
<table width=100%>
<tr valign=top>
<td>
<td>"Ryan" <rgaffuri_at_cox.net>
 Sent by: ml-errors_at_fatcity.com
07/15/2003 06:09 PM
 Please respond to ORACLE-L
 
<td>        
         To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
         cc:        
         Subject:        Re: security without using different usernames</table>
 
 
 management 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. 
 ----- Original Message ----- 
 From: <a href=mailto:DGoulet_at_vicr.com>Goulet, Dick</a> 
 To: <a href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L</a> 
 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 
-----Original Message----- 

From:</b> Ryan [mailto:rgaffuri_at_cox.net]<b><br>
Sent:</b> Tuesday, July 15, 2003 6:29 PM<b><br>
To:</b> Multiple recipients of list ORACLE-L<b><br>

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. 
  
 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. 
  
 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. 
  
 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? 
  
 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? 
Received on Tue Jul 15 2003 - 17:32:01 CDT