 SYS-GRANT on V_$SESSION impossible [message #281724] |
Mon, 19 November 2007 02:05  |
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 #281987 is a reply to message #281726] |
Tue, 20 November 2007 04:59  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
