Re: RE: security without using different usernames

i know you can get the IP in a client-server mode and i think its with sys_context. Ill have to look it up. in web mode you have to use a client side script. I know there is a javabean you can write to do it(its actually in the forms 9i demo). dont know how to do it in .net(which is what we are using), but Im sure its doable. .net is just a clone of java anyway with a different framework.

the IP sounds more scalable in case we go to the web in the future. Ill have to check with the client and see how they purchase IP addressse and if any of the users are logging in from home etc... when LANs are setup businesses typically buy IP addresses in batches. So you get something like

122.122.122.xxx for your building.

what you have to go through to overcome a poor design :(
>
> From: "Mark Brooks" <mark_at_foxhillgroup.com>
> Date: 2003/07/15 Tue PM 11:59:30 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: security without using different usernames
>
> Some time in the past year or so I was reading an article about a similar
> problem where they needed to change behavior based on where the connection
> originated. I remember something about using IP addresses to drive the
> different behaviors. Sorry I can't remember more. It didn't apply to
> anything I was doing at the time so I didn't really get into the details.
> Might be something to consider.
>
> -----Original Message-----
> Mark Richard
> Sent: Tuesday, July 15, 2003 6:29 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> I think the idea was always "roles plus setting current schema" - not just
> roles on their own. Heck, I'd be so paranoid that I'd make the default
> schema empty rather than belonging to a specific site so that if the
> executable isn't renamed then it connects to an empty database - it will
> make it much easier to track the chance.
>
> I was considering suggesting looking at the IP address to try and determine
> originating site but it carries too much risk - dynamic IP's, changing
> IP's, etc. It seems like renaming the executable might be the safest
> approach - as long as you make the default schema redundant.
>
> I guess you might have some impact on your ability to tune and debug the
> application and monitor usage when everyone is connecting as the same user
> but that will be something you'll have to live with. I guess you could
> shoe-horn some other parameters in with the "set schema" command if
> different sites have different performance demands (sort_area_size, etc) -
> just create a config table in the default schema and write a fancy logon
> trigger.
>
>
>
>
> "Ryan"
> <rgaffuri_at_cox.net To: Multiple recipients
> of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > cc:
> Sent by: Subject: Re: security
> without using different usernames
> ml-errors_at_fatcity
> .com
>
>
> 16/07/2003 09:44
> Please respond to
> ORACLE-L
>
>
>
>
>
>
> roles 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.
>
> roles plus setting the schema is possible to be 'safe' with security. I
> like that idea.
> ----- Original Message -----
> From: AK
> To: Multiple recipients of list ORACLE-L
> 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 -----
> From: Ryan
> To: Multiple recipients of list ORACLE-L
> 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.
>
> 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?
>
>
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> >>>>
> Privileged/Confidential information may be contained in this message.
> If you are not the addressee indicated in this message
> (or responsible for delivery of the message to such person),
> you may not copy or deliver this message to anyone.
> In such case, you should destroy this message and kindly notify the sender
> by reply e-mail or by telephone on (61 3) 9612-6999.
> Please advise immediately if you or your employer does not consent to
> Internet e-mail for messages of this kind.
> Opinions, conclusions and other information in this message
> that do not relate to the official business of
> Transurban City Link Ltd
> shall be understood as neither given nor endorsed by it.
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> >>>>
>
>
>
>
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Privileged/Confidential information may be contained in this message.
> If you are not the addressee indicated in this message (or responsible for
> delivery of the message to such person), you may not copy or deliver this
> message to anyone.
> In such a case, you should destroy this message and kindly notify the sender
> by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
> Please advise immediately if you or your employer does not consent to
> Internet e-mail for messages of this kind.
> Opinions, conclusions and other information in this message that do not
> relate to the official business of Transurban Infrastructure Developments
> Limited and CityLink Melbourne Limited shall be understood as neither given
> nor endorsed by them.
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mark Richard
> INET: mrichard_at_transurban.com.au
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mark Brooks
> INET: mark_at_foxhillgroup.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Wed Jul 16 2003 - 06:06:17 CDT