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: Ryan <rgaffuri_at_cox.net>
Date: Tue, 15 Jul 2003 18:46:38 -0400
Message-Id: <25929.337907@fatcity.com> 





This is a multi-part message in MIME format.



------=_NextPart_000_10E8_01C34B01.6C622E10
Content-Type: text/plain;


        charset="iso-8859-1"


Content-Transfer-Encoding: quoted-printable



RE: upgrade to AIX 5roles wont work. The tables in the different schemas =
all have the same names. The application is not coded to =
'schemaA.table'. Its just set to the table.=20



roles plus setting the schema is possible to be 'safe' with security. I =
like that idea.=20





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



------=_NextPart_000_10E8_01C34B01.6C622E10
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.2600.0" name=3DGENERATOR>

<STYLE></STYLE>

</HEAD>

<BODY bgColor=3D#ffffff>

<DIV><FONT face=3DArial size=3D2>roles wont work. The tables in the =

different=20


schemas all have the same names. The application is not coded to=20
'schemaA.table'. Its just set to the table. </FONT></DIV>


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

<DIV><FONT face=3DArial size=3D2>roles plus setting the schema is =

possible to be=20


'safe' with security. I like that idea. </FONT></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=3Doramagic_at_hotmail.com =


href=3D"mailto:oramagic_at_hotmail.com">AK</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 =
7:24=20


PM</DIV>


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


  different usernames</DIV>


  <DIV><BR></DIV>


  <DIV><FONT face=3DArial size=3D2>you can create multiple roles also . =
So if you=20


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


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


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


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


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


    have to live with it. This is not a high enough transaction database =
to=20


    warrant seperate 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=20


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


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


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


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


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


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


    I 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 =
Received on Tue Jul 15 2003 - 17:46:38 CDT












Original text of this message













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