0、收回权限
SELECT 'REVOKE '||PRIVILEGE||' FROM BAIPX;' FROM dba_sys_privs WHERE GRANTEE='BAIPX';
1、收回dba权限
revoke dba from BAIPX;
2、创建角色并授权相关权限
create role BAIPX_ROLE;
grant select on v_$sysstat to BAIPX_ROLE;
grant delete any table to BAIPX_ROLE;
grant insert any table to BAIPX_ROLE;
grant select any table to BAIPX_ROLE;
grant unlimited tablespace to BAIPX_ROLE;
grant execute any procedure to BAIPX_ROLE;
grant update any table to BAIPX_ROLE;
grant SELECT ANY TABLE to BAIPX_ROLE;
grant CREATE VIEW to BAIPX_ROLE;
grant ALTER SESSION to BAIPX_ROLE;
grant DROP PUBLIC SYNONYM to BAIPX_ROLE;
grant CREATE DATABASE LINK to BAIPX_ROLE;
grant CREATE ANY DIRECTORY to BAIPX_ROLE;
grant SELECT ANY DICTIONARY to BAIPX_ROLE;
grant CREATE SYNONYM to BAIPX_ROLE;
grant CREATE PUBLIC SYNONYM to BAIPX_ROLE;
grant CREATE TYPE to BAIPX_ROLE;
grant CREATE LIBRARY to BAIPX_ROLE;
grant CREATE PUBLIC DATABASE LINK to BAIPX_ROLE;
grant CREATE TRIGGER to BAIPX_ROLE;
grant DEBUG CONNECT SESSION to BAIPX_ROLE;
grant CREATE TABLE to BAIPX_ROLE;
grant CREATE CLUSTER to BAIPX_ROLE;
grant CREATE SESSION to BAIPX_ROLE;
grant CREATE SEQUENCE to BAIPX_ROLE;
grant DROP PUBLIC DATABASE LINK to BAIPX_ROLE;
grant CREATE PROCEDURE to BAIPX_ROLE;
3、把角色授予给用户
grant BAIPX_ROLE to BAIPX;
grant connect, resource to BAIPX;
grant unlimited tablespace to BAIPX;
4、查看用户权限
###查询某用户的被赋予的系统权限
select * from dba_sys_privs where GRANTEE='BAIPX';
###查询某用户被赋予的角色权限
select * from dba_role_privs where GRANTEE='BAIPX';
###查询角色内包含的权限
select * from role_sys_privs where role='BAIPX';
授予相同权限
select table_name from dict where table_name like '%PRIVS%';
select 'grant '|| PRIVILEGE|| ' ' || ' on ' || OWNER||'.'|| TABLE_NAME||' TO ' || GRANTEE|| ';'from dba_tab_privs where grantee='WSCRM';
select 'grant '|| GRANTED_ROLE || ' to ' || GRANTEE||';' from dba_role_privs where grantee='WSCRM';
select 'grant '|| privilege|| ' to ' || grantee||';' from dba_sys_privs where grantee='WSCRM';