Sfrgttk

Why do most space probes survive for far longer than they were designed for?

What happens if you declare more than $10,000 at the US border?

Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash

Does anyone recognize this inequality?

Blender, bezier curve looks

Does limiting the number of sources help simplify the game for a new DM with new and experienced players?

Why don't the fuse connections in programmable ROM cause the whole matrix to be shorted together?

Father gets chickenpox, but doesn't infect his two children. How is this possible?

I have a single ethernet switch. Should I use spanning tree?

Drawing a function without knowing its definition

How can I learn to care less because it makes me sick?

How to know if I am a 'Real Developer'

Does a star need to be inside a galaxy?

How to explain one side of Super Earth is smoother than the other side?

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

How aware are characters of their class in-universe?

Why do climate experts from the UN/IPCC never mention Grand Solar Minimum?

How to make clear what a part-humanoid character looks like when they're quite common in their world?

Is this an email from Apple or fraud?

Karatsuba multiplication

Was Rowling inspired by the railway station scene in Prince Caspian?

What does paperwork mean in this sentence?

What happens when the last remaining players refuse to kill each other?

Branching points detection in processed image

How does GRANT privileges function when recursive privileges?

What are reasonable privileges to grant typical users?Only “grant usage”, but can still select, drop, create?How do I give root@localhost permission to grant privileges in MySQL?Minimal grants for readonly single-table access on PostgreSQLConfused by GRANT ALL PRIVILEGES ON *.* (…) WITH GRANT OPTIONWhen are privileges listed in l and when not?Default privileges to execute functionCreate a user with privileges to create usersHow to restore the privileges of the user postgres in PostgreSQL?What are the minimal permissions required for the “Users and Privileges” tab in MySQL Workbench to function in MySQL 5.6

0

I'm trying to understand how does granting privileges work on SQL.

According to what I understand, one user has privilege as long as said user has been granted privilege by someone or owns the database. And the second condition I though was true, is that a user may have a certain privilege only if someone the privilege chain connects to the owner/admin of the table.

Now, I'm using PostgreSQL to test how this works in an actual DBMS.

For example, let's say we have a table t1 and the users u1, u2 and u3. Also let's assume u1 is the owner.

• Now, u1 grants SELECT ON t1 TO u2 WITH GRANT OPTION
  


• Then, u2 grants SELECT ON t1 TO u3 WITH GRANT OPTION
  


• At last, u3 grants SELECT ON t1 TO u2 WITH GRANT OPTION
  

This should leave a sort of recursion of grants between u2 and u3. This means I can safely REVOKE the link between u1 and u2. According to SQL the privileges from u2 and u3 should be removed because they have no connection to the owner/admin of the table, but instead PostgreSQL allows this to happens and you can actually still issue SELECT on t1 even after the privilege was revoked.

So the question is, is this the expected behaviour on PostgreSQL/any RDBMS, or am I understanding wrong how SQL privileges should work. If someone could clarify this a bit for me, I would be thankful.

I was using these slides to understand. The last slide is the case I'm talking about.

postgresql permissions

share|improve this question

edited 2 hours ago

a_horse_with_no_name

39.9k776112

asked 3 hours ago

Marcos JotaMarcos Jota

1

New contributor

Marcos Jota is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The manual states: "For example, if table t1 is owned by role g1, of which role u1 is a member, then u1 can grant privileges on t1 to u2, but those privileges will appear to have been granted directly by g1"
  
  – a_horse_with_no_name
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @a_horse_with_no_name Sure, but that happens when a certain role is member of another role. In this particular case each role has permits and if you grant them and check z t1 you will see that u2 granted r* to u3 and u3 granted r* to u2. PD: Thanks for the edit :)
  
  – Marcos Jota
  2 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What Postgres version? Tried to reproduce this on 11, and when trying to close the grant loop, I get ERROR: grant options cannot be granted back to your own grantor. If it works on older versions, I guess this means they recognised it as a bug and fixed it.
  
  – Nick Barnes
  5 mins ago
  
  
  

  
  
  
  

add a comment | 

0

I'm trying to understand how does granting privileges work on SQL.

According to what I understand, one user has privilege as long as said user has been granted privilege by someone or owns the database. And the second condition I though was true, is that a user may have a certain privilege only if someone the privilege chain connects to the owner/admin of the table.

