HP 3000 Manuals HP 3000 Manuals
GRANT
The GRANT statement gives specified authority to one or more users or
authorization groups.[REV BEG] 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.[REV
END]
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
{ALL [PRIVILEGES] }
{{SELECT } }
{{INSERT } }
GRANT {{DELETE } } ON
{{ALTER } |,...|}
{{INDEX } }
{{UPDATE [({ColumnName} [,...])] } }
{{REFERENCES [({ColumnName} [,...])]} }
{DBEUserID}
{[Owner.]TableName} TO {GroupName} [,...] [WITH GRANT OPTION]
{[Owner.]ViewName } {ClassName}
{PUBLIC }
[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
{{DBEUserID} }
GRANT {RUN ON [Owner.]ModuleName } TO {{GroupName} [,...]}
{EXECUTE ON PROCEDURE [Owner.]ProcedureName} {{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.[REV BEG]
SQL Syntax--Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority
{CONNECT }
{DBA } {DBEUserID}
GRANT{INSTALL [AS OwnerID]}TO {GroupName}[,...]
{MONITOR } {ClassName}
{RESOURCE }
[REV END]
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.[REV BEG]
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.[REV
END]
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.
[REV BEG]
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.
[REV END]
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
{DBEUserID}
GRANT {SECTIONSPACE} [,...] ON DBEFILESET DBEFileSetName TO {GroupName}
{TABLESPACE } {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
1. 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.
DROP GROUP Warehse
2. 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
3. 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
4. Procedure grants
GRANT EXECUTE ON PROCEDURE Process10 TO Managers
GRANT EXECUTE ON PROCEDURE Process12 TO AllUsers
5. 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;
[REV BEG]
6. Grant authority to run SQLMON
GRANT MONITOR TO HelperDBA;
7. Grant a DBEUserID the authority to create modules owned by a
specified OwnerID.
GRANT INSTALL AS JOHN@BROCK TO CLEM@DBMS;
[REV END]