Database Security

View

View Retrieval/ Update

• This Oracle CREATE VIEW example would create a virtual table based on the result set of the SELECT statement. You can now query the Oracle VIEW as follows:

select * from a_view;

• You can modify the definition of an Oracle VIEW without dropping it by using the Oracle CREATE OR REPLACE VIEW Statement

​

​

​

​

• create or replace will update view if view exists, if doesn’t then it creates a view

Example: create or replace view a_view1 as select sid from a_student where sid>=102;

​

​

example

CREATE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)

AS

SELECT employee_id, last_name, salary*12

FROM employee WHERE department_id = 50;

​

Drop View

• To drop a created view
• drop view view_name;

Example: drop view a_view1;

​

Question: Can you update the data in an Oracle VIEW?

Answer: A VIEW in Oracle is created by joining one or more tables. When you update record(s) in a VIEW, it updates the records in the underlying tables that make up the view. So, yes, you can update the data in an Oracle VIEW providing you have the proper privileges to the underlying Oracle tables.

​

​

​

​

Question: Does the Oracle View exist if the table is dropped from the database?

Answer: Yes, in Oracle, the VIEW continues to exist even after one of the tables (that the Oracle VIEW is based on) is dropped from the database. However, if you try to query the Oracle VIEW after the table has been dropped, you will receive a message indicating that the Oracle VIEW has errors.

If you recreate the table (the table that you had dropped), the Oracle VIEW will again be fine.

​

Materialized View

• Materialized view (MV) is indirect access to table data by storing the result of a query in a separate schema object.
• It can be stored in the same db or in different db.
• MV stored in same db as their master tables can improve performance through query rewrite. For queries that involve aggregate data or join the optimizer can rewrite the query to access the precomputed results stored in MV.
• Query rewrite is particularly useful in data warehouse.
• Another name of mv is snapshot.
• MV are schema object that is used to summarize, precompute, replicate, distribute data.
• Suitable in data warehouse, decision support, distributed & mobile computing.
• They consume storage
• Must be refreshed when data in master table changes.
• Their existent is transparent to sql application & user.
• Having limitation: support for incremental refresh.

​

Differentiate view and materialized view

Limitation & Syntax of Materialized View

• Support for incremental refresh is limited.
• Syntax

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

AS

SELECT ...;

• The BUILD clause options are shown below.

IMMEDIATE : The materialized view is populated immediately.

DEFERRED : The materialized view is populated on the first requested refresh.

• The following refresh types are available.

FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.

COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.

FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

• A refresh can be triggered in one of two ways.

ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.

ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

• The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations.
• The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query

Create materialized view

• Example: create materialized view a_mv1 build immediate refresh fast on commit as select sid from a_student;

Error: ORA-01031: insufficient privileges, on a_student

​

It's easier to GRANT or REVOKE privileges to the users through a role rather than assigning a privilege directly to every user. If a role is identified by a password, then, when you GRANT or REVOKE privileges to the role, you definitely have to identify it with the password.

​

​

​

Authentication vs Authorization

Authorization:

• Privileges
• System privileges

Each system privilege allows a user to perform a particular database operation or class of database operations; for example, the privilege to create tablespaces is a system privilege.

• Object privileges

Each object privilege allows a user to perform a particular action on a specific object, such as a table, view, sequence, procedure, function, or package

​

• Roles

​

• Storage setting and Quotas ( Default Tablespace, Temporary Tablespace

Tablespace Quotas, Profiles and Resource Limits )

System Privileges

Syntax

GRANT {system_privilege|role}

[, {system_privilege|role} ]...

TO {user|role|PUBLIC}

[, {user|role|PUBLIC} ]...

[WITH ADMIN OPTION]..

​

REVOKE {system_privilege|role}

[, {system_privilege|role} ]...

FROM {user|role|PUBLIC}

[, {user|role|PUBLIC} ]...

​

​

Object Privileges

GRANT { object_privilege [(column_list)]

[, object_privilege [(column_list)] ]...

|ALL [PRIVILEGES]}

ON [schema.]object

TO {user|role|PUBLIC}[, {user|role|PUBLIC} ]...

[WITH GRANT OPTION]

​

Example

