Student Id: Student Name:

Academic year: 2019-20

Sem-In Examinations-I
B. Tech. (CSE), 2017 Batch
III, 6th Semester
17CS3278 - Database Security
Time: 2 hours Max. Marks: 50

Part-A (4X 3M=12M)

Answer ALL Questions
1. Oracle 11g considers users and user security, even before you think
about the data, because users create data. Interpret various types of
users that we create in Oracle 11g.
appsec, secadm, appusr
2. SYS has a schema (by the same name) and has lots of structures
belonging to his account. Summarize other purposes of SYS user and
System privileges.
SYS Default Oracle system administrator. SYSDBA is a unique system
privilege; let’s call it a “super” system privilege. It provides practically unlimited
administrative ability, there are schema object privileges. a couple of other
super system privileges listed in this query: SYSOPER and SYSASM.
3. Create table by the name customer with columns cust_id, cust_firstname,
cust_lastname, cust_address, cust_city, cust_state, cust_zip,
cust_credit_card_no,cust_creadit_card_exp. Write SQL queries to perform the
following operations.
i.List all the details of all customers.
Select * from customer;
ii. Make cust_id as primary key
ALTER TABLE customer
ADD CONSTRAINT cust_id _pk PRIMARY KEY (cust_id);
iii. Display cust_address, cust_city, cust_state from customer table.
Select cust_address, cust_city, cust_state from customer
iv. Display cust_credit_card_no, cust_creadit_card_exp.of a customer
whose cust_id is 1.
Select cust_credit_card_no, cust_creadit_card_exp. from customer where
cust_id = 1
4. Analyze valid reasons to encrypt data stored in the database.
First valid requirement is you might be forced to comply with a regulation (legal,
industrial, or organizational directive) that states that certain classes of data
must be stored using encryption.
A second valid requirement for encryption is assurance that data can be
protected throughout its life cycle. Think about data storage as a life cycle: data
is created, stored, modified, moved,backed up, and deleted over time.
Part-B (4 X 5M=20M)
Answer ALL Questions
5. List System Privileges Granted to the Security Administrator Role.

System Privileges Granted to the Security Administrator Role as

 CREATE USER, ,ALTER USER, CREATE ROLE,GRANT ANY OBJECT
PRIVILEGE,GRANT ANY PRIVILEGE,GRANT ANY ROLE,CREATE ANY
PROCEDURE,CREATE ANY TRIGGER,CREATE ANY CONTEXT,CREATE
PROFILE, AUDIT SYSTEM, AUDIT ANY;
6. Finally, as SYS, we are going to set up some initial auditing on the
auditing trail itself demonstrate audit trail with suitable examples.
AUDIT SELECT, UPDATE, DELETE
ON sys.aud$
BY ACCESS;
When we designate BY ACCESS for auditing, we are saying that we want
detailed information. The other (possibly default) option is BY SESSION. This
gives less detail, but still audits each occurrence, rather than only providing a
single audit record per session, as in earlier releases of Oracle.
7. Identify the disadvantages in using DBMS_CRYPTO to selectively encrypt
and store highly-sensitive data in the database.
Many organizations used DBMS_CRYPTO to selectively encrypt and store
highly-sensitive data in the database, protecting the contents of the encrypted
data from the DBA. This was a difficult and costly method to protect data.
encrypt your data programmatically before inserting it into the database
(perhaps using DBMS_CRYPTO or by writing your own scheme). Key storage
is particularly challenging, because if the key were to be stored in the database
8. Identify where does the data housed within an Oracle database is stored
and illustrate the format of data file.
Data housed within an Oracle database is stored using a proprietary format that
makes efficient, high-performance data access possible. Datafiles contain
header information, extent details and offset information, and the actual
columns of data as they were inserted. It should be noted that while VARCHAR
and VARCHAR2 are human-readable within a datafile, some other TDE-
supported databases are stored using nonreadable, but reversible, methods
such as HEX.
Part-C (2 X 9M=18M)
Answer ALL Questions
9. a. Instead of granting SYSDBA, the preferred method for giving users
access to the various privileges is to grant the privileges to a role, then
grant the role to the user. Justify the statement
Instead of granting SYSDBA, the preferred method for giving users access to
the various privileges is to grant the privileges to a role, then grant the role to
the user. If you do this, you can always substitute a different user in a job, or
replicate the privileges required for a job to another user by simply granting the
existing role to the new user. (Privileges can be granted directly to users, but
we will mostly avoid that.)
For example, I can create a role named appaccess and grant the ability to read
the application tables to that role. Then I can grant that role to a user. When I
have another user who needs access to the same application, I can permit that
by granting the appaccess role to the new user as well. And when I delete the
first user, the permissions required for other users to access the application do
not disappear in the process.
I need to grant access to the application data as described previously, because
the data tables will be in a schema belonging to the application, and each user
will have her own schema. Users cannot read data in other schemas unless
that access is specifically granted to them or to a special user named PUBLIC.
Anything granted to PUBLIC is granted to all users.
b. Users cannot read data in other schemas predict the way of users
accessing data in others schema.
Users cannot read data in other schemas unless that access is specifically
granted to them or to a special user named PUBLIC. Anything granted to
PUBLIC is granted to all users.
 we need to permit any Oracle user to execute our procedure, and assure that
