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_authorization (the system authorization class). The administrator can specify db_user or db_authorization depending on the method to be called, and store the return value of a method to a variable. In addition, some methods can be called only by DBA or members of DBA group.
Note For the HA environment, you should notice that method call on the master node is not applied to the slave node.
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 character ('') 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_3', '') ON CLASS db_user;
-- Add user_3 who has no password, and store 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 store 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 store 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 example shows 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;
SELECT x FROM db_root;
With find_user, you can determine if the user exists in the database depending on whether the return value is NULL or not.