In Sybase ASE, the database group paradigm allows just one level of hierarchy .. which some development teams might find limiting.
The role paradigm, however, allows complex/nested memberships.
There is one drawback, however: These role assignments do no travel with the database dumps/backups - they are similar to logins.
To illustrate:
Company A uses group assigments in their databases. When they need to load a dump file to another server, they only need to verify master..syslogins matches the source server's syslogins.
Company B uses role assigments in their databases. When they need to load a dump file to another server, they need to verify master..syslogins matches the source server's syslogins .. AND they need to add the roles to the target server .. AND they need to re-apply the role memberships/grants after the DB is loaded.
-- Create a hierarchy in which AcctRole and SalesRole roll up to DeptMgmt
create role DeptMgmt
go
create role AcctRole
go
create role SalesRole
go
sp_role 'grant','AcctRole','DeptMgmt'
go
sp_role 'grant','SalesRole','DeptMgmt'
go
-- Add jsmith to the AcctRole role
sp_role 'grant','AcctRole','jsmith'
go
sp_modifylogin 'jsmith', 'add default role', 'AcctRole'
go
-- Display user/role assigments
select t1.name, t3.name
from syslogins t1, sysloginroles t2, syssrvroles t3
where t2.suid = t1.suid and
t3.srid = t2.srid
order by 1,2
go
|
|
