Security

Modeller users

Modeller users enter modelling assumptions into the Raedfast front-end and process the Model.

The simplest way to grant to the modeller the permissions they need is to make the user the database owner of the Raedfast database.

If this is felt to grant the modeller too much privilege, you can restrict the modeller to the minimum privileges required to operate the system, by adding the user to the rb_modeller Role. This Role confers on the user only those privileges required to operate the Raedfast front-end.

The rb_modeller Role

The specific privileges granted under the rb_modeller Role are as follows:

Grant create table

grant create table to rb_modeller

Grant read/write to application data in dbo and wk

grant update,insert,delete,select on schema :: dbo to rb_modeller

grant update,insert,delete,select on schema :: wk to rb_modeller

Grant read/write and alter to transient working schemas

grant update,insert,delete,select,alter on schema :: wk1 to rb_modeller

grant update,insert,delete,select,alter on schema :: wk2 to rb_modeller

Grant select and execute only, to permanent system schemas

grant select, execute on schema :: rb to rb_modeller

grant select, execute on schema :: rbf to rb_modeller

grant select, execute on schema :: rbapp to rb_modeller

grant select, execute on schema :: wrapper to rb_modeller

grant select, execute on schema :: local to rb_modeller

grant select on schema :: rbstar to rb_modeller

grant select on schema :: star to rb_modeller

The rb_admin User

There is one special SQL Server database User contained within the Raedfast database. This is the rb_admin User. The rb_admin User is assumed by certain Raedfast Stored Procedures (using the Transact SQL Execute As syntax), to carry out functions which are not normally available to members of the rb_modeller Role.

The Raedfast modeller user can only access the privileges available to the rb_admin User by executing the specific Stored Procedures which execute as the rb_admin User.

The specific privileges granted to the rb_admin User are:

alter role db_datareader add member rb_admin

alter role db_datawriter add member rb_admin

alter role db_ddladmin add member rb_admin

alter role db_backupoperator add member rb_admin

alter role rb_modeller add member rb_admin

Analysis Services users

As with SQL Server, users of Analysis Services fall into three groups: system administration, Raedfast modellers and reporting users.

System administrator

System administrators naturally have full control over the Analysis Services instance.

Modeller and users

Raedfast modeller users require read-write privileges over the Analysis Services database, including the ability to delete and recreate the database if the modeller user should add, rename or delete Measures from the Raedfast Model.

Reporting user

Reporting users of Analysis Services can simply be granted Read access.

Building and rebuilding the Analysis Services Database

The Analysis Services database is built automatically by executing stored procedure rb.AsBuildDatabase. This is done on installation of a new Raedfast database.

If measures are subsequently added, renamed or deleted, the Analysis Services database has to be rebuilt, using Task Rebuild Analysis Services Database, which in turn invokes the stored procedure rb.AsBuildDatabase.

Now before the Raedfast Analysis Services database is rebuilt, this stored procedure deletes the existing database, and all security Roles in the database (but not the Server) are wiped out. So as Raedfast builds the new database, the system automatically:

• creates a Role called System Admin in the database, and adds to the Role all SQL Server users who are members of the rb_modeller Role in SQL Server (provided these relate to Windows logins, as is required for Analysis Services, and not SQL Server logins)

• creates a Role called Reader in the database, and adds to the Role all SQL Server users who are members of the built-in db_datareader Role in SQL Server (provided again these relate to Windows logins)

Note that the user executing the procedure rb.AsBuildDatabase must have System Admin privileges on the Analysis Server.

So in order to give a (modeller) user read-write access to the Analysis Services database you should enroll them in the rbmodeller role in Sql Server. And to give a user read-only access you should enroll them in the db_datareader role. Then execute the stored procedure rb.AsBuildDatabase.