Grant select on scott.emp to jeff with grant option

​

Revoke Priviledge

REVOKE { object_privilege

[, object_privilege ]...

| ALL [PRIVILEGES] }

ON [schema.]object

FROM {user|role|PUBLIC}

[, {user|role|PUBLIC} ]...

[CASCADE CONSTRAINTS]

​

ROLES

Benefits of Roles�

• Easier privilege management
• Dynamic privilege management
• Granting through the operating system

CREATE ROLE role

Assign privilege to role

Assign role to user or roles

Data Encryption

• Data encryption is the act of changing electronic information into an unreadable state by using algorithms or ciphers.
• Over time as the public has begun to enter and transmit personal, sensitive information over the internet, data encryption has become more widespread

​

RSA

• The RSA algorithm, introduced in 1977 by Rivest, Shamir, and Adlemen, is an algorithm for public-key cryptography.
• RSA was the first and is still the most widely-used algorithm for public key cryptography and it is used for thousands of applications from e-mail encryption to secure online purchasing.
• It was the first cryptosystem to enable senders to “sign” each message they send so that the recipient has proof of who sent the message.

RSA (Choosing Public / Private Key)

• Pick two large (100 digit) primes p and q.

​

• Let n = p*q

​

• Select a relatively small integer e that is prime to Phi ϕ(n) = (p-1)(q-1) ,

1 < e < ϕ(n)

​

• Find d, the multiplicative inverse of e that is relatively prime to the Phi

That is inverse of public key

