Grant issue

DilipKumar

Hi,

 

I am little confused with the Grant Usage.

As a owner of schema cant i give grant Usage on my own schema to other users?

 

Login as S1 and my schema name is also S1:

 

Create a table:

-->create table S1.test2 as select * from test1;
CREATE TABLE

 

Grant same table to another user called sch2:

-->grant select on S1.test2 to sch2;
WARNING 5682: USAGE privilege on schema "S1" also needs to be granted to "sch2"
GRANT PRIVILEGE

 

As per the above message i tried to grant usage on my schema to other user sch2:

-->grant usage on schema s1 to sch2;
ROLLBACK 2061: "USAGE" privilege(s) for schema "S1" could not be granted to "sch2"
HINT: Check grantor's granting options

Then i logged in as Super user and chek the info of the schema:

 

vertadm=> select * from grants where object_name='S1';
-[ RECORD 1 ]----------+----------------------------------------------------------------------------
grant_id | 81064793496193290
grantor_id | 45035996273704962
grantor | admin
privileges_description | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, USAGE*, CREATE*, TRUNCATE*
object_schema |
object_name | S1
object_id | 81064793496192044
object_type | SCHEMA
grantee_id | 45035996273704962
grantee | admin
-[ RECORD 2 ]----------+----------------------------------------------------------------------------
grant_id | 81064793496193292
grantor_id | 45035996273704962
grantor | admin
privileges_description | USAGE, CREATE
object_schema |
object_name | S1

 

object_type | SCHEMA
grantee_id | 45035996273704962
grantee | admin

 

Thanks in advance.

 

 

Jim_Knicely

Are you sure user s1 is the owner of shema s1? Check with the \dn s1 command...

 

If s1 is the owner, the grant will work ok...

 

 

dbadmin=> create user s1;
CREATE USER

dbadmin=> create schema s1 authorization s1;
CREATE SCHEMA

dbadmin=> create user sch2;
CREATE USER

dbadmin=> grant usage on schema s1 to s1;
GRANT PRIVILEGE

dbadmin=> \dn s1
    List of schemas
 Name | Owner | Comment
------+-------+---------
 s1   | s1    |
(1 row)

dbadmin=> \c dbadmin s1
You are now connected to database "dbadmin" as user "s1".

dbadmin=> grant usage on schema s1 to sch2;
GRANT PRIVILEGE

DilipKumar

Thanks for the clarification.I was not using "authorization" key word while creating the schema.