Database Security Oracle Interview Questions And Answers

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.

Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.

Each database user is assigned a Profile that specifies limitations on various system resources available to the user.

Auditing includes reading, writing and deleting the access on the objects at the table level. The privileges granted to a user can be audited.

There are three levels of auditing:

Statement level
Object level
Privilege level

Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.

Roles are named groups of related privileges that are granted to users or other roles.

Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.

The number of concurrent sessions the user can establish the CPU processing time available to the user's session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user's session the amout of logical I/O available to a single call to ORACLE made by a SQL statement the allowed amount of idle time for the user's session the allowed amount of connect time for the user's session.

Monitoring of user access to aid in the investigation of database use.

Object auditing is the auditing of accesses to specific schema objects without regard to user.

Installing and upgrading the Oracle Server and application tools.
Allocating system storage and planning future storage requirements for the database system. Managing primary database structures (tablespaces) Managing primary objects (table,views,indexes)
Enrolling users and maintaining system security. Ensuring compliance with Oralce license agreement Controlling and monitoring user access to the database. Monitoring and optimizing the performance of the database. Planning for backup and recovery of database information. Maintain archived data on tape
Backing up and restoring the database. Contacting Oracle Corporation for technical support.

The Tablespace to contain schema objects created without specifying a tablespace name.

SYSTEM is the default tablespace. if the user doesnt have a default tablespace, or hasnt mention the default tablespace clause while creating the table, the table would be created in the SYSTEM tablespace.

A user account is a schema which is used to store database objects, applications, and components, and to determine a user's database privileges.

DBA - role Contains all database system privileges.
SYS user account - The DBA role will be assigned to this account. All of the base tables 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.

The steps are :

* First set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in init.ora file
* Then dbs$orapwd file=orapw$ORACLE_SID password=sys force=y
* Then startup force;
* Then grant sysdba to user(any user in the database);
* Then conn user/user
* Then conn user/user as sysdba
* Then show user

SQL * DBA - This allows DBA to monitor and control an ORACLE database.
SQL * Loader - It loads data from standard operating system files (Flat files) into ORACLE database tables.
Export (EXP) and Import (imp) utilities allow you to move existing data in ORACLE format to and from ORACLE database.

If we want to say that our Database is secured then we must confirm first that our Data is validate.There are various methods of validating the Data:1. Accept only known valid Data.2. Reject known Bad Data.3. Sanitize Bad data. We cannot emphasize strongly enough that "Accept Only Known Valid Data" is the best strategy. We do, however, recognize that this isn't always feasible for political, financial or technical reasons, and so we describe the other strategies as well.All three methods must check: * Data Type * Syntax * Length Data type checking is extremely important. The application should check to ensure a string is being submitted and not an object, for instance.Accept Only Known Valid DataAs we mentioned, this is the preferred way to validate data. Applications should accept only input that is known to be safe and expected. As an example, let's assume a password reset system takes in usernames as input. Valid usernames would be defined as ASCII A-Z and 0-9. The application should check that the input is of type string, is comprised of A-Z and 0-9 (performing canonicalization checks as appropriate) and is of a valid length.Reject Known Bad DataThe rejecting bad data strategy relies on the application knowing about specific malicious payloads. While it is true that this strategy can limit exposure, it is very difficult for any application to maintain an up-to-date database of web application attack signatures.Sanitize All DataAttempting to make bad data harmless is certainly an effective second line of defense, especially when dealing with rejecting bad input. However, as described in the canonicalization section of this document, the task is extremely hard and should not be relied upon as a primary defense technique.

The collective amount of disk space available to the objects in a schema on a particular tablespace.

Data abstraction has been achieved in Oracle by separating the client and the server side logic. Therefore the client applications cannot manipulate the data. The triggers perform content based auditing and selectively disable application updates.

Access control can be achieved in Oracle by allowing the users to manipulate the data by using only their definer?s permitted previledges.

we secure data through encryption and decryption.

Roles are the easiest way to grant and manage common privileges needed by different groups of database users. Creating roles and assigning provides to roles. Assign each role to group of users. This 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.

Shut the database
Backup the database
Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file.
Start up the database.

To enable archiving
first shutdown the database in normal mode
then open ur parameter file and include a parameter
archive_log_dest=(give a OS level location)
then come out of it and at OS level create same directory which u have just mentioned in parameter file
then startup mount
then alter database archivelog
then alter database open
in order to know whether ur database is functioning in archive mode or not
type at sql promt
ARCHIVE LOGLIST;