602SQL Documentation Index  

Privileges and Privilege Subjects in SQL

Editing privilege subjects:

Editing relations between privileges and users (or subject privileges):

Creating and Deleting Users, Groups and Roles

Users can be created using the CREATE USER statement and deleted using the DROP USER statement. User passwords can be set using the Set_password function.

User groups can be created using the CREATE GROUP statement and deleted using the DROP GROUP statement.

Application roles can be created using the CREATE ROLE statement and deleted using the DROP ROLE statement.

Privilege Subject Relations

Identifying or modyfying relations between two privilege subjects (e.g. assigning a user into a group, assigning a group into a role) is done using the Set_membership and Get_membership functions

Example:

PROCEDURE `INIT_SET_PRIVILS`();
/*******************************************/
// sets privileges to read, write, insert and update records for all table in the application 
// to the Internet_user role
BEGIN
  DECLARE sqlstr CHAR(500);
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    CALL log_write('Init_set_privils - '||sqlstate||' '||Int2str(sqlcode));
  END;

  FOR row AS INSENSITIVE CURSOR FOR
   SELECT T2.tab_name              // table list
   FROM OBJTAB T1, TABTAB T2 
   WHERE T1.APL_UUID=T2.APL_UUID
    AND (Ord(T1.category)=CATEG_APPL) AND (t1.obj_name=CURRENT_APPLICATION)
  DO
    SET sqlstr = 'GRANT DELETE, INSERT, SELECT, UPDATE ON '||row.tab_name||' TO ROLE INTERNET_USER';
    CALL SQL_execute(sqlstr);   // execute a dynamically created statement
  END FOR;
END

List of topics: