The database administrator (DBA) can check and modify user authorization by calling authorization-related methods defined in db_user where information about database user is stored, and db_authorizations (the system authorization class). The administrator can specify db_user or db_authorization depending on the method to be called, and save the return value of a method to a variable. In addition, some methods can be called only by DBA or members of DBA group.
CALL method_definition ON CLASS auth_class [ TO variable ] [ ; ]
CALL method_definition ON variable [ ; ]
As a class method of db_user class, this method is used to change the users who are currently connected to the database. The name and password of a new user to connect are given as parameters, and they must be string type. If there is no password, a blank string ('') can be used as the parameter. DBA and DBA members can call the login( ) method without a password.
-- Connect as DBA user who has no password
CALL login ('dba', '') ON CLASS db_user;
-- Connect as a user_1 whose password is cubrid
CALL login ('user_1', 'cubrid') ON CLASS db_user;
As a class method of db_user class, this method is used to add a new user. The name and password of a new user to add are given as parameters, and they must be string type. At this time, the new user name should not duplicate any user name already registered in a database. The add_user( ) can be called only by DBA or members of DBA group.
-- Add user_2 who has no password
CALL add_user ('user_2', '') ON CLASS db_user;
-- Add user_3 who has no password, and save the return value of a method into an admin variable
CALL add_user ('user_2', '') ON CLASS db_user to admin;
As a class method of db_user class, this method is used to drop an existing user. Only the user name to be dropped is given as a parameter, and it must be a string type. However, the owner of a class cannot be dropped thus DBA needs to specify a new owner of the class before dropping the user. The drop_user( ) method can be also called only by DBA or members of DBA.
-- Delete user_2
CALL drop_user ('user_2') ON CLASS db_user;
As a class method of db_user class, this method is used to find a user who is given as a parameter. The name of a user to be found is given as a parameter, and the return value of the method is stored into a variable that follows 'to'. The stored value can be used in a next query execution.
-- Find user_2 and save it into a variable called 'admin'
CALL find_user ('user_2') ON CLASS db_user to admin;
This method is an instance method that can call each user instance, and it is used to change a user's password. The new password of a specified user is given as a parameter. General users other than DBA and DBA group members can only change their own passwords.
-- Add user_4 and save it into a variable called user_common
CALL add_user ('user_4','') ON CLASS db_user to user_common;
-- Change the password of user_4 to 'abcdef'
CALL set_password('abcdef') on user_common;
As a class method of db_authorizations class, this method is used to change the owner of a class. The name of a class for which you want to change the owner, and the name of a new owner are given as parameters. At this time, the class and owner that are specified as a parameter must exist in a database. Otherwise, an error occurs. change_owner( ) can be called only by DBA or members of DBA group.
-- Change the owner of table_1 to user_4
CALL change_owner ('table_1', 'user_4') ON CLASS db_authorizations;
The following is an example of a CALL statement that calls the find_user method defined in the system table db_user. It is called to determine whether the database user entered as the find_user exists. The first statement calls the table method defined in the db_user class. The name (db_user in this case) is stored in x if the user is registered in the database. Otherwise, NULL is stored.
The second statement outputs the value stored in the variable x. In this query statement, the DB_ROOT is a system class that can have only one record. It can be used to output the value of sys_date or other registered variables. For this purpose, the DB_ROOT can be replaced by another table having only one record.
CALL find_user('dba') ON CLASS db_user to x;
=== <Result of CALL Command in Line 1> ===
SELECT x FROM db_root;
=== <Result of SELECT Command in Line 1> ===
With find_user, you can determine if the user exists in the database depending on whether the returned value is NULL or not.