Hi All,
In many cases it is found non oracle database needs to connect to oracle database to retrive data. For e.g Microsoft sql servetr or .net application may require to connect nad retrive data . In such cases we generally ODAC (oracle database access ) software provided by oracle. It contains oracle provider for OLE DB which is used to establish a link from Microsft Sql Server datbase to Oracle database
Recently i used oracle provider for OLE DB 10.2.0.1.2 to setup a linked server in sql 2005 with oracle database . Here are the steps which followed
1. Open Sql Server management studio
2. Provide Server name, authentication, Username and Password to connect to connect to your sql.
3. expand the Server objects by clicking on the + sign.
4. Expand the Linked servwers folder & u will see providers folder
5. You will see oracle provider for OLE DB when u expand provider folder
6. Check the check boxes for “Dynamic Parameters ” and “Allow In Process”.
7. Click OK
8. Right click on the linked servers folder and choose new linked server
9.In the New Linked Server Properties dialogne box, do the following
Type in a Linked Server Name
Under Server type, select Other Data Sources – for the provider name, select Oracle Provider for OLE DB.
10. In the Data Source field, type in the Oracle Service name (Sql Net Alias)
11. In the Product name type “Oracle”.
12.Leave Provider String Blank.
13.Now, Click on “Security option” on the left plane.
14.Select ” BE Made using this security context”
15. Type user id and password to connect to oracle database
16.Go to “Server Options” tab in the Linked Server Properties.
17. click “TRUE” for “Collation CVompatible”.
18. Click OK
This will create a Linked server with the name provided by you.
Database is generally meant for multiple users to access the information. However there is always a threat of the misuse of data from the users.To prevent this and monitor appropriate use of data it is possible to audit database usage.
In order to enable auditing the parameter audit_trail must be set in init.ora file or spfile.The possible values for audit_trail parameter is db (true) ,operating system (false) or none.With oracle 10g the parameter can value 1 more value called db_extended.
When the value is set to ‘db (true)’ the auditing information is stored in aud$ table stored in system tablespace of SYS schema.
When the value is set to ‘db_extended’ the field sql_text,sql_bind of clob datatype is populated in aud$ table.
When the value is set to ‘operating system (false)’ the auditing information is stored external to database.
When the value is set ‘none’ the auditing is disabled.
It is possible to audit system privilege or DML on tables either by username, by session or by access.
for e.g
AUDIT create table,alter table,drop table
By scott
whenever sucessful;
here the above example shows the auditing of system privilege of creating table,altering existing table, or droping the table by user scott.
Also the statement ‘whenever sucessful’ makes sure that aud$ is populated only when the statement is sucessfully executed.
Audit update,delete,insert on scott.emp by access whenever not sucessful.
To turn off the auditing the keyword noaudit is substituted in place of audit.
To turn off all the auditing the statement is.
NOAUDIT ALL;
NOAUDIT ALL ON SCOTT.EMP;
Also it is possible the activities on aud$ table itself
AUDIT DELETE on aud$;
The Data dictionary views related auditing are
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
