ITPub博客

首页 > 数据库 > Oracle > Create a Procedure to Show All User Privs and Roles_241997.1

Create a Procedure to Show All User Privs and Roles_241997.1

Oracle 作者:rongshiyuan 时间:2014-02-11 16:46:29 0 删除 编辑

Script to Create a Procedure to Show All User Privs and Roles (Doc ID 241997.1)Script to Create
a Procedure to Show All User Privs and Roles (Doc ID 241997.1)



Applies to:

Oracle Server - Enterprise Edition - Version
8.1.7.4 to 11.2.0.1.0 [Release 8.1.7 to 11.2]
Information  in this document
applies to any platform.
***Checked for relevance on 20-SEP-2012*** 

Purpose


Create a procedure to show all roles and privilege details.

The
following script will generate a procedure LIST_USER_ROLE_PRIVS which may
be
queried to obtain information about which privileges and roles are granted
directly
to a certain user, or which users enjoy a particular
privilege/role. The procedure
will also list the roles/privileges granted
through other roles. Note that those
roles/privileges granted to PUBLIC ARE
NOT listed here although the user can very well
make use of them. For a
complete picture it is necessary to list the privileges/roles
granted to the
user and also list the privileges/roles granted to PUBLIC.


Requirements


Execution Environment:
SQL, SQL*Plus,
iSQL*Plus



Usage:
Use sqlplus,connect as
sysdba and run the script.


Configuring


Access Privileges:
SYSDBA privilege are required.
Use
sqlplus, connect as sysdba and run the script.


Instructions


Instructions:
Run the procedure connected as SYS as
SYSDBA.

After granting the execute privilege on the the procedure
,connect
as that user and check the privileges and roles of any
user.


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in
the way text
editors, e-mail packages, and operating systems handle text
formatting (spaces,
tabs, and carriage returns), this script may not be in an
executable state
when you first receive it. Check over the script to ensure
that errors of
this type are corrected.The script will produce an output file
named [outputfile].
This file can be viewed in a browser or uploaded for
support analysis.


Caution


This sample code is provided for
educational purposes only and not supported by Oracle Support Services. It has
been tested internally, however, and works as documented. We do not guarantee
that it will work for you, so be sure to test it in your environment before
relying on it.

Proofread this sample code before using it! Due to the differences in the
way text editors, e-mail packages and operating systems handle text formatting
(spaces, tabs and carriage returns), this sample code may not be in an
executable state when you first receive it. Check over the sample code to ensure
that errors of this type are corrected.


Script


-- Lists the privileges and roles, regardless of how the roles/privileges
were granted;
-- either directly or indirectly.
SET ECHO off
REM Name
:LIST_USER_ROLE_PRIVS.sql
REM ------------------------------
REM Should be
run as SYS
REM ------------------------------
prompt Creating the
procedure LIST_USER_ROLE_PRIVS
CREATE OR REPLACE PROCEDURE
LIST_USER_ROLE_PRIVS(UNAME VARCHAR2)
IS
U1
VARCHAR2(100):=UNAME;

