oracle 的用户是什么,User 用户 还是 Role 角色 - Oracle 的 Public 是什么？

在 Oracle 数据库中有一个缺省的属主对象称为 Public ，是一个神秘的存在，很多朋友搞不懂这到底是什么。是 Role ？ 还是 User ？

首先我们看 User$ 表，其中 TYPE# 标识了一个用户对象的类型，用户和角色都存储在这个表中，通过类型区分：

SQL> desc user$

Name Null? Type

----------------------------------------- -------- ----------------------------

USER# NOT NULL NUMBER

NAME NOT NULL VARCHAR2(30)

TYPE# NOT NULL NUMBER

PASSWORD VARCHAR2(30)

DATATS# NOT NULL NUMBER

TEMPTS# NOT NULL NUMBER

CTIME NOT NULL DATE

PTIME DATE

EXPTIME DATE

LTIME DATE

RESOURCE$ NOT NULL NUMBER

AUDIT$ VARCHAR2(38)

DEFROLE NOT NULL NUMBER

DEFGRP# NUMBER

DEFGRP_SEQ# NUMBER

ASTATUS NOT NULL NUMBER

LCOUNT NOT NULL NUMBER

DEFSCHCLASS VARCHAR2(30)

EXT_USERNAME VARCHAR2(4000)

SPARE1 NUMBER

SPARE2 NUMBER

SPARE3 NUMBER

SPARE4 VARCHAR2(1000)

SPARE5 VARCHAR2(1000)

SPARE6 DATE

查询可以看到，PUBLIC 的类型是 0 ，和 CONNECT 、RESOURCE 相同，属性上属于角色：

SQL> select user#,type#,name from user$;

USER#TYPE# NAME

---------- ---------- ------------------------------

0 1 SYS

1 0 PUBLIC

2 0 CONNECT

3 0 RESOURCE

4 0 DBA

5 1 SYSTEM

通过 USER$ 可以看一下字段注释，type# 的 0 类型是 Role 毫无疑问的：

create table user$ /* user table */

( user# number not null, /* user identifier number */

name varchar2("M_IDEN") not null, /* name of user */

/* 0 = role, 1 = user, 2 = adjunct schema, 3 = schema synonym */

type# number not null,

password varchar2("M_IDEN"), /* encrypted password */

datats# number not null, /* default tablespace for permanent objects */

tempts# number not null, /* default tablespace for temporary tables */

ctime date not null, /* user account creation time */

ptime date, /* password change time */

exptime date, /* actual password expiration time */

ltime date, /* time when account is locked */

resource$ number not null, /* resource profile# */

audit$ varchar2("S_OPFL"), /* user audit options */

defrole number not null, /* default role indicator: */

/* 0 = no roles, 1 = all roles granted, 2 = roles in defrole$ */

defgrp# number, /* default undo group */

defgrp_seq# number, /* global sequence number for the grp *

spare varchar2("M_IDEN"), /* reserved for future */

astatus number default 0 not null, /* status of the account */

/* 0x00 = 0 = Open */

/* 0x01 = 1 = Locked */

/* 0x02 = 2 = Expired */

/* 0x03 = 3 = Locked and Expired */

/* 0x10 = 16 = Password matches a default value */

lcount number default 0 not null, /* count of failed login attempts */

defschclass varchar2("M_IDEN"), /* initial consumer group */

ext_username varchar2("M_VCSZ"), /* external username */

/* also as base schema name for adjunct schemas */

spare1 number, /* used for schema level supp. logging: see ktscts.h */

spare5 varchar2(1000),

spare6 date

)

cluster c_user#(user#)

/

在 dsec.bsq 文件中，可以看到显式的创建 Public 角色的命令语句，所以毫无疑问，本质上 PUBLIC 是一个角色：

create role public

/

可是很多朋友又提出，通过 dba_roles 视图又无法查找到这个 PUBLIC 角色，何解？

我们看一下 DBA_ROLES 的创建语句，最后一个条件，过滤掉了两个特殊的角色 PUBLIC 和 _NEXT_USER，这是我们查询不到的原因：

create or replace view DBA_ROLES (ROLE, PASSWORD_REQUIRED, AUTHENTICATION_TYPE)

as

select name, decode(password, null, 'NO',

'EXTERNAL', 'EXTERNAL',

'GLOBAL', 'GLOBAL',

'YES'),

decode(password, null, 'NONE',

'EXTERNAL', 'EXTERNAL',

'GLOBAL', 'GLOBAL',

'APPLICATION', 'APPLICATION',

'PASSWORD')

from user$

where type# = 0 and name not in ('PUBLIC', '_NEXT_USER')

/

可以看到，Oracle 的 PUBLIC 是一个 Oracle 不以 Role 对待的特殊 ROLE 。

官方文档这样描述：

The PUBLIC role is a special role that every database user

account automatically has when the account is created.

By default, it has no privileges granted to it, but it does

have numerous grants, mostly to Java objects.

You cannot drop the PUBLIC role, and a manual grant or revoke

of this role has no meaning, because the user account will

always assume this role.

Because all database user accounts assume the PUBLIC role,

it does not appear in the DBA_ROLES and SESSION_ROLES data

dictionary views.

在很多培训或教材中，对 Public 的描述是 非用户、非角色，是对 PUBLIC 特殊性的一种解读，但是存在一定的偏差，在官方文档中首先强调的是『特殊角色』。

所以针对以下这个题目，选项 G 是正确的，其他两个大家自选：

Which three are true about privileges and roles?

A. A role is owned by the user who created it.

B. A role can contain a combination of several privileges and roles.

C. System privileges always set privileges for an entire database.

D. A user has all object privileges for every object in their schema by default.

E. All roles are owned by the SYS schema.

F. PUBLIC can be revoked from a user.

G. PUBLIC acts as a default role granted to every user in a database.