oracle怎么建立角色,Oracle教程之Oracle管理角色（一）

约定不等于承诺〃 2022-10-21 01:11 181阅读 0赞

1、角色的功能：角色(role)是相关权限的集合，使用角色的主要目的是简化权限管理。

建立角色——给角色授权——将角色授予用户/角色

2、查看系统建立的role

09:05:09 SQL> select \* from dba\_roles;

ROLE                           PASSWORD

\------------------------------ --------

CONNECT                        NO

RESOURCE                       NO

DBA                            NO

SELECT\_CATALOG\_ROLE            NO

EXECUTE\_CATALOG\_ROLE           NO

DELETE\_CATALOG\_ROLE            NO

EXP\_FULL\_DATABASE              NO

IMP\_FULL\_DATABASE              NO

RECOVERY\_CATALOG\_OWNER         NO

GATHER\_SYSTEM\_STATISTICS       NO

LOGSTDBY\_ADMINISTRATOR         NO

AQ\_ADMINISTRATOR\_ROLE          NO

AQ\_USER\_ROLE                   NO

GLOBAL\_AQ\_USER\_ROLE            GLOBAL

SCHEDULER\_ADMIN                NO

HS\_ADMIN\_ROLE                  NO

AUTHENTICATEDUSER              NO

ROLE                           PASSWORD

\------------------------------ --------

OEM\_ADVISOR                    NO

OEM\_MONITOR                    NO

WM\_ADMIN\_ROLE                  NO

JAVAUSERPRIV                   NO

JAVAIDPRIV                     NO

JAVASYSPRIV                    NO

JAVADEBUGPRIV                  NO

EJBCLIENT                      NO

JAVA\_ADMIN                     NO

JAVA\_DEPLOY                    NO

CTXAPP                         NO

XDBADMIN                       NO

XDBWEBSERVICES                 NO

OLAP\_DBA                       NO

OLAP\_USER                      NO

MGMT\_USER                      NO

PLUSTRACE                      NO

34 rows selected.

3、建立角色( create role)

SQL> create role pub\_role;

Role created.

SQL> create role prv\_role identified by

oralce;【带口令的，一般非默认角色都应该加上口令，便于分配和管理】

Role created.

4、给角色授权

SQL> grant create session,create table to pub\_role;

Grant succeeded.

SQL> grant select on scott.emp to prv\_role;

Grant succeeded.

5、查看角色拥有的权限

——SYSTEM PRIVILEGE

SQL> select \* from role\_sys\_privs where role='&name';

Enter value for name: DBA

old   1: select \* from role\_sys\_privs where role='&name'

new   1: select \* from role\_sys\_privs where role='DBA'

ROLE                           PRIVILEGE

ADM

\------------------------------ ----------------------------------------

\---

DBA                            CREATE SESSION

YES

DBA                            ALTER SESSION

YES

DBA                            DROP TABLESPACE

YES

DBA                            BECOME USER

YES

DBA                            DROP ROLLBACK SEGMENT

YES

DBA                            SELECT ANY TABLE

YES

DBA                            INSERT ANY TABLE

YES

DBA                            UPDATE ANY TABLE

YES

...............

DBA                            READ ANY FILE GROUP

YES

DBA                            CREATE EXTERNAL JOB

YES

160 rows selected.

SQL> select \* from role\_sys\_privs where role='&name';

Enter value for name: CONNECT

old   1: select \* from role\_sys\_privs where role='&name'

new   1: select \* from role\_sys\_privs where role='CONNECT'

ROLE                           PRIVILEGE

ADM

\------------------------------ ----------------------------------------

\---

CONNECT                        CREATE SESSION

SQL> select \* from role\_sys\_privs where role='&name';

Enter value for name: RESOURCE

old   1: select \* from role\_sys\_privs where role='&name'

new   1: select \* from role\_sys\_privs where role='RESOURCE'

——隐含unlimited tablespace 权限(可以在任何一个表空间上拥有配额)

如果将该角色分配给用户，一般都会将该权限收回，在进行表空间配额的分配

ROLE                 PRIVILEGE                      ADMIN\_OPT

\-------------------- ------------------------------ ---------

RESOURCE             CREATE SEQUENCE                NO

RESOURCE             CREATE TRIGGER                 NO

RESOURCE             CREATE CLUSTER                 NO

RESOURCE             CREATE PROCEDURE               NO

RESOURCE             CREATE TYPE                    NO

RESOURCE             CREATE OPERATOR                NO

RESOURCE             CREATE TABLE                   NO

RESOURCE             CREATE INDEXTYPE               NO

8 rows selected.

SQL> select \* from role\_sys\_privs where role='&name';

Enter value for name: PUB\_ROLE

old   1: select \* from role\_sys\_privs where role='&name'

new   1: select \* from role\_sys\_privs where role='PUB\_ROLE'

ROLE                 PRIVILEGE                      ADMIN\_OPT

\-------------------- ------------------------------ ---------

PUB\_ROLE             CREATE TABLE                   NO

PUB\_ROLE             CREATE SESSION                 NO

——OBJECT PRIVILEGE

SQL> COL PRIVILEGE FOR A20

SQL> select \* from role\_tab\_privs where role='&name';

Enter value for name: PRV\_ROLE

old   1: select \* from role\_tab\_privs where role='&name'

new   1: select \* from role\_tab\_privs where role='PRV\_ROLE'

ROLE                 OWNER           TABLE\_NAME      COLUMN\_NAME

PRIVILEGE            GRANTABLE

\-------------------- --------------- --------------- ---------------

\-------------------- ---------------

PRV\_ROLE             SCOTT           EMP

SELECT               NO

oracle怎么建立角色,Oracle教程之Oracle管理角色（一）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle用户、授权、角色管理

相关 oracle查看数据库角色语句,oracle配备角色语句

相关 oracle怎么建立角色,Oracle教程之Oracle管理角色（一）

相关 oracle 权限、角色集锦

相关 Oracle之角色

相关 Oracle用户、权限、角色管理

相关 [转]Oracle角色、权限

相关 oracle-dba-事务,角色管理

相关 Oracle用户、授权、角色管理

相关 Oracle用户、权限、角色管理