USER MANAGEMENT INTERVIEW QUESTIONS
What is the difference between a user and schema?
A user will not have any of his own objects and will always access others objects. Schema is a collection of objects
After creating user, what are the privileges you would assign?
We will give CONNECT role in 10g (CREATE SESSION if 9i) and any other roles or privileges specified by the application support
What is the disadvantage of resource role?
It will override the quota granted for a user and makes it unlimited
How you will identify the privileges and roles assigned to a user?
Using following views
How to create user and grant the permission in a single command?
SQL> grant create session to user_a identified by <pwd>;
What happens to the objects if we change the default tablespace for the user?
Nothing. They will continue to work as normal
You got a requirement to run a script as user “MARK” and you don’t have the password of MARK. How you will take next step?
We will ask application support. In case they cannot be reached, we can take ASCII password stored in dba_users and change the password. Once work is done we can change the password back using ASCII that we stored earlier
Also we can set current_user option in SQLPLUS
What is the view to check default permanent and temp tablespace values?
Have you implemented password policies?
a. Yes. Using profile management
What are SNIPPED sessions? If there any impact on database to have them?
Those are sessions terminated from database because of crossing IDLE_TIME limit. But disadvantage is even though oracle level sessions are cleared, at OS level processes willstill occupy resources which is a burden to the server
I am using IDLE_TIME as 15 min, but observed that even after 20min idle time, session is not getting disconnected, what is the reason?
RESOURCE_LIMIT parameter is not set to TRUE
As a DBA will you recommend to implement auditing in the database?
Depends on the type of database, but it would be wise to implement atleast on some critical tables
What factors you will choose before you enable auditing?
We need to check about database performance and what level of auditing is required
If auditing is not in place, how you will get to know if a user performed update or not yesterday?
We can use logminer to read the archives which are generated yesterday
Are you maintaining any SOX compliance database?
No. We don’t have any SOX database as of now
How to check which users are granted with sysdba role?
We can check from v$syspw_file view
If we want to restrict users to connect to the database, but want to allow some users to connect, how can we do that?
We need to grant restrcited session privilege for the users whom we want to connect