GRANT system-privilege1 TO user;
GRANT system-privilege1, system-privilege2 TO user;
ANY = the user can perform the privilege on any objects except those owned by SYS
WITH ADMIN OPTION; = allows the user to grant that privilege to other users
REVOKE system-privilege1 FROM user; /* only impacts the one user */
REVOKE ALL PRIVILEGES FROM user;
| %ANY ------------------------------- Analyze Any Audit Any ADVISOR ------------------------------- Advisor CLASS ------------------------------- Execute Any Class CLUSTER ------------------------------- Alter Any Cluster Create Any Cluster Create Cluster Drop Any Cluster CONTEXT ------------------------------- Create Any Context Drop Any Context COST ------------------------------- Alter Resource Cost DATABASE ------------------------------- Administer Database Trigger Alter Database Create Database Link Create Public Database Link Drop Public Database Link Export Full Database Import Full Database DICTIONARY ------------------------------- Analyze Any Dictionary Select Any Dictionary DIMENSION ------------------------------- Alter Any Dimension Create Any Dimension Create Dimension Drop Any Dimension DIRECTORY ------------------------------- Create Any Directory Drop Any Directory EVALUATION CONTEXT ------------------------------- Alter Any Evaluation Context Create Any Evaluation Context Create Evaluation Context Drop Any Evaluation Context Execute Any Evaluation Context FILE GROUP ------------------------------- Manage Any File Group Manage File Group Read Any File Group INDEX ------------------------------- Alter Any Index Create Any Index Drop Any Index | INDEXTYPE ------------------------------- Alter Any Indextype Create Any Indextype Create Indextype Drop Any Indextype Execute Any Indextype JOB ------------------------------- Create Any Job Create External Job Create Job LIBRARY ------------------------------- Alter Any Library Create Any Library Create Library Drop Any Library Execute Any Library LINK ------------------------------- Create Database Link Create Public Database Link Drop Public Database Link MANAGER ------------------------------- Administer Resource Manager MATERIALIZED VIEW ------------------------------- Alter Any Materialized View Create Any Materialized View Create Materialized View Drop Any Materialized View NOTIFICATION ------------------------------- Change Notification OBJECT ------------------------------- Grant Any Object Privilege OPERATOR ------------------------------- Create Any Operator Create Operator Drop Any Operator Execute Any Operator OUTLINE ------------------------------- Alter Any Outline Create Any Outline Drop Any Outline PRIVILEGE ------------------------------- Grant Any Object Privilege Grant Any Privilege PROCEDURE ------------------------------- Alter Any Procedure Create Any Procedure Create Procedure Debug Any Procedure Drop Any Procedure Execute Any Procedure PROFILE ------------------------------- Alter Profile Create Profile Drop Profile .. .. | PROGRAM ------------------------------- Execute Any Program QUERY REWRITE ------------------------------- Global Query Rewrite Query Rewrite QUEUE ------------------------------- Dequeue Any Queue Enqueue Any Queue Manage Any Queue REFRESH ------------------------------- On Commit Refresh RESOURCE ------------------------------- Administer Resource Manager Alter Resource Cost RESUMABLE ------------------------------- Resumable ROLE ------------------------------- Alter Any Role Create Role Drop Any Role Grant Any Role ROLLBACK SEGMENT ------------------------------- Alter Rollback Segment Create Rollback Segment Drop Rollback Segment RULE ------------------------------- Alter Any Rule Create Any Rule Create Rule Drop Any Rule Execute Any Rule RULE SET ------------------------------- Alter Any Rule Set Create Any Rule Set Create Rule Set Drop Any Rule Set Execute Any Rule Set SCHEDULER ------------------------------- Manage Scheduler SEQUENCE ------------------------------- Alter Any Sequence Create Any Sequence Create Sequence Drop Any Sequence Select Any Sequence SESSION ------------------------------- Alter Session Create Session Debug Connect Session Restricted Session SQL PROFILE ------------------------------- Alter Any Sql Profile Create Any Sql Profile Drop Any Sql Profile | SQL TUNING SET ------------------------------- Administer Any Sql Tuning Set Administer Sql Tuning Set SYNONYM ------------------------------- Create Any Synonym Create Public Synonym Create Synonym Drop Any Synonym Drop Public Synonym SYSTEM ------------------------------- Alter System Audit System TABLE ------------------------------- Alter Any Table Backup Any Table Comment Any Table Create Any Table Create Table Delete Any Table Drop Any Table Flashback Any Table Insert Any Table Lock Any Table Select Any Table Under Any Table Update Any Table TABLESPACE ------------------------------- Alter Tablespace Create Tablespace Drop Tablespace Manage Tablespace Unlimited Tablespace TRANSACTION ------------------------------- Force Any Transaction Force Transaction Select Any Transaction TRIGGER ------------------------------- Administer Database Trigger Alter Any Trigger Create Any Trigger Create Trigger Drop Any Trigger TYPE ------------------------------- Alter Any Type Create Any Type Create Type Drop Any Type Execute Any Type Under Any Type USER ------------------------------- Alter User Become User Create User Drop User VIEW ------------------------------- Create Any View Create View Drop Any View Merge Any View Under Any View .. .. |
clear screen
rem +------------------------------------------------------------------+
rem | Script ID: Privs.sql ver 1.0
rem |
rem | Purpose: List privileges by type.
rem |
rem | Developer: Lynn Tobias
rem | Script Date: August 06, 2008
rem |
rem | Table(s)Used: session_privs, v$parameter
rem |
rem | Called by: n/a
rem | Calls: n/a
rem |
rem | Variables: xValue - value from v$parameter showing oracle version #
rem | xWord - the object name used as category headers
rem |
rem | Output: Privilege&xValue..Lst
rem |
rem | Revisions: Dvlr Date Version Comment/Change
rem | ---- -------- ------- ----------------------------+
rem |
rem +------------------------------------------------------------------+
--+- 1 -------------------------------------------------------------------------
--| Get the Oracle version for report header
--+-----------------------------------------------------------------------------
Ttitle Off
Set Heading Off Echo Off Feedback Off Term Off Pagesize 60 NewPage 1
Column Value New_Value xValue
Select Value
From V$Parameter
Where Name = 'compatible';
Set Term On
--+- 2 -------------------------------------------------------------------------
--| This was done outside just to make the actual code easier to read. With
--| subtracting SUBSTRs and INSTRs it got difficult to decifer. It finds the
--| spaces in Privilege. A space is concatenated onto the end so we can find the
--| last word.
--+-----------------------------------------------------------------------------
Create Global Temporary Table GT_Priv_Spaces
On Commit Preserve Rows
As
--+----+----------------------------------------------------------------------
Select Privilege,
Instr(Privilege||' ',' ',1,1) Space1,
Instr(Privilege||' ',' ',1,2) Space2,
Instr(Privilege||' ',' ',1,3) Space3,
Instr(Privilege||' ',' ',1,4) Space4,
Instr(Privilege||' ',' ',1,5) Space5,
Instr(Privilege||' ',' ',1,6) Space6
From Session_Privs;
--+- 2 -------------------------------------------------------------------------
--| Put this info in a global temp table since, besides listing it,
--| I want to count to make sure we got everything, and also look for differences.
--+-----------------------------------------------------------------------------
Create Global Temporary Table GT_Privs
On Commit Preserve Rows
As
--+----------------------------------------------------------------------
Select Word,
Initcap(Privilege) Privilege
From
--+- 2.1 ---------------------------------------------------------
--| These are the exception cases: mainly, more than one word is
--| needed to define the type (or 'ANY' at the end)
--+---------------------------------------------------------------
(
Select Distinct
Case When Privilege Like '%MATERIALIZED VIEW%' Then 'MATERIALIZED VIEW'
When Privilege Like '%RULE SET%' Then 'RULE SET'
When Privilege Like '%SQL PROFILE%' Then 'SQL PROFILE'
When Privilege Like '%FILE GROUP%' Then 'FILE GROUP'
When Privilege Like '%SQL TUNING SET%' Then 'SQL TUNING SET'
When Privilege Like '%ROLLBACK SEGMENT%' Then 'ROLLBACK SEGMENT'
When Privilege Like '%QUERY REWRITE%' Then 'QUERY REWRITE'
When Privilege Like '%EVALUATION CONTEXT%' Then 'EVALUATION CONTEXT'
When Privilege Like '%ANY' Then '%ANY'
Else Word
End
As
Word,
Privilege
From
--+- 2.1.1 ------------------------------------------------------
--| Find each word. Union them so they are one beneath the other
--| Add the space at the end of Privilege to find the last word.
--+--------------------------------------------------------------
(
Select Privilege, Substr(Privilege||' ',1 , Space1 -1)
As
Word
From GT_Priv_Spaces
Union
Select Privilege, Substr(Privilege||' ',Space1+1, Space2-Space1-1)
From GT_Priv_Spaces
Union
Select Privilege, Substr(Privilege||' ',Space2+1, Space3-Space2-1)
From GT_Priv_Spaces
Union
Select Privilege, Substr(Privilege||' ',Space3+1, Space4-Space3-1)
From GT_Priv_Spaces
Union
Select Privilege, Substr(Privilege||' ',Space4+1, Space5-Space4-1)
From GT_Priv_Spaces
Union
Select Privilege, Substr(Privilege||' ',Space5+1, Space6-Space5-1)
From GT_Priv_Spaces
)
--+- 2.1 end ---------------------------------------------------------
--| Exclude Verbs and Adjectives since we want to categories by object
--+-------------------------------------------------------------------
Where Word Is Not Null
And Word Not In ('ALTER', 'ADMINISTER', 'ANALYZE', 'AUDIT', 'BACKUP',
'BECOME', 'CHANGE', 'COMMIT', 'COMMENT', 'CONNECT',
'CREATE','DELETE', 'DEBUG', 'DEQUEUE', 'DROP',
'ENQUEUE', 'EXECUTE','EXPORT', 'FLASHBACK', 'FORCE',
'GRANT', 'IMPORT', 'INSERT', 'LOCK', 'MANAGE',
'MERGE','READ', 'REWRITE', 'SELECT', 'UPDATE'
)
And Word Not In ('ACCESS', 'EXEMPT', 'EXTERNAL', 'FULL', 'IDENTITY',
'ON', 'PUBLIC', 'RESTRICTED', 'SET', 'SQL', 'UNDER',
'UNLIMITED'
)
Order By Word
)
--+- 2 end ---------------------------------------------------------------------
--| Can't throw out 'ANY' until the end or you won't get 'ANALYZE ANY', etc.
--+-----------------------------------------------------------------------------
Where Word <> 'ANY';
--+- 3 -------------------------------------------------------------------------
--| Check that this report pulls all privileges
--+-----------------------------------------------------------------------------
Select 'Privileges On This List: ', Count( Distinct Privilege) From GT_Privs;
Select 'Privileges In Session_Privs: ', Count(*) From Session_Privs;
Prompt
--+- 4 -------------------------------------------------------------------------
--| If there's any privilege not on the report, display it.
--+-----------------------------------------------------------------------------
Select Privilege
From Session_Privs
Minus
Select Distinct Upper(Privilege)
From GT_Privs;
--+- 5 -------------------------------------------------------------------------
--| Spool the actual list of privileges with the oracle version # as header.
--| The ttitle is being used to show the object the privileges are associated with.
--+-----------------------------------------------------------------------------
Spool Privilege&Xvalue..Lst
Prompt Privileges With Oracle Version &Xvalue
Ttitle Left Xword Skip 1 '-------------------------------'
Break On Word Skip Page
Column Word New_Value xWord Noprint
Column Privilege Format A30
Select Word,
Privilege
From GT_Privs;
Prompt
Prompt This Report Has Been Written To Privilege&Xvalue..Lst
Spool Off
Truncate Table GT_Privs;
Drop Table GT_Privs Purge;
Truncate Table GT_Priv_Spaces;
Drop Table GT_Priv_Spaces Purge;
Posts
No comments:
Post a Comment