Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
2,100 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +3
17 Comments
 
LVL 7

Assisted Solution

by:Bigfam5
Bigfam5 earned 80 total points
ID: 9659024
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
ID: 9659041
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
ID: 9659051
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 8

Expert Comment

by:Danielzt
ID: 9659077

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

Expert Comment

by:Danielzt
ID: 9659114
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
ID: 9659172
Daniel, thanx for correction. the moment I submited, what a Bummer!!!
I am in big apple, btw!




0
 
LVL 8

Expert Comment

by:Danielzt
ID: 9659263
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
ID: 9659292
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
 
LVL 8

Expert Comment

by:Danielzt
ID: 9659412
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
ID: 9659658
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 1920 total points
ID: 9659770
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
ID: 9660574
Sounds great!
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9660659
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
ID: 9660741
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
ID: 9670339
>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
ID: 11106258
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
ID: 11106977
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

618 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