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: AK <oramagic_at_hotmail.com>
Date: Tue, 15 Jul 2003 15:25:08 -0700
Message-Id: <25929.337901@fatcity.com> 





This is a multi-part message in MIME format.



------=_NextPart_000_0498_01C34AE5.4647E970
Content-Type: text/plain;


        charset="iso-8859-1"


Content-Transfer-Encoding: quoted-printable



RE: upgrade to AIX 5you can create multiple roles also . So if you have =
schema a,b,c and they use Z as userid to login then create role_a, =
role_b, role_c where role_a has permissions for object in schema a and =
role_b has permissions for schema b  . Enable proper role at the time of =
startup ( embadded in client code ).



-ak












  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.=20



  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.=20



  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.=20



  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?=20



  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_000_0498_01C34AE5.4647E970
Content-Type: text/html;


        charset="iso-8859-1"


Content-Transfer-Encoding: quoted-printable


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: upgrade to AIX 5</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =


charset=3Diso-8859-1">

<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>you can create multiple roles also . So =


if you have=20


schema a,b,c and they use Z as userid to login then create role_a, =
role_b,=20


role_c where role_a has permissions for object in schema a and role_b =
has=20


permissions for schema b&nbsp; . Enable proper role at the time of =
startup (=20

embadded in client code ).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>-ak</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>


<BLOCKQUOTE dir=3Dltr=20


style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">


  <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>

  <DIV=20



  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20


  <A title=3Drgaffuri_at_cox.net href=3D"mailto:rgaffuri_at_cox.net">Ryan</A> =

</DIV>


  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =

title=3DORACLE-L_at_fatcity.com=20


  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list =
ORACLE-L</A>=20


  </DIV>

  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Tuesday, July 15, 2003 =

3:29=20


PM</DIV>


  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> security without using =

different=20


  usernames</DIV>


  <DIV><BR></DIV>

  <DIV><FONT face=3DArial size=3D2>I know this is terrible design, but =

the GUI was=20


  created by a software engineering group that is seperate from the =
database=20


  group. Its not scalable. So Im trying to come up with a more scalable =
method.=20


  I have no power to change their gui. It rides on the database. I have =
to live=20


  with it. This is not a high enough transaction database to warrant =
seperate=20


  instances. </FONT></DIV>


  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>

  <DIV><FONT face=3DArial size=3D2>We have a variety of customers. Each =

of them has=20


  their own versions of data. However, the schema is exactly the same. =
These=20


  tables can get huge, so we dont want to throw them all into the same=20
  schema.</FONT></DIV>


  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>

  <DIV><FONT face=3DArial size=3D2>Right now, due to the fact that the =

GUI has a=20


  series of logins that are the same across clients, each client has its =
own=20


  instance. This isnt very scalable as we get more business. We have to =
create=20


  another instance and ingest data to it. </FONT></DIV>


  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>

  <DIV><FONT face=3DArial size=3D2>Id like to find a way to get all the =

clients in=20


  the same instance with just different schemas and tablespaces. One =
thing I may=20


  have control over would be to slightly rename the executable. If you =
check=20


  v$session, in a client-server application the name of the product =
connecting=20


  to the database is recording. I can handle security based off of that. =



  </FONT></DIV>

  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>

  <DIV><FONT face=3DArial size=3D2>My question is what would be the best =

way? Cant=20


  do synonyms for this since its the same login. I think I saw somewhere =
that=20


  there is a session based 'set' command where you can say use this =
schema. I=20


  think it was on asktom and in reference to a question about public =
synonyms. I=20


  cant find it. Anyone know it? </FONT></DIV>


  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>

  <DIV><FONT face=3DArial size=3D2>Also is it viable to base a context =

off of what=20


  is in v$sesion with a logon trigger? How would I 'redirect' all =
queries to a=20


  specific schema?</FONT></DIV>


  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>

  <DIV><FONT face=3DArial size=3D2>To stress, I cant change the =

application.=20
Received on Tue Jul 15 2003 - 17:25:08 CDT












Original text of this message













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