GRANT

The GRANT statement gives specified authority to one or more users or authorization groups. The following forms of the GRANT statement are described individually:

Grant table or view authority.
Grant RUN or EXECUTE authority.
Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE authority.
Grant SECTIONSPACE or TABLESPACE authority for a DBEFileSet.

For detailed information about security schemes, refer to the "DBEnvironment Configuration and Security" chapter of the ALLBASE/SQL Database Administration Guide.

Scope

ISQL or Application Programs

SQL Syntax — Grant Table or View Authority

  GRANT {ALL [PRIVILEGES] 
         {SELECT 
          INSERT 
          DELETE 
          ALTER 
          INDEX 
          UPDATE [({ColumnName}[,...])] 
          REFERENCES [({ColumnName}[,...])]}|,...|} 
  ON {[Owner.]TableName
      [Owner.]ViewName}TO {DBEUserID 
                           GroupName 
                           ClassName 
                           PUBLIC    } [,...][WITH GRANT OPTION] 
  [BY {DBEUserID
       ClassName}]

Parameters — Grant Table or View Authority

ALL [PRIVILEGES]: is the same as specifying all privileges you can grant on that table or view. For OWNER or DBA the privileges are SELECT, INSERT, DELETE, ALTER, INDEX, UPDATE, and REFERENCES. The word PRIVILEGES is not required; you can include it if you wish to improve readability. ALTER, INDEX, and REFERENCES are not applied when using GRANT ALL on views.
SELECT: grants authority to retrieve data.
INSERT: grants authority to insert rows.
DELETE: grants authority to delete rows.
ALTER: grants authority to add new columns. ALTER authority is not allowed for a view.
INDEX: grants authority to create and drop indexes. INDEX authority is not allowed for a view.
UPDATE: grants authority to change data in existing rows. A list of column names can be specified to grant UPDATE authority only for specific columns. Omitting the list of column names grants authority to update all columns.
REFERENCES: grants authority to reference columns in the table from the foreign keys in other tables. A list of column names can be specified to grant REFERENCES authority only for specific columns. Omitting the list of column names grants authority to reference all columns. REFERENCES authority is not allowed for a view.
[Owner.]TableName: designates a table for which authority is to be granted.
[Owner.]ViewName: designates a view for which authority is to be granted.
TO: The TO clause designates the users, authorization groups, and classes to be given the specified authority. You must specify a login name when specifying a DBEUserID. Authority granted to PUBLIC can be exercised by all users having CONNECT or DBA authority. Granting authority to a class is useful when program modules are owned by a class.
WITH GRANT OPTION: allows the grantee of a privilege to grant that same privilege to another user. If WITH GRANT OPTION is specified, then all privileges being granted in the statement are granted with the grant option to all grantees. The grantee cannot be a group. The authority to grant cannot come solely from group membership.
BY: specifies a DBEUserID or class as grantor of a privilege. This clause is used to provide a parent for an orphaned privilege. The named grantor cannot be a group or PUBLIC.

Authorization — Grant Table or View Authority

If you have DBA or OWNER authority directly (not due to group membership), or were previously granted table privileges with the WITH GRANT OPTION clause, you can issue the GRANT statement with the WITH GRANT OPTION clause for that table or view.

The BY clause can only be used by a DBA.

A user may be granted a privilege from one grantor only. OWNER, DBA, or grantable authority is required to issue the GRANT statement.

SQL Syntax — Grant RUN or EXECUTE Authority

  GRANT {RUN ON [Owner.]ModuleName 
         EXECUTE ON PROCEDURE [Owner.]ProcedureName}TO 
  {{DBEUserID 
    GroupName 
    ClassName} [,...]
    PUBLIC             }

Parameters — Grant RUN or EXECUTE Authority

RUN: grants authority to execute a specified module created interactively or by using a preprocessor.
[Owner.]ModuleName: specifies the name of the module for which authority is to be granted.
EXECUTE: grants authority to execute a specified procedure.
[Owner.]ProcedureName: specifies the name of the procedure for which authority is to be granted.
TO: The TO clause tells which users and authorization groups are to be granted the specified authority. You must specify a login name when specifying a DBEUserID. Authority granted to PUBLIC can be exercised by any user with CONNECT authority.

Authorization — Grant RUN or EXECUTE Authority

If you have DBA authority or OWNER authority, you can issue GRANT statements for any module or procedure.

To grant CONNECT, DBA, or RESOURCE authority, you must have DBA authority.

SQL Syntax — Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority

  GRANT {CONNECT 
         DBA 
         INSTALL [AS OwnerID]
         MONITOR
         RESOURCE             }TO {DBEUserID 
                                   GroupName 
                                   ClassName } [,...]

Parameters — Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority

CONNECT

grants authority to use the CONNECT statement.

DBA

grants authority to issue any valid ALLBASE/SQL statement. A user with DBA authority is exempt from all authorization restrictions.

RESOURCE

grants authority to create tables and authorization groups.

MONITOR

grants authority to run SQLMON.

INSTALL

grants authority to INSTALL modules where the owner name equals the OwnerID. If the "AS OwnerID" clause is omitted, then grants authority to INSTALL modules having any owner name.

