User Management and Database Security Interview Questions
What is database security?
The goal of database security is to prevent unauthorized use of database or its components.Database security depends on system and network security.
What are the main aspects of Oracle database security management?
1) Controlling access to data(authorization)
2) Restricting access to legitimate users (authentication)
3) Ensuring accountability on part of the users(auditing)
4) Safeguarding key data in the database(encryption)
5) Managing the security of the entire organizational information structure (enterprise security)
What is a temporary tablespace?
All users need a temporary tablespace where they can perform operations such as sorting data during SQL execution.
What is a default tablspace?
Users need to have a default tablespace, where their objects will be created if they don't explicitly assign a different tablespace during object creation.
What are the two tablespaces created at database creation time in Oracle 10g database?
In Oracle database 10g at the time of database creation default temporary tablespace and default permanent tablespace for all users will be created during database-creation process.Once these two tablespaces are created,we don't have to specify them again during the database creation process.
What if we dont assign a specific tablespace as a default tablespace?
If we don't assign a default tablespace,the System tablespace becomes the default tablespace.
Why is it needed to create a default tablespace for every user?
If we dont assign a default tablespace System tablespace becomes the default tablespace. If a user creates a very large object in the System tablespace,they might take up all the space in it and make it impossible for the SYS superuser to create any new objects in it,causing the database to come to a grinding halt.This is the main reason why we should always create a default tablespace for every user.
Give the statement to create a usr:-
SQL>CREATE USER (username) IDENTIFIED BY (password);
In case of Oracle database 11g use the following sattement:
SQL>CREATE USER (username) identified by "(password)";
The above query creates a user and password for the user. It is good practice to assign default temporary and permanent tablespace to the users. It is a good practice to ass them at the time of database creation.
How do we get the default tablespaces of a user?
The following query gives the default temporary and permanent tablespace for a user:
SQL>SELECT default_tablespace,temporary_tablespace from dba_users where username='(username)';
Can a user created new connect to the database?
A new user can't connect directly to database,because the user doesn't have any privileges to do so.When a user tries to connect he gets the following error at the SQL prompt:
Ora-01045: user lacks CREATE SESSION privilege; logon denied
How do we rectify the bove error/ORA:01045 error?
In order for a user to connect and start communicating with the database ,he must be granted CREATE SESSION system privilege.The following statement is used:
SQL>GRANT CREATE SESSION to (username);
How to create a user with CONNECT system privilege?
SQL>GRANT CONNECT to (username) identified by (password);
Can a newly created user create database objects(tables,indexes,views,synonyms,sequences etc) in a database?
No,a newly created user won't be able to create database objects directly.Even if the user is assigned default temporary and permanent tablespace at the creation time,it is mandatory to allocate quota on a tablespace to users.
How do you allocate quota on a tablespace to users?
The following command is used to allocate quota on a tablespace:
SQL>ALTER USER (username) QUOTA ON (tablespacename);
What is fine-grained access control?
It is a new security mechanism from Oracle that provides low-level data security.
What is the advantage of fine-grained access control?
Traditional access control techniques like granting roles,privileges,views are broad-based that resulted in unnecessarily restricting users.As a solution Oracle came up with fine-grained access control where control on data access is at low-level.
What are the mechanisms used in fine-grained access control?
Oracle uses two related mechanisms to enforce fine-grained security within the database.They are:
1) Application context
2) Fine-grained access control Policy
What is Virtual Private Database(VPD)?
This term is used to refer to the implementation of fine-grained-access-control policies using application contexts.This offers security at row-level rather than table level.Each user of the application can be limited to seeing only a part of the table's data by using VPD concept.This row-level security is enforced by attaching a security policy directly to a database object such as,table,view or a synonym.It provides a much stronger security than application level security.No matter whatever tool is used(SQL*PLUS,adhoc query tool,report writer),the user can't elude this row-level security enforced by the database server.
What are the uses of fine-grained access control?
We can use the fine-grained access control for the following purposes:
1) Enforce row-level access control through SELECT,INSERT,UPDATE and DELETE statements 2) Create a security policy that controls access based on a certain value of a column
3) Create Policies that are applied the same way always as well as policies that dynamically change during the execution of the query
4) Create sets of scurity policies,called policy groups
How does VPD enforce security?
VPD uses a type of query rewrite to restrict users to certain rows of tables and views. a security policy is attached to the table or tables to which we want to control access,and stored procedures are written to modify any relevant SQL statements made against the tables in the question.
For example,when a user issues an UPDATE statement against the table with such a security policy,Oracle will dynamically append a predicate(a WHERE clause)to the user's statement to modify it and limit the user's access to that table.
This will prevent users belonging to say SALES department from accessing and modifying FINANCE department records.
UPDATE emp set salary=salary*10 will be modified as UPDATE emp set salary=salary*10 where dept='SALES';
What is needed to create a VPD?
To create a VPD,we have to create what is known as an application context and then implement fine-grained access control to enforce the row-level security for database tables and views.The application context helps us create security policiesthat draw upon certain aspects of a user's session information.For example when an user logs into the database,the user ID identifies the user,and based on that piece of information,the application's security policy sets limits on what the user can do within the database.
VPD is simple an implementation of the application context with fine-grained access control.
To what statements can we apply VPD Policy?
VPD policy can be applied to SELECT,INSERT,UPDATE,INDEX and DELETE statements.
Give details on Application context:-
An application context allows us to define a set of application attributes,usually a set of session environmental variables,that we can use to control an application's access to the database.Using application attributes we can supply relevant predicate values for fine-grained access control policy.