|
partitioning is not an option. one of our ingestion
methods to bring data to the production database is transportable tablespaces.
It wont work. So we cant just cram everything into the same table. We ingest
data from different 'staging' servers. that have differing logic. this data is
then transportable to the production servers. We also use imports and sqlloaders
as well. depends on how we get the data. Some data we get from the client, some
we get from other groups in the company.
so FGAC is out and I know how to use it.
Sent: Tuesday, July 15, 2003 7:24
PM
Subject: Re: security without using
different usernames
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)
[EMAIL PROTECTED]
|
| "Ryan"
<[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/15/2003 05:29 PM
Please respond to ORACLE-L
|
To:
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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?
|
