Skip to Main Content
Ted Struik - Oracle
Search For
Home
Home
Ted Struik - Oracle
Search
Site Map
Statistics
Page Errors
Apex
Apex
General
Autocomplete
Conditions
Errors
Items used in PL/SQL
Validations
Dynamic Actions
Logs
Version
App-wide Items
Error Handling
Apex Views
Apex Upgrade 4.2 to 5.1
Apex Upgrade 20.2 to 23.1
Database Dependencies
Show Source Code
URLs
Authorization
Modal Dialogs
Apex Office Print (AOP)
Apex-Sert
Warn on Unsaved Changes
Form Regions
Reports
Checkboxes
Download
Errors
Nowrap Columns
Substitution Strings
Use IR results in pl/sql
Clickable Row
Row Highlight
Translate Filters etc.
Bar Charts
Column Usage
Reset Pagination
Hide IR Actions menu items
Get Classic Report query
Interactive Grid
ORA-20987
ERR-1002
Copying Text
IG Info
Multiple Row Select
Read Only Options
Search
Sort and Search
Images & Fonts
Standard Icons & Fonts
WebDav
Font Awesome
Icons/image popup LOV
Lists
Hierarchy
LOVs
Definition Bug
Set 1-item LOV to Disabled
With Clause Errors
Modal LOV Plugin
HTML / CSS / JavaScript
Vertical Align
Legend CSS
Geolocation
Apex.confirm
Pie Charts
Codemirror
Theme Roller
Catching Key Strokes
Browser Cache
Locale
RequireJS
Collapsible Regions
Find JavaScript
Import / Export
Dynamic Translations
Translated Pages
APEXExport (java)
apex_export (pl/sql)
Component Settings
Charts
Translate Charts
[Reports] Bar Charts
Shuttles
Shuttle Sorting
Disable Shuttle Items
Menus
Accordion-Like
Scroll bug
Authorization
Session State
About Session State
Rollback & Commit
Performance of v()
Apex Clone Session
Request
Session State in Views
Collections
ORA-20101 & ORA-20104
ORA-00001
Max Row Count
(PL)SQL
(PL)SQL
External Tables
SQL Plus
Conditions
Long
ORDImage
Date & Timestamp
CGI environment
Sys_context
Rounding percentages
Apex_string.split in SQL
Dbms_metadata
Instr
Regular Expressions
Rollback to Savepoint
Use table aliases
(Pipelined) Bulk Collect
JSON
Source Code Search (PL/SQL)
Source Code Search (SQL)
Format Models
Pipelined Table Functions
Hint no_unnest
Listagg
DBA
DBA
Backup & Restore
Users
Export & Import
DBMS Jobs
Tablespaces
Table Size
Flush cache
Access Control List (ACL)
Locks
Java
Constraints
Object Dependencies
Package Dependencies 1
Package Dependencies 2
Grants
SQL History
Startup Errors
ORDS
Miscellaneous
Miscellaneous
Holidays
E-mail
NLExtract
Windows
VPN
VMware
Links
Google
Visualization Orgchart
Hide Search Results
Reverse Proxy
To-do List
Cytoscape
Graphics
Upscaling
Amazon Prices
Domoticz
DDoS
Hobbies
Hobbies
Motorcycles
'95 Kawasaki ZZR 600
'94 Kawasaki ZZR 1100
'91 Honda CBR 600 F2
'95 Kawasaki ZZR 1100
'99 Honda CBR 1100 XX
'00 Honda CBR 1100 XX
'02 Kawasaki ZX-12R (NL)
'05 Ducati 999 (NL)
'08 Honda Fireblade
'07 Suzuki Bandit 1250A
'18 Suzuki GSX-S 1000 F
MotoGP
Garfield
Lyrics
Skydive
Woodworking
Pantorouter
16 inch Bandsaw
Introduction
Frame
Wheels
Wheel mounts
Blade guides
Trunions and table
Enclosure
Alignment and more
Box Joint Jig
Foldable Sawhorses
Strip Sander XL
Photography
Sony RX10 IV
Yes Minister
WinAmp
Privileges (for all non-standard users and roles)
Run this as sys or system to list all grants for users and/or roles, while filtering out all "standard" grants.
Change the where clause(s) where needed.
with users_roles_apex as ( -- all standard Apex users & roles select username from sys.dba_users where username in ('FLOWS_FILES') or username like 'APEX\_%' escape '\' --' or username like 'ORDS\_%' escape '\' --' union all select role from sys.dba_roles where role like 'APEX\_%' escape '\' --' ) , users_roles_db as ( -- all standard Oracle users & roles select username from sys.dba_users where username in ( -- List of standard users - source: http://www.morganslibrary.org/reference/users.html -- For database 12c or higher, you can simply use: where oracle_maintained = 'Y' 'ANONYMOUS','APPQOSSYS','AUDSYS','CTXSYS','DBSFWUSER','DBSNMP','DIP','DVF','DVSYS','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','LBACSYS','MDDATA' ,'MDSYS','OJVMSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','PDBADMIN','PM','REMOTE_SCHEDULER_AGENT','SH','SI_INFORMTN_SCHEMA' ,'SPATIAL_CSW_ADMIN_USR','SYS','SYS$UMF','SYSBACKUP','SYSDG','SYSKM','SYSRAC','SYSTEM','WMSYS','XDB','XS$NULL' -- plus some that were not in the list ,'HR' ) union all select role as username from sys.dba_roles where role in ( -- List of standard roles - source: http://www.morganslibrary.org/reference/roles.html -- For database 12c or higher, you can simply use: where oracle_maintained = 'Y' 'ADM_PARALLEL_EXECUTE_TASK','APPLICATION_TRACE_VIEWER','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','AUDIT_ADMIN','AUDIT_VIEWER','AUTHENTICATEDUSER' ,'CAPTURE_ADMIN','CDB_DBA','CONNECT','CSW_USR_ROLE','CTXAPP','DATAPATCH_ROLE','DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE','DBA','DBFS_ROLE' ,'DBJAVASCRIPT','DBMS_MDX_INTERNAL','DV_ACCTMGR','DV_ADMIN','DV_AUDIT_CLEANUP','DV_DATAPUMP_NETWORK_LINK','DV_GOLDENGATE_ADMIN','DV_GOLDENGATE_REDO_ACCESS' ,'DV_MONITOR','DV_OWNER','DV_PATCH_ADMIN','DV_POLICY_OWNER','DV_PUBLIC','DV_REALM_OWNER','DV_REALM_RESOURCE','DV_SECANALYST','DV_STREAMS_ADMIN' ,'DV_XSTREAM_ADMIN','EJBCLIENT','EM_EXPRESS_ALL','EM_EXPRESS_BASIC','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','GDS_CATALOG_SELECT' ,'GGSYS_ROLE','GLOBAL_AQ_USER_ROLE','GSMADMIN_ROLE','GSMUSER_ROLE','GSM_POOLADMIN_ROLE','HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_ROLE','HS_ADMIN_SELECT_ROLE' ,'IMP_FULL_DATABASE','JAVADEBUGPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY','JMXSERVER','LBAC_DBA','LOGSTDBY_ADMINISTRATOR' ,'OEM_ADVISOR','OEM_MONITOR','OLAP_DBA','OLAP_USER','OLAP_XS_ADMIN','OPTIMIZER_PROCESSING_RATE','ORDADMIN','PDB_DBA','PLUSTRACE','PROVISIONER','PUBLIC' ,'RDFCTX_ADMIN','RECOVERY_CATALOG_OWNER','RECOVERY_CATALOG_OWNER_VPD','RECOVERY_CATALOG_USER','RESOURCE','SCHEDULER_ADMIN','SELECT_CATALOG_ROLE','SODA_APP' ,'SYSUMF_ROLE','TKPROFER','WM_ADMIN_ROLE','XDBADMIN','XDB_SET_INVOKER','XDB_WEBSERVICES','XDB_WEBSERVICES_OVER_HTTP','XDB_WEBSERVICES_WITH_PUBLIC' ,'XFILES_ADMINISTRATOR','XFILES_USER','XS_CACHE_ADMIN','XS_CONNECT','XS_NAMESPACE_ADMIN','XS_SESSION_ADMIN' -- plus some that were not in the list ,'DELETE_CATALOG_ROLE' ) ) select owner , grantee , 'grant ' || lower(privilege) || ' on "' || owner || '"."' || table_name || '" to "' || grantee || '"' || decode(grantable, 'YES', ' with grant option', NULL) || ';' as sql_statement from sys.dba_tab_privs where not ( owner in ( select username from users_roles_apex ) and grantee in ( select username from users_roles_apex ) ) -- ignore "apex -> apex" and not ( owner in ( select username from users_roles_apex ) and grantee in ( select username from users_roles_db ) ) -- ignore "apex -> database" and not ( owner in ( select username from users_roles_apex ) and grantee in ( 'PUBLIC', 'ANONYMOUS' ) ) -- ignore "apex -> public/anonymous" and not ( owner in ( select username from users_roles_db ) and grantee in ( select username from users_roles_db ) ) -- ignore "database -> database" and not ( owner in ( select username from users_roles_db ) and grantee in ( select username from users_roles_apex ) ) -- ignore "database -> apex" and not ( owner in ( select username from users_roles_db ) and grantee in ( 'PUBLIC' ) ) -- ignore "database -> public" and table_name not like 'OLD\_%' escape '\' -- ' -- ignore "old" objects and table_name not like 'BIN$%' -- ignore "garbage bin" objects and not (owner = 'SYS' and table_name in (select directory_name from all_directories)) -- ignore directories and not (owner = 'SYS' and table_name like 'QT%_BUFFER') -- ignore queue tables order by owner , grantee , sql_statement
Privileges (for a user)
Run this as any user to list all (filtered) grants.
Change the where clause where needed:
select owner , grantee , 'grant ' || lower(privilege) || ' on "' || owner || '"."' || table_name || '" to "' || grantee || '"' || decode(grantable, 'YES', ' with grant option', NULL) || ';' as sql_statement from user_tab_privs where table_name not like 'OLD\_%' escape '\' --' -- ignore "old" objects and table_name not like 'BIN$%' -- ignore "garbage bin" objects and not (owner = 'SYS' and table_name like 'QT%_BUFFER') -- ignore queue tables and not (owner = 'SYS' and table_name in (select directory_name from all_directories)) -- ignore directories order by owner , grantee , sql_statement