| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: security without using different usernames
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C34B22.95CA72F0
Content-Type: text/plain
Consider DBMS_RLS for row level security (or as in some other Oracle marketing-speak has been called the Virtual Private Database.)
Essentially: you create a package/stored procedure function that returns a predicate that can be plugged into a WHERE clause; in your case, in your package instantiation code, you can go off and look at V$SESSION, and then store some magic value in a package variable; your predicate function will then use this package variable and return some comparison that would yield TRUE or FALSE (in the most simple case, you can have it return "1 = 1" or "1 = 0".) Now, use DBMS_RLS to add a security policy on the table, and give this security policy the name of your stored procedure/function. Here's how it works: when a user (any user) performs any DMLs against the table, Oracle looks at the security policy to find the name of the stored procedure/function; Oracle will then evaluate the stored procedure/function and apply the value of the stored procedure/function to the criteria for accessing the table. Voila, you now can instantly dictate at the table-level what records users will have access to.
...Rudy
-----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?
------_=_NextPart_001_01C34B22.95CA72F0
Content-Type: text/html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1170" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=509403622-15072003><FONT face=Arial color=#0000ff
size=2>Consider DBMS_RLS for row level security (or as in some other Oracle
marketing-speak has been called the Virtual Private
Database.)</FONT></SPAN></DIV>
<DIV><SPAN class=509403622-15072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=509403622-15072003><FONT face=Arial color=#0000ff
size=2>Essentially: you create a package/stored procedure function that returns
a predicate that can be plugged into a WHERE clause; in your case, in your
package instantiation code, you can go off and look at V$SESSION, and then store
some magic value in a package variable; your predicate function will then use
this package variable and return some comparison that would yield TRUE or FALSE
(in the most simple case, you can have it return "1 = 1" or "1 = 0".) Now, use
DBMS_RLS to add a security policy on the table, and give this security policy
the name of your stored procedure/function. Here's how it works: when a user
(any user) performs any DMLs against the table, Oracle looks at the security
policy to find the name of the stored procedure/function; Oracle will then
evaluate the stored procedure/function and apply the value of the stored
procedure/function to the criteria for accessing the table. Voila, you now can
instantly dictate at the table-level what records users will have access
to.</FONT></SPAN></DIV>
<DIV><SPAN class=509403622-15072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=509403622-15072003><FONT face=Arial color=#0000ff
size=2>...Rudy</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Ryan
[mailto:rgaffuri_at_cox.net] <BR><B>Sent:</B> Tuesday, July 15, 2003 6:29
PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B>
security without using different usernames<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>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. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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.
</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>My question is what would be the best way? Cantdo 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? </FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>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?</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>To stress, I cant change the application.Different group with different skillsets. Any suggestions? Received on Tue Jul 15 2003 - 17:44:01 CDT
 |
 |