1 / 61

Oracle Tricks and Techniques in Supporting Systems Administration

Oracle Tricks and Techniques in Supporting Systems Administration Jon Finke Rensselaer Polytechnic Institute SANS 2000 Introduction NOT how to be a Database Administrator Why use Oracle (or other RDBMS) Automation Data Management Access Control Build in Business Rules Auditing

Sharon_Dale
Download Presentation

Oracle Tricks and Techniques in Supporting Systems Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Tricks and Techniques in Supporting Systems Administration Jon Finke Rensselaer Polytechnic Institute SANS 2000

  2. Introduction • NOT how to be a Database Administrator • Why use Oracle (or other RDBMS) • Automation • Data Management • Access Control • Build in Business Rules • Auditing • Date Handling Jon Finke - SANS 2000

  3. Information Flow • HR • Department • Classification • Account Maint • Disk Volumes • Kerberos ID • DCE Principles • Registrar • Major • Level • People • Name • Class/Status • Address • Unix Accounts • Uid, Gid, etc • Status, Expiration • Alumni Dev • Grad Year • ID Office • Sponsor • Status/Expiration • Directories • PH, LDAP • Phone Book /etc/passwd Jon Finke - SANS 2000

  4. Overview • Relational Database Design • Access Control • Database Views • Data Propagation • Stored Procedures and Packages • Database Triggers • Oracle Signals and Pipes • Web Interfaces Jon Finke - SANS 2000

  5. SIMON • Creates and Expires user accounts at RPI • Tools to maintain system files • passwd, group, hosts, aliases, printcap • Accounting/Billing • Printing, Disk, Contract work • Directory Services • Phone Book, ph, ldap, finger Jon Finke - SANS 2000

  6. Simple Relations • Goal: /etc/passwd • Maintain Login Information • Supporting Information • Goal: /etc/group • Maintain Group Information • Maintain Group Membership • How to REALLY do it. Jon Finke - SANS 2000

  7. /etc/passwd root:*:0:0:Mr Big:/bin/sh:/ finkej:*:123:40:Jon Finke:/bin/sh:/home/finkej doylel:*:125:40:Lori Doyle:/bin/sh:/home/doylel • Username and Password • Uid and Gid • Finger Name • Shell • Home Directory Path Jon Finke - SANS 2000

  8. Database Tables • Table and Owner Name • Table Space • Grants (ACLs) • Comments • Columns • Data Type • Data Length • Indexes Jon Finke - SANS 2000

  9. LOGINS Table Jon Finke - SANS 2000

  10. SQL SELECT column1, column2, … FROM table1, … WHERE condition 1 ORDER BY column... Jon Finke - SANS 2000

  11. Generate /etc/passwd Declare Cursor Get_PW is Select Username, Unixuid, Unixgid, PWHash, Gecos, Shell, Path from Logins order by Unixuid; Begin For PW in Get_PW loop putline(PW.Username || ‘:’ || PW.PWHash || ‘:’ || PW.Unixuid || ‘:’ || PW.UnixGid || ‘:’ || PW.Gecos || ‘:’ || PW.Path || ‘:’ || PW.Shell); end loop; End; Jon Finke - SANS 2000

  12. Logins Extended • Owner • Initial Password • Account Type • Budget • Expire Date • Mail Delivery • Change Propagation Jon Finke - SANS 2000

  13. LOGINS Table Jon Finke - SANS 2000

  14. /etc/group wheel:*:0: user:*:40: staff:*:60:finkej catowner:*:80:finkej,doylel • Group Name • Group ID • Password • Member list Jon Finke - SANS 2000

  15. Group Problems • Groups with more than one user. • Users in more than one group. • Allow for username changes. • Platform and Host specific groups. • Automatically remove expired users. Jon Finke - SANS 2000

  16. GROUP Table Jon Finke - SANS 2000

  17. GROUP_MEMBERS Table Jon Finke - SANS 2000

  18. Group Relations Groups user - 40 Logins root - 0 Group_Members 60 - 123 Groups staff - 60 Logins finkej - 123 Group_Members 60 - 123 Groups catowner - 80 Group_Members 80 - 125 Logins doylel - 125 Groups wheel - 0 wheel:*:0: user:*:40: staff:*:60:finkej catowner:*:80:finkej,doylel root:*:0:0:Mr Big:/bin/sh:/ finkej:*:123:40:Jon Finke:/bin/sh:/home/finkej doylel:*:125:40:Lori Doyle:/bin/sh:/home/doylel Jon Finke - SANS 2000

  19. Generate /etc/group Declare Cursor Get_Groups is Select Group_Name, Group_Id, Group_Index from Groups order by Group_Id; Cursor Get_Members (Gindex Number) is Select L.Username from Logins L, Group_Members GM where GM.Group_Index = Gindex and GM.Unixuid = L.Unixuid; Delim varchar2(1); -- Delimiter between usernames Begin For G in Get_Groups loop DB_Out.put(G.Group_Name || ‘:*:’ || G.Group_Id ); Delim := ‘:’; For GM in Get_Members(G.Group_Index) loop DB_Out.put(Delim || GM.Username); Delim := ‘,’; end loop; DB_OUT.new_line; end loop; End; Jon Finke - SANS 2000

  20. oops….. • Uid is a BAD database key • Can’t reuse UID space • Harder to change a user’s UID • Username is too small • Kerberos allows longer names Jon Finke - SANS 2000

  21. Access Control • Types of Access • Select • Update (Table or Column) • Insert • Delete • Reference • Index • Modify Jon Finke - SANS 2000

  22. Access Control • Individual • grant SELECT on LOGINS to OPS$FINKEJ • Group (Role) • Create role ID_ADMIN • grant select on LOGINS to ID_ADMIN • grant ID_ADMIN to OPS$FINKEJ • public Jon Finke - SANS 2000

  23. Oracle Authentication • Oracle ID and Password • Operating System Authentication • OPS$ • Advanced Authentication • Kerberos • SecurID • etc Jon Finke - SANS 2000

  24. Views • Provides an alternate “window” into existing tables. • Looks like a table, but does NOT have any data in it. • Can be used to enhance access control. • Can provide isolation from table definitions changes. Jon Finke - SANS 2000

  25. Simple View Create view ETC_PASSWD as SELECT Username, Uid, Gid, Gecos, ‘/bin/sh’, ’/home/’ || username FROM LOGINS; Grant select on ETC_PASSWD to PUBLIC; Jon Finke - SANS 2000

  26. User View create view MY_LOGINS as Select USERNAME, UID, GID, GECOS, EXPIRE_DATE, MAIL_FWD from LOGINS where USERNAME=lower(substr(USER,5)) and substr(USER,1,4)=‘OPS$’; grant select,update(GECOS,MAIL_FWD) on MY_LOGINS to public; Jon Finke - SANS 2000

  27. Complex View create view ETC_PASSWD as Select L.USERNAME, L.UID, L.GID, L.GECOS, S.SHELL, ‘/home/’ || L.USERNAME from L.LOGINS, S.SOURCE_INFO where L.SOURCE = S.SOURCE; Jon Finke - SANS 2000

  28. Data Propagation • All NEW entries since “last time” • All Changed entries since “last time” • Numeric, Date or Flag • Brute Force Compare • Rollback transactions • Fail Safe operations Jon Finke - SANS 2000

  29. Data Propagation: Numeric • Ever Increasing Sequence Number • Easy to pass numeric values around • Easy to compare values in programs • Requires supporting tables • Internal System Only • Numbers are meaningless to humans Jon Finke - SANS 2000

  30. Sequences • Look like a table • CURRVAL • NEXTVAL • Start and End Points • Increment by • Cycle Jon Finke - SANS 2000

  31. Using Sequences Select Transcount.Nextval from Dual Insert into Logins (Username, Unixuid, When_Inserted) Values (‘finkej’, 123, Transcount.Nextval); Update Groups set When_Member_Updated = Transcount.Nextval where Group_Index = 275; Jon Finke - SANS 2000

  32. Find GROUP “version” Select max(Greatest(when_inserted, when_updated, when_marked_for_delete, when_member_updated)) From Groups; Jon Finke - SANS 2000

  33. Get “NEW” logins Select Last_Done_At into Start_Val from Propagations where Target = ‘LOGINS-ACCOUNTS’; Select max(When_Inserted) into End_Val from Logins; Select Username, Unixuid from Logins where When_Inserted > Start_Val and When_Inserted <= End_Val; ….. Update Propagations set Last_Done_At = End_Val where Target = ‘LOGINS-ACCOUNTS; Jon Finke - SANS 2000

  34. Data Propagation: Date • Date Representation • Y2K Issues • Not Unique • Smallest Unit • Harder to compare inside of applications • Interfacing with other data easier Jon Finke - SANS 2000

  35. Between Databases Select Max(Activity_Date) into Last_Update from Employees; Select Emp_Name, Emp_Id, Emp_Act_Date into Ename, Eid, EAD from HR.Emp_Table@ADMIN_DB where Emp_Act_Date >= Activity Date; Update Employees set Name = Ename, When_Updated = transcount.nextval, Activity_Date = EAD where HR_Emp_Id = Eid; Jon Finke - SANS 2000

  36. Data Propagation: Flag • Single downstream function • Does not provide ordering • Very easy to implement • Can be very fast Jon Finke - SANS 2000

  37. Using Flags Declare Cursor Creation_List is Select Username, Unixuid, Rowid from Logins where Create_Needed = ‘Y’; Begin For L in Creation_List loop Create_Login(L.Username, L.Unixuid); Update Logins set Create_Needed = NULL where Rowid = L.ROWID; end Loop; End; Jon Finke - SANS 2000

  38. Brute Force Compare • Needs a Unique Key • Requires a full scan of the entire data • Helps to load the data into Oracle first. • Method of last resort, but often works. Jon Finke - SANS 2000

  39. Rollback • Changes must be committed • or Rolled Back. Jon Finke - SANS 2000

  40. Stored Procedures and Packages • PL/SQL • Procedural extension to SQL • Simple Procedure or Function • Package • Set of procedures and functions • maintains state • Stored and executed by database server Jon Finke - SANS 2000

  41. PL/SQL • Variables and Constants • SQL built in functions • String, Numeric, Date • Conditionals • Looping Constructs • Exception Handling • Records Jon Finke - SANS 2000

  42. Add_Group_User Procedure Add_Group_User(Uname in varchar2, Gname in varchar2) is U_Uid number; -- Users Uid Grp_Index number; -- Group identifier Begin Select Unixuid into U_Uid from LOGINS where Username = Uname; Select Group_Index into Grp_Index from Groups where Group_Name = Gname; Insert into Group_Members (Unixuid, Group_Index, When_Inserted) Values (U_Uid, Grp_Index, Transcount.Nextval; Update Groups set When_Member_Updates = Transcount.Nextval where Group_Index = Grp_Index; Exception When No_Data_Found then ... End; Jon Finke - SANS 2000

  43. Packages • Package Specification • Public Types and Items • Procedure and Function Declarations • Package Body • Private Types and Items • Procedure and Function Definitions • Initialize Code Jon Finke - SANS 2000

  44. Demo.GetPwEnt Create or Replace Package DEMO as Procedure GETPWENT(Uname out varchar2, Uid out Number); end Demo; Create or Replace Package Body DEMO as Cursor PW_List is Select Username, Unixuid from Logins order by Unixuid; Procedure GETPWENT(Uname out varchar2, Uid out Number) is Begin if not PW_List%IsOpen then Open PW_List; end if; Fetch PW_List into Uname, Uid; if Pw_List%Not_Found then Close Pw_List; end if; End GetPwEnt; End Demo; Jon Finke - SANS 2000

  45. Database Triggers • PL/SQL invoked • On Insert • On Update • On Delete • Applications can NOT bypass triggers • Great for adding business rules, auditing. Jon Finke - SANS 2000

  46. Record Login.Source Changes Create or Replace Trigger LOGINS_UPDATE Before Update of SOURCE,GECOS on LOGINS for each row Begin if :Old.Source != :New.Source then :New.Prev_Source := :Old.Source; Select Sysdate into :New.Source_Change_Date from Dual; end if; End; Jon Finke - SANS 2000

  47. Oracle Signals and Pipes • DBMS_ALERT • Register, Remove • Signal • WaitOne, WaitAny • DBMS_PIPE • Create_Pipe • Pack_Message, Send_Message • Receive_Message, Unpack_Message • Close_Pipe, Purge Jon Finke - SANS 2000

  48. Queue_PW_Change Function Queue_PW_Change(Uname in varchar2, PW in varchar2) return varchar2 is Result varchar2(32); -- What we return Tmp_Cnt number; -- For quick counts Begin Select Count(*), max(Reason) into Tmp_Cnt, Result from Restricted_Logins where Rname = Uname; if Tmp_Cnt > 0 then Return Result; end if; Insert into PW_Change_Queue (Username, New_Pw, Clerk_Id, Change_Pending) Values (Uname, Pw, User, ‘Y’); Dbms_Alert.Signal(‘PASSWORD_CHANGE_PENDING’); End Queue_PW_Change; Grant Execute on Queue_PW_Change to SENIOR_ADMINS; Jon Finke - SANS 2000

  49. Process_PW_Queue Function Wait_For_PW_Signal return number is Result Number; Message Varchar2(255); Timeout Number := 3600; -- Cycle every hour Signal Varchar2(64); begin Signal := ‘PASSWORD_CHANGE_PENDING’; Dbms_Alert.Register(Signal); Dbms_Alert.Waitone(Signal, Message, Result, Timeout); Dbms_Alert.Close(Signal); Return Result; End Wait_For_Pw_Signal; Jon Finke - SANS 2000

  50. Queue_Student_Pw_Change Function Queue_Student_Pw_Change (Uname in varchar2, PW in varchar2) return varchar2 is Target_Src varchar2(32); Begin Select Source into Target_Source from Logins Where username = Uname; if Target_Source != ‘PRIMARY-STU’ then Return ‘Not Student’; end if; Return Get_Pw_Change(Uname, PW); end Queue_Student_PW_Change; Grant Execute on Queue_Student_Pw_Change to STUDENT_ADMINS; Jon Finke - SANS 2000

More Related