Database Administrators and security


Database administrators are a different breed of geek. Their primary function in the computing world is to design, optimize, and protect data. The last aspect, protecting data, makes your life as a client/server developer miserable. This by the way is a very good thing. Going through your first experience with an ‚“evil database administrator ‚½ is more than challenging, but your applications and your client ‚ s data are better for it. It could be you are the administrator, which means it is your responsibility to keep the data safe.

Depending on the backend database, a database administrator can implement security down to the column level for different users. Can your application handle this? Probably not. Our first application was installed and the first thing it failed on was missing columns in our queries. This of course crashed our applications because the user interface was bound to some of these columns . The users did not have access to all fields because they were not supposed to see the information. Remember, anyone with Microsoft Access or another ad hoc reporting tool like Stonefield Query or Crystal Reports and an ODBC driver can open up a table with a simple SQL-Select statement as long as the user has access. This is the reason the database administrators tighten down the databases and make our life miserable.

Then there are the various security schemes the administrators can implement. Do they implement access to the table, to the column, and on stored procedures? In the case of SQL Server, you have Windows authentication and mixed authentication (a combination of SQL Server ‚ s built in security and Windows authentication). The administrator can set various permissions depending on the user, or user group . See Table 2 for some of the settings a database administrator can set for SQL Server. Other backend databases have similar security settings available (see the documentation for the database server for more details).

Table 2. Security settings available in SQL Server.

Database Object

Select

Insert

Update

Delete

Execute

Declarative Referential Integrity
(DRI)

Table

X

X

X

X

‚  

X

Views

X

X

X

X

‚  

X

Column

X

‚  

X

‚   ‚   ‚  

User-defined function

‚   ‚   ‚   ‚  

X

X

Stored Procedure

‚   ‚   ‚   ‚  

X

‚  

The implementation can be complicated. What setting made the user access fail or succeed? The combinations depend on roles, individual object permission settings, and what the administrator decides. This is where application roles come into play.

Application roles are another way of dealing with security. The database administrator can tighten down database objects at will without affecting an application as long as the application associates itself through the connection to the application role stored in SQL Server. The application role is assigned a password. This password is needed to associate the application with the application role. Once the application role is established for the connection to the server, the application can access any database object the application role has access to. The application role is assigned access in the same manner users or user groups get permissions assigned (see Figure 15 ). This simplifies the security from the application deployment viewpoint and allows database administrators to sleep well at night knowing their databases and the information they contain are secure from evil-minded users.


Figure 15: Setting permissions for an application role is very similar to setting permissions for other objects in a SQL Server database.



Deploying Visual FoxPro Solutions
Deploying Visual FoxPro Solutions
ISBN: 1930919328
EAN: 2147483647
Year: 2004
Pages: 232

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net