Oracle SQL Plus The Definitive Guide- P26:Every day, computer professionals the world over wake up, travel to the office, sit down in front of a computer, and begin another day working with that database called Oracle. Programmers write queries and stored procedures. Database administrators monitor performance, make database changes, and perform other maintenance tasks. Operations people may need to back up or recover a database. | previous page page_224 next page Page 224 The script The following script shows you the grants that have been made on any table you own. You can also see grants made on tables owned by other users so long as those grants apply to you. The two queries you saw previously have been unioned together and the columns have been concatenated together to produce readable output. The table name and grantee are shown in the header which changes each time the grantee changes. --DESCRIPTION --This script displays information about security on a table. -- --USAGE -- @SHOW_SECURITY owner. table_name -- SET ECHO OFF SET VERIFY OFF SET FEEDBACK OFF SET PAGESIZE 9999 SET HEADING OFF --Dissect the input argument and get the owner name and --table name into two separate substitution variables. --The owner name defaults to the current user. SET TERMOUT OFF DEFINE s_owner_name DEFINE s_synonym_name COLUMN owner_name NOPRINT NEW_VALUE s_owner_name COLUMN table_name NOPRINT NEW_VALUE s_table_name SELECT DECODE INSTR 1 . 0 USER Default to current user. UPPER SUBSTR 1 1 INSTR 1 . -1 owner_name DECODE INSTR 1 . UPPER 1 Only the table name was passed in. UPPER SUBSTR 1 INSTR 1 . 1 table_name FROM dual SET TERMOUT ON COLUMN grantee NOPRINT NEW_VALUE s_grantee BREAK ON grantee SKIP PAGE TTITLE LEFT PRIVILEGES GRANTED TO s_grantee - ON s_owner_name . s_table_name --Execute a query to show privileges granted at the table level. SELECT grantee privilege DECODE grantable YES with grant option privilege FROM all_tab_privs WHERE table_schema s_owner_name AND table_name s_table_name UNION SELECT grantee previous page page_224 next page previous page page_225 next page Page 225 privilege of column column_name DECODE grantable YES with grant option privilege FROM all_col_privs WHERE table_schema s_owner_name AND table_name s_table_name ORDER BY grantee privilege --Reset everything back to its default. CLEAR COLUMNS CLEAR BREAK UNDEFINE s_owner_name UNDEFINE s_table_name SET VERIFY ON
