Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle ADW User Tips

2019-03-20

Oracle ADW User Tips

Oracle ADW 用户相关的Tips

ADW的限制

Restrictions for Database Features

Restrictions for Database Initialization Parameters

Restrictions for SQL Commands

ADW User 用户相关的Tips

ADW创建时候时候就有admin用户,这admin用户非常重要。

即使admin密码忘记了或者lock了,可以通过adw的web 管理页面进行reset密码,并且密码强度策列比较高。

追加新的用户的话,通过admin连接之后进行创建并赋予dwrole角色给新用户

create user adwc_user001 identified by "...密码...";
grant dwrole to adwc_user001;

密码管理策列是360天需要更新,不能使用同一个密码。

dwrole角色有以下权限:

参考 -> [Manage User Privileges with Autonomous Data Warehouse](https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/manage.html#GUID-50450FAD-9769-4CF7-B0D1-EC14B465B873)

CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
ALTER SESSION
CREATE HIERARCHY
CREATE JOB
CREATE MINING MODEL
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
READ,WRITE ON directory DATA_PUMP_DIR
EXECUTE privilege on the PL/SQL package DBMS_CLOUD

一般赋予新用户可以查看(select) v$mystat / v$statname 的权限,这样可以查看sql执行时候详细的统计信息,如下:

SQL>
SELECT
		s.NAME
	   ,m.VALUE
  FROM v$mystat m, v$statname s
 WHERE m.STATISTIC# = s.STATISTIC#
   AND s.NAME IN (
		 'physical read total bytes'
		,'physical write total bytes'
		,'cell physical IO bytes eligible for predicate offload'
		,'cell physical IO interconnect bytes'
		,'physical read total IO requests'
		,'cell flash cache read hits'
		,'cell IO uncompressed bytes'
		,'cell physical IO interconnect bytes returned by smart scan'
		,'cell physical IO bytes saved by storage index'
		,'cell physical IO bytes sent directly to DB node to balanceCPU'
)
 18  ;

NAME                                                              VALUE
------------------------------------------------------------ ----------
physical read total IO requests                                    1443
physical read total bytes                                     704368640
physical write total bytes                                   3.1907E+10
cell physical IO interconnect bytes                           731907584
cell physical IO bytes eligible for predicate offload        3.1898E+10
cell physical IO bytes saved by storage index                         0
cell physical IO interconnect bytes returned by smart scan            0
cell IO uncompressed bytes                                            0
cell flash cache read hits                                         1268

9 rows selected.

SQL>

Etc.

ADW admin用户权限列表

ADW admin用户权限有以下:

SQL> show user;
USER is "ADMIN"
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
DROP ANY ANALYTIC VIEW
ALTER ANY ANALYTIC VIEW
CREATE ANY ANALYTIC VIEW
CREATE ANALYTIC VIEW
DROP ANY HIERARCHY
ALTER ANY HIERARCHY
CREATE ANY HIERARCHY
CREATE HIERARCHY
DROP ANY ATTRIBUTE DIMENSION
ALTER ANY ATTRIBUTE DIMENSION
CREATE ANY ATTRIBUTE DIMENSION
CREATE ATTRIBUTE DIMENSION
READ ANY TABLE
ALTER ANY CUBE BUILD PROCESS
SELECT ANY CUBE BUILD PROCESS
ALTER ANY MEASURE FOLDER
SELECT ANY MEASURE FOLDER
ALTER LOCKDOWN PROFILE
DROP LOCKDOWN PROFILE
CREATE LOCKDOWN PROFILE
SET CONTAINER
CREATE PLUGGABLE DATABASE
EXEMPT REDACTION POLICY
PURGE DBA_RECYCLEBIN
DROP ANY SQL TRANSLATION PROFILE
ALTER ANY SQL TRANSLATION PROFILE
CREATE ANY SQL TRANSLATION PROFILE
CREATE SQL TRANSLATION PROFILE
ADMINISTER SQL MANAGEMENT OBJECT
UPDATE ANY CUBE DIMENSION
UPDATE ANY CUBE BUILD PROCESS
DROP ANY CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
CREATE CUBE BUILD PROCESS
INSERT ANY MEASURE FOLDER
DROP ANY MEASURE FOLDER
DELETE ANY MEASURE FOLDER
CREATE ANY MEASURE FOLDER
CREATE MEASURE FOLDER
UPDATE ANY CUBE
SELECT ANY CUBE
DROP ANY CUBE
CREATE ANY CUBE
ALTER ANY CUBE
CREATE CUBE
SELECT ANY CUBE DIMENSION
INSERT ANY CUBE DIMENSION
DROP ANY CUBE DIMENSION
DELETE ANY CUBE DIMENSION
CREATE ANY CUBE DIMENSION
ALTER ANY CUBE DIMENSION
CREATE CUBE DIMENSION
COMMENT ANY MINING MODEL
ALTER ANY MINING MODEL
SELECT ANY MINING MODEL
DROP ANY MINING MODEL
CREATE ANY MINING MODEL
CREATE MINING MODEL
EXECUTE ASSEMBLY
EXECUTE ANY ASSEMBLY
DROP ANY ASSEMBLY
ALTER ANY ASSEMBLY
CREATE ANY ASSEMBLY
CREATE ASSEMBLY
ALTER ANY EDITION
DROP ANY EDITION
CREATE ANY EDITION
CHANGE NOTIFICATION
READ ANY FILE GROUP
CREATE ANY SQL PROFILE
ADMINISTER ANY SQL TUNING SET
ADMINISTER SQL TUNING SET
ALTER ANY SQL PROFILE
DROP ANY SQL PROFILE
SELECT ANY TRANSACTION
EXECUTE ANY CLASS
EXECUTE ANY PROGRAM
CREATE JOB
ADVISOR
ANALYZE ANY DICTIONARY
EXECUTE ANY RULE
DROP ANY RULE
ALTER ANY RULE
CREATE ANY RULE
CREATE RULE
EXECUTE ANY RULE SET
DROP ANY RULE SET
ALTER ANY RULE SET
CREATE ANY RULE SET
CREATE RULE SET
EXECUTE ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
CREATE EVALUATION CONTEXT
GRANT ANY OBJECT PRIVILEGE
DEBUG ANY PROCEDURE
DEBUG CONNECT ANY
DEBUG CONNECT SESSION
SELECT ANY DICTIONARY
RESUMABLE
ON COMMIT REFRESH
MERGE ANY VIEW
ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER
DROP ANY OUTLINE
ALTER ANY OUTLINE
CREATE ANY OUTLINE
DROP ANY CONTEXT
CREATE ANY CONTEXT
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
MANAGE ANY QUEUE
DROP ANY DIMENSION
ALTER ANY DIMENSION
CREATE ANY DIMENSION
CREATE DIMENSION
UNDER ANY TABLE
EXECUTE ANY INDEXTYPE
GLOBAL QUERY REWRITE
QUERY REWRITE
UNDER ANY VIEW
DROP ANY INDEXTYPE
ALTER ANY INDEXTYPE
CREATE ANY INDEXTYPE
CREATE INDEXTYPE
EXECUTE ANY OPERATOR
DROP ANY OPERATOR
ALTER ANY OPERATOR
CREATE ANY OPERATOR
CREATE OPERATOR
EXECUTE ANY LIBRARY
DROP ANY LIBRARY
ALTER ANY LIBRARY
UNDER ANY TYPE
EXECUTE ANY TYPE
DROP ANY TYPE
ALTER ANY TYPE
CREATE ANY TYPE
CREATE TYPE
DROP ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
ANALYZE ANY
ALTER RESOURCE COST
DROP PROFILE
ALTER PROFILE
CREATE PROFILE
DROP ANY TRIGGER
ALTER ANY TRIGGER
CREATE ANY TRIGGER
CREATE TRIGGER
EXECUTE ANY PROCEDURE
DROP ANY PROCEDURE
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
CREATE PROCEDURE
FORCE ANY TRANSACTION
ALTER DATABASE
AUDIT ANY
ALTER ANY ROLE
DROP ANY ROLE
CREATE ROLE
DROP PUBLIC DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREATE DATABASE LINK
SELECT ANY SEQUENCE
DROP ANY SEQUENCE
ALTER ANY SEQUENCE
CREATE ANY SEQUENCE
CREATE SEQUENCE
DROP ANY VIEW
CREATE ANY VIEW
CREATE VIEW
DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
DROP ANY SYNONYM
CREATE ANY SYNONYM
CREATE SYNONYM
DROP ANY INDEX
ALTER ANY INDEX
CREATE ANY INDEX
DROP ANY CLUSTER
ALTER ANY CLUSTER
CREATE ANY CLUSTER
CREATE CLUSTER
REDEFINE ANY TABLE
DELETE ANY TABLE
UPDATE ANY TABLE
INSERT ANY TABLE
SELECT ANY TABLE
COMMENT ANY TABLE
LOCK ANY TABLE
DROP ANY TABLE
ALTER ANY TABLE
CREATE ANY TABLE
CREATE TABLE
DROP ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT
DROP USER
ALTER USER
BECOME USER
CREATE USER
UNLIMITED TABLESPACE
DROP TABLESPACE
MANAGE TABLESPACE
ALTER TABLESPACE
CREATE TABLESPACE
RESTRICTED SESSION
ALTER SESSION
CREATE SESSION
AUDIT SYSTEM
ALTER SYSTEM

215 rows selected.

SQL>

Have a good work&life! 2019/03 via LinHong


Similar Posts

Comments