创建用户

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.

ITPub博客

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

最新文章