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

x
?
Solved

EXECUTE ORACLE 8 SQL STATEMENTS WITHOUT LOGIN

Posted on 2011-02-27
19
Medium Priority
?
530 Views
Last Modified: 2013-12-26
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
Comment
Question by:m_jundi
[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
  • 6
  • 5
  • 2
  • +4
19 Comments
 
LVL 3

Expert Comment

by:gopisera
ID: 34991120
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
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34991126
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 252 total points
ID: 34991256
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:m_jundi
ID: 34991297
Hello

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

Thanks
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34991304
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
 

Author Comment

by:m_jundi
ID: 34991615

select * from system.aud$
                     *
ORA-00942: table or view does not exist
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34991623
you need to be connected with a POWER user as SYSTEM or SYS ?
0
 

Author Comment

by:m_jundi
ID: 34991648
I am already connected as System
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34991663
ok
this happend to me once before try this.

select * from aud$;
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34992017
I fear that this feature is also new in oracle 9 ...
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34992070
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
 

Author Comment

by:m_jundi
ID: 34994775
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 248 total points
ID: 34998111
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
 

Author Comment

by:m_jundi
ID: 34998172
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
 
LVL 11

Expert Comment

by:Akenathon
ID: 35000296
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
 

Author Comment

by:m_jundi
ID: 35815140
Actuallyl, it doesn't give enough info. as required
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 37271901
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 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