User accounts are created with the CREATE USER command.
Syntax:
CREATE USER new-user-name IDENTIFIED BY [HASHED] password [(properties)]
It is necessary to provide a password string for the created user account. If the HASHED keyword is used, the string password provides the password hash.
The properties string specifies the set of access properties for the new user account. If omitted, the deafault set of properties is assigned. See the User properties section below for details.
The above commands can only be issued by an administrator.
New users can also be created from the user interface level
When a new user is created his/her user properties can be set. If not, the default combination of user properties (i.e. crw) is assigned.
The string properties can be any of the follwoing:
a – the new user is an administrator
s – the content of all the commands executed by this user will be visible to all users calling the SHOW PROCESSES command
c – permits the user to create tables and views. This user can issue the following commands:
r – if a user with this property creates a new table, then by default all other user will be able to read data from this table (see the Access control chapter for more details).
w - if a user with this property creates a new table, then by default all other user will be able to write data to this table (see the Access control chapter for more details).
The properties r and w have effect only when a particular table is created, i.e. if these properties of the user who created the table are changed later on, the default access rights of other users to this table will not be affected. The same applies to a situation when the owner of a particular table is changed to one with a different set of properties.
User properties can be changed with the ALTER USER command. The new properties determine the privileges for newly created tables and views in the database.
Syntax:
ALTER USER user-name ADD | DROP properties
The ADD variant adds properties to the specified user; if a property is already assigned to the given user nothing changes.
The DROP variant removes properties from the set of properties assigned to the specified user.
Only an administrator can issue the above commands without any restrictions on the added or removed properties. Other users can only change their own properties restricted to the values r, w and s.
User properties can also be changed from the user interface level.
Syntax:
SHOW USER user-name-spec
This command displays the property sets of the users specified by the user-name-spec string. This string is processed using the specification for the GLOB operator.
This command can be issued by all users.
Displayed information:
Any user regardless of privileges will see the names of all users matching the user-name-spec parameter.
The user will not be able to see his/her hashed password.
Any user will be able to see his/her properties, even if other users match the user-name-spec parameter (null values will be displayed for other users).
An administrator can see hashed passwords and properties of all users matching the user-name-spec parameter.
The results are sorted by the user column.
User properties can also be displayed from the user interface level.
When a user (with the c property) creates a table or view in the database he/she automatically becomes its owner.
Syntax:
ALTER TABLE [catalog.]table-name SET OWNER user-name
This command is available to administrators. It makes the user identified by user-name the owner of the table table-name (located in the catalog catalog).
There are two commands for checking table ownership. Both can be executed by any user:
Syntax:
SHOW OWNER [catalog.]table-name
This command displays the owner of the table table-bname (located in the 'catalog' catalog).
Syntax:
SHOW USER TABLES user-name
This command displays the list of all objects owned by the specified user.
Whenever a user attempts to access a table or view for writing or reading, GDBase checks if the user has been granted the appropriate privileges: r for reading a table or view and w for writing.
A detailed description of the operations allowed by these privileges can be found in the Access control chapter.
There are two kinds of privileges that a user may have: default and special.
Default privileges. These are determined by the 'r' and 'w' properties of the user who created a table or view. For example, if there is a user 'user1', who has been created with the command
CREATE USER 'user1' IDENTIFIED BY 'user1password' ('cr')then the default privileges for the tables and views created by the user user1 allow other database users to read data but not write data.
Special privileges. These are granted to and revoked from a given user by an administrator or the owner of a table or view, regardless of the default privileges, over which they have priority. For example, the user user1 (created in the previous paragraph), who owns the table table1, or the administrator, can execute the command
GRANT 'w' ON table1 TO 'user2'
In effect, the user user2 will additionally be able to write to the table table1.
Actual privileges. These are the privileges verified by GDBase whenever a table or view is accessed by a user. If the user has special privileges, then they become actual. Otherwise this role is taken on by the default privileges.
Privileges are granted using the GRANT command. This command can be executed only by an administrator or the owner of the table in question.
Syntax:
GRANT properties ON [catalog.]table-name [ TO ALL ]
This variant of the GRANT command adds the properties listed in the properties string to the set of the default properties of the table table-name (located in the catalog catalog).
If the TO ALL clause is included, then the specified properties are added to the set of special privileges of all users with special privileges for this table.
Syntax:
GRANT properties ON [catalog.]table-name TO user-name
Grants the specified user special privileges for the table table-name (located in the catalog catalog), as specified in properties; from this moment the access to the table by the specified user is determined on the basis of these privileges instead of the default ones. This command works in an additive manner, i.e. it only adds privileges to the set of existing ones.
Syntax:
GRANT DEFAULT ON [catalog.]table-name TO user-name
This command revokes any special privileges the specified user has for the table table-name (located in the catalog catalog). After issuing this command the access rights of the user to the table will be determined solely on the basis of this table's default properties.
Privileges can be revoked using the REVOKE command. This command can be executed only by an administrator or the owner of the table in question.
Syntax:
REVOKE properties ON [catalog.]table-name [ FROM ALL ]
This command removes the properties specified in properties for the table table-name located in the catalog catalog) from the set of the default properties of this table.
Additionally, if the FROM ALL clause is present, the command removes the specified privileges from the set of special privileges of all users with special privileges for this table.
The following commands can be used to display the granted privileges. They can be executed by any user.
Syntax:
SHOW [ DEFAULT USER ] GRANTS [ ON [catalog.]table-name ] FOR user-name
Without the DEFAULT USER clause, this command displays the actual privileges of the specified user. If the table-name is not provided (i.e. the ON ... clause is absent) the actual privileges for for all tables and views are displayed, otherwise only the actual privileges for the specified table are shown.
If the DEFAULT USER clause is present, then the command displays default privileges instead of actual ones. (i.e. any special provileges of the user are disregarded) For non-administartors and users who do not own the table the returned result is the same as without the DEFAULT USER clause.
Syntax:
SHOW [ DEFAULT TABLE ] GRANTS ON [catalog.]table-name
Without the DEFAULT TABLE clause this command displays the actual privileges for the specified table. With the DEFAULT TABLE clause the default privileges are displayed.
The following command can be used to change user passwords:
Syntax:
[ ALTER USER user-name ] SET PASSWORD password
Without the ALTER USER part, this command changes the password of the user executing the command
The variant with the ALTER USER part changes the password of the specified user. It can be executed only by an administartor or the user identified by user-name.
From the user interface level it is possible to change one's own password and clear a user's password.
Syntax:
DROP USER user-name
This command deletes the specified user. It can only be executed by an administrator.
Users can also be deleted from the user interface level .
A user who deletes another user automatically becomes the owner of all objects owned by the deleted user.