Oracle Security Interview Questions & Answers

Question 1. How To Enable Automatic Archiving?

Answer :

1. shutdown the database with normal/emmidiate/transactional
2. modify/include the parameter log_archive_start=true, 
1. log_archive_dest= (location to which the redo log file group is archived), log_archive_format=(in which format you want to apper the archive log file)
3. start up the database.

Question 2. Explain What Are The Steps To Switch A Databases Archiving Mode Between No Archivelog And Archivelog Mode?

Answer :

To check database is in which mode:

1. Conn sys/sys as sysdba
2. select log_mode from v$database

To set database in Archivelog mode:

1. shutdown immediate;
2. startup mount;
3. alter database archivelog;
4.  alter database open;

To set database in NoArchivelog Mode:

1. shutdown immediate;
2. startup mount;
3. alter database noarchivelog;
4. alter database open;

Question 3. What Are Roles? How Can We Implement Roles?

Answer :

If a user wants to give some privileges to another user or a group of users then they will create role and they give this role to the users.let the privileges be 'insert' and 'update' on 'emp' table then instead of specifying them by their names they will create role which inturn includes these methods. this role is further given to the users.

If in futher if he wants to remove some privilege given to the users, he can just alter the role and remove that particular privilege such as he can remove update from this created role..

Later the users cant do updations on that emp table.but still can insert the values into that table.

Hence Roles are the easiest way to grant and manage common privileges needed by different groups of database users.Creating roles and assign each role to group of users will simplify the job of assigning privileges to individual users.

Question 4. What Are The Minimum Parameters Should Exist In The Parameter File (init.ora)?

Answer :

• DB NAME - Must set to a text string of no more than 8 characters and it will be stored inside the datafiles,redo log files and control files and control file while database creation.
• DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters(DB_NAME & DB_DOMAIN)
• CONTORL FILES - List of control filenames of the database.If name is not mentioned then default name will be used.
• DB_BLOCK_BUFFERS - To determine the no of buffers in the buffer cache in SGA.
• PROCESSES - To determine number of operating system processes that can be connected to ORACLE concurrently. The value should be 5 (background process) and additional 1 for each user.
• ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup.

Also optionally  LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and LICENSE_MAX_USERS.

Question 5. What Are The Main Responsibilities Of A Database Administrator?

Answer :

Depending on the company and the department, this role can either be highly specialized or incredibly diverse. The primary role of the Database Administrator is to adminster, develop, maintain and implement the policies and procedures necessary to ensure the security and integrity of the corporate database. Sub roles within the Database Administrator classification may include security, architecture, warehousing and/or business analysis.

Other primary roles will include:

1. Deciding the h/w devices to be used
2. Deciding the user and data to be used by user.
3. Deciding the information content data base system.
4. Deciding the data dictionary contain the data in data.
5. Deciding the validation check on data.
6. Deciding the backup and recovery.
7. Monitoring the response to be changing environment.

Question 6. Can You Explain What Are The Roles And User Accounts Created Automatically With The Database?

Answer :

• DBA - role Contains all database system privileges.
• SYS user account - The DBA role will be assigned to this account. All of the basetables and views for the database's dictionary are store in this schema and are manipulated only by ORACLE.
• SYSTEM user account - It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username.

Question 7. What Are The Database Administrators Utilities Available?

Answer :

So many database utilities are available:

1. SQLPLUS
2. RMAN
3. TKPROF
4. SQLLODAR
5. EXP,IMP AND EXPDP,IMPDP
6. EXPLAN PLAN
7. LSNRCTL

Question 8. Explain What Are The Dictionary Tables Used To Monitor A Database Spaces?

Answer :

DBA_DATA_FILES is view to show the space for the tablespace if you want the calulate the space of the database the all tablespace size is the database size.

Question 9. How To Enforce Security Using Stored Procedures?

Answer :

• Don't grant user access directly to tables within the application.
• Instead grant the ability to access the procedures thataccess the tables.When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

Question 10. Tell Me What Is User Account In Oracle Database?

Answer :

An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges

Question 11. What Is The Step For Rman Backup?

Answer :

RMAN is an Oracle tool for taking the backup and recovering the databases. You can copy, restore, and recover datafiles, control files and archived redo logs (all 3 files or any one file). It has command line utility as well as GUI-based Enterprise Manager Backup. Here we are discussing the command line utility. 

