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
Date Handling
Jon Finke - SANS 2000
2
Information Flow
HR
Account Maint
•Department
•Classification
•Disk Volumes
•Kerberos ID
•DCE Principles
Registrar
•Major
•Level
Alumni Dev
•Grad Year
ID Office
•Sponsor
•Status/Expiration
People
•Name
•Class/Status
•Address
Unix Accounts
•Uid, Gid, etc
•Status, Expiration
Directories
•PH, LDAP
•Phone Book
Jon Finke - SANS 2000
/etc/passwd
3
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
4
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
5
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
6
/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
7
Database Tables
•
•
•
•
•
Table and Owner Name
Table Space
Grants (ACLs)
Comments
Columns
– Data Type
– Data Length
– Indexes
Jon Finke - SANS 2000
8
LOGINS Table
Name
USERNAME
Type
Size Description
varchar2 8 The username that is assigned or reserved.
UNIXUID
number
22
PWHASH
varchar2 13
GECOS
varchar2 240
UNIXGID
SHELL
number
22
varchar2 128
UNIX UID; should usually be between 1000 and 32767; NULL if not
UNIX
UNIX-style hash of current password, if known; If no password is
needed, put "OPEN" here explicitly.
Public personal information as it should appear in system list (aka
gecos)
The Unix Group ID for the username, if different from the default.
Most usernames will take the default
Shell to give to this user
Jon Finke - SANS 2000
9
SQL
SELECT
FROM
WHERE
ORDER
column1, column2, …
table1, …
condition 1
BY column...
Jon Finke - SANS 2000
10
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
11
Logins Extended
•
•
•
•
•
•
•
Owner
Initial Password
Account Type
Budget
Expire Date
Mail Delivery
Change Propagation
Jon Finke - SANS 2000
12
LOGINS Table
Name
Type
Size
USERNAME
varchar2 8
SOURCE
varchar2 20
UNIXUID
number
22
PWHASH
varchar2 13
GECOS
varchar2 240
INITIALPW
varchar2 8
OWNER
number
22
MAIL_DELIVERY
varchar2 80
DISABLED
varchar2 80
COMMENTS
varchar2 240
WHEN_INSERTED number
22
WHEN_UPDATED number
22
WHEN_MARKED_FOR_DELETE
number
22
EXPIRE_DATE
date
7
UNIXGID
number
22
PW_CHANGE_DATE date
7
BUDGET_STR
varchar2 32
PREV_SOURCE
varchar2 20
SOURCE_CHANGE_DATE
date
7
Description
The username that is assigned or reserved.
This identifies the type of username (or reserved entry). This value may be
UNIX UID; should usually be between 1000 and 32767; NULL if not UNIX
UNIX-style hash of current password, if known; If no password is needed,
Public personal information as it should appear in system list (aka gecos)
Cleartext of initial random password, or password when reset by administr
People.id of the person currently responsible for use; usually the only pers
Mail delivery code -- a forwarding address, or special command and param
NULL if okay, otherwise filename of rejection message -- usually manually
Arbitrary comments on this, by human administrator for other administrator
Transaction number at the point when this row was inserted
Transaction number at the point when this row was last changed
When non-null, this is the transaction number when this row became obso
When this login is intended to expire or change to the next state
The Unix Group ID for the username, if different from the default. Most us
The date on when the user last set the pwhash field in the table. This gene
An alpha numeric string that represents the budget number that charges i
Set by a trigger to record the old value of the source field when the source
Set by a trigger, this records when the source field was changed. This can
Jon Finke - SANS 2000
13
/etc/group
wheel:*:0:
user:*:40:
staff:*:60:finkej
catowner:*:80:finkej,doylel
•
•
•
•
Group Name
Group ID
Password
Member list
Jon Finke - SANS 2000
14
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
15
GROUP Table
Name
Type
Size
GROUP_NAME
varchar2 32
GROUP_ID
number
22
GROUP_PASSWD varchar2 32
GROUP_INDEX
number
22
EXPIRE_DATE
date
7
UPDATE_DATE
date
7
WHEN_INSERTED number
22
WHEN_UPDATED
number
22
WHEN_MEMBERS_UPDATED
number
22
WHEN_MARKED_FOR_DELETE
number
22
PLATFORM
varchar2 24
HOSTNAME
varchar2 64
COMMENTS
varchar2 240
Description
The name of the group. This name goes in the firs
The gid part of the group entry. This is the third fie
The passwd field in the group file. This is a text str
An arbitrary identifier for this group entry. It is obta
Date on which this group is to expire. May be null
Date when this group was last changed
A transcount for when this entry was inserted into t
A transcount for when this entry was changed.
A transcount for when a new member is added to t
A transcount for when this group is marked for dele
A platform type or wild card match (using "like") to
A hostname or wild card match (using "like") to hav
Just a space for administrators to discuss the curre
Jon Finke - SANS 2000
16
GROUP_MEMBERS Table
Name
Type
Size Description
The unix uid (as found in simon.logins) of the
member. This provides a degree of isolation
UNIXUID
number 22 from username changes.
The group_index from the groups table. This
is used as a join operator in order to link
GROUP_INDEX
number 22 members to groups.
A simon.transcount of when this member was
inserted into this group. May be useful in
setting "when_members_updated" in the
WHEN_INSERTED number 22 groups file.
A simon.transcount of when this member was
removed from this group. Members with this
value not null, should not be included in this
WHEN_MARKED_FOR_DELETE
number 22 group.
Jon Finke - SANS 2000
17
Group Relations
Groups
user - 40
Groups
staff - 60
Group_Members
60 - 123
Logins
root - 0
Group_Members
60 - 123
Logins
finkej - 123
Group_Members
80 - 125
Logins
doylel - 125
Groups
catowner - 80
Groups
wheel - 0
root:*:0:0:Mr Big:/bin/sh:/
wheel:*:0:
finkej:*:123:40:Jon Finke:/bin/sh:
user:*:40:
doylel:*:125:40:Lori Doyle:/bin/sh
staff:*:60:finkej
catowner:*:80:finkej,doylel
Jon Finke - SANS 2000
18
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
19
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
20
Access Control
• Types of Access
–
–
–
–
–
–
–
Select
Update (Table or Column)
Insert
Delete
Reference
Index
Modify
Jon Finke - SANS 2000
21
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
22
Oracle Authentication
• Oracle ID and Password
• Operating System Authentication
– OPS$
• Advanced Authentication
– Kerberos
– SecurID
– etc
Jon Finke - SANS 2000
23
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
24
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
25
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
26
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;
Source
Shell
PRIMARY_STU /bin/sh
PRIMARY_EMP /bin/bash
Jon Finke - SANS 2000
27
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
28
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
29
Sequences
• Look like a table
– CURRVAL
– NEXTVAL
• Start and End Points
• Increment by
• Cycle
Jon Finke - SANS 2000
30
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
31
Find GROUP “version”
Select max(Greatest(when_inserted,
when_updated,
when_marked_for_delete,
when_member_updated))
From Groups;
Jon Finke - SANS 2000
32
Get “NEW” logins
Select
into
from
where
Last_Done_At
Start_Val
Propagations
Target = ‘LOGINS-ACCOUNTS’;
Select max(When_Inserted)
into End_Val
from Logins;
Select
from
where
and
…..
Update
set
where
Username, Unixuid
Logins
When_Inserted > Start_Val
When_Inserted <= End_Val;
Propagations
Last_Done_At = End_Val
Target = ‘LOGINS-ACCOUNTS;
Jon Finke - SANS 2000
33
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
34
Between Databases
Select Max(Activity_Date)
into Last_Update
from Employees;
Select
into
from
where
Emp_Name, Emp_Id, Emp_Act_Date
Ename, Eid, EAD
[email protected]_DB
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
35
Data Propagation: Flag
•
•
•
•
Single downstream function
Does not provide ordering
Very easy to implement
Can be very fast
Jon Finke - SANS 2000
36
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
37
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
38
Rollback
• Changes must be committed
• or Rolled Back.
Jon Finke - SANS 2000
39
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
40
PL/SQL
• Variables and Constants
• SQL built in functions
– String, Numeric, Date
•
•
•
•
Conditionals
Looping Constructs
Exception Handling
Records
Jon Finke - SANS 2000
41
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
42
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
43
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
44
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
45
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
46
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
47
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
48
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
49
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
50
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
51
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
52
SISWeb Package
•
•
•
•
•
Only part granted to public for execution
Add_Par(Name, Val);
Add_Env(Name, Val);
Entry(progname);
ShowPage;
Jon Finke - SANS 2000
53
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
54
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) := [email protected];
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
55
Voice Mailbox
Jon Finke - SANS 2000
56
Forward
• Check for Changes (SUBMIT)
– Validate new address
– Update database
• Display HTML Form
– Display current forward values
– Display appropriate buttons
Jon Finke - SANS 2000
57
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 || [email protected]);
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 || [email protected] is’);
Jon Finke - SANS 2000
58
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
59
Forward Window
Jon Finke - SANS 2000
60
Oracle Tricks and Techniques in
Supporting Systems Administration
Jon Finke
[email protected]
http://www.rpi.edu/~finkej/Papers.html
Jon Finke - SANS 2000
61
Descargar

Oracle Tricks and Techniques in Supporting Systems