set echo off feedback off verify off pagesize 0 linesize 120

define v_grantee=&1

define v_grant_sel_command_file = .\log\grant_sel_&v_grantee..sql
define v_grant_sel_log_file     = .\log\grant_sel_&v_grantee..log

spool &v_grant_sel_command_file.
prompt spool &v_grant_sel_log_file.
prompt set echo on feedback on
prompt show user

---- Give the original authority to the user ,no exsists ( select null On (select *) almost )  Namely where If the subquery does not return rows , be ---- Then meet NOT EXISTS Medium ----WHERE Clause , The purpose should be to check whether the permissions of the table are lost .

---- Traverse according to the permissions of each table

select
  'grant select on ' || t.table_name || ' to &v_grantee with grant option;'
from     user_tables t
where not exists
  (select null
   from   user_tab_privs p
   where  p.owner      = user
   and    p.table_name = t.table_name
   and    p.grantee    = upper('&v_grantee'))
and user != upper('&v_grantee')
order by t.table_name
/

--

select
  'grant select on ' || v.view_name  || ' to &v_grantee with grant option;'
from     user_views v
where not exists
  (select null
   from   user_tab_privs p
   where  p.owner      = user
   and    p.table_name = v.view_name
   and    p.grantee    = upper('&v_grantee'))
and user != upper('&v_grantee')
order by v.view_name
/
select
  'grant execute on ' || o.object_name || ' to &v_grantee;'
from     user_objects o
where object_type in ('PACKAGE')
and   not exists
  (select null
   from   user_tab_privs p
   where  p.owner      = user
   and    p.table_name = o.object_name
   and    p.grantee    = upper('&v_grantee'))
and user != upper('&v_grantee')
order by o.object_name
/

prompt set echo off feedback off
prompt spool off
spool off

@&v_grant_sel_command_file.

Supplementary test instructions :

data user: for ddl usr

user: for app dml/select ( A synonym for )

patch user: for app supprot user ( A synonym for )

query    : for app supprot user   ( A synonym for )

##

step 1:

Check   data user Whether the table of is given permission usr user.

Variable is : dbUSR

select *    from   user_tab_privs p    where  p.owner      = user    and    p.grantee    = upper('&v_grantee'))    and user != upper('&v_grantee')

step 2.1: Test cancelled data user The table of update/select jurisdiction

revoke update on testfrom dbUSR; revoke  select  on testfrom dbUSR;

step 3.1: The test script 01_schema_rollout.sql Can you put the table of update/select jurisdiction Reauthorize

test 01_schema_rollout.sql result : Cannot be 2 Permissions Reauthorize

step 2.2: Test cancelled data user The table of delete/insert jurisdiction

revoke insert on testfrom dbUSR;

revoke delete on testfrom dbUSR;

step 3.2: Test whether the script can convert the table of delete/insert/update/select jurisdiction Reauthorize

test 01_schema_rollout.sql result : Sure

step  2.3 Delete a table . Then use the backup table (.sql) File recovery , It is feasible. .(.sql The file contains Authorization grant command )

step 3.3 A synonym for Status is invalide, You can ignore .

Here's why : First, create a usable synonym , Then delete the table corresponding to the synonym ,dba_objects The corresponding state is INVALID 了   And then when you go back select When it comes to this synonym ,status It will become VALID.

gen_grant_sel.sql More articles about

  1. call_grant_sel.sql

    set echo offpromptprompt =========================================================================== ...

  2. Based on SQL Server AlwaysOn Cross machine room switching project

    Based on SQL Server AlwaysOn Cross machine room switching project Recently, a customer from Chongqing found walking Qijun , Customer's business is mobile Internet payment , Wechat payment and receipt channel partner , The database stores payment flow and transaction flow ...

  3. SQL Server File group backup and restore for big data relocation

    One . The contents of this article (Contents) The contents of this article (Contents) background (Contexts) Solution (Solution) Relocation steps (Procedure) Relocation script (SQL Codes) ...

  4. Sql Server series : Partition table operation

    1. Introduction to partition table A partitioned table is logically a table , And physically, it's multiple tables . From the user's perspective , Partitioned tables are the same as regular tables . The main purpose of using partitioned tables is to improve the scalability and manageability of large tables and tables with multiple access modes . Partition table is to set data by ...

  5. SQL Server High availability in (2)---- Files and file groups

        Talking about SQL Server Before the high availability of , Let's first talk about the high availability of single instance . In single instance high availability , What can't be ignored is the high availability of files and filegroups .SQL Server Allow some files to be corrupted or offline , Permit ...

  6. EntityFramework Core Raw SQL

    Preface Let's talk about EF Core The original query in , At present, for simple queries in the project, we can directly use EF You can solve it , But when it comes to multi table query, in order to reach the goal in one step, the original query method is used . Let's take a look . EntityFram ...

  7. from 0 Begin to build SQL Server AlwaysOn Chapter one ( Configure domain control )

    from 0 Begin to build SQL Server AlwaysOn  Chapter one ( Configure domain control ) Chapter one http://www.cnblogs.com/lyhabc/p/4678330.html Second articles http://www.cnb ...

  8. from 0 Begin to build SQL Server AlwaysOn Second articles ( Configure the failover cluster )

    from 0 Begin to build SQL Server AlwaysOn Second articles ( Configure the failover cluster ) Chapter one http://www.cnblogs.com/lyhabc/p/4678330.html Second articles http://www ...

  9. from 0 Begin to build SQL Server AlwaysOn Third articles ( To configure AlwaysOn)

    from 0 Begin to build SQL Server AlwaysOn Third articles ( To configure AlwaysOn) Chapter one http://www.cnblogs.com/lyhabc/p/4678330.html Second articles http://w ...

Random recommendation

  1. VB in NumericUpDown Control How to set trigger events for manual input

    Private Sub numDuration_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) ...

  2. LightOJ 1094 - Farthest Nodes in a Tree( The diameter of the tree )

    http://acm.hust.edu.cn/vjudge/contest/121398#problem/H Not particularly understanding , It's the first time I've had this kind of problem today . Give a link to see the explanation of the great God http://www.cnb ...

  3. CCNA A selection of examples

    3. Refer to the exhibit. Whichtwo statements are true about interVLAN routing in the topology that i ...

  4. python Picture crawler

    import re import urllib import os def rename(name): name = name + '.jpg' return name def getHtml(url ...

  5. Handmade year Java The old A Sales volume

    Java The old A This book has been written for a long time , It finally started yesterday china-pub. JD.COM . Cats on sale on the day of the event , Now? , It's called buying and selling for short , Of course he hasn't arrived yet . Interested people can go to see ha ( Maybe the addresses of other sites will also be disclosed here ): china-p ...

  6. 5_jQuery Selectors

    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN""http://www.w3.org/TR/html4/stric ...

  7. Nginx In event handling connection and read、write The connection of events

    /*********************************************************************  * Author  : Samson  * Date   ...

  8. meta Tag book ( commend )

    html Of meta summary ( Commonly used ) 1.Meta Tag book <!-- Declare the character encoding used by the document --> <meta charset='utf-8'> <!-- priority of use IE most ...

  9. Small procedures under the scope of how to call the global js

    Local wxml file <view>app edition :{{version}}</view> Local js file var app; Page({data:{ }, onLoad:function( ...

  10. BZOJ 1415 Congcong and coco ( expect DP)

    We can use n Time BFS Preprocessing to[][] Array ,to[i][j] It means congcongcong i Point to j Which point will the first step take . So for Congcong in i spot , Coco in j spot , Cong Cong goes first , Definition dp[i][j] Represents the number of steps expected . So obviously there is dp[i ...