they are connected as appusr after the fact. We grant execute to PUBLIC
(everybody) like this:
GRANT EXECUTE ON appsec.p_check_hrview_access TO PUBLIC;
We are going to continue to have our application security user, appsec, run all
the security procedures. In order to accomplish our two-factor authentication, it
need to read the EMPLOYEES table from HR, as well as the tables were
created.
GRANT SELECT ON hr.v_employees_public TO appsec;
GRANT SELECT ON hr.v_sms_carrier_host TO appsec;
GRANT SELECT ON hr.v_emp_mobile_nos TO appsec;

(Or)
10. a. Specify the role that comes with Oracle upon installation and provide
system administrative privileges for the database. Identify the role and
summarize it.
DBA -This predefined role comes with Oracle upon installation and provides
system administrative privileges for the database; we will not grant this role to
any user.
The DBA role is named for the job it enables: database administrator. It is
similar to the SYSDBA super system privilege. Traditionally, the DBA role was
granted to those users who needed to manage the database. The DBA role is
almost as powerful as the SYSDBA privilege, but it can be modified to have
some of its privileges removed. In recent releases of Oracle, database
administrators have been discouraged from using the DBA role. Rather, they
are encouraged to create their own roles and to grant just those administrative
privileges that are required. Therefore, we will not grant DBA role to any user.
We will be creating a security administrator role, secadm_role. We will use this
role for most of our administrative actions. It will have a variety of privileges, but
only those required for the scope of this book. This approach adheres to the
concept of “least privilege,” which means providing only the privileges required
for the task at hand.
Oracle Database Vault is a product that allows you to use DBA and other
privileged roles while restricting their access. This is geared toward Department
of Defense and national security users, where a database administrator does
not necessarily have access to the data.
b. Mention the role that allows users to connect to oracle. Predict the role
and discus about the role.
CONNECT- This predefined role allows users to connect to Oracle; we are
encouraged not to use predefined roles, so we won’t. Security Administrator
must be connected directly to the Oracle Database. When a user connects to
an Oracle database, he acquires all his default roles, and all the privileges
associated with those roles. The CREATE SESSION privilege is required for a
user to connect to an Oracle database.Traditionally, this has been
accomplished through a predefined role (it exists when you install Oracle)
named CONNECT. In current releases of Oracle database, CONNECT only has
the one privilege
11. a. Write a p_check_hrview_access Procedure for that grants current user
an Secure App Role. When IP address of current user environment is like
is 192.168% or 127 and who works between 7 A.M to 6 P.M.
CREATE OR REPLACE PROCEDURE appsec.p_check_hrview_access
AUTHID CURRENT_USER
AS
BEGIN
IF( ( SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) LIKE '192.168.%' OR
SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) = '127.0.0.1' )
AND
 TO_CHAR( SYSDATE, 'HH24' ) BETWEEN 7 AND 18
)
THEN
EXECUTE IMMEDIATE 'SET ROLE hrview_role';
END IF;
END;
b. Write an SQL query that creates public view by the name
hr.v_employees_public on Employees table present in hr schema. (Hint:
employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, manager_id, department_id, Salary, Commission).
CREATE OR REPLACE VIEW hr.v_employees_public
AS SELECT
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
manager_id,
department_id
FROM hr.employees;
(Or)
12. a. Illustrate with an simple example of the Technical Requirement for
Encryption.
Let’s illustrate the point by looking at an example. We’ll base this on the
customer table described. You can see the elements that are in need of
attention:
sh@AOS> desc customer
Name Null? Type
---------------------------------------- -------- ---------------------
CUST_ID NOT NULL NUMBER(6)
CUST_FIRSTNAME VARCHAR2(20)
CUST_LASTNAME VARCHAR2(20)
CUST_ADDRESS VARCHAR2(40)
CUST_CITY VARCHAR2(15)
CUST_STATE VARCHAR2(2)
CUST_ZIP VARCHAR2(10)
CUST_CREDIT_CARD_NO VARCHAR2(19)
CUST_CREDIT_CARD_EXP VARCHAR2(4)
sh@AOS>
A look at the table shows the values of the data:
1* select * from customer where rownum < 2
sh@AOS> /
CUST_ID CUST_FIRSTNAME CUST_LASTNAME CUST_ADDRESS
---------- -------------------- -------------------- -----------------
CUST_CITY CU CUST_ZIP CUST_CREDIT_CARD_NO CUST
--------------- -- ---------- ------------------- ----
1001 David Knox 202 Peachtree Rd.
Reston VA 20190 5466-1112-2233-9342 1008
As is shown here, the credit card number and expiration data are clearly visible
within the context of the database (as they should through SQL), so we know
exactly what we are looking for in the datafile. Look closely at the datafile
(customer_info.dbf in the example code) and you can pick out the good bits of
data using commonly available editors or with operating system tools such
as grep or strings.
b. Write an SQL query to encrypt information present in a column called
credit_card_no present in Customer table and analyze the keywords
present in the Query.[Hint: Columns in customer table include cust_id,
cust_name, cust_address,cust_city,cust_state, credit_card_no,
creditcard_exp_date].

