You can control access
to table contents at the column level by using ACLs. You can apply
column-level security ACLs to individual users at the user level,
or to collections of users at the group level. SPD Server enforces
precedence for user and group ACL permissions: first, user ACL permissions
are applied, then group ACL restrictions are applied. SPD Server user
permissions override SPD Server group permissions.
When you use an ACL
statement to create a protected column in a table, all individual
users or groups are automatically denied access to the protected column
until you explicitly grant them ACL permission to access it. When
you issue an ACL statement to grant or deny the contents of a table
column to a single user or to a user group, the protected column automatically
becomes unavailable to
all individual
users and user groups, unless you specifically give them access to
the protected column.
Consider a scenario
in which a testing department hires a new member, Joe. Joe has applied
for classified security clearance, but his security clearance level
will not be certified for several weeks. All members of the department
use an SPD Server table called Testing that contains a column of classified
information. Joe needs access to all of the Testing table except the
protected column, and the rest of his group needs access to the whole
Testing table. Here are steps to give Joe and the other members of
the department the correct permissions:
-
You submit a user-level
ACL statement to restrict the secure column in table Testing from
Joe.
Joe is explicitly denied
access, but because the column is now a protected entity, all other
users who access the Testing table are also denied access to the column
by default.
-
Instead of issuing user-level
column ACL permissions to the rest of the testing group individually,
you issue a group-level ACL column permission to the user group Testgroup.
The permission must explicitly grant access to the protected column.
After a column is protected
with ACL security, you must grant explicit permissions in order for
any user (or groups of users) to be able to access the column content.
-
SPD Server reads the
user-level ACL permissions first, and gives Joe access to the table
Testing, but restricts him from the secure column.
-
SPD Server reads the
group ACL permissions and grants all of the Testgroup members access
to the full table, including the secure column.
Joe is a member of Testgroup,
but the user-level ACL permissions maintain precedence over group-level
ACL permissions. All the members of Testgroup have full table access,
except Joe. Joe's user-level ACL column security restriction prevents
him from accessing the classified column.
Now consider another
scenario, in which John manages a group Devgroup whose members record
their billable project hours and codes in an SPD Server table. In
that table, manager John keeps billing-rate information based on employee
salaries in a protected column Rate. Only John should be able to see
the entire table, and the rest of the Devgroup should be able to see
the table minus the Rate column. In this case, you create column security
by protecting the Rate column with a user-level ACL permission statement
for John. The Devgroup members can have full table permissions at
the group level, but cannot see the protected column because John's
user-level column security ACLs override any group-level ACLs for
the Devgroup table.
For example code that implements column-level security,
see Column-Level Security Example.