For example, we have TEST and PROD databases. Here TEST is the catalog database and PROD is the target database for which the backup has to be taken. You may loose your backup if you have your catalog and target databases on the same box and the box crashes .So it is always advisable to keep a separate database for RMAN catalog . 

Create two databases. One for RMAN catalog(Test) and one target(PROD). Both the Databases should be archive log mode(for security).

Question 12. What Is Meant By Physical Order And Logical Order In Clustered Index And Non Clustered Index?

Answer :

It is mean it is primary key column.

Question 13. How To Prevent Unauthorized Use Of Privileges Granted To A Role?

Answer :

By creating a Role with a password

Question 14. What Is Default Tablespace In Oracle Security?

Answer :

its the tablespace in to which the objects created by a user go.default tablespace can alloted to a user while creating he user or can also be changed after creating the user. the default table space of a user is system tablespace unless mentioned specfically while creating the user.

Question 15. What Is Tablespace Quota In Oracle Security?

Answer :

Amount of tablespace that can be used by a user for creating his own database objects is called talespace quota.its a way of limiting the usage of memory or disk space by user or restricting him by giving a fixed disk space.

Question 16. What Is A Profile In Oracle Security?

Answer :

Profile is information contain that particular modules.

Question 17. Explain What Are The Options Available To Refresh Snapshots?

Answer :

1. COMPLETE - Tables are completely regenerated using the snapshot's query and the master tables every time the snapshot referenced.
2. FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.
3. FORCE - Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.

Question 18. Explain Snapshot Log?

Answer :

It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.

Question 19. Explain When Will The Data In The Snapshot Log Be Used?refer

Answer :

snapshort log is used updated data for master table and if any recored is updated after certen period to refer the master table collect the snapshot log. because tha table size is very large it take lot of time. thus by ues the snapshot log.

Question 20. Explain What Are The Benefits Of Distributed Options In Databases?

Answer :

• Database on other servers can be updated and those transactions can be grouped together with others in a logical unit.
• Database uses a two phase commit.

Question 21. What Is Different Between View And Sequence?

Answer :

• A view is a logical table based on a table or another view.  A view contain no data itself but it is like a window  through which data from tables can be views and changed. 
• A sequence is a user created database object that can be shared by multiple user to generate unique integers.

Question 22. What Is Dynamic Data Replication?

Answer :

Dynamic data replication is feture of oracle actualy the to more database ex:- this is production database and other database with and synconoze with the production databse throuth the dynamic replication. if any change the proction database the symaltantinasly change the other database the replication. the benefite the the user can access any were ether prodction or syn with other database.

Question 23. How To Enforce Refrencial Integrity In Snapshots?

Answer :

Time the references to occur when master tables are not in use.Peform the reference the manually immdiately locking the master tables. We can join tables in snopshots by creating a complex snapshots that will based on the master tables.

Question 24. Differentiate The Simple And Complex, Snapshots?

Answer :

• A simple snapshot is based on a query that does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.
•  A complex snapshots contain atleast any one of the above.

Question 25. Do You Know What Are The Various Type Of Snapshots?

Answer :

snapshots is three types of complite,fast and force.

Question 26. What Is Snapshots?

Answer :

snapshot is nothing but a consistent data for master table if any chance the master table snapshot is fetch the updated data for master table.

Question 27. How To Reduce The Network Traffic?

Answer :

• Replication of data in distributed environment. 
• Using snapshots to replicate data.
• Using remote procedure calls.

Question 28. How To Specify The Archived Log File Name Format And Destination?

Answer :

By setting the following values in init.ora file:

LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc

(%S - Log sequence number and is zero left paded,%s - Log sequence number not padded. %T - Thread number lef-zero-paded and %t - Thread number not padded).

The file name created is arch 0001 are if %S is used.

LOG_ARCHIVE_DEST = path.

Question 29. What Is The Use Of Analyze Command?

Answer :

To perform one of these function on an index,table, or cluster:

• to collect statisties about object used by the optimizer and store them in the data dictionary.
• to delete statistics about the object used by object from the data dictionary.
• to validate the structure of the object.
• to identify migrated and chained rows of the table or cluster.