mkrivenko
asked on
Trying to identify in the ON LOGON TRIGGER the name of the client application that has initiated the session
I'm trying to create an ON LOGON trigger where I'm trying to identify name of the client application that has initiated the connection
I know this information is in the v$session and this SQL works when running from the account that has DBA privs:
SELECT UPPER(PROGRAM) FROM V$SESSION WHERE AUDSID = SYS_CONTEXT('USERENV','SES
However it does not want to compile in the trigger I'm getting the following error:
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
I tried to put this SQL into a string and do and Execute Immediate, but then it gives this error when the trigger fires:
ORA-00942: table or view does not exist
What would be the way identify the client application name for that session in the account that does not have DBA privilages?
I know this information is in the v$session and this SQL works when running from the account that has DBA privs:
SELECT UPPER(PROGRAM) FROM V$SESSION WHERE AUDSID = SYS_CONTEXT('USERENV','SES
However it does not want to compile in the trigger I'm getting the following error:
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
I tried to put this SQL into a string and do and Execute Immediate, but then it gives this error when the trigger fires:
ORA-00942: table or view does not exist
What would be the way identify the client application name for that session in the account that does not have DBA privilages?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Bigfam5: you are quicker this time..;-)
but I think you should assign the SELECT on SYS.V_$SESSION table instead of v$session view.
but I think you should assign the SELECT on SYS.V_$SESSION table instead of v$session view.
ask your DBA do this:
grant select on sys.V_$SESSION to public
Bigfam5,seazodiac
where are you guys?
a little correction.
v$session is not a view. it's a synonym equal to sys.V_$SESSION.
sys.V_$SESSION is a view.
where are you guys?
a little correction.
v$session is not a view. it's a synonym equal to sys.V_$SESSION.
sys.V_$SESSION is a view.
Daniel, thanx for correction. the moment I submited, what a Bummer!!!
I am in big apple, btw!
I am in big apple, btw!
Bigfam5,seazodiac:
is this weird?
sys.V_$SESSION is a view.
V$SESSION is a synonym equal to sys.V_$SESSION.
but please look at the definition for sys.V_$SESSION view:
CREATE OR REPLACE VIEW v_$session (
saddr,
sid,
serial#,
audsid,
paddr,
user#,
username,
command,
ownerid,
taddr,
lockwait,
status,
server,
schema#,
schemaname,
osuser,
process,
machine,
terminal,
program,
type,
sql_address,
sql_hash_value,
prev_sql_addr,
prev_hash_value,
module,
module_hash,
action,
action_hash,
client_info,
fixed_table_sequence,
row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#,
logon_time,
last_call_et,
pdml_enabled,
failover_type,
failover_method,
failed_over,
resource_consumer_group,
pdml_status,
pddl_status,
pq_status )
AS
select "SADDR","SID","SERIAL#","A
from v$session
does Oracle hide something background?
is this weird?
sys.V_$SESSION is a view.
V$SESSION is a synonym equal to sys.V_$SESSION.
but please look at the definition for sys.V_$SESSION view:
CREATE OR REPLACE VIEW v_$session (
saddr,
sid,
serial#,
audsid,
paddr,
user#,
username,
command,
ownerid,
taddr,
lockwait,
status,
server,
schema#,
schemaname,
osuser,
process,
machine,
terminal,
program,
type,
sql_address,
sql_hash_value,
prev_sql_addr,
prev_hash_value,
module,
module_hash,
action,
action_hash,
client_info,
fixed_table_sequence,
row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#,
logon_time,
last_call_et,
pdml_enabled,
failover_type,
failover_method,
failed_over,
resource_consumer_group,
pdml_status,
pddl_status,
pq_status )
AS
select "SADDR","SID","SERIAL#","A
from v$session
does Oracle hide something background?
hehe, Daniel, now we are friggin's hitting the same path.
just out of curiosity, I pulled the same stuff from dba_views. and I meant to ask you guys too.
I am back to my Oracle , thinking more....
But anyhow, for mkrivenko's solution, I think Grant SELECT ANY TABLE TO the account or public is the SAFEST to warrant the error will go away. do you guys agree?
just out of curiosity, I pulled the same stuff from dba_views. and I meant to ask you guys too.
I am back to my Oracle , thinking more....
But anyhow, for mkrivenko's solution, I think Grant SELECT ANY TABLE TO the account or public is the SAFEST to warrant the error will go away. do you guys agree?
if only to do
Grant SELECT ANY TABLE TO the account.
there is still a problem.
because the trigger is using for the whole database. you do not know which user will log in.
so I think
Grant SELECT ANY TABLE TO PUBLIC is better than Grant SELECT ANY TABLE TO user.
how's you guys think of it?
Grant SELECT ANY TABLE TO the account.
there is still a problem.
because the trigger is using for the whole database. you do not know which user will log in.
so I think
Grant SELECT ANY TABLE TO PUBLIC is better than Grant SELECT ANY TABLE TO user.
how's you guys think of it?
Yes, oracle hides the real definition of the V$ or V_$ views,
Unfortunately, since I moved from one company to the next I lost the query needed to 'view' the views.
I'll keep looking though.
Oh and I'm in DC
Unfortunately, since I moved from one company to the next I lost the query needed to 'view' the views.
I'll keep looking though.
Oh and I'm in DC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sounds great!
I think RRainville has digressed quite a bit.
Remember what we are discussing here is not how to make the database more secure, but to make the asker's problem go away.
Even for the argument RRainville has just put up, I happened to disagree a lot.
First of all v_$session is already a data dictionary view. Remember when we assign GRANT SELECT ANY TABLE TO PUBLIC , in fact, oracle does exactly the
same as we assign the select from the regular view to the user, because after all, users can only access data dictionary view tables for most of times (there are some exceptions of course)
I don't see any security breach at all when assigning the "SELECT ANY TABLE" to public.
Instead, this will save a DBA from a lot of hassle in the future.
Remember what we are discussing here is not how to make the database more secure, but to make the asker's problem go away.
Even for the argument RRainville has just put up, I happened to disagree a lot.
First of all v_$session is already a data dictionary view. Remember when we assign GRANT SELECT ANY TABLE TO PUBLIC , in fact, oracle does exactly the
same as we assign the select from the regular view to the user, because after all, users can only access data dictionary view tables for most of times (there are some exceptions of course)
I don't see any security breach at all when assigning the "SELECT ANY TABLE" to public.
Instead, this will save a DBA from a lot of hassle in the future.
seazodiac
sorry if you disagree with my point of view
but in the databases that I manage
there is no way I'm going to grant SELECT ANY TABLE to Public
that means that anybody can view anybody else data in the database
Sorry but just as an example simple employees should not be able to see Financial data managed by the accouting dept
This (SELECT ANY TABLE) could be done if the database is only for one group of people and all have the same access rights
then maybe I'd grant it
anyway this is getting off subject
both methods will do the job in the end
cheers
sorry if you disagree with my point of view
but in the databases that I manage
there is no way I'm going to grant SELECT ANY TABLE to Public
that means that anybody can view anybody else data in the database
Sorry but just as an example simple employees should not be able to see Financial data managed by the accouting dept
This (SELECT ANY TABLE) could be done if the database is only for one group of people and all have the same access rights
then maybe I'd grant it
anyway this is getting off subject
both methods will do the job in the end
cheers
>there is no way I'm going to grant SELECT ANY TABLE to Public
RRainville is 100% correct. Who and why would you give these permissions to everybody. The rule of thumb (for me anyway) is to give anough permissions to the users just to do thier job and no more!!! Remember, if the db breaks its up to you to fix it no one else!!
Regards
Smurff
RRainville is 100% correct. Who and why would you give these permissions to everybody. The rule of thumb (for me anyway) is to give anough permissions to the users just to do thier job and no more!!! Remember, if the db breaks its up to you to fix it no one else!!
Regards
Smurff
I have a similar problem with v$session in a stored procedure.
The problem is, I already have the 'GRANT SELECT ANY TABLE TO xxxx WITH ADMIN OPTION;' for both the role AND the user.
I still get the ORA-00942 when compiling.
I tried setting the role to 'none' in SQLPlus, and sure enough, I can't select from v$session.
But as stated, I have the "SELECT ANY" privilege for both the user and the role, so why does this happen?
This stored procedure worked fine on Oracle8i 8.1.5 and 8.1.6, but not on Oracle9j 9.2.0.1.
What has changed?
The problem is, I already have the 'GRANT SELECT ANY TABLE TO xxxx WITH ADMIN OPTION;' for both the role AND the user.
I still get the ORA-00942 when compiling.
I tried setting the role to 'none' in SQLPlus, and sure enough, I can't select from v$session.
But as stated, I have the "SELECT ANY" privilege for both the user and the role, so why does this happen?
This stored procedure worked fine on Oracle8i 8.1.5 and 8.1.6, but not on Oracle9j 9.2.0.1.
What has changed?
A new role was added in 9i
you need SELECT_CATALOG_ROLE
catalog(sys) tables are not accessible with the select any table privilege anymore
you need SELECT_CATALOG_ROLE
catalog(sys) tables are not accessible with the select any table privilege anymore
and grant the user account with "SELECT ANY TABLE" system privilege.
this is probably due to the fact that system privileges that are assigned through a role are not visible in PL/SQL block such as stored procedure or triggers. ie. ROLES are disabled in the stored procedures and triggers.