The scope of this tutorial is to give you an introduction to the CUBRID database security. In this 1st part, we will talk about the CUBRID security model, the security support available in SQL and show examples.
Note: It is not in the scope of this tutorial to explain how to setup CUBRID in terms of security interaction with the OS or regarding the development of secure CUBRID applications – they are subject to separate upcoming tutorials. Also, we are assuming a certain familiarity of the reader with database security concepts – if you need, you can find a good introduction here: http://en.wikipedia.org/wiki/Database_security.
CUBRID Security model
Generally speaking, database security includes mechanisms that control access to and use of the database at the object level. Database security policy determines which users have access to a specific schema object, and the specific types of actions allowed for each user on the object.
CUBRID Security model is based on the Access Control List (ACL).
ACL means that different users will have varying levels of access to certain databases and tables and that they will be limited to performing only certain operations. For example, a user with full privileges to a database will be able to perform a range of operations such as SELECT, DELETE, UPDATE and INSERT, while a user with limited privileges would only be able to use the SELECT operation and so on.
In CUBRID, the main security “actors” are:
As you can see from the above image, a CUBRID database user can have members, which will have the same authorization(s). In other words, if authorization A is granted to a user, the same authorization is also granted to all members belonging to the user.
In CUBRID, a database user and its members are called a "group".
Authorizations in a CUBRID database can be setup at the following levels:
- Tables/Views/Serials access
- Stored procedures access
- Triggers access
- Grant/Revoke authorizations
Login access to the database is automatically granted to all users-members defined in the database. The only way to deny login is to delete the user-member from the database.
In CUBRID, the smallest grant unit of authorization is a table. This means that CUBRID does not support authorizations at the columns level.
The following table summarizes the type of privileges associated with CUBRID database objects:
Allows to modify table
Allows to delete data from the table
Allows to create a new table
Permission to drop table
Allows to query data from the table
Allows to create records in the table
Allows to call table methods or instance methods
Allows to call table methods or instance methods
Permission to update data in the table
The table name can be changed only by the table owner, dba and dba members. The other users must be granted permission to change the name by the owner or dba.
The dba user and the members of the dba group are automatically granted all authorizations for all tables.
The way to grant an access privilege on a view is the same as on a table – see “Table“ above.
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 queries in the virtual table's query specification.
All users including public can create a serial object. Once it is created, all users can get the number by using CURRENT_VALUE and NEXT_VALUE.
Only owner of a created serial object and dba can update or delete a serial object. If an owner is public, all users can update or delete it.
A Java stored function/procedure can be deleted only by the user who published it or by dba members. For example, if a public user published the 'sp_int' Java stored function, only the public or dba members can delete it.
Depends on trigger type
Trigger authorization is not granted explicitly. Authorization on the table trigger is automatically granted to the user if the authorization is granted on the event target table described in the trigger definition. In other words, triggers that have table targets (INSERT, UPDATE, etc.) are seen by all users.
User triggers (COMMIT and ROLLBACK) are seen only by the user who defined the triggers. All authorizations are automatically granted to the trigger owner.
A user trigger can only be changed by its owner.
CUBRID “out-of-the-box” users
By default, whenever you create a CUBRID database, there are two “out-of-the-box” users which are always created automatically: dba and public.
User dba can access all object in the database, meaning that it has authorization at the highest level. And it is only dba which has sufficient authorization to add, alter and delete database users.
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.
The public user is also created automatically with any CUBRID database, but it is granted only limited access by default.
All users of the database are automatically members of the public.
Therefore, all database users have the authorizations which are granted to public. For example, if authorization B is added to public group, all database members will automatically have the B authorization granted.
Granting authorization to the public means granting it to all database users.
Managing users and groups
Note: Only dba and dba members can create, drop and alter users by using SQL statements.
In the next section, we will present various examples of SQL statements for managing users and groups.
For more details and to find the exact SQL syntax that is available in CUBRID, please read the CUBRID manuals and documentation:
Creating, Updating and Deleting Users & Members
In this section, we will go through the SQL statements that are used for managing users & members, using examples.
Create user “Fred” with password “1234”:
CREATE USER Fred PASSWORD '1234';
The following is an example in which a user is created and then members are added to the user:
- 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
- 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 same results as above can be achieved using 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;
Change password for user “Fred” to “1a2b3c4d”:
ALTER USER Fred PASSWORD '1a2b3c4d';
Delete user “Fred”:
DROP USER Fred;
You must grant appropriate authorization to other users (groups) before allowing them to access the tables you created (and own). You can find the syntax for the SQL used for grant and revoke here:
You don't need to grant authorization individually because the members of the granted group have the same authorization.
Remember: The access to a Table or View created by a public user is allowed to all other users.
You can grant the following types of operations authorization to a user by using the GRANT SQL statement:
- SELECT: Allows to read the table definitions and retrieve records.
- 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.
Using WITH GRANT OPTION in a GRANT statement allows the grantee of authorization to grant that same privilege to another user. The grantor of authorization must be the owner of all tables listed before the grant operation or have WITH GRANT OPTION specified.
You can revoke privileges using the REVOKE statement. The privileges granted to a user can be revoked anytime. If more than one privilege is granted to a user, all or part of the privileges can be revoked. In addition, if privileges on multiple tables are granted to more than one user using one GRANT statement, the privileges can be selectively revoked for specific users and tables.
If the privilege (WITH GRANT OPTION) is revoked from the grantor, the privilege granted to the grantee by that grantor is also revoked (automatically).
SELECT authorization for the olympic table is granted to “Fred” (all members of “Fred” user):
GRANT SELECT ON olympic TO Fred;
SELECT, INSERT, UPDATE and DELETE authorization for the nation and athlete tables are granted to “Jenifer” and “Daniel” (all members belonging to “Jenifer” and “Daniel”):
GRANT SELECT, INSERT, UPDATE, DELETE ON nation, athlete TO Jenifer, Daniel;
All authorization for the game and event tables is granted to all users:
GRANT ALL PRIVILEGES ON game, event TO public;
The GRANT statement grants search authorization for the record and history tables to “Ross”, and WITH GRANT OPTION allows “Ross” to grant the same authorization to another user:
GRANT SELECT ON record, history TO Ross WITH GRANT OPTION;
REVOKE statement is used to allow “John” only the SELECT privilege while maintaining all the privileges for “Fred” granted before. If “John” granted the privileges to another user, the grantee is also allowed to use the SELECT privilege only:
REVOKE INSERT, UPDATE, DELETE ON nation, athlete FROM John;
The REVOKE statement is used to revoke all privileges granted to “Fred” before. If the statement is executed, “Fred” is not being allowed to perform any operation on the nation and athlete tables:
REVOKE ALL PRIVILEGES ON nation, athlete FROM Fred;
Using CUBRID Admin Client to manage users
One easy way to manage users is through the GUI provided by CUBRID Manager Client, from where you can:
- Browse users
- Create and Delete users
- Manage user permissions