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','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?
mkrivenkoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bigfam5Commented:
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
seazodiacCommented:
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
seazodiacCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DanielztCommented:

ask your DBA do this:
grant select on sys.V_$SESSION to public
0
DanielztCommented:
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
seazodiacCommented:
Daniel, thanx for correction. the moment I submited, what a Bummer!!!
I am in big apple, btw!




0
DanielztCommented:
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
seazodiacCommented:
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
DanielztCommented:
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
Bigfam5Commented:
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
RRainvilleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanielztCommented:
Sounds great!
0
seazodiacCommented:
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
RRainvilleCommented:
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
smurffCommented:
>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
aitsghCommented:
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
RRainvilleCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.