Change User Access
The OpenBase Manager includes graphical tools that make it easy to manage database users. But for those who need to manage users programmatically, this section offers some instruction.
OpenBase SQL database users are stored in the _SYS_USERS table. Records that are inserted into this table are automatically used for database user authentication. The password() function is specifically designed to insert an encrypted password into the record. Here is an example:
update _SYS_USERS SET password = password(“mypassword”) where username = "admin"
GRANT… ACCESS… TO
GRANT… ACCESS… TO provides a way to grant and revoke permission for users to access tables. For your convenience, all users have access to all tables before any permissions have been assigned. When a specific user is assigned access to a table, all other users which had access previously by default are assumed not to have access. If you are not concerned about security, we recommend that you leave the permissions alone.
The OpenBase Manager includes graphical tools that make it easy to edit and maintain user permissions. However, for those applications that require a knowledge of how permissions are granted and revoked, we have included this section. Please note that GRANT… ACCESS… TO is not part of the ANSI SQL standard. Here is an example:
GRANT joe, fred ACCESS +su-di TO MOVIES, TALENT
GRANT… ACCESS… TO will assign the specified access to the users and tables listed in the statement. The access portion is specified as a string of characters as shown above. They are defined as follows:
s SELECT access selects data from the specified tables.
u UPDATE access updates data in the specified tables.
i INSERT access inserts data into the specified tables.
d DELETE access deletes data from the specified tables.
The + and - which precede the letters tell whether to revoke access (-) or grant access (+). If you wish to assign one type of access without altering setting for other types of access, only include the access letters which you wish to change.
GRANT staff, marketing ACCESS +sui-d TO MOVIES
This example shows how you can grant and revoke access to groups of people. In this case, the staff and marketing groups have been given access to read and change data but not to delete.
The group permissions do not overwrite the permissions for individuals. Users are allowed to perform inserts, updates, selects and deletes on tables where either the user or the users group has been granted access.