Modules for an application are created and installed when that application is preprocessed using one of the SQL preprocessors. Modules can also be installed by using the ISQL INSTALL command. See the ALLBASE/ISQL Reference Manual for more details.

TO

The TO clause specifies the users, authorization groups, and classes to be given the specified authority. You must specify a login name when specifying a DBEUserID. Granting DBA authority to a class is useful when program modules are owned by a class.

Description — Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority

If MONITOR authority is granted to a user, authorization group, or class that already has DBA authority, a warning is returned and explicit MONITOR authority is not granted since a DBA already has MONITOR authority.
If DBA authority is granted to a user, authorization group, or class that already has MONITOR authority, MONITOR authority is upgraded to DBA authority.

Authorization — Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority

If you have OWNER authority for a table, view, or module, you can issue GRANT statements for that table or view. If you have DBA authority, you can issue GRANT statements for any table, view, or module. To grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE authority, you must have DBA authority.

SQL Syntax — Grant DBEFileSet Authority

  GRANT {SECTIONSPACE 
         TABLESPACE		   } [,...] ON DBEFILESET DBEFileSetName TO
  {DBEUserID 
   GroupName 
   ClassName 
   PUBLIC																	     } [,...]

Parameters — Grant DBEFileSet Authority

SECTIONSPACE

grants authority to store sections in the specified DBEFileSet.

A grant of SECTIONSPACE causes a check to see whether the STOREDSECT table has yet been created for the DBEFileSet. If there is no related STOREDSECT table, it is created.

When a user specifies a DBEFileSet for a section in a CREATE TABLE (check constraints), ALTER TABLE (check constraints), CREATE PROCEDURE, CREATE RULE, or PREPARE statement, in preprocessing, or in the ISQL INSTALL command, the owner of the section is checked for SECTIONSPACE authority on the DBEFileSet. If the user does not have SECTIONSPACE authority, the default SECTIONSPACE DBEFileSet is used instead. (See the SET DEFAULT DBEFILESET statement.) This applies even if the user has DBA authority.

TABLESPACE

grants authority to store table and long column data in the specified DBEFileSet.

When a user specifies the IN DBEFileSet clause in a CREATE TABLE statement for either the table or for a LONG column, the owner of the table is checked for TABLESPACE authority on the DBEFileSet. If the user does not have TABLESPACE authority, the default TABLESPACE DBEFileSet is used instead (See the SET DEFAULT DBEFILESET statement.) This applies even if the user has DBA authority.

DBEFileSetName

designates the DBEFileSet for which authority is to be granted.

Description

The execution of this statement causes modification to the HPRDBSS.SPACEAUTH system catalog table. Refer to the ALLBASE/SQL Database Administration Guice "System Catalog" chapter.

Authorization — Grant DBEFilesSet Authority

To grant SECTIONSPACE or TABLESPACE, you must have DBA authority. If you have DBA authority, you can issue the GRANT statement for any DBEFileSet.

Examples

Authorization groups
CREATE GROUP Warehse GRANT CONNECT TO Warehse GRANT SELECT, UPDATE (BinNumber,QtyOnHand,LastCountDate) ON PurchDB.Inventory TO Warehse
These two users will be able to start DBE sessions for PartsDBE, retrieve data from table PurchDB.Inventory, and update three columns in the table.
ADD Clem, George TO GROUP Warehse
Clem no longer has any of the authorities associated with group Warehse.
REMOVE Clem FROM GROUP Warehse
Because this group does not own any database objects, it can be deleted. George no longer has any of the authorities once associated with the group.
DROP GROUP Warehse
Using the WITH GRANT OPTION clause
Clem and George have the SELECT privilege on the Inventory table as well as the ability to grant the SELECT privilege on this table to other users or a class with the WITH GRANT OPTION clause or to a group or PUBLIC (without the WITH GRANT OPTION).
GRANT SELECT ON PurchDB.Inventory TO Clem, George WITH GRANT OPTION
Module grants
GRANT RUN ON Statistics TO HelperDBA GRANT RUN ON MyProg TO PUBLIC
Rows associated with module Statistics are deleted from the system catalog.
DROP MODULE Statistics
Authorization information for MyProg is retained, but the program is deleted from the system catalog. You can re-preprocess MyProg and do not have to redefine its authorization.
DROP MODULE MyProg PRESERVE

Procedure grants

   GRANT EXECUTE ON PROCEDURE Process10 TO Managers
   GRANT EXECUTE ON PROCEDURE Process12 TO AllUsers

DBEFileSet grants
Grant the ability to store sections in DBEFileSet1 to PUBLIC.
GRANT SECTIONSPACE ON DBEFILESET DBEFileSet1 TO PUBLIC;
Grant the ability to store table and long column data in DBEFileSet2 to PUBLIC.
GRANT TABLESPACE ON DBEFILESET DBEFileSet2 TO PUBLIC;
Grant authority to run SQLMON
GRANT MONITOR TO HelperDBA;
Grant a DBEUserID the authority to create modules owned by a specified OwnerID.
GRANT INSTALL AS John TO Clem;