[e* d mod [ϕ(n) = 1]

​

RSA …..

• (e,n) is the public key. To encrypt M, compute
• En(M) Cipher = M^e(mod n)

​

• (d,n) is the private key. To decrypt C, compute
• De(C) Pt = C^d(mod n)

​

• Let p = 11, q = 5
• n = pq = 55
• phi(n)= (p-1)(q-1) = 40 == { 3, 7 , 9, 11…}
• Possible e:, … (let’s use 7)
• Find d: 7*d (mod(40)) = 1 .(apply Euclidean algo.)
• Public key: (7, 55)
• Private key: (23, 55)
• Message=3
• En(42) = 3^7 (mod 55) = 42
• De(81) = 42^23(mod 55) = 3

​

• Let p = 11, q = 13
• n = pq = 143
• phi(n)= (p-1)(q-1) = 120 = 3 x 23 x 5
• Possible e: 7, 11, 13, 17, … (let’s use 7)
• Find d: 7*d = 1(mod 120) = 103 e*d=1 mod phi(n)
• Public key: (7, 143)
• Private key: (103, 143)
• Message=h=7
• En(42) = 7^7 (mod 143) = 6
• De(81) = 6^103(mod 143) = 7

​

Strengths of RSA

• No prior communication needed
• Highly secure (for large enough keys)
• Well-understood
• Allows both encryption and signing

​

• The security of the RSA algorithm and messages encrypted using the algorithm relies on the difficulty of factoring the value of n. If n could be easily factored into the corresponding values of p and q, then one could easily find the value of d.
• The RSA Assumption is that the RSA Problem is hard to solve when n is sufficiently large and randomly generated.

​

Missing Information

Sometimes we don’t know what value an entry in a relation should have

• We know that there is a value, but don’t know what it is
• There is no value at all that makes any sense

Two main methods have been proposed to deal with this

• NULLs can be used as markers to show that information is missing
• A default value can be used to represent the missing value

​

NULL’s

• NULL is a placeholder for missing or unknown value of an attribute. It is not itself a value.
• Codd proposed to distinguish two kinds of NULLs:
• A-marks: data Applicable but not known (for example, someone’s age)
• I-marks: data is Inapplicable (telephone number for someone who does not have a telephone, or spouse’s name for someone who is not married)

​

Problems with NULLs

• Additional problems for SQL: do we treat NULLs as duplicates? Do we include them in count, sum, average and if yes, how? How do arithmetic operations behave when an argument is NULL?

​

Theoretical solutions

• Use three-valued logic instead of classical two-valued logic to evaluate conditions.
• When there are no NULLs around, conditions evaluate to true or false, but if a null is involved, a condition will evaluate to the third value (‘undefined’, or ‘unknown’ or ‘unk’).

​

• Default values are an alternative to the use of NULLs
• If a value is not known a particular placeholder value - the default - is used
• These are actual values, so don’t need 3VL etc.

​

Boolean Operators

3 Value Logic-3VL

For example A=3, B=4 and C=unk

Check

1. a>b and b>c= 3>4 and 4>unk=f and unk=f
2. a>b or b>c=3>4 or 4 > unk=f or unk=unk
3. A<b or b<c=3<4 or 4<unk=t or unk=t
4. not(A=c)=not(3=unk)=not(unk)=unk

​

Maybe Boolean Operator

Exists expression

• If r is a relation with tuples t(1),t(2),…,t(m)
• V is a range variable that range over r
• P(v) is a Boolean expression in which v occurs as a free variable
• Then the expression:::

Exists V(p(v)) is defined to be equivalent to False or (p(t1) or p(t2) or… or p(tm))

Exists (Marital_status=Married)

=f or (t or unk or f)

=f or t

=t

Exists (Maybe((Marital_status=Married) ))

=f or (maybe(t) or maybe(unk) or maybe(f))

=f or (f or t or f)

=f or t

=true

ForAll expression

• If r is a relation with tuples t(1),t(2),…,t(m)
• V is a range variable that range over r
• P(v) is a Boolean expression in which v occurs as a free variable
• Then the expression:::

ForAll V(p(v)) is defined to be equivalent to true and (p(t1) and p(t2) and… and p(tm))

Forall (Marital_status=Married)

=t and (t and unk and f)

=t and f

=f

IS_UNK operator

It takes a single scaler operand and returns true if that operand evaluates to unk otherwise false

Exists (IS_UNK(name)

=f or (f or f or t)

=t

Forall(IS_UNK(name)

=t and (f and f and t)

=f

IF_UNK operator

IF_UNK operator takes 2 scaler operands and return the value of 1^st operand unless that operand evaluates to unk

IF_UNK(exp1,exp2)= IF_UNK(exp1) then return exp2 else exp1;

Note: exp1 & exp2 must be of same type

Example:::: IF_UNK k(name,’no name’)

Whenever name is unknown ‘no name’ will be the default value

UNK== unk ?

UNK= the value unknown

unk=the unknown truth value

X is a boolean variable can have values like true, false or unk.

“x is unk”= value of x is known to be unk

“x is UNK”= value of x is not known

​

Example1

1. EXISTS V( V. SCHOLARSHIP_AMOUNT=unk)
2. FORALL V( (V.STU_ID!=3) && (V.ELECTIVE_SUBJECT !=.NET))
3. EXISTS V (IS_UNK(V.ELECTIVE_SUBJECT))
4. FORALL V(V.STU_ID <10 OR V.STU_ID>=0)
5. EXISTS V(MAYBE (IS_UNK(V.STU_NAME)))

Solution1

1. EXISTS V( V. SCHOLARSHIP_AMOUNT=unk)= F 0R (F OR T OR F OR T OR T OR F)=T
2. FORALL V( (V.STU_ID!=3) && (V.ELECTIVE_SUBJECT !=.NET))= T AND ((T AND T) AND (T AND F) AND (F AND F) AND (T AND T ) AND (T AND UNK ) AND (T AND F ))= T AND T AND F AND F AND T AND UNK AND F =F
3. EXISTS V (IS_UNK(V.ELECTIVE_SUBJECT))=F OR F OR F OR F OR F OR T OR F =T
4. FORALL V(V.STU_ID <10 OR V.STU_ID>=0)= T AND ((T OR T)AND (T OR T) AND (T OR T) AND (T OR T) AND (T OR T) AND (T OR T))=T AND T AND T AND T AND T AND T AND T =T
5. EXISTS V(MAYBE (IS_UNK(V.STU_NAME)))=F OR (MB(F) OR MB(F) OR MB(F) OR MB(T) OR MB(F) OR MB(T))=F OR F OR F OR F OR F OR F OR F =F

​

Example2

a) EXISTS(maybe(V.Branch_id=B003))

b) FORALL V (maybe(city=unk) or is_unk(Emp_head))

c) EXISTS V(is_unk(Branch_Name))