1

CHAPTER 4. DATABASE
SECURITY
 Outline
2

 Introduction
 Database Security Basics
 Oracle User Management
 Granting and Revoking Authority
 Types of Privileges
 GRANTing Table Privileges
 GRANTing Database Object Privileges
 GRANTing System Privileges
 GRANTing Program and Procedure Privileges
 GRANTing to PUBLIC
 REVOKing Privileges
 Cascading REVOKEs
 Roles
 Oracle Roles Management
 ORACLE Profiles Management
 Introduction
3

 There are no default authorizations given to any user

just because the user can log in to the DBMS.
 For a user to be able to perform any DBMS operation
or function, one of the following conditions must be
satisfied:
 The user has been granted the ability to perform that
function or operation, or
 That operation or function has been granted generically to
all users
 All database resources are controlled by the DBMS.
 The DBA typically is responsible for administering
database security.
 Database Security Basics…
4

 Before authorizing a user using the DB resources, (s)he

needs to be enrolled in the DBMS: Create an account
for the user
 A user account is username and password.
 A user account has an identifier ID generated by the
DBMS
 Some DBMSs use the operating system login ID and
password as the DBMS login ID and password; others
require an additional login ID and password to be
created specifically for DB access and security.
 Database Security Basics…
5

 The DBA is required to provide certain information

about the account when it is created:
 Default database: Name of the DB to which the user will be
able to connect (login).
 Default language: language assigned to the login when
using the DBMS if multiple languages are supported.
 Name: the actual full name of the user associated with this
login.
 Additional details may be specified: e-mail, phone number,
office location, business unit, and so on. This is useful for
documentation purposes.
 Database Security Basics…
6

 Passwords should be changed regularly over time

to make it difficult for hackers and miscreants
(criminals) to gain access to the DBMS
surreptitiously1.
 DBMS may provide an option to lock the login.
Locking a login prohibits the user from accessing
the DBMS, but it does not actually drop the login
from the system.

 (1) ‫خلسة‬
 Database Security Basics
7

 Some DBMSs provide additional controls on logins

and passwords.
 Example:
 Number of failed login attempts before the account is
locked
 Number of days a password is valid, the grace period for
changing an expired password
 Number of days the account can remain locked when the
password expires
 Reusability of passwords (number of days before a
password can be reused and the maximum number of times
a password can be reused)
 Oracle User Management: CREATE
8

CREATE USER UserName IDENTIFIED { BY password|

EXTERNALLY }
[ DEFAULT TABLESPACE TablespaceName
[QUOTA { integer [ K | M ] | UNLIMITED } ON
TablespaceName ] ]
[TEMPORARY TABLESPACE TablespaceName
[QUOTA {integer [ K | M ] | UNLIMITED } ON
TablespaceName].]
[PROFILE ProfileName ]
[PASSWORD EXPIRE ]
[ACCOUNT { LOCK |UNLOCK } ] ;
Example
CREATE USER user1 IDENTIFIED BY pw1;
After Login, what user1 can do ?
 Oracle User Management: ALTER
9

ALTER USER UserName

