Posted on

Oracle user management interview preparation

Once the oracle database is installed, it needs to be available for clients be it end-users, applications etc. The access and authorization of users trying to access, manipulate the information stored in database should be under control
1) Why should a DBA be considerate about server default accounts?
When oracle database is installed and created, server default account are the ones that gets created by default. These accounts come with set of default passwords. Henceforth, hackers find it extremely easy to hack the system via these accounts. The primary responsibility of a DBA is that they should be aware of these accounts and secure them properly. At times if an account is not needed, it can be removed for safety reasons.
2) Does all edition of oracle come with same default accounts?
No. The default accounts depends on the following factors
a) Database edition – Depending on edition the features supported vary and hence does the accounts
b) Version – Database version determines the accounts
c) Install option chosen – At the time of oracle software installation, database creation we have option to choose. Depending on that default account created varies
3) How does a DBA secure database? What is the fundamental job of DBA with respect to user management?
When it comes to user management, here are the basic job duties of an Oracle DBA
a) Create new users
b) Managing user accounts – This starts with restricting the user access via profile creation. PRofile is a set of privileges
c) Secure the user password
d) Implement proper authentication method
4) How do you secure server default user accounts?
As the server default users that are installed and created in database depends on many different factors, first step would be to identify the users that are present in the system. As with normal users, default user accounts have their information stored in dba_users view. To make the job easy and readable, create a html file with list of users in database
set markup html on
spool user_details.html
select * from dba_users order by 1;
spool off
set markup html off
Once we get the above output, next step would be to lock and expire the default accounts
alter user username account lock;
To lock and expire the password use the below command:
alter user username password expire account lock;
5) What is the significance of expiring password?
Password can be expired so that next time a user login, they need to provide new password
6) What happens when an account is locked?
User is not allowed to connect to database. To make the user login, account needs to be explicitly unlocked
alter user username account unlock;
7) What happens when SYS user account is locked?
Yes. We can lock the sys user account. However, if there has been password file or OS level authenticaiton implementation we can still log into system as SYS user