1. Computing

Discuss in my forum

Access Controls in SQL

Users and Roles

By

Security is paramount to database administrators seeking to protect their gigabytes of vital business data from the prying eyes of unauthorized outsiders and insiders attempting to exceed their authority. All relational database management systems provide some sort of intrinsic security mechanisms designed to minimize these threats. They range from the simple password protection offered by Microsoft Access to the complex user/role structure supported by advanced relational databases like Oracle and Microsoft SQL Server. This article focuses on the security mechanisms common to all databases that implement the Structured Query Language (or SQL). Together, we'll walk through the process of strengthening data access controls and ensuring the safety of your data.

Server-based databases all support a user concept similar to that used in computer operating systems. If you're familiar with the user/group hierarchy found in Microsoft Windows NT and Windows 2000, you'll find that the user/role groupings supported by SQL Server and Oracle are very similar.

It is highly recommended that you create individual database user accounts for each person who will be accessing your database. It's technically possible to share accounts between users or simply use one user account for each type of user that needs to access your database, but I strongly discourage this practice for two reasons. First, it will eliminate individual accountability -- if a user makes a change to your database (let's say by giving himself a $5,000 raise), you won't be able to trace it back to a specific person through the use of audit logs. Furthermore, if a specific user leaves your organization and you wish to remove his or her access from the database, you'll be forced to change the password that all users rely upon.

The methods for creating user accounts vary from platform to platform and you'll have to consult your DBMS-specific documentation for the exact procedure. Microsoft SQL Server users should investigate the use of the sp_adduser stored procedure. Oracle database administrators will find the CREATE USER command useful. You also might want to investigate alternative authentication schemes. For example, Microsoft SQL Server supports the use of Windows NT Integrated Security. Under this scheme, users are identified to the database by their Windows NT user accounts and are not required to enter an additional user ID and password to access the database. This approach is extremely popular among database administrators because it shifts the burden of account management to the network administration staff and it provides the ease of a single sign-on to the end user.

If you're in an environment with a small number of users, you'll probably find that creating user accounts and assigning permissions directly to them is sufficient for your needs. However, if you have a large number of users, you'll most likely be overwhelmed by the burden of maintaining accounts and proper permissions. To ease this burden, relational databases support the notion of roles. Database roles function similarly to Windows NT groups. User accounts are assigned to role(s) and permissions are then assigned to the role as a whole rather than the individual user accounts. For example, we could create a DBA role and then add the user accounts of our administrative staff to this role. Once we've done this, we can assign a specific permission to all present (and future) administrators by simply assigning the permission to the role. Once again, the procedures for creating roles varies from platform to platform. MS SQL Server administrators should investigate the sp_addrole stored procedure while Oracle DBAs should use the CREATE ROLE syntax.

Once you've populated your database with users and roles, it's time to begin assigning permissions. We'll discuss that in the next section of this article. Read on!
  1. About.com
  2. Computing
  3. Databases
  4. Learning SQL
  5. Access Controls in SQL

©2014 About.com. All rights reserved.