Skip to main content

Grant issue

  • December 3, 2016
  • 2 replies
  • 15 views

DilipKumar
Forum|alt.badge.img+1

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.

 

 

2 replies

Jim_Knicely
Forum|alt.badge.img+2
  • Participating Frequently
  • December 9, 2016

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
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • December 22, 2016

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