根据某个dba用户，创建一个新用户,并且新用户仅支持登录和查询,没有其它权限

2020-02-24 1703 星期一 根据某个dba用户，创建一个新用户,并且新用户仅支持登录和查询,没有其它权限

1.查看存储过程或者function不通过的原因

select sysdate,t.* from SYS.USER_ERRORS t; --查看存储过程或者function不通过的原因

2.创建用户

--–创建用户,ok,2020-02-24 1411
create user c##abcd_prod_query 
identified by abcfddww445555;
--------授予查询任何表,ok,2020-02-24 1412
grant select any table to c##abcd_prod_query ; 
----授予查询任何字典,ok,2020-02-24 1413
grant select any dictionary to c##abcd_prod_query ;
---授予zhangsan用户创建session的权限，即登陆权限,2020-02-24 1416 星期一,ok
grant create session to c##abcd_prod_query  ;
grant connect to c##abcd_prod_query  ;--2020-02-24 1425 星期一,ok

3.授权日志表

-- Create table
create table GRANT_USE_LOG
(
  ID          VARCHAR2(32),
  USER_NAME   VARCHAR2(50),
  DBA_USER    VARCHAR2(50),
  QUERY_USER  VARCHAR2(50),
  BATCH_ID    VARCHAR2(50),
  SQL         VARCHAR2(500),
  NUM         NUMBER,
  CREATE_DATE DATE
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Add comments to the table 
comment on table GRANT_USE_LOG
  is '根据dab用户-创建用户授权-仅查询-20200224';
-- Add comments to the columns 
comment on column GRANT_USE_LOG.ID
  is '主键唯一';
comment on column GRANT_USE_LOG.USER_NAME
  is '操作数据的用户';
comment on column GRANT_USE_LOG.DBA_USER
  is '具有dba权限的用户';
comment on column GRANT_USE_LOG.QUERY_USER
  is '仅查询的用户';
comment on column GRANT_USE_LOG.BATCH_ID
  is '批次号';
comment on column GRANT_USE_LOG.SQL
  is '执行sql';
comment on column GRANT_USE_LOG.NUM
  is '序号';
comment on column GRANT_USE_LOG.CREATE_DATE
  is '创建时间';

3 . 给新建的用户，授权仅查询，不能新增/修改/删除数据，不能创建表，不能删除表

create or replace procedure grant_user_only_query(
p_user_name     IN VARCHAR2,/*入参:当前操作的用户*/
p_user_dba      IN VARCHAR2,/*入参:dba用户*/
p_user_query    IN VARCHAR2,/*入参:仅查询功能用户*/
p_is_debug      IN VARCHAR2,/*入参:是否开始debug输出中间环节Y/N不区分大小写,传空不输出*/
p_result_code   OUT number ,/*出参:返回码：0-成功 1-失败*/
p_result_msg    OUT varchar2 /*出参:返回错误信息*/
)as
/*
select sysdate,t.* from SYS.USER_ERRORS t; 查看存储过程或者function不通过的原因
2020-02-24 1510 星期一  oracle用户库中创建仅有查询权限的用户-仅查询
2020-02-24 1830 星期一 问题
[Error Code:-20000].SQLERRM:ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes
解决方案：
在plsql中编辑存储过程时，存储过程中的begin…end中，begin后面直接加上DBMS_OUTPUT.ENABLE(buffer_size => null) ，表示输出buffer不受限制。
或者在调用存储过程时，begin后面加上DBMS_OUTPUT.ENABLE(buffer_size => null) ，本次执行任务输出buffer不受限制。
————————————————
版权声明：本文为CSDN博主「黄宝康」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/huangbaokang/article/details/92593502
*/
v_bacth_id      varchar2(32)       := '';/*批次号*/
v_sqm           varchar2(10)       := '''';/*单引号Single quotation mark*/
v_sysstr        varchar2(32767)    := '';/*系统串*/
v_sysdate_log   varchar2(100)      := '';/*系统时间:日志*/
v_dfm_log       varchar2(100)      := 'yyyy-mm-dd hh24:mi:ss.ff';/*系统时间v_dataformate*/
v_dfm_data      varchar2(100)      := 'yyyy-mm-dd hh24:mi:ss';/*系统时间v_dataformate*/
v_str           varchar2(32767)    := '';/*变量*/
v_sql           varchar2(32767)    := '';/*SQL语句变量*/
v_sql2          varchar2(32767)    := '';/*SQL语句变量*/
v_is_same       varchar2(10)       := 'N';/*是否找到用户：默认N没有找到*/
v_num           number             := 0;/*计数*/
v_split_line    varchar2(100)      := '***********分割线****************';
cursor query_user is select distinct t.OWNER from all_tab_comments t;/*查询所有用户的表,视图等。去掉，查所有的用户*/
/* 标准游标sql
cursor query_table is select t.table_name from all_tab_comments t  where t.owner = 'C##DBA' and t.table_name not like 'BIN$%'
*/
cursor query_table is select t.table_name from all_tab_comments t  where t.owner = p_user_dba and t.table_name not like 'BIN$%';
begin
  DBMS_OUTPUT.ENABLE(buffer_size => null);
      p_result_code  := 0;
      p_result_msg   := '';
     /********************************************************************/
     v_sql := 'select sys_guid() from dual';
     dbms_output.put_line('查询系统uuid,sql = ' || v_sql);
     execute immediate v_sql into v_bacth_id;
     /********************************************************************/
      v_sql := 'select to_char(systimestamp,' ||v_sqm|| v_dfm_log ||v_sqm|| ') from dual';
      dbms_output.put_line('查询系统时间sql = ' || v_sql);
      execute immediate v_sql into v_sysdate_log;
      v_sysstr  := '当前系统时间为:[' || v_sysdate_log                   || ']'
                               ||',DBA用户:[' || p_user_dba              || ']'
                               ||',仅查询用户:[' || p_user_query         || ']'
                               ||',操作人['||p_user_name                 || ']';
      dbms_output.put_line(v_split_line);
      /********************************************************************/
      /*******************游标for循环开始*****************************************/
       dbms_output.put_line('查找用户,begin,....................');
      for temp in query_user loop --temp为临时变量名,自己任意起
        v_num := v_num + 1;
          if (p_is_debug is not null and p_is_debug = 'Y') then
	           dbms_output.put_line(v_num || ',查询用户:' || temp.owner); --输出某个字段,使用"变量名.列名"即可。
          end if;
          if temp.owner = upper(p_user_dba) then
	           dbms_output.put_line(v_num  || ',找到一样的,库:[' || temp.owner || '],传入:[' || p_user_dba || ']'); --输出某个字段,使用"变量名.列名"即可。
             v_is_same := 'Y';
          end if;
       end loop;
       dbms_output.put_line('查找用户,end,....................');
     /*******************游标for循环结束*****************************************/
     dbms_output.put_line('v_is_same :' || v_is_same);
      dbms_output.put_line(v_split_line);
      v_num :=0;/*重置计数*/
     if v_is_same = 'Y' then
        dbms_output.put_line('权限用户,begin,....................');
          /*******************游标for循环开始*****************************************/
         for temp2 in query_table loop --temp为临时变量名,自己任意起
           v_num := v_num + 1;
            if (p_is_debug is not null and p_is_debug = 'Y') then
               dbms_output.put_line(temp2.table_name); --输出某个字段,使用"变量名.列名"即可。
            end if;
             /*grant select,insert,update,delete,all on 表名 to 用户名 生成很多条这个*/
             v_sql  := 'grant select on ' || temp2.table_name ||' to ' || p_user_query;
             v_sql2 := 'insert into grant_use_log (id,user_name,dba_user,query_user,batch_id,sql,num,create_date)values (
                    sys_guid(),'
                    ||v_sqm||  p_user_name        ||v_sqm||','
                    ||v_sqm||  p_user_dba         ||v_sqm||','
                    ||v_sqm||  p_user_query       ||v_sqm||','
                    ||v_sqm||  v_bacth_id         ||v_sqm||','
                    ||v_sqm||  v_sql              ||v_sqm||','
                    ||v_num||','
                    ||'sysdate)';
             execute immediate v_sql;/*执行权限*/
              dbms_output.put_line('第' || v_num || ',执行权限,' || v_sql);
             execute immediate v_sql2;/*插入log*/
             dbms_output.put_line('第' || v_num || ',插入log,' || v_sql2);
          end loop;
        dbms_output.put_line('权限用户,end,....................');
	   else
        p_result_code := 1;
        p_result_msg  := v_sysstr || ',没有找到用户';
        dbms_output.put_line(p_result_msg); --输出某个字段,使用"变量名.列名"即可。
     end if;
     /***********************************************************************************/
     commit;
EXCEPTION
      WHEN OTHERS THEN
      ROLLBACK;
      /*释放资源,如游标,临时表;回滚事务*/
      p_result_code := 1; /*传递给ETL_TOOL非0错误代码*/
      p_result_msg  := '[Error Code:' || to_char(SQLCODE) || '].SQLERRM:' || SQLERRM;
end grant_user_only_query;