Now, I'm using PostgreSQL to test how this works in an actual DBMS.

For example, let's say we have a table t1 and the users u1, u2 and u3. Also let's assume u1 is the owner.

• Now, u1 grants SELECT ON t1 TO u2 WITH GRANT OPTION
  


• Then, u2 grants SELECT ON t1 TO u3 WITH GRANT OPTION
  


• At last, u3 grants SELECT ON t1 TO u2 WITH GRANT OPTION
  

This should leave a sort of recursion of grants between u2 and u3. This means I can safely REVOKE the link between u1 and u2. According to SQL the privileges from u2 and u3 should be removed because they have no connection to the owner/admin of the table, but instead PostgreSQL allows this to happens and you can actually still issue SELECT on t1 even after the privilege was revoked.

So the question is, is this the expected behaviour on PostgreSQL/any RDBMS, or am I understanding wrong how SQL privileges should work. If someone could clarify this a bit for me, I would be thankful.

I was using these slides to understand. The last slide is the case I'm talking about.

postgresql permissions

share|improve this question

edited 2 hours ago

a_horse_with_no_name

39.9k776112

asked 3 hours ago

Marcos JotaMarcos Jota

1

New contributor

Marcos Jota is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The manual states: "For example, if table t1 is owned by role g1, of which role u1 is a member, then u1 can grant privileges on t1 to u2, but those privileges will appear to have been granted directly by g1"
  
  – a_horse_with_no_name
  2 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @a_horse_with_no_name Sure, but that happens when a certain role is member of another role. In this particular case each role has permits and if you grant them and check z t1 you will see that u2 granted r* to u3 and u3 granted r* to u2. PD: Thanks for the edit :)
  
  – Marcos Jota
  2 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What Postgres version? Tried to reproduce this on 11, and when trying to close the grant loop, I get ERROR: grant options cannot be granted back to your own grantor. If it works on older versions, I guess this means they recognised it as a bug and fixed it.
  
  – Nick Barnes
  5 mins ago
  
  
  

  
  
  
  

add a comment | 

I'm trying to understand how does granting privileges work on SQL.

According to what I understand, one user has privilege as long as said user has been granted privilege by someone or owns the database. And the second condition I though was true, is that a user may have a certain privilege only if someone the privilege chain connects to the owner/admin of the table.

Now, I'm using PostgreSQL to test how this works in an actual DBMS.

For example, let's say we have a table t1 and the users u1, u2 and u3. Also let's assume u1 is the owner.

• Now, u1 grants SELECT ON t1 TO u2 WITH GRANT OPTION
  


• Then, u2 grants SELECT ON t1 TO u3 WITH GRANT OPTION
  


• At last, u3 grants SELECT ON t1 TO u2 WITH GRANT OPTION
  

This should leave a sort of recursion of grants between u2 and u3. This means I can safely REVOKE the link between u1 and u2. According to SQL the privileges from u2 and u3 should be removed because they have no connection to the owner/admin of the table, but instead PostgreSQL allows this to happens and you can actually still issue SELECT on t1 even after the privilege was revoked.

So the question is, is this the expected behaviour on PostgreSQL/any RDBMS, or am I understanding wrong how SQL privileges should work. If someone could clarify this a bit for me, I would be thankful.

I was using these slides to understand. The last slide is the case I'm talking about.

postgresql permissions

share|improve this question

edited 2 hours ago

a_horse_with_no_name

39.9k776112

asked 3 hours ago

Marcos JotaMarcos Jota

1

New contributor

Marcos Jota is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 2 hours ago

a_horse_with_no_name

39.9k776112

asked 3 hours ago

Marcos JotaMarcos Jota

1

New contributor

Marcos Jota is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 2 hours ago

a_horse_with_no_name

39.9k776112

asked 3 hours ago

Marcos JotaMarcos Jota

1

New contributor

Marcos Jota is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 2 hours ago

a_horse_with_no_name

39.9k776112

edited 2 hours ago

a_horse_with_no_name

39.9k776112

asked 3 hours ago

Marcos JotaMarcos Jota

1

New contributor

Marcos Jota is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 hours ago

Marcos JotaMarcos Jota

1