脚本是提高工作效率的好助手

创建用户

SQL> create user test identified by oracle
2 ;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> grant create table to test;

Grant succeeded.

SQL> grant unlimited tablespace to test;

Grant succeeded.

下面通过一个脚本查询用户的角色以及角色对应的系统或对象权限，和用户的系统或对象权限。

SQL> select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
select null grantee, username granted_role from dba_users
where username =upper('TEST')
union
select grantee, granted_role
from dba_role_privs
union
select grantee, privilege
from dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
2 3 4 5 6 7 8 9 10 11 12 13 14 15
User, his roles and privileges
--------------------------------------------------------------------------------
TEST
CONNECT                            <<<<<<角色
   CREATE SESSION
   SET CONTAINER
CREATE TABLE                   <<<<<<对象权限
RESOURCE                          <<<<<<角色
  CREATE CLUSTER
  CREATE INDEXTYPE
  CREATE OPERATOR
  CREATE PROCEDURE
  CREATE SEQUENCE
  CREATE TABLE
  CREATE TRIGGER
  CREATE TYPE
  SODA_APP
UNLIMITED TABLESPACE    <<<<<系统权限

16 rows selected.