The DBO can become another user using the setuser command. This allows
the DBO to assume that user’s permissions. This is commonly used to either
test permissions or, if someone besides the DBO is the object owner, to
change object permissions or remove an object.
Using setuser with no username changes the user back to dbo.
setuser [‘user name’]
Fine-Grained Access Control (FGAC)
Version 12.5 added a feature that lets object owners restrict access at a row
level by defining access rules and binding those rules to the table. Access to
data can be further controlled by setting application contexts and creating
This row-level access control enables the database owner or table owner
to control the rows in a table that users can access, based on their identifica-
tion or profile and the privileges the user has from the application level.
Adaptive Server enforces row-level access control for all data manipulation
languages (DMLs), which prevents users from bypassing the access control
to get to the data.
Domain rules allow table owners to control the values that users can enter into
a particular column that is using a base data type or any column that is using a
user-defined data type. Rules are enforced during inserts and updates. Adap
tive Server Enterprise enables row-level protection through access rules.
Access rules are enforced on select, update, and delete operations. Adaptive
Server enforces the access rules on all columns that are read in a query, even if
the columns are not included in the select list. In other words, for a given
query, Adaptive Server enforces the domain rule on the table that is updated,
and the access rule on the tables that are read.
Using access rules is similar to using views or an ad hoc query with
where clauses, and does not cause performance degradation. The query is
compiled and optimized after the access rules are attached. Therefore, if there
are indexes on the columns that have access rules, the queries may perform
Chapter 6: Security
Access Rules Using Java Functions and Application
Application developers can write flexible access rules using Java and applica
tion contexts. For example, you can write a rule that is hierarchical. If table T
contains all the employee schedules, then the president can see all employee
schedules. Individual VPs can see their own schedules and their direct
reports’ work schedules, but not the president’s schedule. Access rules can be
bound to user-defined data types just like other types of rules, using
sp_addtype. Adaptive Server enforces the access rule on user tables that use
these user-defined data types. This relieves the database owner and table
owner from the task of binding access rules to columns in their normalized
For example, there can be a user-defined data type named username for
which the base type is varchar(30). The database owner or table owner can
create an access rule and bind it to username. The owners can then use
username in any tables that their application will use. Adaptive Server
enforces the access rule on the tables that have columns of the username data
Syntax for Access Rules
The access parameter is used in the create rule syntax to allow creation of
access rules. For example, a table owner creates and populates table T
(username char(30), title char(20), classified_data char(1024)):
“AA”, “Administrative Assistant”, “Memo to President”
“AA”, “Administrative Assistant”, “Tracking Stock Movements”
“VP1”, “Vice President”, “Meeting Schedule”
“VP2”, “Vice President”, “Meeting Schedule”
The table owner creates a default and a domain rule on the username column.
The domain rule ensures that the column is updated with correct values. If the
default and domain rules are not created, there is a potential security problem
in which the user can insert a row into the table with arbitrary data that will
not be qualified by the access rule.
The table owner then creates an access rule and binds it to the username
column using sp_bindrule.
create default uname_default
sp_bindefault uname_default, "T.username"
create access rule uname_acc_rule
138 Chapter 6: Security