How Do I Grant Schema Permissions to a User?

This section describes how to grant the query permission for a schema as an example. For more information, see "How Do I Grant Table Permissions to a User?" in FAQ. You can grant:

• Permission for a table in a schema
• Permission for all the tables in a schema
• Permission for tables to be created in the schema

Assume that there are users jim and mike, and two schemas named after them. User mike needs to access tables in schema jim, as shown in Figure 1.

Figure 1 User mike accesses a table in SCHEMA jim.

1. Connect to your database as dbadmin. Run the following statements to create users jim and mike. Two schemas will be created and named after the users by default.

   1 2	CREATE USER jim PASSWORD '{password}'; CREATE USER mike PASSWORD '{password}';

2. Create tables jim.name and jim.address in schema jim.

   1 2	CREATE TABLE jim.name (c1 int, c2 int); CREATE TABLE jim.address (c1 int, c2 int);

3. Grant the access permission of schema jim to user mike.

   1	GRANT USAGE ON SCHEMA jim TO mike;

4. Grant user mike the permission to query table jim.name in schema jim.

   1	GRANT SELECT ON jim.name TO mike;

5. Start a new session and connect to the database as user mike Verify that user mike can query the jim.name table but not the jim.address table.

   1 2	SELECT * FROM jim.name; SELECT * FROM jim.address;

   

6. In the session started by user dbadmin, grant user mike the permission to query all the tables in schema jim.

   1	GRANT SELECT ON ALL TABLES IN SCHEMA jim TO mike;

7. In the session started by user mike, verify that mike can query all tables.

   1 2	SELECT * FROM jim.name; SELECT * FROM jim.address;

   

8. In the session started by user dbadmin, create table jim.employ.

   1	CREATE TABLE jim.employ (c1 int, c2 int);

9. In the session started by user mike, verify that user mike does not have the query permission for jim.employ. It indicates that user mike has the permission to access all the existing tables in schema jim, but not the tables to be created in the future.

   1	SELECT * FROM jim.employ;

   

10. In the session started by user dbadmin, grant user mike the permission to query the tables to be created in schema jim. Create table jim.hobby.

    1 2	ALTER DEFAULT PRIVILEGES FOR ROLE jim IN SCHEMA jim GRANT SELECT ON TABLES TO mike; CREATE TABLE jim.hobby (c1 int, c2 int);

11. In the session started by user mike, verify that user mike can access table jim.hobby, but does not have the permission to access jim.employ. To let the user access table jim.employ, you can grant permissions by performing 4.

    1	SELECT * FROM jim.hobby;