More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How to implement database security ?
This is a brief run-down of the features and ideas you can use to implement database security:
Logins, Roles, Users, Aliases and Groups
* sp_addlogin - Creating a login adds a basic authorisation for an account - a username and password - to connect to the server. By default, no access is granted to any individual databases.
* sp_adduser - A user is the addition of an account to a specific database.
* sp_addalias - An alias is a method of allowing an account to use a specific database by impersonating an existing database user or owner.
* sp_addgroup - Groups are collections of users at the database level. Users can be added to groups via the sp_adduser command.
A user can belong to only one group - a serious limitation that Sybase might be addressing soon according to the ISUG enhancements requests. Permissions on objects can be granted or revoked to or from users or groups.
* sp_role - A role is a high-level Sybase authorisation to act in a specific capacity for administration purposes. Refer to the Sybase documentation for details.
Recommendations
Make sure there is a unique login account for each physical person and/or process that uses the server. Creating generic logins used by many people or processes is a bad idea - there is a loss of accountability and it makes it difficult to track which particular person is causing server problems when looking at the output of sp_who. Note that the output of sp_who gives a hostname - properly coded applications will set this value to something meaningful (ie. the machine name the client application is running from) so you can see where users are running their programs. Note also that if you look at master..sysprocesses rather than just sp_who, there is also a program_name. Again, properly coded applications will set this (eg. to 'isql') so you can see which application is running. If you're coding your own client applications, make sure you set hostname and program_name via the appropriate Open Client calls. One imaginative use I've seen of the program_name setting is to incorporate the connection time into the name, eg APPNAME-DDHHMM (you have 16 characters to play with), as there's no method of determining this otherwise.
Set up groups, and add your users to them. It is much easier to manage an object permissions system in this way. If all your permissions are set to groups, then adding a user to the group ensures that users automatically inherit the correct permissions - administration is *much* simpler.
Objects and Permissions
Access to database objects is defined by granting and/or revoking various access rights to and from users or groups. Refer to the Sybase documentation for details.
Recommendations
The ideal setup has all database objects being owned by the dbo, meaning no ordinary users have any default access at all. Specific permissions users require to access the database are granted explicitly. As mentioned above - set permissions for objects to a group and add users to that group. Any new user added to the database via the group then automatically obtains the correct set of permissions.
Preferably, no access is granted at all to data tables, and all read and write activity is accomplished through stored procedures that users have execute permission on. The benefit of this from a security point of view is that access can be rigidly controlled with reference to the data being manipulated, user clearance levels, time of day, and anything else that can be programmed via T-SQL. The other benefits of using stored procedures are well known (see Q1.5.8). Obviously whether you can implement this depends on the nature of your application, but the vast majority of in-house-developed applications can rely solely on stored procedures to carry out all the work necessary. The only server-side restriction on this method is the current inability of stored procedures to adequately handle text and image datatypes (see Q1.5.12). To get around this views can be created that expose only the necessary columns to direct read or write access.
Views
Views can be a useful general security feature. Where stored procedures are inappropriate views can be used to control access to tables to a lesser extent. They also have a role in defining row-level security - eg. the underlying table can have a security status column joined to a user authorisation level table in the view so that users can only see data they are cleared for. Obviously they can also be used to implement column-level security by screening out sensitive columns from a table.
Triggers
Triggers can be used to implement further levels of security - they could be viewed as a last line of defence in being able to rollback unauthorised write activity (they cannot be used to implement any read security). However, there is a strong argument that triggers should be restricted to doing what they were designed for - implementing referential integrity - rather being loaded up with application logic.
Administrative Roles
With Sybase version 10 came the ability to grant certain administrative roles to user accounts. Accounts can have sa-level privilege, or be restricted to security or operator roles - see sp_role.
Recommendations
The use of any generic account is not a good idea. If more than one person requires access as sa to a server, then it is more accountable and traceable if they each have an individual account with sa_role granted.
(Continued on next question...)
