 |
» |
|
|
|
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 logon
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
logon 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 ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL for more details.
- TO
The TO clause specifies the users, authorization groups, and
classes to be given the specified authority. You must specify a logon
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 Guide "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 now will be able to start DBE sessions for PartsDBE,
retrieve data from table PurchDB.Inventory, and update three
columns in the table.
ADD CLEM@DBMS, GEORGE@DBMS TO GROUP Warehse
|
CLEM@DBMS no longer has any of the authorities associated
with group Warehse.
REMOVE CLEM@DBMS FROM GROUP Warehse
|
Because this group does not own any database objects,
it can be deleted. GEORGE@DBMS no longer has any
of the authorities once associated with the group. Using the WITH GRANT OPTION clause
CLEM@DBMS and GEORGE@DBMS have the SELECT privilege on the Inventory
table as well as the ability to grant the SELECT privilege on this
table to other users.
GRANT SELECT
ON PurchDB.Inventory
TO CLEM@DBMS, GEORGE@DBMS 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. 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@BROCK TO CLEM@DBMS;
|
|
