Data and information are valuable assets. Data is used at many business levels Operations and transactions. Tactical management. Strategic management. There are many databases and applications in an organization. Someone has to be responsible for organizing, controlling, and sharing data.
Data and information are valuable assets.
Data is used at many business levels
Operations and transactions.
There are many databases and applications in an organization.
Someone has to be responsible for organizing, controlling, and sharing data.
Data Administrator (DA)
Provide centralized control over the data.
Selection of DBMS.
Act as data and database advocate.
Coordinate data integrity, security, and control.
Install and upgrade DBMS.
Create user accounts and monitor security.
In charge of backup and recovery of the database.
Monitor and tune the database performance.
Coordinate with DBMS vendor and plan for changes.
Maintain DBMS-specific information for developers.
ALTER SYSTEM SET TIMED_STATISTICS=true;
ALTER SYSTEM SET USER_DUMP_DEST= ‘newdir’;
ALTER SESSION SET SQL_TRACE = true;
Run your query
ALTER SESSION SET SQL_TRACE = false;
SET STATEMENT_ID = ‘Your title’
Requires output table be setup first.
call count cpu elapsed disk query current rows
----- ------ ----- ------- ----- ----- ------- ----
Parse 1 0.02 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 12 0.00 0.00 0 823 4 164
----- ------ ----- ------- ----- ----- ------- ----
total 14 0.02 0.04 0 823 4 164
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26
Rows Row Source Operation
164 NESTED LOOPS
303 NESTED LOOPS
197 TABLE ACCESS FULL SALE
498 INDEX RANGE SCAN (object id 3398)
164 TABLE ACCESS BY INDEX ROWID MERCHANDISE
604 INDEX UNIQUE SCAN (object id 3388)
Data storage requirements
Time to develop
Cost to develop
Networks / communication
Delivering in stages
User needs / priorities
Normalization by user views
Distribute individual sections
Assign forms and reports
Standards for application programming.
Layout and techniques.
Variable & object definition.
Data access and ownership.
Backup and recovery plans.
User and operator training.
Size and growth
Performance / delays
Backup and recovery
Detect need for change
Size and speed
Structures / design
Requests for additional data.
Difficulties with queries.
Delays in implementing changes
Time to recognize needs.
Time to get agreement and approval.
Time to install new hardware.
Time to create / modify software.
Backups are crucial!
Journals / logs
Rollback / Roll forward
Protecting software and data.
Employees / Insiders
Dial-up / home access
Who owns data?
Do not release data to others.
Do not read data unnecessarily.
Report all infractions and problems.
Hardware backup facilities
Continuous backup (mirror sites)
Data and software
Policies and procedures
Write it down
Train all new employees
Test it once a year
Allowable time between disaster and business survival limits.
Disaster planning and testing.
Fire monitoring and control.
Control physical access.
Physical access limitations
Guards and video monitoring
Badges and tracking
Consultants and Business alliances
Limited data access
Limited physical access
Paired with employees
Letting a competitor see the strategic marketing plans.
Letting employees change their salary numbers.
Preventing a finance officer from retrieving data needed to get a bank loan.
Do not use “real” words.
Do not use personal (or pet) names.
Include non-alphabetic characters.
Use at least 6 (8) characters.
Change it often.
Too many passwords!
Finger / hand print readers
Retina (blood vessel) scans
The one-minute password.
Card matched to computer.
Best method for open networks / Internet.
Limit access to hardware
Fire and environment monitors.
Employee logs / cards.
Access from network nodes.
Software and data usage.
User calls modem
Modem gets name, password
Modem hangs up phone
Modem calls back user
Machine gets final password
Access to directories
View / File scan
Access to files
DBMS usually needs most of these
Assign by user or group.
DBMS access controls
Open / Run
Owners and administrator
Need separate user identification / login to DBMS.
Table columns (SQL 92+)
Permissions apply to entire table or query.
Use query to grant access to part of a table.
Give all employees read access to name and phone (phonebook).
Give managers read access to salary.
Employee(ID, Name, Phone, Salary)
SELECT Name, Phone
Grant Read access to Phonebook
for group of Employees.
Grant Read access to Employee
for group of Managers.
Revoke all access to Employee
for everyone else (except Admin).
Purchasing manager can add new suppliers, but cannot add new orders.
Clerk must use SupplierID from the Supplier table, and cannot add a new supplier.
Set up a secure workgroup
Create a new Admin user.
Enable security by setting a password
Remove the original Admin user.
Run the Security Wizard in the database to be secured.
Assign user and group access privileges in the new database.
Encrypt the new database.
Save it as an MDE file.
Protection for open transmissions
Weak operating systems
Trap doors / escrow keys
U.S. export limits
64 bit key limit
Breakable by brute force
Typical hardware:2 weeks
Special hardware: minutes
Single key: e.g., DES
Using Takao’s private key ensures it came from him.
Using Makiko’s public key means only she can read it.
*Basic Supplier data: ID, Name, Address, Phone, ZipCode, CityID