Home » SQL & PL/SQL » SQL & PL/SQL » Querying table permissions to see which users have them (18)
Querying table permissions to see which users have them [message #689533] Mon, 05 February 2024 15:16 Go to next message
Darth Waiter
Messages: 58
Registered: October 2020
Member
In my test environment, I created a user C##SOMEBODY, and that user created a few tables.
I want to find out who has permissions to those tables. That user should be included in the results because it owns its tables.
So far I tried (results in the comments on the 1st line of each query):

select -- no results
     rp.grantee
    ,rp.granted_role
from dba_role_privs rp
    connect by prior rp.granted_role = rp.grantee start with rp.grantee = :1
order by 1,2,3;

select -- CREATE PROCEDURE, CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE
     sp.grantee
    ,sp.privilege
from dba_sys_privs sp
where 1=1
--    and sp.privilege in ('SELECT','INSERT','DELETE','UPDATE')
    and (
        sp.grantee = :1
        or grantee in (
            select rp.granted_role
            from dba_role_privs rp
                connect by prior rp.granted_role = rp.grantee start with rp.grantee = :1
        )
    )
order by 1,2;

select -- something unrelated that I explicitly granted but not the tables created by the user
     tp.grantee
    ,tp.privilege
from dba_tab_privs tp
where 1=1
--    and tp.privilege in ('SELECT','INSERT','DELETE','UPDATE')
    and (
        tp.grantee = :1
        or tp.grantee in (
            select rp.granted_role 
            from dba_role_privs rp
                connect by prior rp.granted_role = rp.grantee start with rp.grantee = :1
        )
    )
order by 1,2,3,4;
I checked the above queries' outputs against some accepted answers re table permissions, on stack exchange, and they return exactly the same results.
There must be something else that I am missing because the user clearly has select/insert/update/delete permissions on the tables that it owns. What do I need to query to see that?

Appreciate your help!
Re: Querying table permissions to see which users have them [message #689534 is a reply to message #689533] Mon, 05 February 2024 22:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The owner of the table automatically has all privileges on its own tables.  Dba_tab_privs only lists privileges granted to others.  Please see the demonstration below.  You can combine the below with your other queries for role and sys privileges.


C##SCOTT@XE_21.3.0.0.0> CREATE USER C##SOMEBODY IDENTIFIED BY SOMEBODY QUOTA UNLIMITED ON USERS
  2  /

User created.

C##SCOTT@XE_21.3.0.0.0> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO C##SOMEBODY
  2  /

Grant succeeded.

C##SCOTT@XE_21.3.0.0.0> CONNECT C##SOMEBODY/SOMEBODY
Connected.
C##SOMEBODY@XE_21.3.0.0.0> 
C##SOMEBODY@XE_21.3.0.0.0> 
C##SOMEBODY@XE_21.3.0.0.0> 
C##SOMEBODY@XE_21.3.0.0.0> CREATE TABLE test_tab AS SELECT * FROM DUAL
  2  /

Table created.

C##SOMEBODY@XE_21.3.0.0.0> GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO C##SCOTT
  2  /

Grant succeeded.

C##SOMEBODY@XE_21.3.0.0.0> CONNECT C##SCOTT/TIGER
Connected.
C##SCOTT@XE_21.3.0.0.0> 
C##SCOTT@XE_21.3.0.0.0> 
C##SCOTT@XE_21.3.0.0.0> 
C##SCOTT@XE_21.3.0.0.0> SET LINESIZE 100
C##SCOTT@XE_21.3.0.0.0> COLUMN grantee	   FORMAT A30
C##SCOTT@XE_21.3.0.0.0> COLUMN table_name  FORMAT A30
C##SCOTT@XE_21.3.0.0.0> COLUMN privilege   FORMAT A30
C##SCOTT@XE_21.3.0.0.0> SELECT 'C##SOMEBODY' grantee, table_name, 'OWNER-ALL' privilege
  2  FROM   dba_tables
  3  WHERE  owner = 'C##SOMEBODY'
  4  UNION ALL
  5  SELECT grantee, table_name, privilege
  6  FROM   dba_tab_privs
  7  WHERE  owner = 'C##SOMEBODY'
  8  /

GRANTEE                        TABLE_NAME                     PRIVILEGE                             
------------------------------ ------------------------------ ------------------------------        
C##SOMEBODY                    TEST_TAB                       OWNER-ALL                             
C##SCOTT                       TEST_TAB                       DELETE                                
C##SCOTT                       TEST_TAB                       INSERT                                
C##SCOTT                       TEST_TAB                       SELECT                                
C##SCOTT                       TEST_TAB                       UPDATE                                

5 rows selected.

[Updated on: Mon, 05 February 2024 23:02]

Report message to a moderator

Re: Querying table permissions to see which users have them [message #689535 is a reply to message #689534] Tue, 06 February 2024 06:08 Go to previous messageGo to next message
Darth Waiter
Messages: 58
Registered: October 2020
Member
Implicit permissions. Got it!
Thank you!
Re: Querying table permissions to see which users have them [message #689537 is a reply to message #689535] Tue, 06 February 2024 13:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Don't forget about roles. Table privilege can be granted to a role. That role can be granted to another role R1 and R1 can be granted to role R2 and so on and finally role Rn can be granted to some user.

SY.
Re: Querying table permissions to see which users have them [message #689538 is a reply to message #689537] Tue, 06 February 2024 14:17 Go to previous messageGo to next message
Darth Waiter
Messages: 58
Registered: October 2020
Member
Did I forget about them?
Re: Querying table permissions to see which users have them [message #689540 is a reply to message #689533] Wed, 07 February 2024 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I want to find out who has permissions to those tables.

Here's an example of what Solomon underlined.
We create a table in TEST schema, 3 users T1, T2, T3 and 3 roles R1, R2, R3 which are respectively granted to T1, T2 and T3 and R1 is granted to R2 and R2 to R3.
Then we give privileges on the table to SCOTT and the roles:
create table test.t (id int, val varchar2(10));
create user t1 identified by t1;
create user t2 identified by t2;
create user t3 identified by t3;
create role r1;
create role r2;
create role r3;
revoke r1 from michel;
revoke r2 from michel;
revoke r3 from michel;
grant r1 to r2, t1;
grant r2 to r3, t2;
grant r3 to t3;
grant select on test.t to scott, r1;
grant insert, delete on test.t to r2;
grant update (val) on test.t to r3;
Execution:
SQL> create table test.t (id int, val varchar2(10));

Table created.

SQL> create user t1 identified by t1;

User created.

SQL> create user t2 identified by t2;

User created.

SQL> create user t3 identified by t3;

User created.

SQL> create role r1;

Role created.

SQL> create role r2;

Role created.

SQL> create role r3;

Role created.

SQL> revoke r1 from michel;

Revoke succeeded.

SQL> revoke r2 from michel;

Revoke succeeded.

SQL> revoke r3 from michel;

Revoke succeeded.

SQL> grant r1 to r2, t1;

Grant succeeded.

SQL> grant r2 to r3, t2;

Grant succeeded.

SQL> grant r3 to t3;

Grant succeeded.

SQL> grant select on test.t to scott, r1;

Grant succeeded.

SQL> grant insert, delete on test.t to r2;

Grant succeeded.

SQL> grant update (val) on test.t to r3;

Grant succeeded.
The following query gives how come the privileges across the roles:
SQL> Column priv_ FORMAT A30  HEADING 'Privilege'
SQL> Column user_ FORMAT A20  HEADING 'User/Role'
SQL> Column path_ FORMAT A30  HEADING 'Privilege path'
SQL> Break on priv_
SQL> With
  2    granted as (
  3      Select owner, table_name, privilege, grantee
  4      from dba_tab_privs
  5      where grantee != 'SYS'
  6        and owner = 'TEST'
  7        and table_name = 'T'
  8      union all
  9      Select owner, table_name , privilege||' ('||column_name||')', grantee
 10      from dba_col_privs
 11      where grantee != 'SYS'
 12        and owner = 'TEST'
 13        and table_name = 'T'
 14    ),
 15    paths as (
 16      select connect_by_root granted_role granted_role, grantee,
 17             substr(sys_connect_by_path(granted_role,'/'),2) path
 18      from dba_role_privs
 19      connect by granted_role = prior grantee
 20      start with granted_role in (select grantee from granted)
 21    )
 22  Select 'All privileges' priv_,  t.owner user_, '<Owner>' path_
 23  from dba_tables t
 24  where owner = 'TEST'
 25    and table_name = 'T'
 26  union
 27  Select g.privilege priv_, grantee user_, '<Direct>' path_
 28  from granted g
 29  union
 30  Select g.privilege priv_, p.grantee user_, p.path path_
 31  from granted g, paths p
 32  where p.granted_role = g.grantee and p.grantee != 'MICHEL'
 33  order by 1, 2, 3
 34  /
Privilege                      User/Role            Privilege path
------------------------------ -------------------- ------------------------------
All privileges                 TEST                 <Owner>
DELETE                         R2                   <Direct>
                               R3                   R2
                               T2                   R2
                               T3                   R2/R3
INSERT                         R2                   <Direct>
                               R3                   R2
                               T2                   R2
                               T3                   R2/R3
SELECT                         R1                   <Direct>
                               R2                   R1
                               R3                   R1/R2
                               SCOTT                <Direct>
                               T1                   R1
                               T2                   R1/R2
                               T3                   R1/R2/R3
UPDATE (VAL)                   R3                   <Direct>
                               T3                   R3

[Updated on: Wed, 07 February 2024 10:49]

Report message to a moderator

Re: Querying table permissions to see which users have them [message #689541 is a reply to message #689540] Wed, 07 February 2024 10:51 Go to previous messageGo to next message
Darth Waiter
Messages: 58
Registered: October 2020
Member
Your queries are different from mine in that you query column privileges. Is it possible for a user not to have implicit or explicit table privileges but still access data if they have column privileges? If not then I probably do not need permission so granular as column permissions.
Re: Querying table permissions to see which users have them [message #689542 is a reply to message #689541] Wed, 07 February 2024 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is possible:
SQL> grant update (sal) on michel.emp to t1;

Grant succeeded.

SQL> grant create session to t1;

Grant succeeded.

SQL> conn t1/t1
Connected.
T1> update michel.emp set sal=sal+1000;

14 rows updated.

[Updated on: Wed, 07 February 2024 12:35]

Report message to a moderator

Re: Querying table permissions to see which users have them [message #689543 is a reply to message #689542] Wed, 07 February 2024 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also don't forget those who have "% ANY TABLE" system privileges and can then access all tables.

[Updated on: Wed, 07 February 2024 12:42]

Report message to a moderator

Re: Querying table permissions to see which users have them [message #689544 is a reply to message #689543] Wed, 07 February 2024 13:32 Go to previous messageGo to next message
Darth Waiter
Messages: 58
Registered: October 2020
Member
Will my queries not show that?
Re: Querying table permissions to see which users have them [message #689545 is a reply to message #689544] Wed, 07 February 2024 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your queries don't address your issue (the one I quoted).

Re: Querying table permissions to see which users have them [message #689546 is a reply to message #689543] Wed, 07 February 2024 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also don't forget if PUBLIC appears in the query result this means that every user can do the authorized action.
As in (line INDEX):
SQL> grant index on test.t to public;

Grant succeeded.

SQL> Column priv_ FORMAT A30  HEADING 'Privilege'
SQL> Column user_ FORMAT A20  HEADING 'User/Role'
SQL> Column path_ FORMAT A30  HEADING 'Privilege path'
SQL> Break on priv_ skip 1
SQL> With
  2    granted as (
  3      Select owner, table_name, privilege, grantee
  4      from dba_tab_privs
  5      where grantee != 'SYS'
  6        and owner = 'TEST'
  7        and table_name = 'T'
  8      union all
  9      Select owner, table_name , privilege||' ('||column_name||')', grantee
 10      from dba_col_privs
 11      where grantee != 'SYS'
 12        and owner = 'TEST'
 13        and table_name = 'T'
 14    ),
 15    paths as (
 16      select connect_by_root granted_role granted_role, grantee,
 17             substr(sys_connect_by_path(granted_role,'/'),2) path
 18      from dba_role_privs
 19      connect by granted_role = prior grantee
 20      start with granted_role in (select grantee from granted)
 21    )
 22  Select 'All privileges' priv_,  t.owner user_, '<Owner>' path_
 23  from dba_tables t
 24  where owner = 'TEST'
 25    and table_name = 'T'
 26  union
 27  Select g.privilege priv_, grantee user_, '<Direct>' path_
 28  from granted g
 29  union
 30  Select g.privilege priv_, p.grantee user_, p.path path_
 31  from granted g, paths p
 32  where p.granted_role = g.grantee and p.grantee != 'MICHEL'
 33  order by 1, 2, 3
 34  /
Privilege                      User/Role            Privilege path
------------------------------ -------------------- ------------------------------
All privileges                 TEST                 <Owner>

DELETE                         R2                   <Direct>
                               R3                   R2
                               T2                   R2
                               T3                   R2/R3

INDEX                          PUBLIC               <Direct>

INSERT                         R2                   <Direct>
                               R3                   R2
                               T2                   R2
                               T3                   R2/R3

SELECT                         R1                   <Direct>
                               R2                   R1
                               R3                   R1/R2
                               SCOTT                <Direct>
                               T1                   R1
                               T2                   R1/R2
                               T3                   R1/R2/R3

UPDATE (VAL)                   R3                   <Direct>
                               T3                   R3

[Updated on: Thu, 08 February 2024 14:12]

Report message to a moderator

Re: Querying table permissions to see which users have them [message #689547 is a reply to message #689546] Thu, 08 February 2024 13:49 Go to previous message
Darth Waiter
Messages: 58
Registered: October 2020
Member
Great, with that I can piece together permissions that I need.
Previous Topic: Dad, Grand Pa and Great Grand Pa !
Next Topic: Creating Global vs Local Indexes for Partitioned Table
Goto Forum:
  


Current Time: Thu Feb 29 00:23:03 CST 2024