150 lines
4.6 KiB
SQL
150 lines
4.6 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display priviledges
|
|
* Compability:
|
|
* Parameters : 1 - Grantee - Default Value - %, (Use % as wildcard)
|
|
* 2 - Owner - Default Value - %, (Use % as wildcard)
|
|
* 3 - Granted
|
|
* 4 - Privilege
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -------------------------------------------------------
|
|
* 04-Feb-15 Vishal Gupta Added privilege as input parameter and fixed some bugs
|
|
* 26-Feb-13 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE grantee
|
|
UNDEFINE owner
|
|
UNDEFINE objectname
|
|
UNDEFINE privilege
|
|
|
|
DEFINE grantee="&&1"
|
|
DEFINE owner="&&2"
|
|
DEFINE objectname="&&3"
|
|
DEFINE privilege="&&4"
|
|
|
|
|
|
COLUMN _grantee NEW_VALUE grantee NOPRINT
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _objectname NEW_VALUE objectname NOPRINT
|
|
COLUMN _privilege NEW_VALUE privilege NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&grantee','','%',UPPER('&&grantee')) "_grantee"
|
|
, DECODE('&&owner','','%',UPPER('&&owner')) "_owner"
|
|
, DECODE('&&objectname','','%',UPPER('&&objectname')) "_objectname"
|
|
, DECODE('&&privilege','','%',UPPER('&&privilege')) "_privilege"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
|
|
PROMPT ***************************************************************
|
|
PROMPT * P R I V I L E G E S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Grantee = '&&grantee'
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - ObjectName = "&&objectname"
|
|
PROMPT * - Privilege = "&&privilege"
|
|
PROMPT ***************************************************************
|
|
|
|
COLUMN grantee HEADING "Grantee" FORMAT a30
|
|
COLUMN grantor HEADING "Grantor" FORMAT a20
|
|
COLUMN granted_role HEADING "Granted Role" FORMAT a30
|
|
COLUMN admin_option HEADING "Admin|Option" FORMAT a6
|
|
COLUMN default_role HEADING "Default|Role" FORMAT a7
|
|
COLUMN privilege HEADING "Privilege" FORMAT a40
|
|
COLUMN object_name HEADING "Object Name" FORMAT a40
|
|
COLUMN grantable HEADING "Grantable" FORMAT a10
|
|
COLUMN hierarchy HEADING "Hierarchy" FORMAT a10
|
|
|
|
PROMPT **********************************
|
|
PROMPT * R O L E P R I V I L E G E S
|
|
PROMPT **********************************
|
|
SELECT p.grantee
|
|
, p.granted_role
|
|
, p.admin_option
|
|
, p.default_role
|
|
FROM dba_role_privs p
|
|
WHERE p.grantee like '&&grantee' ESCAPE '\'
|
|
AND 'SYS' like '&&owner' ESCAPE '\'
|
|
AND p.granted_role like '&&objectname' ESCAPE '\'
|
|
AND '&&privilege' = '%'
|
|
ORDER BY p.grantee
|
|
, p.granted_role
|
|
;
|
|
|
|
PROMPT
|
|
PROMPT **************************************
|
|
PROMPT * S Y S T E M P R I V I L E G E S
|
|
PROMPT **************************************
|
|
|
|
SELECT p.grantee
|
|
, p.privilege
|
|
, p.admin_option
|
|
FROM dba_sys_privs p
|
|
WHERE p.grantee LIKE '&&grantee' ESCAPE '\'
|
|
AND 'SYS' LIKE '&&owner' ESCAPE '\'
|
|
AND '&&objectname' = '%'
|
|
AND p.privilege LIKE '&&privilege' ESCAPE '\'
|
|
ORDER BY p.grantee
|
|
, p.privilege
|
|
;
|
|
|
|
PROMPT
|
|
PROMPT **************************************
|
|
PROMPT * O B J E C T P R I V I L E G E S
|
|
PROMPT **************************************
|
|
SELECT p.grantee
|
|
, p.owner || '.' || p.table_name object_name
|
|
, p.grantor
|
|
, p.privilege
|
|
, p.grantable
|
|
, p.hierarchy
|
|
FROM dba_tab_privs p
|
|
WHERE p.grantee LIKE '&&grantee' ESCAPE '\'
|
|
AND p.owner LIKE '&&owner' ESCAPE '\'
|
|
AND p.table_name LIKE '&&objectname' ESCAPE '\'
|
|
AND p.privilege LIKE '&&privilege' ESCAPE '\'
|
|
ORDER BY p.grantee
|
|
, object_name
|
|
, p.privilege
|
|
;
|
|
|
|
|
|
PROMPT
|
|
PROMPT **************************************
|
|
PROMPT * C O L U M N P R I V I L E G E S
|
|
PROMPT **************************************
|
|
SELECT p.grantee
|
|
, p.owner || '.' || p.table_name object_name
|
|
, p.column_name
|
|
, p.grantor
|
|
, p.privilege
|
|
, p.grantable
|
|
FROM dba_col_privs p
|
|
WHERE p.grantee LIKE '&&grantee' ESCAPE '\'
|
|
AND p.owner LIKE '&&owner' ESCAPE '\'
|
|
AND p.table_name LIKE '&&objectname' ESCAPE '\'
|
|
AND p.privilege LIKE '&&privilege' ESCAPE '\'
|
|
ORDER BY p.grantee
|
|
, object_name
|
|
, p.privilege
|
|
;
|
|
|
|
@@footer
|