Oracle Database Security Interview Questions, Answers, and Explanations
EQUITY PRESS
Oracle Database Security Interview Questions, Answers, and Explanations
ISBN 978-1-60332-008-5
Smashwords Edition
Copyright© 2006 Equity Press and ORA COOKBOOK all rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording or otherwise) without either the prior written permission of the publisher or a license permitting restricted copying in the United States or abroad.
The scanning, uploading and distribution of this book via the internet or via any other means with the permission of the publisher is illegal and punishable by law. Please purchase only authorized electronic editions, and do not participate in or encourage piracy of copyrighted materials.
The programs in this book have been included for
instructional value only. They have been tested with
care but are not guaranteed for any particular purpose.
The publisher does not offer any warranties or
representations nor does it accept any liabilities with
respect to the programs.
Trademarks: All trademarks are the property of their respective owners. Equity Press is not associated with any product or vendor mentioned in this book.
Please visit our website at www.oracookbook.com
REGISTER YOUR BOOK
You are entitled to FREE UPDATES for Oracle Security Interview Questions, Answers, and Explanations – as well as discounts on all Equity Press books and Information Technology career preparation materials.
Please visit http://www.oracookbook.com/register
Question 1: Grant to Select Session Details
Question 3: SELECT access to VIEWS through ROLES
Question 6: Trigger to Prevent Any User to Login
Question 10: Delete the user’s name
Question 11: Restricted User Role
Question 12: Forget Sys and System Password
Question 13: Decrypt Password Field
Question 14: Password and Login Feature
Question 15: Unable to Use Connect-Time
Question 16: History of Passwords
Question 17: Security auditing in Oracle 10g One
Question 18: Check all Privilege Available
Question 21: Schema which only selects tables
Question 22: Set connect as sysdba?
Question 23: Unlock an Account
Question 24: Security of SYS AS SYSDBA Password
Question 27: Security Oracle when using the Internet
Question 28: Password doesn't work for SYSDBA
Question29: Keeping a history of the V$SESSION
Question 32: Any Password works
Question 34: AUDIT_ACTIONS Table
Question 39: Wrap Multiple Files
Question 40: Verifying Security Patches
Question 41: Password Expires Soon
Question 42: Create Package/Trigger Privileges
Question 43: Directory and Creating Directory Rights
Question 44: Table Access Query
Question 45: Restricted Access in Oracle
Question 46: Rule/format for the username and password
Question 48: Database Security through Roles
Question 49: Creating role with password
Question 52: Change password of Sysdba
Question 55: External Authentication
Question 56: Recovering the sys password
Question 58: Automatic password expiration
Question 59: Cannot populate audit log table
Question 60: Role needed to get over the problem
Question 61: Problem after changing password
Question 62: Connectivity: SQL*Net and Security
Question 63: New role object privileges
Question 64: audit parameter not working
Question 66: Issue with fine grain policies
Question 67: Privilege to a user
Question 68: Trigger auditing in Ora 8.1.7.4.1
Question 69: Role to give user to load data
Question 70: Revoking all permissions from user
Question 71: Password decryption
Question 73: sysdba/resource privilege
Question 74: Audit sql statement
Question 75: Mixing letters and numbers in passwords
Question 76: Not able to Grant Write Permission
Question 77: DBMS_OBFUSCATION_TOOLKIT
Question 79: Audit user activities in database
Question 80: Impact in locking the account “Scott”
Question 82: DEFAULT PASSWORDS
Question 85: Oracle system created DBA
Question 87: Authentication in Stored Procedure
Question 88: GRANT inside procedure
Question 89: Grant execute privileges
Question 90: Trigger code for audit columns
Question 91: DBMS_OBFUSCATION_TOOLKIT
Question 92: UserName, Password and Host String
Question 93: Error in pl/sql and encryption
Question 94: PL/SQL Random Password Generator
Question 95: Checking Oracle Users Password
Question 96: Verifying database role
Question 98: Password encryption and decryption
Question 100: Which role I was granted
Question 101: Security-Public Objects
Question 1: Grant to Select Session Details
Developers use Toad, and they want to use the session browser option to see the SQL running and open other cursor session specific details.
What grant can be given to them so that the schema they logged on, can access that information?
A. With SELECT_CATALOG_ROLE, you can see the session details, but you will not be able to trace the session. Click the "trace this session" button
in the session browser:
2.1 Date : Tue, 13 Mar 2006 09:32:58 -0600
2.2 Address: 004E3DWW1
2.3 Module: TOAD.exe
2.4 Type : EOraError
2.5 Message: ORA-06550: line 1, column 7: PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored.
Is it possible to include @ in the username like such as rkl@study.com.? As this happens, the study.com is added by the application during login to the database. If the username is rkl1, then the login name sent to database for authentication is rkl1@study.com.
Is it possible for Oracle to ignore this @study.com and authenticate just the username?
A. You can try the ff:
Use the '/' trick
C:\>sqlplus "\"rkl@study.com\""/pwd
SQL*Plus: Release 9.2.0.6.0 - Production on Mar Mar 14 12:00:04 2006
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL>
Question 3: SELECT access to VIEWS through ROLES
I want to confirm if the VIEWS are used in procedure/functions/packages.
Is it possible to give SELECT access to VIEWS through roles and then roles to users?
A. Yes, it is possible. The view must be explicitly granted to the user or the user needs to select any table privileges.
Does anyone have a script that can give me everything that is being audited on the DB, and what it’s audited for after the audit trail is set for the DB?
A. I don’t have the script, but you can see a part of it in this link:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#10459
perhaps, you can get the required info:
DBA_OBJ_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS
it describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL
list all audit trail entries. USER view shows audit trail entries relating to current user.
DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT
it contains audit trail records for all objects in the system. USER view lists audit trail records for statements concerning objects that are accessible to the current user.
DBA_AUDIT_SESSION
USER_AUDIT_SESSION
Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.
DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT
Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user.
Question 5: user_ts_quotas shows removed tablespace info
Some users were given unlimited table space privilege on some table spaces. Those table spaces have been dropped, however when the users query user_ts_quotas, it would still show the removed table spaces. In reality, those table spaces were dropped long time before. It seems like oracle does not maintain dependency between quotas on table spaces, even if it does not exist. We have recreated those table spaces and reduce the quota to 0 and then drop it. Guess what, oracle still shows 0 quotas. Is there any way to refresh or workaround so users only see the table spaces where they have the quotas, rather 0, or whatever quotas on table spaces, even if they don’t exist?
A. I can see that the Oracle still shows 0 quotas even after recreating the table spaces and then removing them. Please be informed that it is a known good old bug and what you did is the standard fix.
However, it seems that there is still a bug. You may also check TS$ to see more anomalies. The ts$.online$ has a more specific entry to describe the status of tablespace.
1-available and normal
3-dropped
3-errored during creation and never been created -->? I have no idea.
1* select name,online$ from ts$
sys@9i > /
If you are using oracle version is 9.2.0.7, the database is sitting on HP box. You may see the following running with this oracle version:
--grant unlimited tablespace to user Scott on junk1 tablespace
sql>ALTER USER Scott QUOTA unlimited ON junk1;
--now go back and grant 0 quota on junk1 tablespace to Scott
sql>alter user Scott quota 0 on junk1;
--Now drop the tablespace junk1
connect back Scott/tiger
sql>select * from user_ts_quotas
and you will see the junk1 tablespace under tablespace_name although. It is gone and if the data dictionary is working right. It should have updated the information and wipe the removed table from the view.
Question 6: Trigger to Prevent Any User to Login
How can I create a trigger on any user to prevent login on a particular time or day?
A. This article may help you if you are looking for a logon trigger on database.
http://www.unix.org.ua/orelly/oracle/guide8i/ch06_02.htm
You may not restrict a dba to prevent them from logging. However you could play around to harass the other users:
create or replace trigger trig_log_user
AFTER LOGON ON DATABASE
WHEN (USER ='HR')
Declare
v_hr varchar2 (20);
Begin
Select to_char(sysdate, 'HH24') into v_hr from dual;
--after 10AM, does not allow connection.
If v_hr >=10 then
raise_application_error (-100023, 'go home');
End if;
End;
/
Question 7: demobld in Windows Environment for a New User
I have created a new user, but I am unable to demobld that particular user. I know how to do this in Linux environment, but I don't know how to do it in windows environment.
A. You have to search Demobld.sql, and find its path in windows. For e.g.:
if it is in D:\Orawin95\DBS, then run demobld as:
SQL> @ D:\orawin95\dbs\demobld <enter>
It will Exit sqlplus, and you have to login again.
Can you tell me how to change a username, like Scott to James?
A. This will require several steps:
1. Export current schema (Scott's)
2. Create a new user (James)
3. Import scott.dmp into James
4. Drop user Scott
Is it possible for any ordinary oracle user to get a DBA role without having to be granted by any DBA’s like Sys n System?
A. You have to grant DBA to user1. This will give the user1 an access to DBA role. And only another DBA or sys/system can grant this feature.
Question 10: Delete the user’s name
I’d like to know what syntax is needed to delete the user’s name.
A. SQL> DROP USER _the_user CASCADE;
Cascade is optional. This will drop all the user's objects prior to the drop of the user itself.
Question 11: Restricted User Role
What are the roles and system privileges I have to use to create a role assigned to users and allow them to select and insert operations on the tables? Either by using direct SQL statements or via triggers, functions, stored procedures, table Altering operations or drop tables will not be allowed.
A. While creating the role, you have to use:
sql> grant connect, select any table, insert any table to myrole;
Then use,
SQL> drop user x;
User dropped.
SQL> create user x identified by x;
User created.
SQL> grant myrole to x;
Grant succeeded.
If the user is already available after having the table created, then he may have a chance of dropping the objects of his schema and thus will be given permission.
Question 12: Forget Sys and System Password
Where can I find the sys and system password file?
Do I need to login to database?
There was a file:
orapwd file=$HOME/ADMIN/PFILE/orapw$ORACLE_SID, and it has changed in oracle 10g.
A. You can find it by using the "orapwd" utility to create a new password file with a new SYS password.
$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>
Where
File - name of password file (mand),
Password - password for SYS (mand),
Entries - maximum number of distinct DBA and OPERs (opt),
Force - whether to overwrite existing file (opt),
There are no spaces around the equal-to (=) character.
Question 13: Decrypt Password Field
How can I decrypt a password field in a user’s table in SQL 2000 SP4?
A. Applications normally store password hashes in the database and not encrypted passwords. Trying to decrypt them is probably not going to get you anywhere.
Question 14: Password and Login Feature
I have written a web based application in PL/SQL. It sits on top of an Oracle database. What I want to do is, whenever a user visits certain web pages, the application asks for a login and password.
Where can I find information on how to do this?
I would rather put a security wrapper around the code, so that when you call a transaction, it first checks to see if you are logged in, and only proceeds if you are logged in or else prints a message.
A. Is this a mod_plsql application? If so, you can set a cookie when the user authenticates. Successive procedure calls can check if the session is valid.
When the user login:
If (i_submit like '%Login%' and v_user_exists = 1) then
owa_util.mime_header('text/html', false);
owa_cookie.send('userid', i_user, sysdate+1);
owa_cookie.send('passwd', i_passwd, sysdate+1);
owa_util.http_header_close;
In each protected procedure:
v_userid_cookie:= owa_cookie.get('userid');
v_passwd_cookie:= owa_cookie.get('passwd');
If (v_passwd_cookie.num_vals < 1 and ...) then
htp.p('<FONT COLOR=RED><B>Please login first!/FONT>');
return -1;
end if;
Question 15: Unable to Use Connect-Time and Idle Time
I have set the resource_limit=true. I created a profile with the following:
session_per_user=2
connect_time=5
idle_time=1
I assigned this profile to a user, but when I connect as this user, it doesn’t allow me to more than 2 sessions. I can work on my session without any restriction imposed by connect_time and idle_time
A. Your system can be replicated to some extent.
Set the same values for Scott’s profile.
SQL> select * from dba_profiles
2 where profile='CHECK_PROF'
11:53:44 SQL> create table t2 (n1 number);
Create table t2 (n1 number)
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
11:57:19 SQL> conn scott/tiger
Connected
12:02:27 SQL> create table n1 (col1 number);
Table created
12:03:02 SQL> drop table n1;
Table dropped
12:05:09 SQL>
As I can interpret, if the session is remained idle for more than 2-3 minutes then it gets disconnected, otherwise it remains connected.
Question 16: History of Passwords
Where can I find the history of the passwords for controlling the password limits, such as password_life_time, password_resuse_max ,password_reuse_time etc.?
What is the name of the table/view??
A. You can find the history of the passwords through the table listed below:
user$ and user_history$
Login as sysdba to access these.
Check out with dba_users,user_users, and you will get an idea of how a users password is maintained. The passwords were all encrypted and we cannot break it.
Question 17: Security auditing in Oracle 10g One
I'm looking for possibilities like grained security auditing for Enterprise version, only in oracle 10g SE One. Is it possible to log all events, like who has viewed exact table at exact time?
Is there a way to log such information as Oracle 10g One? Which user has viewed some data from database without using fine grained auditing or programming this into application?