RDB Roles and Permissions

The Research Database has three custom roles:

NSWERS_db_reader

This role is the one to give normal NSWERS users SELECT (i.e. Read-Only) access to almost all the the tables in RDB. It currently does not include permissions to:

• _SchemaVersions
• _SyncMetaData
• ps2_financialaid
• ps4_financialaid

NSWERS_db_restricted

This rule grants SELECT permission to:

• ps2_financialaid
• ps4_financialaid

NSWERS_wh_builder

This role grants SELECT,INSERT,UPDATE,ALTER,DELETE permissions (i.e. CRUD) on:

• All warehouse tables (starting with wh_)
• _SchemaVersions
• _SyncMetaData

Granting Restricted Access to Financial Aid Tables

In order to grant access to non-NSWERS_db_restricted members, a view must be created. As policies indicate this should be time-boxed, the view definition should include the start and end times.

Example script to restrict access to between 1/1/2026 and 1/15/2026 to 2-year PS institution:

CREATE VIEW [dbo].[vw_ps2financialaid_<projectname or identifier>]
AS
SELECT   dbo.ps2_financialaid.*
FROM         dbo.ps2_financialaid
WHERE CURRENT_DATE between '2026-01-01' and '2026-01-15';
GO

Example script to restrict access to between 1/1/2026 and 1/15/2026 to all PS institution:

ALTER VIEW [dbo].[vw_financialaid_<projectname or identifier>]
AS
SELECT   dbo.ps2_financialaid.* FROM dbo.ps2_financialaid
UNION
SELECT   dbo.ps4_financialaid.* FROM dbo.ps4_financialaid
WHERE CURRENT_DATE between '2026-01-01' and '2026-02-15';
GO

After creating the view, permissions need to be granted for the user:

  GRANT SELECT ON vw_financialaid_<projectname or identifier> to [<user>@nebraska.edu]