Table owners can control access to table contents at the column level by defining
column ACLs. Column ACLs can be defined for individual users at the user level, or
for collections of users at the group level. SPD Server enforces precedence for user
and group ACL permissions: First, user ACL permissions are applied, and then group
ACL restrictions
are applied. User permissions override 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 other
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 or her security clearance
level will not be certified for several weeks. All members of the
department use a 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 or her group needs access to
the whole Testing table. Here are steps to give Joe and the other
members of the department the correct permissions:
-
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.
-
Issue a group-level ACL column permission to the user group Testgroup. Explicitly
grant group Testgroup access
to the protected column.
After a column is protected with ACL security, you must grant explicit permissions
in order for any user (or group 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 or
her 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.
Now consider another scenario, in which John manages a group Devgroup whose members
record their billable project hours and codes in a 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 ACL overrides any group-level ACLs for the Devgroup table.
For example code that implements column-level security,
see Column-Level Security Example.