oracle查询用户的概要文件,Oracle用户、概要文件、权限及角色实例

用户、概要文件、权限及角色实例

1、创建角色change

SQL> create role  change identified by zhubajie;

Role created

SQL> select * from dba_roles where role='CHANGE';

ROLE                           PASSWORD_REQUIRED AUTHENTICATION_TYPE

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

CHANGE                         YES               PASSWORD

2、给角色赋权select any table

SQL> grant select any table to change;

Grant succeeded

SQL> select * from role_sys_privs where role='CHANGE';

ROLE                           PRIVILEGE            ADMIN_OPTION

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

CHANGE                         SELECT ANY TABLE     NO

3、创建用户sudaji

SQL> create user sudaji

2  identified by sudaji

3  default tablespace lianxi

4  temporary tablespace lianxi_temp

5   quota 38M on lianxi

6    quota 28M on lianxi_index;

User created

4、给用户sudaji授权

SQL> grant connect,resource,change to sudaji;

Grant succeeded

5、给用户sudaji默认角色

SQL> alter user sudaji default role all except change;

User altered

6、查看sudaji用户信息

select * from dba_users where username='SUDAJI';

7、查看用户sudaji使用表空间限制

SQL> select username,tablespace_name,BYTES/1024/1024 MB,

2  MAX_BYTES/1024/1024 MAX_BYTES

3   from dba_ts_quotas where username='SUDAJI';

USERNAME                       TABLESPACE_NAME                        MB  MAX_BYTES

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

SUDAJI                         LIANXI                                  0         38

SUDAJI                         LIANXI_INDEX                            0         28

8、新建4个用户

SQL> create user tangceng identified by tangceng default tablespace lianxi temporary

tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;

User created

SQL> create user sunwukong identified by sunwukong default tablespace lianxi temporary

tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;

User created

SQL> create user zhubajie identified by zhubajie default tablespace lianxi temporary

tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;

User created

SQL> create user shaheshang identified by shaheshang default tablespace lianxi temporary

tablespace lianxi_temp quota 38M on lianxi quota 28M on lianxi_index;

User created

9、给新建4个用户授权

SQL> grant connect,resource,change to tangceng,sunwukong,zhubajie,shaheshang;

Grant succeeded

10、新建的4个用户默认角色

SQL> alter user tangceng default role all except change;

User altered

SQL> alter user sunwukong default role all except change;

User altered

SQL> alter user zhubajie default role all except change;

User altered

SQL> alter user shaheshang default role all except change;

User altered

11、查看新建用户的表空间信息

SQL> select username,default_tablespace,temporary_tablespace,created,profile

2  from dba_users where default_tablespace like 'LIAN%';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED     PROFILE

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

ZHUBAJIE                       LIANXI                         LIANXI_TEMP                    2017-9-25 1 DEFAULT

SUDAJI                         LIANXI                         LIANXI_TEMP                    2017-9-25 1 DEFAULT

SHAHESHANG                     LIANXI                         LIANXI_TEMP                    2017-9-25 1 DEFAULT

TANGCENG                       LIANXI                         LIANXI_TEMP                    2017-9-25 1 DEFAULT

SUNWUKONG                      LIANXI                         LIANXI_TEMP                    2017-9-25 1 DEFAULT

CAT                            LIANXI                         LIANXI_TEMP                    2017-9-22 1 DEFAULT

6 rows selected

SQL> select username,tablespace_name,BYTES/1024/1024 MB,

2    MAX_BYTES/1024/1024 MAX_BYTES

3  from dba_ts_quotas where tablespace_name like 'LIAN%';

USERNAME                       TABLESPACE_NAME                        MB  MAX_BYTES

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

CAT                            LIANXI                                  0         50

TANGCENG                       LIANXI_INDEX                            0         28

SHAHESHANG                     LIANXI                                  0         38

SUNWUKONG                      LIANXI_INDEX                            0         28

SUNWUKONG                      LIANXI                                  0         38

ZHUBAJIE                       LIANXI_INDEX                            0         28

ZHUBAJIE                       LIANXI                                  0         38

SUDAJI                         LIANXI_INDEX                            0         28

SHAHESHANG                     LIANXI_INDEX                            0         28

TANGCENG                       LIANXI                                  0         38

SUDAJI                         LIANXI                                  0         38

11 rows selected

12、查询权限及角色

SQL> select * from dba_role_privs where grantee in ('TANGCENG','SUNWUKONG','ZHUBAJIE','SHAHESHANG');

GRANTEE         GRANTED_ROLE    ADMIN_OPTION DEFAULT_ROLE

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

ZHUBAJIE        CONNECT         NO           YES

TANGCENG        CONNECT         NO           YES

SUNWUKONG       CONNECT         NO           YES

SHAHESHANG      CONNECT         NO           YES

TANGCENG        RESOURCE        NO           YES

SUNWUKONG       RESOURCE        NO           YES

ZHUBAJIE        CHANGE          NO           NO

SUNWUKONG       CHANGE          NO           NO

TANGCENG        CHANGE          NO           NO

SHAHESHANG      CHANGE          NO           NO

ZHUBAJIE        RESOURCE        NO           YES

SHAHESHANG      RESOURCE        NO           YES

12 rows selected