AGENDA • Understand theneed for security. • Learn about System Permissions and Object permissions. • Understand the purpose of Roles. • Explore Security-focused SQL. 2
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 3
4.
DATA ADMIN MATRIX •Data Security documentation tool. • Crafted by DA, Carried out by DBA. 4 Role Object(s) Permission s Order Entry Orders Order Items Insert, Read Credit Approval Customers Update
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. 6
7.
7 H T TP : / / D O C S . O R A C L E . C O M / C D / B 2 8 3 5 9 _ 0 1 / S E R V E R . 1 1 1 / B 2 8 2 8 6 / S T A T E M E N T S _ 9 0 1 3 . H T M # S T H R E F 8 5 1 3 SYSTEM PRIVILEGES REFERENCE
8.
8 L E T’ S C H E C K O U T O E M A N D D O A D E M O . G R A N T R E V O K E SYSTEM PRIVILEGES EXAMPLE
9.
ROLES • Special databaseobject • 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. 9
10.
10 L E T’ S C H E C K O U T O E M A N D D O A D E M O . C R E A T E R O L E G R A N T ROLE EXAMPLES
11.
GROUP ACTIVITY DATA/ DB ADMIN Data Administrator’s Hat • Construct a Data Administrator’s Matrix of Role, Object(s) and Permissions(s) for Customers on the Amazon 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. 11 Recall Fudgemart Has: Customers, Employees, Products, Orders, Departments, Vendors, and Timesheets.
