Database Programming

1. Database Programming Summing it up

2. The Overview • Database Programming is just programming that interacts with databases • There are two aspects or views: • The view from the database admin and developer • The view from the application developer

3. The Database View • From the database view there are several aspects to keep in mind: • The structure, purpose and integrity of the database • The structure for accessing the database (logins users, schema, roles) • Security (making sure that users have the access they need, but no more)

4. Structure and purpose • We built a database to track music venues, artists and shows. • As part of this we had to define who would use the database.

5. Database Access • We made a schema for each of the users and created a role for the venue users that stored their permissions and had access to the schema. • We created a login for the venue schema and assigned them to the venue role. • At various times we had to add permissions to that role.

6. Security • A large part of our security was defined in the schema ownership of objects and in the role. • But we also helped protect data security by establishing relational constraints, check constraints (headliner, opener, festival). • The XML Schema collections we created insured that only xml matching our designated structure could be inserted into the xml description fields. This also protects data security.

7. Building and Internal Data Layer • One of the strategies for protecting a database is to create an internal data application layer consisting of schema, stored procedures, views and functions. (we did a bit of this—sort of the skeleton.) • We build a small CLR(C#) procedure to show that these objects could be done in languages other than SQL. • It is not uncommon to build a complete access layer out of stored procedures and views so that an application never accesses the underlying tables except through one of these objects.

8. The application view • An application that interacts with a database must be able to get the data it needs, but should not be able to get data it is not supposed to see. • It also should not be able to damage the database whether intentionally or unintentionally

9. Things we did • We played with several aspects of application development • We read the xml from the database and applied an xsltstylesheet to it for display. • Our main application used LINQ to create an auto generated data layer in the web site that interacted with the data tables and procedures. • We used the VenueLogin to insure that the application only had the access and permissions needed to do the job

10. Other aspects • We used ASP.Net but tried to keep an object oriented approach • We build a hash class that hashed our passwords to match the passwords in the database. • We built a login class to check the login and save the userid (venueID) to a session variable so that we could limit their view of the data to their own venue and shows.

11. Other Aspects continued • We created a form for them to add a show • The artists were listed in a drop down box. If the artist was not available they could push a button and add a new artist.

12. Summary • Although we did it in a fairly cursory manner, We looked at database programming from both the view of the database developer and the application developer. • The two have different needs, but must work together both to protect the data and to let the data be used for the purposes it was intended to meet.