Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

GRANT

Description

Grants various privileges to the specified users or user group for the database. There are different forms of the GRANT statement for various purposes:

  • To grant database-wide privileges, either system administration (DBA) or general creation (RESOURCE)
  • To grant privileges on the specified tables or view
  • To grant the privilege to execute the specified stored procedure

Syntax

GRANT { RESOURCE, DBA }

TO {user_name } [ , { user_name } ] ... ;

GRANT { privilege [ , privilege ] ... | ALL [ PRIVILEGES ] }

ON table_name

TO { { user_name | usergroup_name } [ , {user_name | usergroup_name} ] ... | PUBLIC }

[WITH GRANT OPTION] ;

GRANT EXECUTE ON procedure_name

TO { { user_name | usergroup_name } [ , { user_name | usergroup_name } ] ... | PUBLIC } ;

privilege ::

{ SELECT | INSERT | DELETE | ALTER | INDEX

| UPDATE [ (column, column, ... ) ]

| REFERENCES [ (column, column, ... ) ] }

Arguments

DBA

Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges. This argument an only be applied to users.

RESOURCE

Allows the specified users to issue CREATE statements. The RESOURCE privilege does not allow users to issue DROP statements on database objects. Only the owner of the object and users with the DBA privilege can drop database objects. This argument an only be applied to users.

SELECT

Allows the specified users to read data in the table or view.

INSERT

Allows the specified users to add new rows to the table or view.

DELETE

Allows the specified users to delete rows in the table or view

ALTER

Allows the specified users to modify the table or view

INDEX

Allows the specified users to create an index on the table or view.

UPDATE [ (column, column, ... ) ]

Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named.

REFERENCES [ (column, column, ... ) ]

Allows the specified users to refer to the table from other tables’ constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named. For more detail on constraint definitions, see Column Constraints.

ALL

Grants all privileges for the table or view.

ON table_name

The table or view for which FairCom DB SQL grants the specified privileges.

EXECUTE ON procedure_name

Allows execution of the specified stored procedure.

TO user_name [ , user_name ] ...

The list of users for which FairCom DB SQL grants the specified privileges.

TO PUBLIC

Grants the specified privileges to any user with access to the system.

WITH GRANT OPTION

Allows the specified users to grant their access rights or a subset of their rights to other users.

Examples

Note: You must commit, or turn on auto commit, to save your changes. For example, ISQL defaults to auto commit off, so if you grant a permission and exit, your changes are discarded.

GRANT RESOURCE TO user1;

GRANT SELECT ON custmaster TO odbc_group;

GRANT ALTER ON cust_view TO dbuser1 ;

GRANT SELECT ON newcustomers TO dbuser2 ;

GRANT EXECUTE ON sample_proc TO searle;

commit;

Authorization

The user granting DBA or RESOURCE privileges must have the DBA privilege.

The user granting privileges on a table must have any of the following privileges:

  • DBA privilege
  • Ownership of the table
  • All the specified privileges on the table, granted with the WITH GRANT OPTION clause

    SQL Compliance

    SQL-92, ODBC Core SQL grammar. Extensions: ALTER, INDEX, RESOURCE, DBA privileges

    Environment

    Embedded SQL, interactive SQL, ODBC applications

    Related Statements

    REVOKE

TOCIndex