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