Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: security without using different usernames

RE: security without using different usernames

From: Rudy Zung <rzung_at_printcafe.com>
Date: Tue, 15 Jul 2003 18:44:01 -0400
Message-Id: <25929.337905@fatcity.com>


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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>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? </FONT></DIV>   <DIV><FONT face=Arial size=2></FONT>&nbsp;</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>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>To stress, I cant change the application. 
  Different group with different skillsets.&nbsp;Any suggestions? Received on Tue Jul 15 2003 - 17:44:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US