Scope of object privileges

All objects are either global (for example, a database, user, or group) or local (that is, they exist within a database; for example, a table or view). You can assign an object privilege so that it applies globally (to all objects within all databases) or locally (to a single object within a single database).

The following example starts as a local definition and moves to a more global definition.

If you use SQL commands to manage account permissions, the database to which you are connected has meaning when you issue a GRANT command. If you are connected to the system database (this database has the name SYSTEM), the privilege applies to all databases. If you are connected to a specific database, the privilege applies only within that database.

Starting in release 7.0.3, you can use a fully-qualified object notation to set the scope of object privileges from any database. The fully-qualified object notation has the format:
database.schema.object
where:
database
A specific database name or the keyword ALL to grant the privilege across all databases.
schema
A specific schema name or the keyword ALL for all schemas in the specified database value.
object
An object class such as TABLE, VIEW, and so on, or a specific object name.
For example, to assign a privilege to an object in a particular database and a particular schema, sign on to the database and grant the privilege on the object to a user or group. For this type of privilege, the object must exist, and this privilege overrides any other defined privilege.
MYDB.SCH1(USER1)=> GRANT LIST ON testdb TO user1;

Similarly, you could sign into any database to which you have access and grant the permission using a fully qualified name:

DEV.TEST(USER2)=> GRANT LIST ON mydb.sch1.testdb TO user1;
To assign a privilege to a class of objects in a particular database and a particular schema, sign on to the database and grant the privilege on the class to a user or group. When you assign a privilege to a class (such as table), the system allows the user or group that privilege on all the objects of that class whether the object existed at the time of the grant.
MYDB.SCH1(USER1)=> GRANT SELECT ON TABLE TO user1;

Similarly, you could sign into any database to which you have access and grant the permission using a fully qualified name:

DEV.TEST(USER2)=> GRANT SELECT ON MYDB.ALL.TABLE TO user1;
To assign a privilege to a class of objects in all databases, sign on to the system database and grant the privilege on the class to a user or group. When you define privileges for user object classes within the system database, the system assumes you are requesting a global scope.
SYSTEM.ADMIN(ADMIN)=> GRANT SELECT ON TABLE TO user1;

Similarly, you could sign into any database to which you have access and grant the permission using a fully qualified name:

DEV.TEST(USER2)=> GRANT SELECT ON ALL.ALL.TABLE TO user1;
Note: When you use the GRANT command to grant privileges at a database level and at the system level, the grant issued for the database overrides the grant issued at the system level.
Privilege Precedence: Netezza Performance Server for Cloud Pak for Data and Netezza Performance Server for Cloud Pak for Data System order of precedence for permissions:
  1. Privileges granted on a particular object within a particular database and a particular schema, for systems that support multiple schemas
  2. Privileges granted on an object class within a particular database and a particular schema, for systems that support multiple schemas
  3. Privileges granted on a particular object within all schemas of a particular database
  4. Privileges granted on an object class within all schemas of a particular database
  5. Privileges granted on an object within the system database
  6. Privileges granted on an object class within the system database

You can assign multiple privileges for the same object for the same user. The Netezza Performance Server system uses the rules of precedence to determine which privileges to use. For example, you can grant users privileges on a global level, but user privileges on a specific object or database level override the global permissions. For example, assume the following three GRANT commands:

Within the system database, enter:
system.admin(admin)=> GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE ON 
  TABLE TO user1
Within the dev database, enter:
dev.schema(admin)=> GRANT SELECT,INSERT,UPDATE ON TABLE TO user1
Within the dev database, enter:
dev.schema(admin)=> GRANT SELECT, LOAD ON customer TO user1
By using these grant statements and assuming that customer is a user table, user1 has the following permissions:
  • With the first GRANT command, user1 has global permissions to SELECT, INSERT, UPDATE, DELETE, or TRUNCATE any table in any database.
  • The second GRANT command restricts user1 permissions specifically on the dev database. When user1 connects to dev, user1 can run only SELECT, INSERT, or UPDATE operations on tables within that database.
  • The third GRANT command overrides privileges for user1 on the customer table within the dev database. As a result of this command, the only actions that user1 can perform on the customer table in the dev database are SELECT and LOAD.
The following table lists the slash commands that display the privileges for users and groups:
Table 1. Slash commands to display privileges
Command Description
\dg List groups (both user and resource groups) except _ADMIN_.
\dG List user groups and their members.
\dp <user> List the privileges that were granted to a user either directly or by membership in a user group.
\dpg <group> List the privileges that were granted to a user group.
\dpu <user> List the privileges that were granted to a user directly and not by membership in a user group.
\du List users.
\dU List users who are members of at least one user group and the groups of which each is a member.
When you revoke privileges, make sure you sign on to the same database (and, for a multiple schema system, the same schema) where you granted the privileges, or use the fully qualified name forms that match the locations in which you granted the privileges. Then, you can use these slash commands to verify the results.