CURSOR one IS SELECT u1.name USERNAME ,U2.NAME
ROLENAME ,SUBSTR(SPM.NAME,1,27) PRIVILEGE
FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$
SA2,SYS.USER$ U1, SYS.USER$ U2,SYS.SYSTEM_PRIVILEGE_MAP SPM
WHERE
SA1.GRANTEE# = U1.USER#
AND SA1.PRIVILEGE# = U2.USER#
AND U2.USER# =
SA2.GRANTEE# (+)
AND SA2.PRIVILEGE# = SPM.PRIVILEGE (+) AND
(U1.NAME
IN
(SELECT GRANTEE FROM DBA_ROLE_PRIVS connect by
prior
granted_role=GRANTEE start with GRANTEE IN
(SELECT NAME FROM USER$
WHERE user# in
(select privilege# from sysauth$ t1, user$ t2
where
t1.grantee#=t2.user# and t2.name=U1)
)
UNION
SELECT GRANTED_ROLE FROM
DBA_ROLE_PRIVS connect by prior
granted_role=GRANTEE start with GRANTEE IN

(SELECT NAME FROM USER$ WHERE user# in
(select privilege# from sysauth$
t1, user$ t2
where t1.grantee#=t2.user# and t2.name=U1)
)
)
OR
U1.NAME=U1
)
ORDER BY U2.USER#, U2.NAME ;

CURSOR two IS select
u.name username,spm.name privilege
from user$ u, sysauth$ s,
system_privilege_map spm
where u.user#=s.grantee# and
s.privilege#=spm.privilege
AND U.NAME=U1
ORDER BY
1,2;

BEGIN
dbms_output.put_line(rpad('USERNAME',30,'
')||rPAD('ROLENAME',21,' ')||rPAD('PRIVILEGE',21,'
'));
DBMS_OUTPUT.PUT_LINE('----------------------------- --------------------
--------------------');
for y in one loop

dbms_output.put_line(rPAD(y.USERNAME,30,' ')||rPAD(y.ROLENAME,21,'
')||rPAD(y.PRIVILEGE,21,' '));
end loop;
for x in two
loop
dbms_output.put_line(rPAD(x.username,51,' ')||rPAD(x.privilege,21,'
'));
end loop;

END;
/

set verify off
prompt Grant the
procedure list_user_role_privs to any user.
grant execute on
list_user_role_privs to &&username;

REM Connect as
&&username and run the procedure.
REM UNAME to be given in capital
letters
prompt Enter that user's password to connect
connect
&&username

prompt Enter user to probe
set serveroutput on size
1000000
begin
SYS.LIST_USER_ROLE_PRIVS('&UNAME');
end;
/

undef
username
set verify on

-- end script LIST_USER_ROLE_PRIVS.sql


Sample Output










 


Connect SYS as
sysdba

R3 <==== ALTER SYSTEM, ALTER USER, ALTER ANY TABLE
|
R2
<==== DELETE ANY TABLE, BECOME USER
|
R1 <==== UPDATE ANY
TABLE
|
TEST <==== CREATE SESSION
|
R4

drop role
R1;
create role R1;

drop role R2;
create role R2;

drop role
R3;
create role R3;

drop role R4;
create role R4;

drop user
TEST cascade;
create user TEST identified by TEST;

grant CREATE
session to TEST;

grant UPDATE ANY TABLE to R1;
grant R1 to TEST
;

grant DELETE ANY TABLE, BECOME USER to R2;
grant R2 to
R1;

grant ALTER SYSTEM, ALTER USER, ALTER ANY TABLE to R3;
grant R3 to
R2;

grant CREATE ROLE to R4;
grant R4 to TEST;

SQL>
@LIST_USER_ROLE_PRIVS.sql
Creating the procedure
LIST_USER_ROLE_PRIVS

Procedure created.

Grant the procedure
list_user_role_privs to any user.
Enter value for username:
SYSTEM

Grant succeeded.

Enter that user's password to
connect
Enter password:
Connected.
Enter user to probe
Enter value
for uname: TEST
USERNAME ROLENAME PRIVILEGE
-----------------------------
-------------------- --------------------
TEST R1 UPDATE ANY TABLE
TEST
R1
R1 R2 DELETE ANY TABLE
R1 R2 BECOME USER
R1 R2
R2 R3 ALTER ANY
TABLE
R2 R3 ALTER USER
R2 R3 ALTER SYSTEM
TEST R4 CREATE ROLE
TEST
CREATE SESSION

PL/SQL procedure successfully completed.



Disclaimer

EXCEPT WHERE EXPRESSLY
PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS
AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND,
WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND
NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE
OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B)
THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY
CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED
IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY
FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE
DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.


ORACLE
RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME
WITHOUT NOTICE.



Reviewed for Relevance 9-SEP-2010



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-1079633/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3213298