Re: security without using different usernames

From: <JApplewhite_at_austin.isd.tenet.edu>
Date: Tue, 15 Jul 2003 17:24:38 -0500
Message-Id: <25929.337900@fatcity.com>

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

Ryan,

To make a particular schema the focus for a session:

Alter Session Set Current_Schema = <TheSchema> ;

Better yet, instead of proliferating the same schema for each client, convert the tables to partitioned tables in a single schema, with each partiion being for a specific client. That way you can add and remove clients by adding and dropping partitions. Each client's partitions could even be in tablespace(s) on separate drives to isolate their I/O from others. If all the schemas will always be identical, then partitioned tables are the way to go. If you'll customize the app and data structures for some clients, then you'll have to stick with individual schemas.

If you use partitioned tables, use FGAC (Fine-Grained Access Control, AKA Row-Level Security) and Application Context to control security - see the Concepts doc and Application Developers Guide for details. Using those features of Oracle, you could limit each client to only their own partitions.

I've used it in the past and It works very well. In fact, I'm in the last stages of designing and implementing an FGAC solution by which the 162 Campus-level Student Information databases in our District will be consolidated into a single Oracle database, with users at each Campus only seeing the tables for their School. We already have all 53,000 tables and 75,000 indexes (those are not typos!) of this 3rd Party App in an Oracle database. I'm just adding the security piece to keep them out of each other's business (or, "bidness", as we say in Texas). ;-)

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 05:29 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        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 007B156386256D64_=
Content-Type: text/html; charset="us-ascii"

 Ryan,
 
 To make a particular schema the focus for a session:
 
 Alter Session Set Current_Schema = <TheSchema> ; 
 

Better yet, instead of proliferating the same schema for each client, convert the tables to partitioned tables in a single schema, with each partiion being for a specific client.  That way you can add and remove clients by adding and dropping partitions.  Each client's partitions could even be in tablespace(s) on separate drives to isolate their I/O from others.  If all the schemas will always be identical, then partitioned tables are the way to go.  If you'll customize the app and data structures for some clients, then you'll have to stick with individual schemas.
 
 If you use partitioned tables, use FGAC (Fine-Grained Access Control, AKA Row-Level Security) and Application Context to control security - see the Concepts doc and Application Developers Guide for details.  Using those features of Oracle, you could limit each client to only their own partitions.
 
 I've used it in the past and It works very well.  In fact, I'm in the last stages of designing and implementing an FGAC solution by which the 162 Campus-level Student Information databases in our District will be consolidated into a single Oracle database, with users at each Campus only seeing the tables for their School.  We already have all 53,000 tables and 75,000 indexes (those are not typos!) of this 3rd Party App in an Oracle database.  I'm just adding the security piece to keep them out of each other's business (or, "bidness", as we say in Texas).     ;-)
 

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 05:29 PM
 Please respond to ORACLE-L
 
<td>        
         To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
         cc:        
         Subject:        security without using different usernames</table>
 
 
 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:24:38 CDT