Visual C++ 6 Unleashed

Visual C++ 6 Unleashed

By MICKEY WILLIAMS and David Bennett

Data Control Language

Most database developers will probably not be overly concerned with the security of individual objects within their database. (That's the Database Administrator's job.) However, for those developers who need to handle security programmatically, the Data Control Language must be used to maintain security within a Visual C++ program. Data Control Language is the segment of the SQL language that allows you to work with user privileges for objects in the database. DCL uses the following two SQL commands to work with objects in the database:

This section reviews the use of GRANT and REVOKE.

Note

Smaller databases, such as Microsoft Access, may not allow security administration outside of their environment. For example, Microsoft Access handles all security management through their menu structure, and doesn't allow GRANT and REVOKE commands.

Granting Privileges

The SQL language allows you to grant certain privileges on a particular object to a set of users. The privileges that can be granted are listed here:

Privileges for a certain object are granted with a GRANT statement, such as the following:

GRANT SELECT ON Employee TO PUBLIC

The preceding example makes use of the PUBLIC keyword to grant the SELECT privilege on the Employee table to all users. You can also grant several privileges to several users in a single statement, as shown in the following code line. You cannot, however, grant privileges to multiple objects in the same statement.

GRANT SELECT, INSERT ON Employee TO Bob, Doug

Some databases allow security at the column level. For the UPDATE and REFERENCES privileges, you can grant access to specific columns, as in the following example, which allows Bob and Doug to update only the Salary and Dept columns.

GRANT UPDATE (Salary, Dept) ON Employee TO Bob, Doug

You can grant EXECUTE privileges to users to give them the rights to execute a stored procedure. The syntax to grant Bob and Doug access to execute the GetDepartment procedure is as follows:

GRANT EXECUTE ON GetDepartment TO Bob, Doug

TIP

Procedures can bypass much security. For example, although Bob and Doug were able to execute the GetDepartment procedure, they still had no access to individual employee records. Procedures are a good way to supplement security by giving individuals or groups access to limited information within a database.

Revoking Privileges

You can revoke privileges for database objects by using REVOKE statements, which use syntax similar to the GRANT statements shown earlier in this chapter. For example, if you used the following statement to grant privileges:

GRANT SELECT ON Royalties TO Ed, Alex, Michal, Dave

you could revoke a user's privileges to the Royalties table with a statement like this:

REVOKE SELECT ON Royalties FROM Dave

You can also add CASCADE or RESTRICT modifiers to your REVOKE statements. If you want to revoke a user's privileges for a certain table, it would also make sense to revoke the user's privileges on any views that require access to that table. The CASCADE modifier will do this for you. On the other hand, the RESTRICT modifier will prevent you from revoking a privilege that is required according to other privileges the user has been granted.

Using Roles

Usually developers don't grant or revoke privileges to users on-the-fly. A better way to grant privileges is to create roles or user groups that are granted privileges.

For example, say Bob and Doug were in the payroll department. Only members of the payroll department are allowed to update employee information. Rather than trying to manage every single member of the payroll department, you could instead create a "payroll role." In Oracle, this is accomplished through the Oracle SQL syntax:

CREATE ROLE payroll

After creating the role, you grant permissions to the role:

GRANT SELECT, UPDATE, INSERT, DELETE ON Employee TO payroll

After granting permission, you assign the individual to the group. In Oracle, this is accomplished through assigning a user to the default role. The following Oracle syntax assigns Bob to the payroll role:

ALTER USER Bob DEFAULT ROLE payroll

Roles are handled differently in almost every database. For example, Sybase has an sp_role stored procedure that controls roles. If your job requires that you handle database security, check your database documentation for information on how to handle roles or user groups. You'll be glad you did.

Share ThisShare This

Informit Network