Home » RDBMS Server » Server Administration » SYS-GRANT on V_$SESSION impossible (Oracle9i)
icon10.gif  SYS-GRANT on V_$SESSION impossible [message #281724] Mon, 19 November 2007 02:05 Go to next message
NanteJena
Messages: 11
Registered: November 2007
Location: Jena
Junior Member
Hi,

I'm really helpless!

Recently I tried to execute a GRANT on V_$SESSION (as SYS!) and got the following errors:

GRANT SELECT ON v_$session TO xy;
grant select on v_$session to xy
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 27, column 12:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 25, column 5:
PL/SQL: SQL Statement ignored

The View exists, SYS can select from it.
The errors survive a CREATE OR REPLACE VIEW v_$session AS SELECT * FROM v$session; too!
GRANTs on other V_$-View (eg. V_$TRANSACTION) are possible.
A DDL-Event-Trigger that couldt prevent the GRANT I can't find.

What's happened?
I'd be much obliged if someone had any idea!
Re: SYS-GRANT on V_$SESSION impossible [message #281726 is a reply to message #281724] Mon, 19 November 2007 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have a DDL trigger. Search it.

A lot of topics on this: http://www.orafaq.com/forum/?SQ=87de0b248efa23538d7b8b384918972e&t=search&srch=error+occurred+at+recursive+SQL+level+&btn _submit=Search&field=all&forum_limiter=&search_logic=AND&sort_order=DESC&author=

Regards
Michel
Re: SYS-GRANT on V_$SESSION impossible [message #281987 is a reply to message #281726] Tue, 20 November 2007 04:59 Go to previous message
NanteJena
Messages: 11
Registered: November 2007
Location: Jena
Junior Member
You are right! I found this DDL trigger from SYSTEM:

CREATE OR REPLACE TRIGGER system.trigger_schemax_log
AFTER DDL ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
-- ...
BEGIN
IF ORA_DICT_OBJ_OWNER='SCHEMAX'
AND (ORA_SYSEVENT<>'GRANT' AND ORA_SYSEVENT<>'REVOKE'
AND ORA_DICT_OBJ_NAME NOT LIKE 'xxx_%' AND ORA_LOGIN_USER<>'yyy')
THEN
SELECT osuser,terminal,program
INTO v_osuser,v_client,v_prog
FROM v$session
WHERE audsid=sys_context('USERENV','SESSIONID');
INSERT INTO system.log_tabelle
(event,...)
VALUES
(ORA_SYSEVENT,...);
COMMIT;
IF ORA_SYSEVENT IN('ALTER','DROP','TRUNCATE')
THEN
-- (mail)
END IF;
END IF;
END;
/

After I disabled it, SYS was able to grant the SELECT!
Thanks.
Previous Topic: Archive log summary
Next Topic: Database is not mounted
Goto Forum:
  


Current Time: Wed Dec 07 12:38:33 CST 2016

Total time taken to generate the page: 0.08875 seconds