Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27

    Unanswered: RLS + Logon Trigger = I can't even log in as SYS anymore (Oracle 9iR2, Win)

    I'm not even sure where to start...RLS/VPD is one of the areas of Oracle that I haven't had much exposure to, and I was in the process of modifying an existing schema to support an additional application. Since the same schema is supporting two apps, RLS was implemented to keep the data from the shared tables out of the wrong users' experiences.

    No big deal...straight forward stuff.

    Everything worked fine until I applied a policy to a table that is checked at login.

    I don't know how much it will benefit at this point to get into a ton of detail because the main issue right now is that I cannot log into the DB. I tried as SYS and it hangs. Tried as a user that is not supposed to be involved with the RLS and it hangs.

    I logged into the server directly and tried:
    SQLPLUS " / @sttdev as sysdba"

    and it gave me an insufficient privs error, switched the user to the ORA_DBA group and it then hung like every other time I tried on any other machine.

    This isn't good...

    If someone could please help I would be so thankful...here is what i have from the alert log and corresponding trace file:

    Mon May 01 15:02:37 2006
    Errors in file e:\oracle\admin\sttdev\udump\sttdev_ora_2280.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-03113: end-of-file on communication channel
    ORA-06512: at line 15

    Dump file e:\oracle\admin\sttdev\udump\sttdev_ora_2280.trc
    Mon May 01 14:57:20 2006
    ORACLE V9.2.0.5.0 - Production vsnsta=0
    vsnsql=12 vsnxtr=3
    Windows 2000 Version 5.0 Service Pack 4, CPU type 586
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    Windows 2000 Version 5.0 Service Pack 4, CPU type 586
    Instance name: sttdev

    Redo thread mounted by this instance: 1

    Oracle process number: 14

    Windows thread id: 2280, image: ORACLE.EXE


    *** SESSION ID9.2086) 2006-05-01 14:57:20.245
    *** 2006-05-01 14:57:20.245
    ----------------------------------------------------------
    Policy function execution error:
    Logon user : xxxUSER1
    Table/View : BASINVADM.BASINV_USER
    Policy name : SELECT_BY_APP_TYPE
    Policy function: BASINVADM.BASSEC.GETUSERSELPREDICATE
    ORA-03113: end-of-file on communication channel
    ORA-06512: at "BASINVADM.BASSEC", line 158
    ORA-06512: at line 1
    *** 2006-05-01 14:57:20.245
    ----------------------------------------------------------
    Policy function execution error:
    Logon user : xxxUSER1
    Table/View : BASINVADM.SITE_TYPE_LIB
    Policy name : SELECT_BY_APP_TYPE
    Policy function: BASINVADM.BASSEC.GETUSERSELPREDICATE
    ORA-28112: failed to execute policy function
    ORA-06512: at "BASINVADM.BASSEC", line 158
    ORA-06512: at line 1
    *** 2006-05-01 14:57:20.245
    ----------------------------------------------------------
    Policy function execution error:
    Logon user : xxxUSER1
    Table/View : BASINVADM.BASINV_USER
    Policy name : SELECT_BY_APP_TYPE
    Policy function: BASINVADM.BASSEC.GETUSERSELPREDICATE
    ORA-28112: failed to execute policy function
    ORA-06512: at "BASINVADM.BASCONST", line 23
    ORA-06512: at "BASINVADM.BASSEC", line 194
    ORA-06512: at "BASINVADM.BASSEC", line 217
    ORA-06512: at "BASINVADM.BASSEC", line 244
    ORA-06512: at line 1
    Dump file e:\oracle\admin\sttdev\udump\sttdev_ora_2280.trc
    Mon May 01 15:02:37 2006
    ORACLE V9.2.0.5.0 - Production vsnsta=0
    vsnsql=12 vsnxtr=3
    Windows 2000 Version 5.0 Service Pack 4, CPU type 586
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    Windows 2000 Version 5.0 Service Pack 4, CPU type 586
    Instance name: sttdev

    Redo thread mounted by this instance: 1

    Oracle process number: 14

    Windows thread id: 2280, image: ORACLE.EXE


    *** 2006-05-01 15:02:37.522
    *** SESSION ID9.2088) 2006-05-01 15:02:37.522
    Error in executing triggers on connect internal
    *** 2006-05-01 15:02:37.522
    ksedmp: internal or fatal error
    ORA-00604: error occurred at recursive SQL level 1
    ORA-03113: end-of-file on communication channel
    ORA-06512: at line 15

  2. #2
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    ok, so i found this:
    http://asktom.oracle.com/~tkyte/article2/28112.html

    looks to me like it's hanging b/c of a "NO DATA FOUND" error.

    shoddy coding on my part...blame accepted

    now how can i log in to right this wrong? Thanks in advance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •