oracle tricks and techniques in supporting systems administration l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle Tricks and Techniques in Supporting Systems Administration PowerPoint Presentation
Download Presentation
Oracle Tricks and Techniques in Supporting Systems Administration

Loading in 2 Seconds...

play fullscreen
1 / 61

Oracle Tricks and Techniques in Supporting Systems Administration - PowerPoint PPT Presentation


  • 232 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oracle Tricks and Techniques in Supporting Systems Administration' - Sharon_Dale


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
oracle tricks and techniques in supporting systems administration

Oracle Tricks and Techniques in Supporting Systems Administration

Jon Finke

Rensselaer Polytechnic Institute

SANS 2000

introduction
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

slide3

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

overview
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

simon
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

simple relations
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

etc passwd
/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

database tables
Database Tables
  • Table and Owner Name
  • Table Space
  • Grants (ACLs)
  • Comments
  • Columns
    • Data Type
    • Data Length
    • Indexes

Jon Finke - SANS 2000

logins table
LOGINS Table

Jon Finke - SANS 2000

slide10
SQL

SELECT column1, column2, …

FROM table1, …

WHERE condition 1

ORDER BY column...

Jon Finke - SANS 2000

generate etc passwd
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

logins extended
Logins Extended
  • Owner
  • Initial Password
  • Account Type
  • Budget
  • Expire Date
  • Mail Delivery
  • Change Propagation

Jon Finke - SANS 2000

logins table13
LOGINS Table

Jon Finke - SANS 2000

etc group
/etc/group

wheel:*:0:

user:*:40:

staff:*:60:finkej

catowner:*:80:finkej,doylel

  • Group Name
  • Group ID
  • Password
  • Member list

Jon Finke - SANS 2000

group problems
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

group table
GROUP Table

Jon Finke - SANS 2000

group members table
GROUP_MEMBERS Table

Jon Finke - SANS 2000

group relations
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

generate etc group
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

slide20
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

access control
Access Control
  • Types of Access
    • Select
    • Update (Table or Column)
    • Insert
    • Delete
    • Reference
    • Index
    • Modify

Jon Finke - SANS 2000

access control22
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

oracle authentication
Oracle Authentication
  • Oracle ID and Password
  • Operating System Authentication
    • OPS$
  • Advanced Authentication
    • Kerberos
    • SecurID
    • etc

Jon Finke - SANS 2000

views
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

simple view
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

user view
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

complex view
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

data propagation
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

data propagation numeric
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

sequences
Sequences
  • Look like a table
    • CURRVAL
    • NEXTVAL
  • Start and End Points
  • Increment by
  • Cycle

Jon Finke - SANS 2000

using sequences
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

find group version
Find GROUP “version”

Select max(Greatest(when_inserted,

when_updated,

when_marked_for_delete,

when_member_updated))

From Groups;

Jon Finke - SANS 2000

get new logins
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

data propagation date
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

between databases
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

data propagation flag
Data Propagation: Flag
  • Single downstream function
  • Does not provide ordering
  • Very easy to implement
  • Can be very fast

Jon Finke - SANS 2000

using flags
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

brute force compare
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

rollback
Rollback
  • Changes must be committed
  • or Rolled Back.

Jon Finke - SANS 2000

stored procedures and packages
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

pl sql
PL/SQL
  • Variables and Constants
  • SQL built in functions
    • String, Numeric, Date
  • Conditionals
  • Looping Constructs
  • Exception Handling
  • Records

Jon Finke - SANS 2000

add group user
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

packages
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

demo getpwent
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

database triggers
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

record login source changes
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

oracle signals and pipes
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

queue pw change
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

process pw queue
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

queue student pw change
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

web interface
Web Interface
  • Lots of Options and Versions
    • OAS 2, OAS 3, Application Server, etc
    • CGI-BIN + Perl, etc.
  • Different User Models
    • Anonymous Queries/Surveys
    • Self Subscription
    • External Authentication

Jon Finke - SANS 2000

oas 3 packages custom interface
OAS 3 Packages + Custom Interface
  • Oracle Account for every user
  • Kerberos enabled secure web server
    • Apache with Raven, etc.
  • Locally written CGI-BIN interface program
    • Verifies Kerberos and connects to Oracle
    • Loads Selected CGI-BIN variable into package
    • Calls standard entry point
    • Passes results back to browser

