Database Grant Examples
Here are examples of granting permissions on a database:
1. Define a query row limit of 100 rows on the new_accts database for user Ralph:
GRANT QUERY_ROW_LIMIT 100
ON DATABASE new_accts TO ralph;
2. Prohibit group prodrams from creating tables and database procedures in the new_accts database:
GRANT NOCREATE_TABLE, NOCREATE_PROCEDURE
ON DATABASE new_accts TO prodrams;
3. A database privilege can be superseded by issuing a subsequent GRANT statement for the user authorization. For example, assume that user karenk has been granted a query row limit of 1000 rows on the customers database:
GRANT QUERY_ROW_LIMIT 1000
ON DATABASE customers TO karenk;
Her job changes and she does not need to access so much of the database, so the DBA issues a new GRANT statement giving her a query row limit of 250:
GRANT QUERY_ROW_LIMIT 250
ON DATABASE customers TO karenk;
This new privilege replaces the old 1000-row privilege. If the DBA subsequently revokes the new limit:
GRANT NOQUERY_ROW_LIMIT
ON DATABASE customers TO karenk;
karenk’s query row limit privilege for the database becomes undefined (the old limit of 1000 is not re-established). At this point if no value for QUERY_ROW_LIMIT has been defined for any of the other authorization identifiers associated with karenk’s session, then the number of rows that her session’s queries can return is unrestricted.