CREATE TABLE sh.customer_enc (

2 cust_id NUMBER(6) PRIMARY KEY,
3 cust_firstname VARCHAR2(20),
4 cust_lastname VARCHAR2(20),
5 cust_address VARCHAR2(40),
6 cust_city VARCHAR2(15),
7 cust_state VARCHAR2(2),
8 cust_zip VARCHAR2(10),
9 cust_credit_card_no VARCHAR2(19) encrypt using 'AES192',
10 cust_credit_card_exp VARCHAR2(4)
11 )
12 TABLESPACE customer_info_protected
13 /
At this point, by reinserting the data into the newly created table, customer_enc,
you can validate that the credit card numbers are protected. here we encrypted
the data Cust_credit_card_no using AES algorithm with key size as 192 for the
column.
SELECT owner, table_name, column_name, encryption_alg, salt FROM dba_encrypted_columns;
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SALT
------- ------------------------- ------------------- ------------------ -----
SH CUSTOMER_ENC TABLE_TYPE AES 192 bits key YES
ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT ['nomac'] [no salt]);
Alter table foo modify (columnA datatype encrypt);
You should consider when encrypting an existing column.we mentioned that
adding a MAC to the encrypted data adds 20 bytes; including a salt will add a
16 bytes, and the encryption algorithm itself adds a few bytes per column as it
“pads” data to create evenly sized blocks of data to encrypt.