Granting Authorities

Because you created MUSICDBE, you have DBA (database administrator authority), which lets you grant authorities to other users. In a simple authorization scheme, you first grant CONNECT authorization to permit access to the DBEnvironment itself. Then you grant table authorities to specific users or groups of users. You can also include the special user PUBLIC, which includes anyone who has the authorization to CONNECT to the DBEnvironment. Use the following statements to create an authority scheme for two groups of users, announcers and librarians, while excluding all others (PUBLIC). First, create two authorization groups:

   isql=> CREATE GROUP Librarians; Return

   isql=> CREATE GROUP Announcers; Return

Next, add users. In MPE/iX:

   isql=> ADD Ann@Library, Peter@Library TO Return

   > GROUP Librarians; Return

   isql=> ADD Fred@Control, Julia@Control TO Return

   > GROUP Announcers; Return

In HP-UX:

   isql=> ADD Ann, Peter TO Return

   > GROUP Librarians; Return

   isql=> ADD Fred, Julia TO Return

   > GROUP Announcers; Return

Next, grant the needed authorities, as follows:

   isql=> GRANT CONNECT TO Return

   > Announcers, Librarians; Return

   isql=> GRANT ALL ON Albums Return

   > TO Librarians; Return

   isql=> GRANT ALL ON Titles Return

   > TO Librarians; Return

   isql=> GRANT SELECT ON Albums Return

   > TO Announcers; Return

   isql=> GRANT SELECT ON Titles Return

   > TO Announcers; Return

Finally, revoke authorities on PUBLIC tables from PUBLIC:

   isql=> REVOKE ALL ON Albums Return

   > FROM PUBLIC; Return

   isql=> REVOKE ALL ON Titles Return

   > FROM PUBLIC; Return

   isql=> COMMIT WORK; Return

Because you created these tables as PUBLIC (shareable by everyone) in an earlier step, you need to remove authorities from PUBLIC. This retains the locking characteristics of PUBLIC tables while restricting access to just those users specified in your GRANT statements. This is the normal procedure for tables that will have restricted but still multi-user access in the DBEnvironment.

You can also use views to provide restricted access to portions of tables. After creating the view, you can grant access on it to a specific user or group:

   isql=> GRANT SELECT ON Selections TO PUBLIC; Return