Row Level Access Rules example

1> sp_configure "enable row level access", 1
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable row level access 0 0 1 1 switch dynamic
(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'enable row level access' does not increase the amount of memory Adaptive Server uses.
(return status = 0)
1>
ACCESS RULES:
1> create table tstudent ( name varchar(10), id int )
2>

1> insert into tstudent values ( 'sysadmin', 1234)
2>
….
….

1> select * from tstudent
2>
name id
---------- -----------
sysadmin 1234
sa 3456
test 34567
sa 34566


1> create access rule rule_student as @name = suser_name()

1> sp_bindrule rule_student,"tstudent.name"
2>
Rule bound to table column.
(return status = 0)
1> select suser_name()
2>
------------------------------
sa
(1 row affected)
1> select * from tstudent
2>
name id
---------- -----------
sa 3456
sa 34566
(2 rows affected)
1> sp_unbindrule "tstudent.name",NULL, 'all'
2>
Rule unbound from table column.
(return status = 0)
1> select * from tstudent
2>
name id
---------- -----------
sysadmin 1234
sa 3456
test 34567
sa 34566
(7 rows affected)
1> drop rule rule_student
2>
1>