IDENTIFIED { BY password| EXTERNALLY }
[ DEFAULT TABLESPACE TablespaceName
[TEMPORARY TABLESPACE TablespaceName
[QUOTA {integer [ K | M ] | UNLIMITED } ON
TablespaceName ].]
[PROFILE ProfileName ]
[DEFAULT ROLE {role[, role, …]
| ALL | ALL EXCEPT role[,role [,role,…]
| NONE }
[PASSWORD EXPIRE ]
[ACCOUNT { LOCK |UNLOCK } ] ;
Example
ALTER USER user1 IDENTIFIED by Newpw1;
 Oracle User Management: DROP
10

DROP USER UserName [ CASCADE];

CASCADE : If the user owns objects (he has created), the

CASCADE option becomes Mandatory otherwise Oracle reject
DROPping the user.
Example
DROP USER user1 CASCADE;

The user is removed from the DD with all his objects

(tables, views, Synonyms, indexes, stored procedures…).
 Granting and Revoking Authority
11

 The DBA uses Data Control Language (DCL) to

control DB and authorization.
 DCL statements are used to control which users have
access to which objects and commands.
 DCL statements comprise two basic types:
 GRANT: Assigns a privilege (permission) to a DB user.
 REVOKE: Withdraw a privilege from a DB user.

So what are the privileges for a DB user?

 Types of Privileges
12

 A DBMS provides privileges to access data, create DB objects...

 Table privileges are granted to enable users to access tables,
and views.
 Types of privileges commonly provided by modern DBMSs:
 Table privileges: to control who can access and modify the data within
tables or views
 Database objects privileges : to control who can create and drop DB
objects
 System privileges: to control who can perform certain types of system
wide activities
 Program privileges: to control who can create, modify, and use DB
programs
 Stored procedure privileges: to control who can execute specific functions
and stored procedures
 GRANTing Table Privileges
13

GRANT {TablePrivilege | RoleName | ALL PRIVILEGES }

[ (Col1 [,Col2]… )]
[, {TablePrivilege | RoleName | ALL PRIVILEGES } ] [ (Col1
[,Col2]… )]…
ON ObjectName
TO { UserName | RoleName| PUBLIC } [, {UserName |
RoleName | PUBLIC } ] …
[ WITH GRANT OPTION ] ;
Example: To enable user1 to delete rows from the EMP table:
system - GRANT DELETE on EMP to user1;
System - GRANT UPDATE on EMP to user1 WITH GRANT OPTION;
User 1 can do this – GRANT UPDATE on EMP(Salary) to user2;
If user1 write – GRANT DELETE on EMP to user2;
GRANT ALL PRIVILEDGES on EMP to PUBLIC;
 GRANTing Table Privileges
14

 TablePrivilege:
 SELECT: enables the user to execute SELECT from the table/view

 INSERT: enables the user to INSERT rows into the table/view

 UPDATE: enables the user to UPDATE the table/view

 DELETE: enables the user to DELETE rows from the table/view

 ALL PRIVILEGES : enables the user all privileges to SELECT,

INSERT, UPDATE, and DELETE on the table/view
 Col : columns concerned with the privilege,
 WITH GRANT OPTION : authorizes the receiver of the privilege to
retransmit the received privilege to other users/roles.
 PUBLIC: The object becomes accessible by ALL users (enrolled now or
in the future).
 GANTing Table Privileges
15

 Some table privileges can be specified at the

column level. Doing so can be desirable when
certain users must be permitted to modify specific
columns of a table but not other columns.
 The SELECT and UPDATE privileges can be granted
for specific columns.
 Example: to enable user1 to update only the JOB
column in the EMP table,

GRANT UPDATE on EMP(JOB) to user1;

 GANTing Database Object Privileges
16

 Database object privileges control which users have

the permission to create database structures.
 Generally, the DBMS will provide options to grant
CREATE privileges on each type of database
object, including databases, tablespaces, tables,
indexes, triggers, defaults, and user-defined data
types.
 GANTing Database Object Privileges
17

GRANT {SystemPrivilege | RoleName | ALL

PRIVILEGES }
[, {SystemPrivilege | RoleName | ALL PRIVILEGES
}]…
TO { userName| RoleName | PUBLIC } [,{userName
|RoleName
| PUBLIC } ]…
[ IDENTIFIED BY Password]
[ WITH ADMIN OPTION ] ;
Examples of SystemPrivilege:
CREATE TABLE,
CREATE SESSION,
etc.
 GANTing Database Object Privileges
18

 Example: To enable user1 to create tables and

indexes

GRANT CREATE TABLE, CREATE INDEX TO user1;

 GANTing System Privileges
19

 System privileges control which users can use certain

DBMS features and execute certain DBMS commands.
 The system privileges available will vary from DBMS
to DBMS but may include the ability to archive
database logs, shut down and restart the database
server, start traces for monitoring, manage storage,
and manage database caches.
 Example: To enable user1 to start performance traces
GRANT TRACE TO user1;
 GANTing Program and Procedure Privileges
20

 Granting the EXECUTE privilege gives the user

permission to execute a program or a stored
procedure.
 Example: To enable user3 and user4 to execute the
stored procedure named proc1,

GRANT EXECUTE ON proc1 TO user3, user4;

 GANTing to PUBLIC
21

 DBA can choose to GRANT a particular

authorization to PUBLIC.
 When authorization is granted to PUBLIC, the
DBMS will allow anyone who can log in to the DBMS
that particular authority.
 Grants made to PUBLIC cannot be given with the
WITH GRANT OPTION, as everyone is in PUBLIC.
 Example: To grant everyone the authority to
DELETE rows from the EMP table,
GRANT DELETE ON emp TO PUBLIC;
 GANTing to PUBLIC
22


Whenever a privilege is granted to PUBLIC, the DBA
loses control over that database object or resource:
anyone can access or use the object or resource as
specified by the GRANT statement.
 REVOKing Privileges
23

 The REVOKE statement is useful to remove

privileges that were previously granted.
 Example: To revoke the ability to update the JOB
column of the EMP table from user1,
REVOKE UPDATE ON emp(job) from user1;
 Revoking a PUBLIC privilege will not remove
that privilege from any user to whom it was granted in
a separate GRANT statement.
 Cascading REVOKEs
24

 When privileges are revoked, the DBMS must

decide whether additional revokes are necessary,
based on the privileges being revoked.
 When one revoke causes the DBMS to revoke
additional related privileges, it is called cascading
REVOKEs.
 Cascading REVOKEs Example
25

 Consider the authority hierarchy depicted in the figure below:

 Joe has been granted the ability to grant a privilege, say X, to

others.
 Joe grants X to Pete and Phil with the GRANT option.
 Pete in turn grants X to Bruce.
 Joe also grants X to Don, but without the GRANT option.
 Cascading REVOKEs
26

 if we revoke the X privilege from Joe. In this case, not

only will Joe no longer have the X privilege, but the
DBMS will also remove the authority from Pete, Phil, and
Don. Furthermore, because Phil's X privilege was revoked,
the effect of the revoke will cascade to Bruce, too.
 To minimize the impact of cascading REVOKEs, avoid
granting privileges using the WITH GRANT OPTION.
 The EXCEPT option
27

 In some DBMSs it is possible to GRANT a privilege to

all users except a specific user. Oracle offers the
EXCEPT clause as an option for the GRANT
statement:
GRANT DELETE ON titles TO PUBLIC EXCEPT
user1;
 This statement grants everyone the authority to
DELETE data from the TITLES table. Because of the
EXCEPT clause, the individual user1 is barred from
deleting data from this table.
 Roles
28

 A role is essentially a collection of privileges.

 The DBA can create a role and assign certain
privileges to that role.
 Then the role can be assigned to one or more users.
 Using roles, the administration of database security
is simplified.
 Oracle role can be enabled or disabled, and may
have a password on activation.
 Roles benefits
29

 Granting privileges
without role with role
Users

Privileges

Grant one by one Grant the role, use has all

access for all task in a role
 Oracle Roles Management
30

 Create
CREATE ROLE name_role
[ NOT IDENTIFIED ]
[ IDENTIFIED {BY password | EXTERNALLY }];
 Creates an EMPTY role
GRANT: assigns privileges to a role
 Modify
ALTER ROLE name_role
[ NOT IDENTIFIED ] [ IDENTIFIED BY password ]
 The role is withdrawn from all users and from all other roles where it was
assigned
 Activation
SET ROLE name_role [ IDENTIFIED BY password ]
 Oracle Roles Management
31

 In addition to granting privileges to individual users,

the DBMS may provide the capability to assign:
 Privileges to a role, which is then GRANTED to
others
 Specific Built-in (standard) groups of privileges to
users
 Roles
32

Example
This script CREATEs a role named MANAGER, GRANTs privileges on
certain tables and procedures to the role, and then assigns user1
the MANAGER role. Additional users can be assigned the
MANAGER role later.
CREATE role MANAGER;
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO
MANAGER;
GRANT SELECT, INSERT, UPDATE, DELETE ON job_title TO
MANAGER;
GRANT EXECUTE ON payroll TO MANAGER;
GRANT MANAGER TO user1;
 CREATE ROLE
33

Specify NOT IDENTIFIED to indicate that this role is authorized by the database and that no password is required to enable the role.

Use the IDENTIFIED clause to indicate that a user must be authorized by the specified method before the role is enabled with the SET ROLE statement

If you omit both the NOT IDENTIFIED clause and the IDENTIFIED clause, then the role defaults to NOT IDENTIFIED.

The BY password clause lets you create a local role and indicates that the user must specify the password to the database when enabling the
role.

The USING package clause lets you create an application role, which is a role that can be enabled only by applications using an authorized
package. If you do not specify schema, then the database assumes the package is in your own schema.
Specify EXTERNALLY to create an external role. An external user must be authorized by an external service, such as an operating system or
third-party service, before enabling the role.

Specify GLOBALLY to create a global role. A global user must be authorized to use the role by the enterprise directory service before the role is
enabled at login.
34

CREATE ROLE dw_manager;

CREATE ROLE dw_manager

IDENTIFIED BY warehouse;

CREATE ROLE warehouse_user IDENTIFIED GLOBALLY;

CREATE ROLE warehouse_user IDENTIFIED EXTERNALLY;

35

ALTER ROLE warehouse_user NOT IDENTIFIED;

ALTER ROLE dw_manager

IDENTIFIED BY data;

ALTER ROLE dw_manager IDENTIFIED USING [Link];

36

Specify ALL to enable all roles granted to you for the current session except those optionally listed in
the EXCEPT clause.

Specify NONE to disable all roles for the current session, including the DEFAULT role.

SET ROLE dw_manager IDENTIFIED BY warehouse;

SET ROLE ALL;

SET ROLE ALL EXCEPT dw_manager;

SET ROLE NONE;

 GRANT ROLE to user
37

grant_system_privileges::=

grant_object_privileges::=

[Link]
38

CREATE USER jward ALTER USER avyrros

IDENTIFIED BY AZ7BC2 IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts TEMPORARY TABLESPACE temp_ts
QUOTA 500K ON data_ts QUOTA 100M ON data_ts
TEMPORARY TABLESPACE temp_ts QUOTA 0 ON test_ts
PROFILE clerk; PROFILE clerk;
GRANT create session TO jward;
 Groups
39

 Group-level authority or Group, is similar to roles.

 Each DBMS provides built-in Groups that cannot be
changed.
 Each DBMS implements group-level database
security in different ways and with different group
names and privileges. However, there are some
similarities across DBMSs.
 Groups
40

ORACLE Groups (called predefined roles) are:

◼ CONNECT: enables to create a session (log in)
◼ RESOURCE : = CONNECT + use the system resources
◼ DBA : All system privileges for the DB administration with
possibly GRANTing them.
◼ EXP_FULL_DATABASE ( DBA ) : DB exportation.
◼ IMP_FULL_DATABASE ( DBA ) : DB importation.
The system views DBA_ROLES, DBA_ROLE_PRIVS,
ROLE_ROLE_PRIVS... Are used to access these groups

[Link]
rk.102/b14266/[Link]#i1009127
 Limit the Number of ADMINs
41

 A single organization should limit the number of

users who are assigned the DBA role.
 A user with DBA capabilities is very powerful. Only
corporate DBAs and systems programmers should
be granted this level of authority.
 End users, managers, and application development
personnel do not need DBA authority to do their
jobs.
 Group-Level Security and Cascading REVOKEs
42

Same cascading logic applies as with REVOKEs

that occur as a result of the WITH GRANT
option; i.e.,
 Depending on the group, some users who have
been assigned group-level privileges can grant
privileges to other users. If the group-level
authority is revoked from that user, any
privileges that user has granted will also be
revoked.
 ORACLE Profiles …
43

The word profile refers to a collection of attributes that apply to a user, enabling a single point of
reference for any of multiple users that share those exact attributes. User profiles in Oracle
Internet Directory contain a wide range of attributes pertinent to directory usage and
authentication for each user. Similarly, profiles in Oracle Label Security contain attributes useful
in label security user administration and operations management.

 A profile enables controlling users activities on the DB:

 At a Session level
◼ Number of sessions per user (SESSIONS_PER_USER)
◼ Log in Duration (CPU_PER_SESSION)
◼ Idle time acceptable (IDLE_TIME)
◼ Max bytes occupied in the SGA,… (PRIVATE_SGA), …
 At each usage of the DB (execute SQL command)
◼ CPU Time (CPU_PER_CALL )
◼ Max number of accessed blocks (LOGICAL_READS_PER_CALL)
 Password Management
◼ Password life duration (PASSWORD_LIFE_TIME )
◼ Max Number of its reuse,… (PASSWORD_REUSE_MAX…)
 ORACLE Profiles …
44

 A profile enables a group of limits on resources:

 Identified with name
 Associated to a user to control his activities
 A default profile called DEFAULT is assigned to the user
when created
 When a user reaches the limit of resources assigned to him,
Oracle stops the execution of the operation, rollback the
transaction and raises an error
 Views associated with profiles:
 DBA_PROFILES, DBA_USERS
 ORACLE Profiles Management
45

❑ CREATE PROFILE
CREATE PROFILE name_profile LIMIT
[SESSION_PER_USER value]
[CPU_PER_SESSION value] -- in seconds/100
[CPU_PER_CALL value] -- in seconds/100
[CONNECT_TIME value] -- in mn
[IDLE_TIME value] -- in mn
[LOGICAL_READS_PER_SESSION value]
[LOGICAL_READS_PER_CALL value]
[COMPOSITE_LIMIT {integer [K|M]| UNLIMITED | DEFAULT}]
[PRIVATE_SGA value]
[FAILED_LOGIN_ATTEMPTS value] -- Nb of attempts before blocking
[PASSWORD_LIFE_TIME value]
[{PASSWORD_REUE_TIME | PASSWORD_REUE_MAX} value]
[PASSWORD_LOCK_TIME value]
[PASSWORD_GRACE_TIME value]
[PASSWORD_VERIFY_FUNCTION value];

Where value := {integer | UNLIMITED | DEFAULT}

46

CREATE PROFILE clerk LIMIT

SESSIONS_PER_USER 1
IDLE_TIME 30
CONNECT_TIME 600;

CREATE USER jfee

IDENTIFIED BY wildcat
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp_ts
QUOTA 500K ON users
PROFILE clerk;

CREATE USER dcranney

IDENTIFIED BY bedrock
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp_ts
QUOTA unlimited ON users;

CREATE USER userscott

IDENTIFIED BY scott1;
47

Account Locking

CREATE PROFILE prof LIMIT

FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 30;
ALTER USER johndoe PROFILE prof;

The maximum number of failed login attempts for the user johndoe is four, and the amount of time the
account will remain locked is 30 days. The account will unlock automatically after the passage of 30
days.

ALTER USER johndoe ACCOUNT UNLOCK;

ALTER USER susan ACCOUNT LOCK;

48

Password Aging and Expiration

CREATE PROFILE prof LIMIT

FAILED_LOGIN_ATTEMPTS 4 The profile assigned to johndoe includes the specification of
PASSWORD_LOCK_TIME 30 a grace period: PASSWORD_GRACE_TIME = 3. The first
PASSWORD_LIFE_TIME 90; time johndoe tries to log in to the database after 90 days (this
can be any day after the 90th day, that is, the 70th day,
ALTER USER johndoe PROFILE prof; 100th day, or another day), he receives a warning message
that his password will expire in three days. If three days
pass, and he does not change her password, then the
CREATE PROFILE prof LIMIT password expires. After this, he receives a prompt to change
FAILED_LOGIN_ATTEMPTS 4 his password on any attempt to log in, and cannot log in
PASSWORD_LOCK_TIME 30 until he does so.
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 3;
ALTER USER johndoe PROFILE prof;
 ORACLE Profiles Management
49

❑ DROP PROFILE

DROP PROFILE name_profile [ CASCADE ];

CASCADE deletes the profile for all concerned users and

replaces it with the DEFAULT profile;
 Other Database Security Mechanisms
50

 Views and stored procedures can be used for

security purposes, even though that is not their main
purpose.
 Database Triggers are very powerful for security
issues that could not be specified declaratively (i.e.,
with SQL declarative statements)
 Using VIEWS for Security
51

 A view is a window on the database; it enables to

view (access) a subset (i.e., a part) of the database
data necessary for a user/group of users.
 It is possible to simplify some aspects of DB security
by creating views to protect your data: Only
persons authorized to use the view will be able to
access the data.
 Using VIEWS for Security…
52

 Assume your organization has deployed an

employee table EMP(…, Fname, Lname,
middle_initial, address, telephone, salary,
…Deptno).
 Granting the SELECT privilege on EMP to a group
of users can cause a security problem. While
application security is maintained with this scenario,
personal security is not, because the user could
access the personal details, including salary
information, of fellow employees.
 Using VIEWS for Security…
53

 A view could be created that hides the sensitive information

from the EMP table.
 By creating the view with excluding the sensitive columns, users
can be granted the SELECT privilege on the view and will be
able to access employee’s information that is deemed*
appropriate.
 Example
CREATE VIEW V_emp AS
SELECT fname, lname, middle_initial,
address, state, zip_code
FROM emp;
❑ The information in salary and telephone columns are hidden.
* Considered, estimated
 Using VIEWS for Security…
54

 This example shows a view that specifies only certain

columns from the base table: This is called Vertical
restriction. Sal

 Once the user has been granted the SELECT privilege

on the view, only the information specified in the view
columns can be retrieved.
 Using VIEWS for Security…
55

 Views can also be used to provide row-level security based on the content
of data. This is called horizontal restriction
 Horizontal restriction is implemented by coding the appropriate WHERE
clauses into the view. Dept
no

 Example
20

CREATE view emp_dept20

AS
20
SELECT fname, lname, middle_initial,
address, state, zip_code
FROM emp
WHERE deptno = 20;
❑ When users select from the view, only rows that match the predicate will be
returned. This view will return only those employees who work in
department 20.
 Oracle CREATE VIEW command
56

CREATE view View_Name (col1NAme, col2Name, …..)

AS
SELECT exp1, exp2, ….
FROM Table_Name
WHERE Condition
WITH GRANT OPTION];

❑ Vertical restriction (select)

❑ Horizontal restriction (Where)

The WITH GRANT OPTION option means that ……

Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles.

You can specify WITH GRANT OPTION only when granting to a user or to PUBLIC, not when granting to a role.
 Using Stored Procedures for Security
57

 Stored procedures can be used to provide an

additional level of security.
 Stored procedures can be coded that access only
row- and/or column-level subsets of data. The
ability to execute these stored procedures can then
be granted to users.
 Using Synonyms for Security
58

 Synonyms can be used to provide an additional level of

security.
 A synonym hides the physical name of the object
 A synonym is an alternate logical name for a DB object
(table, view, sequence…).
 CREATE SYNONYM name_Synonym FOR obj_name;
 Examples
CREATE SYNONYM EMPLOYEES FOR EMP;
Creates a synonym for the EMP table;
CREATE SYNONYM PERSONS FOR EMPLOYEES
Creates a synonym for the synonym EMPLOYEES;
 Summary
59

 The DB security issues are considered through several

mechanisms offered by DBMSs:
 User name and passwords
 Tables Privileges: ……………………………………………….
 System Privileges: ……………………………………………….
 Roles: …………………………………………………………….
 Groups: (standard roles created by Oracle at the Installation)
 Profiles: limit the usage of resources
 Views, Stored procedures, and DB triggers
 Creating Synonyms or Renaming DB Objects
60

End of Chapter