|
|
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.
ISQL or Application Programs
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}]
- 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.
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.
GRANT {RUN ON [Owner.]ModuleName
EXECUTE ON PROCEDURE [Owner.]ProcedureName} TO
{{DBEUserID
GroupName
ClassName} [,...]
PUBLIC }
- 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.
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.
GRANT {CONNECT
DBA
INSTALL [AS OwnerID]
MONITOR
RESOURCE } TO {DBEUserID
GroupName
ClassName } [,...]
- 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.
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.
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.
GRANT {SECTIONSPACE
TABLESPACE } [,...] ON DBEFILESET DBEFileSetName TO
{DBEUserID
GroupName
ClassName
PUBLIC } [,...]
- 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.
To grant SECTIONSPACE or TABLESPACE, you must have DBA authority. If you have
DBA authority, you can issue the GRANT statement for any DBEFileSet.
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;
|