Granting and Revoking Privileges

After you set up the AuthIDs, SecondaryIDs and groups, you can define the privileges and authority for each, using the SQL GRANT and REVOKE commands. Set up the granted privileges before or after turning on security. However, any grants you issue when security is off will not take effect until you turn security on.

Note: Warning:

Be sure you are operating in the location for which you want to define privileges. Each location has its own DSNDB06 database for maintaining authorization information. (To change locations, use the SET LOCATION TO command in interactive SQL or change your default location in the SQLWizard or Options Utility.)

The basic GRANT command formats supported by XDB Servers are shown below. The REVOKE command formats are similar.

Database Privileges

GRANT privilege 
    ON DATABASE database-name 
    TO authid 
    [ WITH GRANT OPTION ]

System Privileges

GRANT privilege 
    TO authid 
    [ WITH GRANT OPTION ]

Table or View Privileges

GRANT {ALL | privilege} 
    ON TABLE table-name 
    TO {PUBLIC | PUBLIC AT ALL LOCATIONS | authid} 
    [WITH GRANT OPTION]

Column Privileges

GRANT UPDATE(column-name) 
    ON TABLE table-name 
    TO {PUBLIC | PUBLIC AT ALL LOCATIONS | authid} 
    [WITH GRANT OPTION]

where:

`privilege`	is one or more Authorities and/or privileges, separated by commas
`authid`	is a user's Primary AuthID, a SecondaryID or a GroupID.
UPDATE (`column-name`)	is one or more occurrences of this phrase separated by commas. You must enter the word UPDATE and the column name in parentheses for each column on which the update privilege is to be granted.
PUBLIC	is used to grant the privilege to all users at the current location.
PUBLIC AT ALL LOCATIONS	is used to grant the privilege to all users, including remote users who are accessing this location.

See the SQL Reference for detailed information about using these commands, including descriptions of the various privileges and authorities that are available for each.