Re: Oracle 9 NT Authentication "conn / as sysdba" - Role issue ?

On 29 May 2006 10:36:19 -0700, "Davep"
<davepylatuk_at_centurysystems.net> wrote:

>Hello all.
>
>I have succeeded in getting Oracle NT authentication working but am
>having a related permission problem.
>
>I create Oracle users in this format "OPS$JOHN" for example, as long as
>I have a domain user named 'JOHN' in the domain the server is
>running.... JOHN can connect to Oracle in SQL PLUS by typing:
>
>>conn / as sysdba;
>
>The problem is that I have database roles to enforce DB security. User
>JOHN is supposed to have only read only for a few tables. I have
>previously enforced this by having a READ_ONLY role assigned to JOHN.
>This has worked perfectly until now....
>
>Now, once JOHN logs in with NT authentication as indicated above he has
>read/write on all tables ? How do I have an NT login adhere to the DB
>roles assigned to a user ?
>
>Any help would be appreciated.

There are two forms of O/S authentication, and you are mixing them up.

Connect / looks at the presence of an OPS$<user> account. Connect / as sysdba looks whether the user account is in the ora_<sid>_dba group, disregarding any other groups. And yes, when you 'connect / as sysdba', you *are* SYSDBA, once connected, so you are more powerful than any user with the DBA role.

--
Sybrand Bakker, Senior Oracle DBA