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  |
 |
Darth Waiter
Messages: 78
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;
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  |
 |
Barbara Boehmer
Messages: 9090
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 #689540 is a reply to message #689533] |
Wed, 07 February 2024 10:46 |
 |
Michel Cadot
Messages: 68647
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;
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.
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 #689546 is a reply to message #689543] |
Wed, 07 February 2024 15:06 |
|
Michel Cadot
Messages: 68647
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 |
|
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 14:46:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
