• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

EXECUTE ORACLE 8 SQL STATEMENTS WITHOUT LOGIN

Dear Experts

Is it possible to execute Sql Statements(Insert, Update, select) withput login to a database ? using a connection string . I am trying to log all failed / successful logins from a powerbuilder application. I tried the Connect & Connect using in powerbuilder but it didn't help.

Thanks
0
m_jundi
Asked:
m_jundi
  • 6
  • 5
  • 2
  • +4
2 Solutions
 
gopiseraCommented:
Unable to get your question.  can you give in detaii

does the tns entires are good.
Please check the tns entires and try to connect

username/password@tnsentry



0
 
Aaron ShiloChief Database ArchitectCommented:
hi

what you need is a trigger at the database level check this :

connect sys/manager;
 
create table
   stats$user_log
(
   user_id           varchar2(30),
   session_id           number(8),
   host              varchar2(30),
   last_program      varchar2(48),
   last_action       varchar2(32),
   last_module       varchar2(32),
   logon_day                 date,
   logon_time        varchar2(10),
   logoff_day                date,
   logoff_time       varchar2(10),
   elapsed_minutes       number(8)
)
;
 

Designing a logon trigger

 
create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   null,
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null,
   null
);
END;
/
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ashilo,
 AFTER LOGIN would not be able to catch failed logins ...
 also, that feature is only available as from oracle 9.x, and not in oracle 8

so:
>Is it possible to execute Sql Statements(Insert, Update, select) withput login to a database ?
no, that is not possible

so, you could only log them to something else than the oracle database in the first place
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
m_jundiAuthor Commented:
Hello

Thanks ashilo for the help, but as angelIII says , this applys for successful login only

Thanks
0
 
Aaron ShiloChief Database ArchitectCommented:
ok
i dug into the back of my head to try and remember some oracle audit stuff

you can use this  :

audit connect whenever not successful;

view results of failed logon
select * from system.aud$;
0
 
m_jundiAuthor Commented:

select * from system.aud$
                     *
ORA-00942: table or view does not exist
0
 
Aaron ShiloChief Database ArchitectCommented:
you need to be connected with a POWER user as SYSTEM or SYS ?
0
 
m_jundiAuthor Commented:
I am already connected as System
0
 
Aaron ShiloChief Database ArchitectCommented:
ok
this happend to me once before try this.

select * from aud$;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I fear that this feature is also new in oracle 9 ...
0
 
Aaron ShiloChief Database ArchitectCommented:
ye ANGELLLL
your right i just checked it on one of our legacy systems.

sorry m_jundi

BTW : since other audit options are available in 8i your suppose to have the aud$ table.
0
 
m_jundiAuthor Commented:
I have Oracle 8i, I am able to brows this view but no records were added to this view, it is always empty, shoud I enable Audit some where ? like init.ora?

0
 
johnsoneSenior Oracle DBACommented:
Just to clarify.  You can audit login failure with a database level trigger.  You cannot use AFTER LOGON as was posted before, you need to use AFTER SERVERERROR.  Then you check for a login error message.

The base shell can be found in the doc here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm

However as has been mentioned, this is not available in 8i.
0
 
m_jundiAuthor Commented:
ashilo, it worked, need to Activate Audit Trail from Init.ora,
let me take a look at aud$ if it has what I need.
0
 
AkenathonCommented:
You just need:

AUDIT CREATE SESSION;

That will create audit records BOTH for successful and unsuccessful attempts. Then, instead of going to SYS.aud$ (it's NOT system, it's SYS), you should use the view which was made for that exact purpose:

SELECT * FROM DBA_AUDIT_SESSION;

You need to set AUDIT_TRAIL=DB and bounce the instance. As a bonus, you'll get some statistics on resource consumption after each session ends. From time to time, you ought to backup and TRUNCATE SYS.AUD$, just keep an eye on it.

Connections AS SYSDBA or SYSOPER do not create records in sys.aud$ (because they can happen even when the instance is NOT started, so there's no way to get the records inserted). Look for audit evidence of those in $ORACLE_HOME/rdbms/audit if you need it :-)
0
 
m_jundiAuthor Commented:
Actuallyl, it doesn't give enough info. as required
0
 
leonstrykerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now