Top 29 Oracle Security Interview Questions You Must Prepare 23.Apr.2024

Profile is information contain that particular modules.

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.

1. shutdown the database with normal/emmidiate/tractional
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.

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

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.

• 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.

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.

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.

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.

• 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.

• 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.

• 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.

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;

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.

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.

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).

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.

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.

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.

snapshots is three types of complite,fast and force.

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.

It is mean it is primary key column.

By creating a Role with a password

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

• 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.

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

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

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.

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.