Navigation

CUBRID has two types of users by default: DBA and PUBLIC. At initial installation of the product, no password is set.

All users have authorization granted to the PUBLIC user. All users of the database are automatically the members of PUBLIC. Granting authorization to the PUBLIC means granting it all users.

The DBA user has the authorization of the database administrator. The DBA automatically becomes the member of all users and groups. That is, the DBA is granted the access for all tables. Therefore, there is no need to grant authorization explicitly to the DBA and DBA members. Each database user has a unique name. The database administrator can create multiple users simultaneously using the cubrid createdb utility (see cubrid Management Utilities for details). A database user cannot have a member who already has the same authorization. If authorization is granted to a user, all members of the user is automatically granted the same authorization.

The following example shows how to create a user (Fred), change a password, and delete the user.

CREATEUSERFred;ALTERUSERFredPASSWORD'1234';DROPUSERFred;

The following example shows how to create a user and add member to the user. By the following statement, company becomes a group that has engineering, marketing and design as its members. marketing becomes a group with members smith and jones, design becomes a group with a member smith, and engineering becomes a group with a member brown.

In CUBRID, the smallest grant unit of authorization is a table. You must grant appropriate authorization to other users (groups) before allowing them to access the table you created.

You don't need to grant authorization individually because the members of the granted group have the same authorization. The access to the (virtual) table created by a PUBLIC user is allowed to all other users. You can grant access authorization to a user by using the GRANT statement.

operation: Specifies an operation that can be used when granting authorization. The following table shows operations.

SELECT: Allows to read the table definitions and retrieve records. The most general type of permissions.

INSERT: Allows to create records in the table.

UPDATE: Allows to modify the records already existing in the table.

DELETE: Allows to delete records in the table.

ALTER: Allows to modify the table definition, rename or delete the table.

INDEX: Allows to call table methods or instance methods.

EXECUTE: Allows to call table methods or instance methods.

ALL PRIVILEGES: Includes all permissions described above.

table_name: Specifies the name of a table or virtual table to be granted.

user: Specifies the name of a user (group) to be granted. Enter the login name of the database user or PUBLIC, a system-defined user. If PUBLIC is specified, all database users are granted with the permission.

WITH GRANT OPTION: WITH GRANT OPTION allows the grantee of authorization to grant that same authorization to another user.

The following example shows how to grant the SELECT authorization for the olympic table to Fred (including his members).

GRANTSELECTONolympicTOFred;

The following example shows how to grant the SELECT, INSERT, UPDATE and DELETE authorization on the nation and athlete tables to Jenifer and Daniel (including their members).

The following example shows how to grant every authorization on the game and event tables to all users.

GRANT ALL PRIVILEGES ON game, event TO public;

The following example shows how to grant retrieving authorization on the record and history tables to ROSS. Using WITH GRANT OPTION allows ROSS to grant retrieving to another users. Ross can grant authorization to others within her authorization.

GRANTSELECTONrecord,historyTORossWITHGRANTOPTION;

The grantor of authorization must be the owner of all tables listed before the grant operation or have WITH GRANT OPTION specified.

Before granting SELECT, UPDATE, DELETE and INSERT authorization for a virtual table, the owner of the virtual table must have SELECT and GRANT authorization for all the tables included in the query specification. The DBA user and the members of the DBA group are automatically granted all authorization for all tables.

To execute the TRUNCATE statement, the ALTER, INDEX, and DELETE authorization is required.

You can revoke authorization using the REVOKE statement. The authorization granted to a user can be revoked anytime. If more than one authorization is granted to a user, all or part of the authorization can be revoked. In addition, if authorization on multiple tables is granted to more than one user using one GRANT statement, the authorization can be selectively revoked for specific users and tables.

If the authorization (WITH GRANT OPTION) is revoked from the grantor, the authorization granted to the grantee by that grantor is also revoked.

operation : Indicates an operation that can be used when granting authorization (see Syntax in Granting Authorization for details).

table_name : Specifies the name of the table or virtual table to be granted.

user : Specifies the name of the user (group) to be granted.

The following example shows how to grant SELECT, INSERT, UPDATE and DELETE authorization to Fred and John so that they can perform on the nation and athlete tables.

GRANTSELECT,INSERT,UPDATE,DELETEONnation,athleteTOFred,John;

The following example shows how to execute the REVOKE statement; this allows John to have only SELECT authorization. If John has granted authorization to another user, the user is also allowed to execute SELECT only.

REVOKEINSERT,UPDATE,DELETEONnation,athleteFROMJohn;

The following example shows how to execute the REVOKE statement revoking all authorization that has granted to Fred. Fred is not allowed to execute any operations on the nation and athlete tables once this statement is executed.

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, or db_authorizations (the system authorization class). The administrator can specify db_user or db_authorizations 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

Note that method call made by the master node is not applied to the slave node in the HA environment.

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 arguments, and they must be string type. If there is no password, a blank character ('') can be used as the argument. DBA and DBA members can call the login( ) method without a password.

-- Connect as DBA user who has no passwordCALLlogin('dba','')ONCLASSdb_user;-- Connect as a user_1 whose password is cubridCALLlogin('user_1','cubrid')ONCLASSdb_user;

add_user( ) method

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 arguments, 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 passwordCALLadd_user('user_3','')ONCLASSdb_user;-- Add user_3 who has no password, and store the return value of a method into an admin variableCALLadd_user('user_2','')ONCLASSdb_usertoadmin;

drop_user( ) method

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 an argument, 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_2CALLdrop_user('user_2')ONCLASSdb_user;

find_user( ) method

As a class method of db_user class, this method is used to find a user who is given as an argument. The name of a user to be found is given as an argument, 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'CALLfind_user('user_2')ONCLASSdb_usertoadmin;

set_password( ) method

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 an argument. 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_commonCALLadd_user('user_4','')ONCLASSdb_usertouser_common;-- Change the password of user_4 to 'abcdef'CALLset_password('abcdef')onuser_common;

change_owner() method

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 arguments. At this time, the class and owner that are specified as an argument must exist in a database. Otherwise, an error occurs. change_owner( ) can be called only by DBA or members of DBA group. The ALTER ... OWNER query has the same role as the method. See Changing Owner.

-- Change the owner of table_1 to user_4CALLchange_owner('table_1','user_4')ONCLASSdb_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.