Message-Id: <25929.337907@fatcity.com> From: "Ryan" Date: Tue, 15 Jul 2003 18:46:38 -0400 Subject: Re: security without using different usernames 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 ----- Original Message -----=20 From: AK=20 To: Multiple recipients of list ORACLE-L=20 Sent: Tuesday, July 15, 2003 7:24 PM Subject: Re: security without using different usernames 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 ----- Original Message -----=20 From: Ryan=20 To: Multiple recipients of list ORACLE-L=20 Sent: Tuesday, July 15, 2003 3:29 PM 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.=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 RE: upgrade to AIX 5
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.
 
roles plus setting the schema is = possible to be=20 'safe' with security. I like that idea.
----- Original Message -----
From:=20 AK
To: Multiple recipients of list = ORACLE-L=20
Sent: Tuesday, July 15, 2003 = 7:24=20 PM
Subject: Re: security without = using=20 different usernames

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  . Enable proper role at the time of = startup=20 ( embadded in client code ).
 
-ak
 
 
 
 
 
 
 
----- Original Message -----
From:=20 Ryan
Sent: Tuesday, July 15, 2003 = 3:29=20 PM
Subject: security without = using=20 different usernames

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.
 
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.
 
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.
 
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.
 
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?
 
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?
 
To stress, I cant change the =