Managing access with users, groups, and table settings

This topic includes the following sections:

• Level-based users and groups
• Privilege-based users and privileges
• Understanding access levels for tables and groups

 

xfODBC access to a Synergy database and the system catalog is controlled in one of two ways:

• By level-based users and groups, which are managed using the DBA program.
• By privilege-based users and their privileges (database and object privileges), which are managed with SQL statements.

Table access levels also play a part, though that varies depending on the type of user. See Understanding access levels for tables and groups below.

Although you can give users read/write access to data via xfODBC, for updating Synergy data we strongly recommend using a Synergy application that is designed to efficiently maintain database integrity. See Statements that modify data for more information.

Level-based users and groups

With level-based users (the only type of user available until Connectivity Series version 11), access to the database is controlled by access levels assigned to tables and groups, and a user’s access is based on membership in a group. This type of user is supported if your system catalog has SODBC_USERS and SODBC_GROUPS files — i.e., if the system catalog was generated with the -p option for dbcreate or the “Initialize users and groups” option for the DBA program.

When you add support for level-based users and groups by “initializing”, an initial set of users and groups is created. This set includes three default users (DBA, DBADMIN, and PUBLIC) and two default groups (SYSTEM and USER). Once you have this initial set, you can manage access to your database by using these users and groups and by creating other users and groups. See Managing level-based users and groups for more information.

Privilege-based users and privileges

A system catalog supports privilege-based users if it has GENESIS_AUTHS and GENESIS_USERS tables and files (see System catalog) — i.e., if it was generated using dbcreate with the -c and -a options. With this type of user,

• standards-compliant passwords and password encryption are used. Passwords can be up to 30 characters long and are encrypted using a SHA512 encoding scheme.
• access to the database is controlled by database privileges.
• Access to database tables and views are controlled by object privileges.
• users and user privileges (database and object) are added and managed using SQL statements.
• there are two default users, DBA and DBADMIN, which both have the DBA database permission.

See Managing privilege-based users and privileges for more information.

Additionally, table access levels are used only to determine whether read/write access or read-only access is allowed for a table. See Understanding access levels for tables and groups below.

Understanding access levels for tables and groups

Tables and groups have access levels, which range from 0 to 255.

• For level-based users, the access level of a group applies to all users in the group. For users in a group to access a table, that group must have an access level equal to or greater than the table’s access level. Access levels are further defined by odd and even numbers. Even numbers allow read-only access; odd numbers allow read/write access. This applies to both tables and groups. (See Level-based access illustrated below to see how this works.) There is an exception: if a group is set to level 255, members of that group have read/write access to all tables, even tables with access levels that are even numbers.
• For privilege-based users, table access levels are used, but only to determine whether read-only or read-write access is allowed for the table. An even number allows read-only access. An odd number allows read/write access. For example, a table with an access level of 101 would be writable, while a table with an access level of 100 would be read-only. There is an exception: users with the DBA database privilege have read/write access to all tables, even tables with access levels that are even numbers. (Groups are not used for privilege-based users, so there are no group access levels.)

When you generate the system catalog, all database tables are assigned an access level of 100 (read-only) by default. (System tables are assigned an access level of 99.) You can use a conversion setup file to change table access levels. For more information, see Modifying table access levels.

Level-based access illustrated

The table below illustrates how access levels work for level-based users. Note the following:

• Users in group 1 cannot access table B because their access level (100) is less than that of the table (101).
• Even with an access level of 254, group 3 cannot write to table B because 254 is an even number, meaning group 3 has read-only access. In order for a group to have read/write access to a table, both the group and the table must have an odd-numbered access level.
• Users in group 4 are “super users”. Because their access level is 255, they have read/write access to all tables, even tables with read-only access.

  	Group 1 access=100 (R)	Group 2 access=101 (R/W)	Group 3 access=254 (R)	Group 4 access=255 (R/W)
  Table A access=100 (R)	Read	Read	Read	Read/write
  Table B access=101 (R/W)	No access	Read/write	Read	Read/write