Solved

Trying to identify in the ON LOGON TRIGGER the name of the client application that has initiated the session

Posted on 2003-10-31
17
2,030 Views
Last Modified: 2007-12-19
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','SESSIONID');

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?
0
Comment
Question by:mkrivenko
  • 5
  • 5
  • 3
  • +3
17 Comments
 
LVL 7

Assisted Solution

by:Bigfam5
Bigfam5 earned 20 total points
Comment Utility
That because the trigger, if you will, does not have permission to select from V$Session view, even if you can issue (successfully) this select in SqlPlus

You need to grant select on v$session to USER

0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
try to log in as SYSDBA
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.

0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
Bigfam5: you are quicker this time..;-)

but I think you should assign the SELECT on SYS.V_$SESSION table instead of v$session view.
0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility

ask your DBA do this:
grant select on sys.V_$SESSION to public
0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
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.
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
Daniel, thanx for correction. the moment I submited, what a Bummer!!!
I am in big apple, btw!




0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
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#","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"
from v$session

does Oracle hide something background?

0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
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?
0
 
LVL 7

Expert Comment

by:Bigfam5
Comment Utility
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

0
 
LVL 2

Accepted Solution

by:
RRainville earned 480 total points
Comment Utility
Hi guys just to add in

I do not like the idea of giving SELECT ANY TABLE to PUBLIC
anybody can select anything ouch!!

I've done this kind of stuff before for an app

here's what I did

As sys
Create a view based on v_$session with only the needed columns say APP_PROGRAM_VW

then
grant SELECT TO PUBLIC ON SYS.APP_PROGRAM_VW

then
create public synonym APP_PROGRAM_VW FOR SYS.APP_PROGRAM_VW

and select from that view
no security breach!!



0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility
Sounds great!
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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.

0
 
LVL 2

Expert Comment

by:RRainville
Comment Utility
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
0
 
LVL 3

Expert Comment

by:smurff
Comment Utility
>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
0
 

Expert Comment

by:aitsgh
Comment Utility
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?
0
 
LVL 2

Expert Comment

by:RRainville
Comment Utility
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now