Grant object privileges

When you grant object privileges to a user or group, you must decide if the privilege should be scoped to one or more databases, one or more schemas, or effective globally across all the databases in the system. In releases before 7.0.3, administrators assigned global versus local privileges by making those GRANTs or REVOKEs in specific locations. For example:

• For a global privilege, you would connect to the system database and grant the privilege for the object class to the user or group.
• For a privilege specific to a database, you would connect to that database and grant the privilege for the object to the user or group.

Starting in Release 7.0.3, you can continue to use these methods for assigning local and global privileges, and you can also use fully qualified object names (database.schema.obj_ or_obj_class) and classes to set the scope from any database/schema connection. You must be the admin user, or you must have privileges to alter the privileges of the users or groups that you want to manage.

In the fully qualified object name, you could specify the following:

• For database, you could specify a database name to scope the privilege to a specific database, or the keyword ALL to specify all databases including any databases created in the future.
• For schema, you could specify a schema name to scope the privilege to a specific schema, or the keyword ALL to specify all schemas including any schemas created in the future.
• For the obj_or_obj_class value, you can specify an object name to scope the privilege to that object. You can also specify an object class such as Database, Table, View, Synonym or others to allow the privilege for all objects of that type.

For example, to grant user1 the privilege to access all databases and schemas, run this command from any database connection:

GRANT list ON all.all.database TO user1;
GRANT

If you make a global grant in this manner, you can refine access to specific databases by changing the user access in that database. For example, if you want user1 to have no access to the database named secure_info, you must grant user1 a set of reduced access prvileges to that target database. For example:

GRANT select ON secure_info TO user1;
GRANT

In this example, the user has select access to the database, which is not sufficient to allow a connection to the database if user1 is not the owner of the database. For more information about privileges, see the IBM® Netezza® System Administrator’s Guide.