TOC PREV NEXT

 

 

 

SQL Security and Privileges


This chapter describes PointBase security and privileges. Schemas are an integral part of security in PointBase. When creating a PointBase user, they do not have any access privileges to schemas or other data objects within the database. The PointBase RDBMS only permits the schema owner to grant privileges to the schema and data objects within the schema. The schema owner can grant privileges to the following data objects in the schema:

Table 1 describes the privileges that the schema owner can grant users for tables and columns:
Table 1 : User Privileges for Tables and Columns
Privilege Statements
Privilege
Description
DELETE
Allows a user to delete rows from tables within the schema
INSERT
Allows a user to insert rows of data into tables within the schema
REFERENCES
Allows a user to set up references to primary keys within the schema
SELECT
Allows a user to select rows from tables within the schema
TRIGGER
Allows a user to create triggers on tables within the schema
UPDATE
Allows a user to update rows in tables within the schema
EXECUTE
Allows users to execute functions or stored procedures within the schema

Granting and Revoking Privileges

When a PointBase database is first created the only user is the default user PUBLIC with a password of PUBLIC. The PUBLIC user owns the default PUBLIC schema. For security reasons, PointBase does not recommend using this schema to store sensitive data. Like any PointBase user, PUBLIC must be granted the appropriate privileges to access data objects in schema owned by other users.

The PUBLIC user can be used initially to create new users and new schema. The PUBLIC user will own any new schema that it creates unless otherwise specified during schema creation. New users are then able to create their own new schema and users, and grant appropriate privileges on schema that they own. All new users must be granted privileges to use the PUBLIC schema if this is required.

To grant the ability for a user to pass a privilege on to other users once granted, you must specify the optional WITH GRANT OPTION qualifier when granting the privilege.

GRANT Statement Syntax

GRANT privilege-list
ON object
TO user-list [ WITH GRANT OPTION ]

Use the GRANT statement to grant privileges on a data object. The following describes the GRANT statement syntax.

Privilege-list Syntax
privilege [ , privilege [ , privilege ]...] | ALL PRIVILEGES

Privilege Syntax
SELECT [ ( column-name [ , column-name ]...)]
| DELETE
| INSERT [ ( column-name [ , column-name ]...)]
| UPDATE [ ( column-name [ , column-name ]...)]
| REFERENCES [ ( column-name [ , column-name ]...)]
| TRIGGER [ ( column-name [ , column-name ]...)]
| EXECUTE

Usage Notes

Object Syntax
[ TABLE ] table-name
|SPECIFIC routine_type specific_routine-name
|routine_type routine_name (parameter_types_list)
[ TRIGGER ] trigger-name

Usage Notes

User-list Syntax
user [ , user ]... [WITH GRANT OPTION] | PUBLIC

Usage Notes

Examples

GRANT SELECT
ON customer_tbl
TO marketing_mgr;

GRANT DELETE,INSERT,UPDATE
ON discount_code_tbl
TO financial_mgr
WITH GRANT OPTION;

GRANT ALL PRIVILEGES
ON sales_rep_data_tbl
TO hr_mgr

REVOKE Statement Syntax

REVOKE [ GRANT OPTION FOR ] privilege_list
ON object
FROM user_name [ RESTRICT | CASCADE ]

The REVOKE statement takes privileges away from users. The arguments are similar to the GRANT statement. The major difference is the additional RESTRICT or CASCADE keyword and the GRANT OPTION FOR clause. The following describes the optional clauses GRANT OPTION FOR and RESTRICT or CASCADE.

NOTE: If none of the privileges that you are trying to revoke actually exist, an error is raised.

RESTRICT | CASCADE

If you use RESTRICT keyword, the privilege will be revoked only from the specified user. If the specified user granted had the WITH GRANT OPTION and granted the same privilege to other users, they will retain the privilege.

If you use CASCADE, it will revoke the privilege and any dependent privileges as a result of your grant. A dependent privilege is one that could exist, if you granted the privilege that you're trying to revoke, which is what you are trying to achieve as a result of your REVOKE statement.

If the optional RESTRICT or CASCADE keywords are not used, PointBase uses RESTRICT by default.

GRANT OPTION FOR

If he optional GRANT OPTION FOR clause is used, the WITH GRANT OPTION right is revoked. The actual privilege itself is not revoked. the GRANT OPTION is revoked. CASCADE and RESTRICT may be used in the same way as a normal REVOKE statement.


 
TOC PREV NEXT