Home » RDBMS Server » Security » how do i get what are the roles assigned to a user
how do i get what are the roles assigned to a user [message #18980] Sun, 24 February 2002 23:13 Go to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
I have few questions

1) What is the query to see what are the roles and privelages granted to a user/all users

2) What is the query to see what are the sql statements issued by each user and when

3) What is the query to see what all permissions does a user have on objects

4) How to track which user has connected when,worked on what tables

Any help is appreciated..
Re: how do i get what are the roles assigned to a user [message #18986 is a reply to message #18980] Mon, 25 February 2002 01:06 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
1. select * from dba_role_privs
or select * from user_role_privs
2. go for auditing
3. see (user/all/dba)_tab_privs
4. go for auditing
Re: how do i get what are the roles assigned to a user [message #18993 is a reply to message #18980] Mon, 25 February 2002 04:42 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
save script below in file and run ..

Note: if u want privileges granted to public also, assign 'Y' to pub_flag in script

--script starts here

set serveroutput on size 100000
set feed off
clear buffer
set define on
declare
p_user varchar2(30):=upper('&USER');
pub_flag varchar2(1):='N';
CURSOR c1 IS
SELECT a.table_name,a.privilege FROM dba_tab_privs a
WHERE grantee=p_user or (pub_flag='Y' and grantee='PUBLIC');
CURSOR c2 is
SELECT granted_role FROM dba_role_privs WHERE grantee=p_user or (pub_flag='Y' and grantee='PUBLIC') ;
CURSOR c3 is
SELECT privilege FROM dba_sys_privs a WHERE grantee=p_user or (pub_flag='Y' and grantee='PUBLIC') ;
l_count Number:=0;
l_syscount number:=0;
l_objcount number:=0;
BEGIN
SELECT count(*) INTO l_count FROM dba_users where username=trim(p_user);
IF l_count=0 then
SELECT count(*) INTO l_count FROM dba_roles where role=trim(p_user);
end if;
IF l_count>0 or p_user='PUBLIC' THEN
dbms_output.put_line(chr(10));
dbms_output.put_line('---System Privileges---');
dbms_output.put_line(chr(10));

FOR crec IN c3 LOOP
dbms_output.put_line(crec.privilege);
l_syscount := l_syscount+1;
END LOOP;
FOR crec1 IN c2 LOOP

FOR crole_rec IN (SELECT c.privilege FROM dba_sys_privs c
WHERE c.grantee =crec1.granted_role ) loop
dbms_output.put_line(crole_rec.privilege);
l_syscount := l_syscount+1;
END loop;
DECLARE
l_tname VARCHAR2(30);
tname varchar2(30):=crec1.granted_role;
BEGIN
LOOP
l_tname:=tname;
DECLARE
l_child varchar2(255):='';
l_chr varchar2(255):='';
BEGIN
tname :=null;
FOR crec_role IN
(SELECT * FROM role_role_privs
WHERE role=trim(l_tname))
LOOP
tname:=crec_role.granted_role;
l_child:=crec_role.granted_role;
IF length(l_child)>=1 THEN
FOR crole_rec IN (SELECT c.privilege FROM dba_sys_privs c
WHERE c.grantee =l_child ) loop
dbms_output.put_line(crole_rec.privilege);
l_syscount := l_syscount+1;
END loop;
END IF;
END LOOP;
if tname IS null THEN
EXIT;
end if;
END;
END LOOP;

END;
END LOOP;
if l_syscount=0 THEN
dbms_output.put_line('No System Privileges granted for this user');
end if;

dbms_output.put_line(chr(10));
dbms_output.put_line('---Object Privileges---');
dbms_output.put_line(chr(10));
dbms_output.put_line(rpad('Object Name',30,'-')||chr(9)||chr(9)||'Privilege');
dbms_output.put_line(chr(10));
FOR crec IN c1 LOOP
dbms_output.put_line(rpad(crec.table_name,30,'-')||chr(9)||chr(9)||crec.privilege);
l_objcount := l_objcount+1;
END LOOP;
FOR crec1 IN c2 LOOP

FOR crole_rec IN (SELECT c.table_name,c.privilege FROM dba_tab_privs c
WHERE c.grantee =crec1.granted_role ) loop
dbms_output.put_line(rpad(crole_rec.table_name,30,'-')||chr(9)||chr(9)||crole_rec.privilege);
l_syscount := l_syscount+1;
END loop;
DECLARE
l_tname VARCHAR2(30);
tname varchar2(30):=crec1.granted_role;
BEGIN
LOOP
l_tname:=tname;
DECLARE
l_child varchar2(255):='';
l_chr varchar2(255):='';
BEGIN
tname :=null;
FOR crec_role IN
(SELECT * FROM role_role_privs
WHERE role=trim(l_tname) )
LOOP
tname:=crec_role.granted_role;
l_child:=crec_role.granted_role;
IF length(l_child)>=1 THEN
FOR crole_rec IN (SELECT c.table_name,c.privilege FROM dba_tab_privs c
WHERE c.grantee =l_child ) loop
dbms_output.put_line(rpad(crole_rec.table_name,30,'-')||chr(9)||chr(9)||crole_rec.privilege);
l_objcount := l_objcount+1;
END loop;
END IF;
END LOOP;
if tname IS null THEN
EXIT;
end if;
END;
END LOOP;

END;
END LOOP;
if l_objcount=0 then
dbms_output.put_line('No object Privileges granted for this user');
end if;
ELSE
dbms_output.put_line(chr(10));
dbms_output.put_line('User name is not valid. Enter valid username');
END If;
END;
/
set feed on
Previous Topic: how to detect which user has changed the password
Next Topic: how to setup audit trail
Goto Forum:
  


Current Time: Thu Mar 28 11:23:45 CDT 2024