Reply to SQR and OPS$ Accounts

[johng@RMF41.USACE.ARMY.MIL]

> I am having a problem with an item I found in the FAQ section of the
> SQR Users web site.
>
> Q: Are ORACLE OPS$ accounts supported in a connect string for SQR?
> A: Yes. After login as an OPS$ user, the SQR command is:
>         sqr report /@t:machine:database
>
> What follows are the results of several tests I ran to determine how
> SQR and the OPS$ accounts were set up on the system I am using.  Null
> inputs (i.e. tapping enter as a response) are annotated as <null
> input>.
>
> --------------------------------------------------------------------
>
> TEST 1:
>
> :/home> sqlplus /
>
> SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar  7 14:45:29 1996
>
> Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
>
> Connected to:
> Oracle7 Server Release 7.1.4.3.0 - Production Release
> With the distributed and parallel query options
> PL/SQL Release 2.1.4.3.0 - Production
>
*** above, you connected to a LOCAL data base as ops$, as expected

> SQL> exit
> Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release
> With the distributed and parallel query options
> PL/SQL Release 2.1.4.3.0 - Production
> :/home>
>
> ------------------------------------------------------------------------
>
> TEST 2:
>
> :/home> sqlplus /@t:corpdev1:new7
>
> SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar  7 14:46:47 1996
>
> Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
>
> ERROR: ORA-01004: default username feature not supported; logon denied
> ORA-01988: remote os logon is not allowed
>
*** above, you failed to connect to a REMOTE data base as ops$,
    I believe due to the fact that REMOTE_OS_AUTHENT (sp?) was not enabled
    in that data base's init.ora file

> Enter user-name: <null input>
>
> Connected to:
> Oracle7 Server Release 7.1.4.3.0 - Production Release
> With the distributed and parallel query options
> PL/SQL Release 2.1.4.3.0 - Production
>
*** above was connection to LOCAL data base again, same as in test 1 above,
    since you provided no '@t:corpdev1:new7' sqlnet connect string, just
    null input

> SQL> exit
> Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release
> With the distributed and parallel query options
> PL/SQL Release 2.1.4.3.0 - Production
> :/home>
>
> ------------------------------------------------------------------------
>
> TEST 3:
>
> :/home> sqlplus
>
> SQL*Plus: Release 3.1.3.5.1 - Production on Thu Mar  7 14:47:59 1996
>
> Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.
>
> Enter user-name: 12345
> Enter password: <entered password>
>
> Connected to:
> Oracle7 Server Release 7.1.4.3.0 - Production Release
> With the distributed and parallel query options
> PL/SQL Release 2.1.4.3.0 - Production
>
*** above, you connected to a LOCAL data base as 12345 user, as expected

> SQL> exit
> Disconnected from Oracle7 Server Release 7.1.4.3.0 - Production Release
> With the distributed and parallel query options
> PL/SQL Release 2.1.4.3.0 - Production
> :/home>
>
> ------------------------------------------------------------------------
>
> TEST 4:
>
> :/home> sqr dropall.sqr /
> SQR: Structured Query Report Writer V3.0.7
> Copyright (C) MITI, 1994, 1995.  All Worldwide Rights Reserved.
>
> (SQR 5528) ORACLE ORLON error -1004 in cursor 0:
>    ORA-01004: default username feature not supported; logon denied
> (SQR 4701) Can't logon to the database.
>
> SQR: Program Aborting.
> :/home>
>
*** Above should have been a connection to a LOCAL data base as ops$ user.
    What you see though is a problem we experienced when upgrading to
    Oracle7 v7.1.4 from Oracle v6.0.33, and using SQR v2.5.7 as was
    delivered on the SQR install tape.  For whatever reason, the sqr
    executable delivered required that REMOTE_OS_AUTHENT be enabled in
    the data base init.ora file, even for LOCAL ops$ connects.  We finallly
    solved this by relinking the sqr executable which picked up new libraries
    from the new Oracle version we had installed (our and Oracle's opinion),
    after which we were able to do local ops$ sqr sessions without
    REMOTE_OS_AUTHENT being enabled on out local data base, which could
    have been a real security problem!!!

> -----------------------------------------------------------------------
>
> TEST 5:
>
> :/home> sqr dropall.sqr /@t:corpdev1:new7
> SQR: Structured Query Report Writer V3.0.7
> Copyright (C) MITI, 1994, 1995.  All Worldwide Rights Reserved.
>
> (SQR 5528) ORACLE ORLON error -1004 in cursor 0:
>    ORA-01004: default username feature not supported; logon denied
> (SQR 4701) Can't logon to the database.
>
*** Above is the same failure to log on to a REMOTE data base that was
    exhibited in the first part of test 2 above, where REMOTE_OS_AUTHENT
    is not enabled in the remote data base.

> SQR: Program Aborting.
> :/home>
>
> ------------------------------------------------------------------------
>
> TEST 6:
>
> :/home> sqr dropall.sqr
> Enter Username: <null input>
> Enter Password: <null input>
>
> SQR: Structured Query Report Writer V3.0.7
> Copyright (C) MITI, 1994, 1995.  All Worldwide Rights Reserved.
>
> (SQR 5528) ORACLE ORLON error -1004 in cursor 0:
>    ORA-01004: default username feature not supported; logon denied
> (SQR 4701) Can't logon to the database.
>
*** above is same problem as in test 4, the bug in SQR requiring that
    REMOTE_OS_AUTHENT be enabled even for local ops$ logins.

> SQR: Program Aborting.
> :/home>
>
> -----------------------------------------------------------------------
>
> TEST 7:
>
> :/home> sqr dropall.sqr
> Enter Username: 12345
> Enter Password: <entered password>
>
> SQR: Structured Query Report Writer V3.0.7
> Copyright (C) MITI, 1994, 1995.  All Worldwide Rights Reserved.
>
*** above was not an ops$ login to a LOCAL data base, so every thing
    is cool... no OS authentication involved..

> SQR: End of Run.
> :/home>
>
> ---------------------------
>
> After trying these combinations of the suggested command line, I was
> still at a loss as to what the system was expecting.
>
> My question to the learned peoples who read this list is this.  What
> can be done to get the OPS$ accounts work with SQR?  Is it the SQR
> setup? Is it the Oracle database setup? Is it the UNIX setup? Or
> possibly a combination of all 3?
>
> Mara L. Wells
> Senior Consultant
> REALOGIC, Inc.

My suggestion, Mara, is to follow the instructions that came with your
SQR install kit, and relink the sqr executable, making sure that your
environment points to the correct Oracle home for the Oracle version
you are targeting.  This should correct SQR logins in tests 4 and 6.

If you really want to use ops$ logins to remote data bases (t:corpdev1:new7)
then on those data bases you would need to set REMOTE_OS_AUTHENT on in
the init.ora file.  (This was over a year ago, and I don't have my notes
handy, so the name may be slightly different.)  Be aware of the security
hole this opens, as a user anywhere on the network (internet???) running
sql net (asssuming they know your host name and data base name) can connect
to your data base if there is an ops$ account set up for that same user name.

Good luck, let me know if I can answer any questions.

------------------------------------------------------------------------
John Griffin                (205) 890-2401  |       A    TTTTTTT  SSSSS
Advanced Technology Systems (ATS), Inc.     |     A   A     T     S
4801 University Square, Suite 2             |     AaaaA     T     SSSSS
Huntsville, AL  35816-3431                  |     A   A     T         S
johng@rmf41.usace.army.mil                  |     A   A     T     SSSSS
------------------------------------------------------------------------