Successful login via SQLPlus produces a server error
g3000
package.
I dont have a when logon trigger either.
I do have a on db startup trigger ( which I dont think applies ).
Any ideas? I understand what the error is just dont know why. Im loggin
in as SYS ( DBA ).
SQL*Plus: Release 9.2.0.6.0 - Production on Mon Apr 25 13:20:57 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
DECLARE
*
ERROR at line 1:
ORA-01924: role 'SAMS_BASE' not granted or does not exist
ORA-06512: at "SYS.DBMS_SESSION", line 120
ORA-06512: at "SAMS.SAMS_USER_LIB", line 66
ORA-01924: role 'SAMS_SECURITY' not granted or does not exist
ORA-06512: at "SAMS.SAMS_USER_LIB", line 219
ORA-06512: at line 4
SQL>
Matthias Hoys
"g3000" <carlton...@yahoo.com> wrote in message
news:1114450167....@f14g2000cwb.googlegroups.com...
Did you try to install a patchset and did it fail (for example because
SYSTEM tablespace was full) ?
What's the result of : SELECT count(*) FROM DBA_OBJECTS WHERE STATUS =
'INVALID'; ?
Matthias
g3000
SQL> select object_name, object_type from dba_objects where
status='INVALID';
OBJECT_NAME
------------------------------------------------------------------------------
OBJECT_TYPE
------------------
MY_ALL_OBJECTS_AGGS
MATERIALIZED VIEW
SI_LIB_XML
PACKAGE BODY
SQL>
did not have a patchset fail
Sybrand Bakker
wrote:
The original error messages are quite clear.
You are calling the procedure sams.sam_user_lib.
The procedure tries to set roles by means of the dbms_session
package.
However the roles sam_user and sam_security don't exist.
You need to create them and possibly grant privileges to them.
The package sams is third party code, not Oracles
--
Sybrand Bakker, Senior Oracle DBA
DA Morgan
> When I logon to my database via SQLPlus I get an error about a PL/SQL
> package.
> I dont have a when logon trigger either.
Are you sure? The database seems to think otherwise.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
Holger Baer
Does this *only* happen when using SQL*PLUS, or does this also occur
when using something like TOAD, PL/SQL Developer, etc.?
If this is only happening with SQL*PLUS, then maybe you have something
in your login.sql/glogin.sql that calls the procedure SAMS.SAMS_USER_LIB.
HTH
Holger
g3000
this:
SQL> select trigger_name, owner from dba_triggers where
triggering_event='LOGON';
no rows selected
SQL> select trigger_name, owner from dba_triggers where
triggering_event='STARTUP';
no rows selected
SQL>
It is OBVIOUS that the user I am loggin on as (SYS as SYSDBA ) does NOT
have the roles sams_base and sams_security granted.
My point is WHAT code is executing ( without it being an on logon
trigger ) that causes this error just by logging in? Does it have to be
one of those types of triggers?
Does 9i perform some kind of implicit check of object privs upon simply
connecting?
The errror is in code that is in another schema.
Now when I grant sams_base and sams_security to sys with admin option I
get an error saying role does not exist when I log on.
The error is a user defined exception string which leads me to believe
the name of the role is misspelled in the code.
I just want to know how the code is executing.
g3000
I dont get this error via TOAD.
Frank van Bortel
> well I know that I dont have any ON STARTUP or ON LOGON triggers from
> this:
>
> SQL> select trigger_name, owner from dba_triggers where
> triggering_event='LOGON';
>
> no rows selected
>
> SQL> select trigger_name, owner from dba_triggers where
> triggering_event='STARTUP';
>
> no rows selected
>
> SQL>
>
> It is OBVIOUS that the user I am loggin on as (SYS as SYSDBA ) does NOT
> have the roles sams_base and sams_security granted.
>
iirc, logging on as sysdba, using the sys account, does *not*
fire on logon triggers - else it would be possible to lock
everybody out :)
--
Regards,
Frank van Bortel
DA Morgan
So do I.
Perhaps it is time to start looking at that wealth of objects
found with the following query:
SELECT object_name
FROM dba_objects
WHERE object_name LIKE 'V%SQL%';
Then please let us know what you found.
Rauf Sarwar
g3000 wrote:
> Also my login.sql script is not doing it I checked that out.
>
> I dont get this error via TOAD.
Have you also checked glogin.sql under ORACLE_HOME\sqlplus\admin?
I added this at the end of my glogin.sql
DECLARE
BEGIN
dbms_session.set_role('BOGUS_ROLE');
END;
/
and here is the error message from sqlplus logon,
DECLARE
*
ERROR at line 1:
ORA-01919: role 'BOGUS_ROLE' does not exist
ORA-06512: at "SYS.DBMS_SESSION", line 120
ORA-06512: at line 3
Regards
/Rauf
g3000
is a call to logon_proc. In the text for that proc ( which is wrapped )
I do see DBMS_SESSION with a colon behind it.
SQL> select trigger_body from dba_triggers where
trigger_name='SYS_LOGON';
TRIGGER_BODY
--------------------------------------------------------------------------------
logon_proc;
SQL> select trigger_body from dba_triggers where
trigger_name='SYS_LOGOFF';
TRIGGER_BODY
--------------------------------------------------------------------------------
logoff_proc(lt.getSid);
SQL> select text from user_source where name='LOGON_PROC' and
type='PROCEDURE';
TEXT
--------------------------------------------------------------------------------
procedure logon_proc wrapped
0
abcd
<snipped>
Makes me wonder if this is where it comes from.
DA Morgan here are the query results:
SQL> select object_name from dba_objects where object_name LIKE
'V%SQL%';
OBJECT_NAME
--------------------------------------------------------------------------------
V_$SQL
V_$SQLAREA
V_$SQLTEXT
V_$SQLTEXT_WITH_NEWLINES
V_$SQLXS
V_$SQL_BIND_DATA
V_$SQL_BIND_METADATA
V_$SQL_CURSOR
V_$SQL_PLAN
V_$SQL_PLAN_STATISTICS
V_$SQL_PLAN_STATISTICS_ALL
OBJECT_NAME
--------------------------------------------------------------------------------
V_$SQL_REDIRECTION
V_$SQL_SHARED_CURSOR
V_$SQL_SHARED_MEMORY
V_$SQL_WORKAREA
V_$SQL_WORKAREA_ACTIVE
V_$SQL_WORKAREA_HISTOGRAM
V$SQL
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQLXS
OBJECT_NAME
--------------------------------------------------------------------------------
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_REDIRECTION
V$SQL_SHARED_CURSOR
V$SQL_SHARED_MEMORY
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
OBJECT_NAME
--------------------------------------------------------------------------------
V$SQL_WORKAREA_HISTOGRAM
34 rows selected.
SQL>
DA Morgan
I knew what magic views were there. What you need to do is use
them to look inside and see what is executing. The SQL statement
was just a hint of where to look.
Holger Baer
> I found that there is one on longon trigger named ' SYS_LOGON ' there
> is a call to logon_proc. In the text for that proc ( which is wrapped )
> I do see DBMS_SESSION with a colon behind it.
>
> SQL> select trigger_body from dba_triggers where
> trigger_name='SYS_LOGON';
>
> TRIGGER_BODY
> --------------------------------------------------------------------------------
> logon_proc;
>
> SQL> select trigger_body from dba_triggers where
> trigger_name='SYS_LOGOFF';
>
> TRIGGER_BODY
> --------------------------------------------------------------------------------
> logoff_proc(lt.getSid);
>
> SQL> select text from user_source where name='LOGON_PROC' and
> type='PROCEDURE';
>
> TEXT
> --------------------------------------------------------------------------------
> procedure logon_proc wrapped
> 0
> abcd
> <snipped>
>
> Makes me wonder if this is where it comes from.
>
Then comment it out, for god's sake! Easiest thing to do I should think.
Cheers,
Holger
g3000
typo of the role name.
And commenting out does not tell me HOW and WHY its executing.
Holger Baer
Excuse me? You've got an trigger sys_logon executing logon_proc.
If you modify the sys_logon trigger to not execute logon_proc that
*will* tell you if that is the source of the error message when
using SQL*PLUS (if this is still your issue).
And since the code of the procedure is wrapped, how did you look
at the code anyway?
Finally, you say that this also happens when connecting with sysdba
privileges. So it *can't* be a logon trigger, since those wouldn't
execute for sys logons:
sys@DEMO10G>create or replace trigger sys_logon after logon
2 on database
3 begin
4 dbms_session.set_role('BOGUS_ROLE');
5* END;
sys@DEMO10G>/
Trigger created.
sys@DEMO10G>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning and Data Mining options
baer@casp003:~> sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Apr 28 08:15:51 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning and Data Mining options
sys@DEMO10G>exit
Ok, no trigger fired for sys.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning and Data Mining options
baer@casp003:~> sqlplus /
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Apr 28 08:15:55 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot SET ROLE in a trigger
ORA-06512: at "SYS.DBMS_SESSION", line 124
ORA-06512: at line 2
Oh dear, you can't even set a role in a trigger, so we can safely dismiss this track.
Which means, that we have to go back to my initial suggestion backed up by Rauf Sarwar
to check login.sql *and* glogin.sql which you deliberately ignored.
> DECLARE
> *
> ERROR at line 1:
> ORA-01924: role 'SAMS_BASE' not granted or does not exist
> ORA-06512: at "SYS.DBMS_SESSION", line 120
> ORA-06512: at "SAMS.SAMS_USER_LIB", line 66
> ORA-01924: role 'SAMS_SECURITY' not granted or does not exist
> ORA-06512: at "SAMS.SAMS_USER_LIB", line 219
> ORA-06512: at line 4
>
>
> SQL>
Look at the first line: DECLARE. This is an anonymous pl/sql block. This *has* to be executed
through some startup scripts, except if logon_proc does use native dynamic sql, but no, wait:
sys@DEMO10G>create or replace procedure logon_proc
2 is
3 begin
4 execute immediate 'declare
5 l_dummy int;
6 begin
7 l_dummy := 0;
8 dbms_session.set_role (''BOGUS'');
9 end;';
10 end;
11 /
Procedure created.
sys@DEMO10G>exec logon_proc;
BEGIN logon_proc; END;
*
ERROR at line 1:
ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at "SYS.DBMS_SESSION", line 124
ORA-06512: at line 5
ORA-06512: at "SYS.LOGON_PROC", line 4
ORA-06512: at line 1
So check your glogin.sql. Or better: try from a different client - do you still get this error?
Regards,
Holger
g3000
code. I said it was wrapped. When code is
wrapped you still can see SOME statements
I saw DBMS_SESSION and some other
package calls.
SQL> SELECT trigger_name, trigger_type from dba_triggers where
owner='SYS';
TRIGGER_NAME TRIGGER_TYPE
------------------------------ ----------------
AURORA$SERVER$SHUTDOWN BEFORE EVENT
AURORA$SERVER$STARTUP AFTER EVENT
AW_DROP_TRG AFTER EVENT
CDC_ALTER_CTABLE_BEFORE BEFORE EVENT
CDC_CREATE_CTABLE_AFTER AFTER EVENT
CDC_CREATE_CTABLE_BEFORE BEFORE EVENT
CDC_DROP_CTABLE_BEFORE BEFORE EVENT
NO_VM_CREATE BEFORE EVENT
NO_VM_DROP BEFORE EVENT
SYS_LOGOFF BEFORE EVENT
SYS_LOGON AFTER EVENT
2. I KNOW that no other ON LOGON trigger is
firing.
3. I removed my SQLPATH variable from my
environment and it is still happening. If I log
in as SYS or the owner of the code I get
C:\>sqlplus "sys/<snip>@samsdb9i as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Apr 28 12:20:35 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
PL/SQL procedure successfully completed.
I will continue to search for a glogin or login script that has that
pl/sql call in it.