Jon Finke - SANS 2000

sisweb package
SISWeb Package
  • Only part granted to public for execution
  • Add_Par(Name, Val);
  • Add_Env(Name, Val);
  • Entry(progname);
  • ShowPage;

Jon Finke - SANS 2000

web support packages
Web Support Packages
  • htp, htf
    • Generate HTML primitives
  • WebSis_Utils
    • Get_Val, Get_Env
    • Get_Gecos
    • Comp_Vals
    • Check_And_Set_Vals
    • Sis_Intro

Jon Finke - SANS 2000

voice mailbox
Voice_Mailbox

Procedure Voice_Mailbox is

Cursor Get_Vm is

Select Mailbox, Password, Name from My_Voice_Mailbox;

Result Get_Vm%RowType;

Tcom varchar2(64) := ‘Tcom@RPI.EDU’;

begin

Websis_Util.Sis_Intro(‘Student Voice Mailbox’);

htp.center(htf.strong(‘Voice Mailbox information for ‘ ||

Websis_Util.Get_Gecos));

Open Get_Vm;

Fetch Get_Vm into Result;

if Get_Vm%NotFound then

htp.strong(‘No voice mailbox found.’);

htp.p(‘Please contact ‘ || htf.mailto(Tcom,Tcom));

return;

end if;

htp.p(‘Your voice mailbox is ‘ || Result.Mailbox);

htp.p(‘ and the password is ‘ || Result.Password);

htp.p(‘For hints on using your voice mail box, );

htp.anchor(‘http://www.rpi.edu/tele/vm.html’,’click here.’);

end Voice_Mailbox;

Jon Finke - SANS 2000

voice mailbox56
Voice Mailbox

Jon Finke - SANS 2000

forward
Forward
  • Check for Changes (SUBMIT)
    • Validate new address
    • Update database
  • Display HTML Form
    • Display current forward values
    • Display appropriate buttons

Jon Finke - SANS 2000

slide58

Procedure Forward is

Uname varchar2(8); Fwd varchar2(80); New_Fwd varchar2(80);

Begin

Websis_Utils.Sis_Intro(‘RCS Username Email Forwarding’);

Select Username,Mail_Delivery

into Uname, Fwd from My_Logins;

htp.center(‘RCS email forwarding for ‘ || Uname || ‘@rpi.edu’);

Sub_Val := Websis_Utils.Get_Val(‘SUBMIT’);

if Sub_Val = ‘Remove Forward’ then

Update My_Logins set Mail_Delivery=Null;

Fwd := Null;

elsif Sub_Val = ‘Set Forward’ then

New_Fwd := Websis_Utils.Get_Val(‘FWD’);

if Validate_Forward(New_Fwd) then

Update My_Logins set Mail_Delivery=New_Fwd;

Fwd := New_Fwd;

end if;

end if;

htp.p(‘Email to ‘ || uname || ‘@rpi.edu is’);

Jon Finke - SANS 2000

slide59

htp.formOpen(‘forward.cgi’);

If Fwd is Null then

htp.p(‘Not currently forwarded. To forward email, enter the destination address in the box below and click the ’);

htp.italic(‘Set Forward’); htp.p(‘ button.’); htp.br;

htp.formText(‘FWD’,80,80);

htp.formSubmit(‘SUBMIT’,’Set Forward’);

else

htp.p(‘being forwarded to ‘ || fwd || ‘,’);

htp.p(‘To change the forward of your email, enter the destination address in the box below and click the ‘);

htp.italic(‘Set Forward’); htp.p(‘ button. To remove the current forward, click the ‘);

htp.italic(‘Remove Forward’); htp.p(‘ button.’); htp.br;

htp.formText(‘FWD’,80,80,fwd);

htp.formSubmit(‘SUBMIT’,’Set Forward’);

htp.formSubmit(‘SUBMIT’,’Remove Forward’);

end if;

htp.formClose;

End Forward;

Jon Finke - SANS 2000

forward window
Forward Window

Jon Finke - SANS 2000

oracle tricks and techniques in supporting systems administration61
Oracle Tricks and Techniques in Supporting Systems Administration

Jon Finke

finkej@rpi.edu

http://www.rpi.edu/~finkej/Papers.html

Jon Finke - SANS 2000