Database Tables

SQL Language in 602SQL

Functions in SQL

Privileges and Privilege Subjects in SQL

Editing privilege subjects:

• Creating and deleting users, groups and roles.
• Setting and identifying relations between privilege subjects.

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

• Identifying privileges of the specified privilege subject to the specified table or table records (details)
• Setting privileges (grant or revoke) for the specified subjects on a table or table records (see the GRANT and REVOKE SQL statements).

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:

• Determining Privileges in the SQL Language

Database Tables

SQL Language in 602SQL

Functions in SQL