Read-only repository!

                Often I hop into the BOBJ technical forums lately. Only few topics really seize my attention, others I don’t know much about or I don’t want to plow into it. One such discussion was the possibility of creating two logins for a local repository with one account having the capability to change and other to just read only. Can this be done within the management console? .The answer is no. Finding a solution to this need a basic understanding of the repositories and how it works.

                What is a local repository? .Technically it’s a database contains metadata describing the objects (design components) in the repository. The login information to enter into the repository with the designer is the same as the login information to access the repository DB. So our objective here is clear: We need two logins to the same repository (not at the same time).But only one should be having the authorization to modify the objects and the other should be able to view only. Let’s look how can could implement his.

Here’s one of the solution with SQL server 2005.

1. Create a login called DS_Read_Only_Login in data base server where your local repository exists

2.Associate DS_Read_Only_Login with existing local repository LocalRepo (Make sure that LocalRepo already associated with the login DS_Full_Perm_Login which has the full permission) 

3. Grant SELECT and UPDATE permission for DS_Read_Only_Login to LocalRepo (UPDATE permission is required as when you login the designer, the table AL_USERS gets updated in the local repository.)(How to grant the permission: Object explorer -> Right click on the DB->Properties ->Permission)

4. Open the designer and login using DS_Full_Perm_Login and execute one of the error free jobs. And make sure that its executing without any error.

5. Open the designer and login using DS_Read_Only_Login and execute the same job. You will see the series of "Permission denied" errors!

 Here's the kicker, is it possible for the user with permission DS_Read_Only_Login able to modify the objects, as the permission UPDATE is granted for them? .No

When you modify the objects within the location repository and try to save, the first table which get affected (an INSERT) is AL_LANG.Since we have only UPDATE permission granted for DS_Read_Only_Login, INSERT operation fails and saving the designer objects too.

Hope this explanation serves up the purpose. Please share your thoughts about this post and we are looking forward to hear any other alternative solution.