Advanced Databases 1

DBA: Security

Advanced Databases 2

Agenda

• Understand the need for security.• Learn about System Permissions

and Object permissions.• Understand the purpose of Roles.• Explore Security-focused SQL.

Advanced Databases

Guide to Oracle 10g

3

Data Admin –vs- DB Admin

Data Administration

• Owns the DATA• High-level function• Policy & Procedure

Planning• DBMS independent• Strategic• Maps out Security

Plans

Database Administration

• Owns the Database• Technical function• Policy &Procedure

Enforcement• DBMS dependent• Tactical• Carries out Security

Plans

Advanced Databases

Guide to Oracle 10g

4

Data Admin Matrix

• Data Security documentation tool.• Crafted by DA, Carried out by DBA.

Role Object(s) Permissions

OrderEntry

OrdersOrder Items

Insert, Read

Credit Approval

Customers Update

Advanced Databases 5

Security Explained via Cheezy Graphic

UserSystemPrivilege

RoleObject

Advanced Databases 6

Specifying System Privileges

• System privilege– Right to perform a specific action– DBA grants system privileges to users or roles– Use System Privileges page in Create User

page

• Object privilege– Associated permissions of an object– DBA or users grant to users or roles

• Admin Option- gives user permissions to grant rights.

Advanced Databases 7

System Privileges Reference

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm#sthref8513

Advanced Databases 8

System Privileges Example

Let’s check out OEM and do a demo.GRANTREVOKE

Advanced Databases

Guide to Oracle 10g

9

Roles

• Special database object • Represents collection of system

privileges • Can be assigned to multiple users• Create role– Can inherit privileges from other roles

• Simpler than assigning system privileges to each user.

Advanced Databases 10

Role Examples

Let’s checkout OEM and do a demo.CREATE ROLE

GRANT

Advanced Databases 11

Group Activity Data / DB AdminWith Fudgemart

Data Administrator’s Hat• Construct a Data

Administrator’s Matrix of Role, Object(s) and Permissions(s) for Customers on the Fudgemart website.

Database Administrator’s Hat• Carry out the security

role set by the data administrator.

• Write CREATE / GRANT statements to make a role for the WEB user to be included in.

Recall Fudgemart Has: Customers, Employees, Products, Orders, Departments, Vendors, and Timesheets.

Advanced Databases 12

Questions