Re: External Authentication please Oracle 8i

Tom wrote:
> I am a SQL Server user, developer since 6. Now I have a project on
> Oracle 8i.
>
> Here is the background. In order to aid in testing and debugging, I
> have set up an Oracle instance on my internal company network (Windows
> 2000 Server DC) I set the developer version of 8i (which means no
> patches available to me) up on a Windows 2000 professional box. After
> 2 failed attempts (I was thinking too much) I followed a few posts
> here and everything works. I should clarify SQL+ works. I didnt set
> up anything else after the first 2 attempts. But I created my
> instance, called utc and added the tables I need for testing. So far
> so good.
>
> In order to input the data I created the tables on SQL Server 2000 and
> used DTS to transfer them to Oracle 8i. Again all worked and I can
> access from my app using utc as instance and system ans user and
> manager as password. I can hit it from remote box on network where I
> develope as long as I use the utc instance with system/manager
> username.
>
> Here is my delema. My client uses external authentication. So for
> userid and password I need to send nothing and nothing. I have coded
> this and he has tested on his site and it works. But in order for me
> to test on my network I need to use the username and password which i
> do by using #if_debug define. I would really like to use the same
> method as him. Could someone point me to a link or explain in details
> for the Microsoft impaired how to set up external authentication?
> I've purchased 2 books and neither explain how to set up external
> authentication. They only mention that its not recomended. I agree
> and understand completey. But in this case I will only be using for
> duration of project and want to use it.
>
> Im hoping for something like
> 1. add this to this path/file on Oracle server.
> 2. add this to this path/file on oracle client.
>
> I know in reading the posts here that I am realy asking for
> harassment, but please restrain yourself.

It's tempting, but I'll keep my thoughts for myself ;-)

First of all: Don't use system for anything else but administration! (And even there you shouldn't use it always but that's another story).

Basically what you do is:

1.) Create an user that hold's the schema (tables, views, procedures, packages) of your application.

2.) Create a role ( or more if necessary ) that has all necessary rights for *USING* the application. In Oracle context that means typically to grant execute on the stored procedures / packages your application (hopefully) consists of.

create role myapp_basic_role ;

grant create session, alter session to myapp_basic_role; grant execute on schema_user.package_name to myapp_basic_role; [...]

3.) For each user that want's to use your application create a user with external authentification:

create user foo identified externally
default tablespace <something else but system> temporary tablespace temp;

grant myapp_basic_role to foo;

Next you either create synonyms for the packages, or as first step you let your application issue

        alter session set current_schema=myapp_schema;

That should get you started, at least with this information you should have enough keywords to start searching the documentation which is freely available at http://tahiti.oracle.com

HTH Holger Received on Thu Dec 11 2003 - 07:54:54 CST