On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, September 1, 2008

System Privileges

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
..
..
For a downloadable attachment, click here.
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;

No comments:

Post a Comment