User Management¶
Database User¶
To know the user name’s writing rule, see Identifier.
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 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.
Note
From version 11.3 of CUBRID, DBA members can also query objects of all users in the system catalog virtual class.
CREATE USER¶
DBA and DBA members can create, drop and alter users by using SQL statements. At the initial installation, passwords for users are not configured.
CREATE USER user_name
[PASSWORD password]
[GROUPS user_name [{, user_name } ... ]]
[MEMBERS user_name [{, user_name } ... ]]
[COMMENT 'comment_string'];
DROP USER user_name;
ALTER USER user_name PASSWORD password;
user_name: specifies the user name to create, delete or change.
password: specifies the user password to create or change.
comment_string: specifies a comment for the user.
The following example shows how to create a user (Fred), change a password, and delete the user.
CREATE USER Fred;
ALTER USER Fred PASSWORD '1234';
DROP USER Fred;
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.
CREATE USER company;
CREATE USER engineering GROUPS company;
CREATE USER marketing GROUPS company;
CREATE USER design GROUPS company;
CREATE USER smith GROUPS design, marketing;
CREATE USER jones GROUPS marketing;
CREATE USER brown GROUPS engineering;
The following example shows how to create the same groups as above but use the MEMBERS keyword instead of GROUPS.
CREATE USER smith;
CREATE USER brown;
CREATE USER jones;
CREATE USER engineering MEMBERS brown;
CREATE USER marketing MEMBERS smith, jones;
CREATE USER design MEMBERS smith;
CREATE USER company MEMBERS engineering, marketing, design;
User’s COMMENT¶
A comment for a user can be written as follows.
CREATE USER designer GROUPS dbms, qa COMMENT 'user comment';
A comment for a user can be changed as the following ALTER USER statement.
ALTER USER DESIGNER COMMENT 'new comment';
You can see a comment for a user with this syntax.
SELECT name, comment FROM db_user;
GRANT¶
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 users. You can grant access authorization to a user by using the GRANT statement.
GRANT operation [{, operation }] ON [schema_name.]table_name [{, [schema_name.]table_name}]
TO user [{,user }] [WITH GRANT OPTION] ;
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 create indexes on columns to speed up searches.
EXECUTE: Allows to call table methods or instance methods.
ALL PRIVILEGES: Includes all permissions described above.
schema_name: Specifies the schema name of the table or virtual table. If omitted, the schema name of the current session is used.
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 smith (including his members).
GRANT SELECT ON olympic TO smith;
The following example shows how to grant the SELECT, INSERT, UPDATE and DELETE authorization on the nation and athlete tables to brown and jones (including their members).
GRANT SELECT, INSERT, UPDATE, DELETE ON nation, athlete TO brown, jones;
The following example shows how to grant every authorization on the tbl1 and tbl2 tables to all users(public).
CREATE TABLE tbl1 (a INT);
CREATE TABLE tbl2 (a INT);
GRANT ALL PRIVILEGES ON tbl1, tbl2 TO public;
The following example shows how to grant retrieving authorization on the record and history tables to brown. Using WITH GRANT OPTION allows brown to grant retrieving to another users. brown can grant authorization to others within his authorization.
GRANT SELECT ON record, history TO brown WITH GRANT OPTION;
Note
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.
REVOKE¶
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.
REVOKE operation [{, operation}] ON [schema_name.]table_name [{, [schema_name.]table_name}]
FROM user [{, user}] ;
operation: Indicates an operation that can be used when granting authorization (see Syntax in GRANT for details).
schema_name: Specifies the schema name of the table or virtual table. If omitted, the schema name of the current session is used.
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 smith and jones so that they can perform on the nation and athlete tables.
GRANT SELECT, INSERT, UPDATE, DELETE ON nation, athlete TO smith, jones;
The following example shows how to execute the REVOKE statement; this allows jones to have only SELECT authorization. If jones has granted authorization to another user, the user is also allowed to execute SELECT only.
REVOKE INSERT, UPDATE, DELETE ON nation, athlete FROM jones;
The following example shows how to execute the REVOKE statement revoking all authorization that has granted to smith. smith is not allowed to execute any operations on the nation and athlete tables once this statement is executed.
REVOKE ALL PRIVILEGES ON nation, athlete FROM smith;
ALTER … OWNER¶
Database Administrator (DBA) or a member of the DBA group can change the owner of table, view, trigger, and Java stored functions/procedures by using the following query.
ALTER (TABLE | CLASS | VIEW | VCLASS | TRIGGER | PROCEDURE | FUNCTION) [schema_name.]name OWNER TO user_id;
schema_name: Specifies the schema of the object. If omitted, the schema name of the current session is used.
name: The name of schema object of which owner is to be changed
user_id: User ID
ALTER TABLE test_tbl OWNER TO public;
ALTER VIEW test_view OWNER TO public;
ALTER TRIGGER test_trigger OWNER TO public;
ALTER FUNCTION test_function OWNER TO public;
ALTER PROCEDURE test_procedure OWNER TO public;