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.
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.
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;
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;
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;
- Privileges granted on a particular object within a particular database and a particular schema, for systems that support multiple schemas
- Privileges granted on an object class within a particular database and a particular schema, for systems that support multiple schemas
- Privileges granted on a particular object within all schemas of a particular database
- Privileges granted on an object class within all schemas of a particular database
- Privileges granted on an object within the system database
- 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:
system.admin(admin)=> GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE ON
TABLE TO user1
dev
database, enter:dev.schema(admin)=> GRANT SELECT,INSERT,UPDATE ON TABLE TO user1
dev
database, enter:dev.schema(admin)=> GRANT SELECT, LOAD ON customer TO user1
- 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 todev
, 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 thedev
database are SELECT and LOAD.
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. |