2楼的兄弟 这句话应该放在整个SQL语句的什么位置啊 我刚才是这么放的,如下:create or replace trigger login_on_info after logon on database Begin grant select any dictionary to watcher; insert into login_log(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program) select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program from sys.v_$session where AUDSID = USERENV('SESSIONID'); END;编译还是报错了。Compilation errors for TRIGGER WATCHER.LOGIN_ON_INFOError: PLS-00103: 出现符号 "GRANT"在需要下列之一时: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe Line: 4 Text: grant select any dictionary to watcher;
--你直接sys用户登入然后执行那个语句就可以了。 --假设你建立触发器的用户是testgrant select any dictionary to test; grant select on v_$session to test; --我在test用户下测试了一下,没有报错。 [TEST@myorcl] SQL>CREATE TABLE LOGIN_LOG( 2 SESSION_ID VARCHAR2(20), 3 LOGIN_ON_TIME DATE, 4 LOGIN_OFF_TIME DATE, 5 USER_IN_DB VARCHAR2(20), 6 MACHINE VARCHAR2(20), 7 IP_ADDRESS VARCHAR2(20), 8 RUN_PROGRAM VARCHAR2(20) 9 );表已创建。[TEST@myorcl] SQL>CREATE OR REPLACE TRIGGER LOGIN_ON_INFO 2 AFTER LOGON ON DATABASE 3 BEGIN 4 INSERT INTO LOGIN_LOG 5 (SESSION_ID, 6 LOGIN_ON_TIME, 7 LOGIN_OFF_TIME, 8 USER_IN_DB, 9 MACHINE, 10 IP_ADDRESS, 11 RUN_PROGRAM) 12 SELECT AUDSID, 13 SYSDATE, 14 NULL, 15 SYS.LOGIN_USER, 16 MACHINE, 17 SYS_CONTEXT('USERENV', 'IP_ADDRESS'), 18 PROGRAM 19 FROM SYS.V_$SESSION 20 WHERE AUDSID = USERENV('SESSIONID'); 21 END; 22 /触发器已创建[TEST@myorcl] SQL>
--sys登录
grant select any dictionary to user_name;
after logon on database
Begin
grant select any dictionary to watcher;
insert into login_log(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
from sys.v_$session where AUDSID = USERENV('SESSIONID');
END;编译还是报错了。Compilation errors for TRIGGER WATCHER.LOGIN_ON_INFOError: PLS-00103: 出现符号 "GRANT"在需要下列之一时:
begin case declare exit
for goto if loop mod null pragma raise return select update
while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 4
Text: grant select any dictionary to watcher;
--你直接sys用户登入然后执行那个语句就可以了。
--假设你建立触发器的用户是testgrant select any dictionary to test;
grant select on v_$session to test;
--我在test用户下测试了一下,没有报错。
[TEST@myorcl] SQL>CREATE TABLE LOGIN_LOG(
2 SESSION_ID VARCHAR2(20),
3 LOGIN_ON_TIME DATE,
4 LOGIN_OFF_TIME DATE,
5 USER_IN_DB VARCHAR2(20),
6 MACHINE VARCHAR2(20),
7 IP_ADDRESS VARCHAR2(20),
8 RUN_PROGRAM VARCHAR2(20)
9 );表已创建。[TEST@myorcl] SQL>CREATE OR REPLACE TRIGGER LOGIN_ON_INFO
2 AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT INTO LOGIN_LOG
5 (SESSION_ID,
6 LOGIN_ON_TIME,
7 LOGIN_OFF_TIME,
8 USER_IN_DB,
9 MACHINE,
10 IP_ADDRESS,
11 RUN_PROGRAM)
12 SELECT AUDSID,
13 SYSDATE,
14 NULL,
15 SYS.LOGIN_USER,
16 MACHINE,
17 SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
18 PROGRAM
19 FROM SYS.V_$SESSION
20 WHERE AUDSID = USERENV('SESSIONID');
21 END;
22 /触发器已创建[TEST@myorcl] SQL>
楼主对这个语句的理解有偏差,这个语句的做用是授权。你的触发器中访问了触发器所在的用户没有权限的表,所以需要使用授权语句将该表的权限授给这个用户。授权语句是不需要写在触发器中的,只要用pl/sql执行后就可以了。