分类目录归档：数据库

Oracle read only用户—23ai新特性:只读用户

发表于 2025 年 1 月 11 日由惜分飞

23ai版本支持用户级别设置read only特性,对于在某些情况下,为了数据的一致性,是一个比较方便的特性,而不是以前版本通过权限控制实现只读,比如授权create session+表/视图等查询权限
下面创建一个用户u_readonly,并授权dba权限,创建一个表进行测试

[oracle@xifenfei ~]$ ss

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Jan 11 21:12:09 2025
Version 23.5.0.24.07

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07

SQL> 
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud an
d Engineered Systems


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XIFENFEI                       MOUNTED
SQL> alter session set container=xifenfei;

Session altered.

SQL> alter database open;

Database altered.

SQL> create user u_readonly identified by oracle;

User created.

SQL> grant dba to u_readonly;

Grant succeeded.

SQL>  conn u_readonly/oracle@127.0.0.1/xifenfei
Connected.
SQL> create table t_xff as select * from dba_objects;

Table created.

SQL> select count(1) from t_xff;

  COUNT(1)
----------
     70951

修改用户为只读特性,然后进行dml/ddl操作会报ORA-28194: Can perform read operations only

SQL> conn / as sysdba
Connected.
SQL>  alter session set container=xifenfei;

Session altered.

SQL> alter user u_readonly read only;

User altered.

SQL> conn u_readonly/oracle@127.0.0.1/xifenfei
Connected.
SQL> delete from t_xff;
delete from t_xff
            *
ERROR at line 1:
ORA-28194: Can perform read operations only


SQL> insert into t_xff select * from dba_objects;
insert into t_xff select * from dba_objects
            *
ERROR at line 1:
ORA-28194: Can perform read operations only


SQL> select count(1) from t_xff;

  COUNT(1)
----------
     70951

SQL> create table t1 as select * from dba_users;
create table t1 as select * from dba_users
*
ERROR at line 1:
ORA-28194: Can perform read operations only

直接使用create user命令创建一个只读用户

SQL>  conn / as sysdba
Connected.
SQL> alter session set container=xifenfei;

Session altered.

SQL> create user u_readonly2 identified by oracle read only;

User created.

SQL> grant dba to u_readonly2;

Grant succeeded.

SQL>  conn u_readonly2/oracle@127.0.0.1/xifenfei
Connected.
SQL> create table t_xifenfei as select * from dba_objects;
create table t_xifenfei as select * from dba_objects
*
ERROR at line 1:
ORA-28194: Can perform read operations only

修改只读用户为读写模式

SQL> conn / as sysdba
Connected.
SQL>  alter session set container=xifenfei;

Session altered.

SQL> alter user u_readonly2 read write;

User altered.

SQL> conn u_readonly2/oracle@127.0.0.1/xifenfei
Connected.
SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> delete from t_xifenfei where rownum<100;

99 rows deleted.

SQL> commit;

Commit complete.

查看用户是否处于只读状态

SQL> select username,read_only from dba_users  where created>sysdate-1;

USERNAME                       READ_O
------------------------------ ------
U_READONLY2                    NO
U_READONLY                     YES

在只读用户中，使用动态plsql直接直接dml也报ORA-28194: Can perform read operations only

SQL> conn u_readonly/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from t_xff;

  COUNT(1)
----------
     70951

SQL> delete from t_xff;
delete from t_xff
            *
ERROR at line 1:
ORA-28194: Can perform read operations only


SQL> DECLARE   
  2      v_sql VARCHAR2(1000);
  3  BEGIN
  4      v_sql := 'delete from t_xff where rownum<1000';
  5      EXECUTE IMMEDIATE v_sql;
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-28194: Can perform read operations only
ORA-06512: at line 5

判断用户是否只读的底层基表属性user$.spare1

SQL> conn / as sysdba
Connected.
SQL> alter session set container=xifenfei;

Session altered.
SQL> COL NAME FOR A30
SQL>  select name,decode(bitand(spare1, 67108864), 67108864, 'YES', 'NO')
  2   read_only from user$ where name like 'U_READONLY%'
  3  /

NAME                           READ_O
------------------------------ ------
U_READONLY                     YES
U_READONLY2                    NO

迁移awr快照数据到自定义表空间

发表于 2025 年 1 月 9 日由惜分飞

在19c中有些情况,考虑把awr的快照数据存储在非sysaux表空间,可以通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS来进行设置

sys@ORA19C 21:57:02> select BANNER_FULL from v$version;

BANNER_FULL
----------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0


Elapsed: 00:00:00.01

PROCEDURE MODIFY_SNAPSHOT_SETTINGS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RETENTION                      NUMBER                  IN     DEFAULT
 INTERVAL                       NUMBER                  IN     DEFAULT
 TOPNSQL                        NUMBER                  IN     DEFAULT
 DBID                           NUMBER                  IN     DEFAULT
 TABLESPACE_NAME                VARCHAR2                IN     DEFAULT
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RETENTION                      NUMBER                  IN     DEFAULT
 INTERVAL                       NUMBER                  IN     DEFAULT
 TOPNSQL                        VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT
 TABLESPACE_NAME                VARCHAR2                IN     DEFAULT

这两个proc,主要是TOPNSQL一个是number类型,一个是varchar2类型
If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting.
If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.
进行了简单的测试,确认是部分awr的分区表设置到新表空间中

sys@ORA19C 21:41:51> CREATE TABLESPACE AWRTBS DATAFILE '/data/oradata/ORA19C/awrtbs01.dbf' size 128M autoextend on;

Tablespace created.

Elapsed: 00:00:00.53
sys@ORA19C 21:42:21> exec dbms_workload_repository.modify_snapshot_settings(tablespace_name=> 'AWRTBS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.53

sys@ORA19C 21:53:56> execute dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.44
sys@ORA19C 21:53:58> select segment_name,PARTITION_NAME,segment_type from dba_segments where tablespace_name='AWRTBS';

SEGMENT_NAME                   PARTITION_NAME                                               SEGMENT_TYPE
------------------------------ ------------------------------------------------------------ ---------------
WRH$_FILESTATXS                WRH$_FILESTATXS_1232450071_2690                              TABLE PARTITION
WRH$_SQLSTAT                   WRH$_SQLSTAT_1232450071_2690                                 TABLE PARTITION
WRH$_SYSTEM_EVENT              WRH$_SYSTEM_EVENT_1232450071_2690                            TABLE PARTITION
WRH$_WAITSTAT                  WRH$_WAITSTAT_1232450071_2690                                TABLE PARTITION
WRH$_LATCH                     WRH$_LATCH_1232450071_2690                                   TABLE PARTITION
WRH$_LATCH_MISSES_SUMMARY      WRH$_LATCH_MISSES_SUMMARY_1232450071_2690                    TABLE PARTITION
WRH$_DB_CACHE_ADVICE           WRH$_DB_CACHE_ADVICE_1232450071_2690                         TABLE PARTITION
WRH$_ROWCACHE_SUMMARY          WRH$_ROWCACHE_SUMMARY_1232450071_2690                        TABLE PARTITION
WRH$_SGASTAT                   WRH$_SGASTAT_1232450071_2690                                 TABLE PARTITION
WRH$_SYSSTAT                   WRH$_SYSSTAT_1232450071_2690                                 TABLE PARTITION
WRH$_PARAMETER                 WRH$_PARAMETER_1232450071_2690                               TABLE PARTITION
WRH$_SEG_STAT                  WRH$_SEG_STAT_1232450071_2690                                TABLE PARTITION
WRH$_SERVICE_STAT              WRH$_SERVICE_STAT_1232450071_2690                            TABLE PARTITION
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SESSION_HISTORY_1232450071_2690                  TABLE PARTITION
WRH$_SYSMETRIC_HISTORY         WRH$_SYSMETRIC_HISTORY_1232450071_2690                       TABLE PARTITION
WRH$_LATCH_CHILDREN            WRH$_LATCH_CHILDREN_1232450071_0                             TABLE PARTITION
WRH$_LATCH_PARENT              WRH$_LATCH_PARENT_1232450071_0                               TABLE PARTITION
WRH$_DLM_MISC                  WRH$_DLM_MISC_1232450071_0                                   TABLE PARTITION
WRH$_INST_CACHE_TRANSFER       WRH$_INST_CACHE_TRANSFER_1232450071_0                        TABLE PARTITION
WRH$_INTERCONNECT_PINGS        WRH$_INTERCONNECT_PINGS_1232450071_0                         TABLE PARTITION
WRH$_TABLESPACE_STAT           WRH$_TABLESPACE_STAT_1232450071_2690                         TABLE PARTITION
WRH$_OSSTAT                    WRH$_OSSTAT_1232450071_2690                                  TABLE PARTITION
WRH$_SYS_TIME_MODEL            WRH$_SYS_TIME_MODEL_1232450071_2690                          TABLE PARTITION
WRH$_SERVICE_WAIT_CLASS        WRH$_SERVICE_WAIT_CLASS_1232450071_2690                      TABLE PARTITION
WRH$_EVENT_HISTOGRAM           WRH$_EVENT_HISTOGRAM_1232450071_2690                         TABLE PARTITION
WRH$_MVPARAMETER               WRH$_MVPARAMETER_1232450071_2690                             TABLE PARTITION
WRH$_CELL_GLOBAL_SUMMARY       WRH$_CELL_GLOBAL_SUMMARY_1232450071_2690                     TABLE PARTITION
WRH$_CELL_DISK_SUMMARY         WRH$_CELL_DISK_SUMMARY_1232450071_2690                       TABLE PARTITION
WRH$_CELL_GLOBAL               WRH$_CELL_GLOBAL_1232450071_2690                             TABLE PARTITION
WRH$_CELL_IOREASON             WRH$_CELL_IOREASON_1232450071_2690                           TABLE PARTITION
WRH$_CELL_DB                   WRH$_CELL_DB_1232450071_2690                                 TABLE PARTITION
WRH$_CELL_OPEN_ALERTS          WRH$_CELL_OPEN_ALERTS_1232450071_2690                        TABLE PARTITION
WRH$_IM_SEG_STAT               WRH$_IM_SEG_STAT_1232450071_2690                             TABLE PARTITION
WRM$_PDB_IN_SNAP               WRM$_PDB_IN_SNAP_1232450071_2690                             TABLE PARTITION
WRH$_CON_SYSMETRIC_HISTORY     WRH$_CON_SYSMETRIC_HISTORY_1232450071_2690                   TABLE PARTITION
WRM$_ACTIVE_PDBS               WRM$_ACTIVE_PDBS_1232450071_2690                             TABLE PARTITION
WRH$_CON_SYSSTAT               WRH$_CON_SYSSTAT_1232450071_2690                             TABLE PARTITION
WRH$_CON_SYSTEM_EVENT          WRH$_CON_SYSTEM_EVENT_1232450071_2690                        TABLE PARTITION
WRH$_PROCESS_WAITTIME          WRH$_PROCESS_WAITTIME_1232450071_2690                        TABLE PARTITION
WRH$_ASM_DISK_STAT_SUMMARY     WRH$_ASM_DISK_STAT_SUMMARY_1232450071_2690                   TABLE PARTITION
WRH$_AWR_TEST_1                WRH$_AWR_TEST_1_1232450071_2690                              TABLE PARTITION
WRH$_SESS_NETWORK              WRH$_SESS_NETWORK_1232450071_2690                            TABLE PARTITION
WRH$_CON_SYS_TIME_MODEL        WRH$_CON_SYS_TIME_MODEL_1232450071_2690                      TABLE PARTITION

43 rows selected.

Elapsed: 00:00:00.01
sys@ORA19C 21:54:08>

发表在 Oracle | 标签为 modify_snapshot_settings | 评论关闭

.hmallox加密mariadb/mysql数据库恢复

发表于 2025 年 1 月 6 日由惜分飞

有客户运行在win机器上的mariadb数据库被勒索加密了,加密扩展名为.hmallox
hmallox

HOW TO BACK FILES.txt文件内容

Hello

Your data has been stolen and encrypted
We will delete the stolen data and help with the recovery of encrypted files after payment has been made

Do not try to change or restore files yourself, this will break them
We provide free decryption for any 3 files up to 3MB in size on our website

How to contact with us:
1) Download and install TOR browser by this link: https://www.torproject.org/download/
2) If TOR blocked in your country and you can't access to the link then use any VPN software
3) Run TOR browser and open the site: wtyafjyxxxxxxxxxxxxxxxxxxxxxxxxljoyuklaad.onion/mallox/privateSignin
4) Copy your private ID in the input field. Your Private key: D7xxxxxxxxxxxxxxx90
5) You will see chat, payment information and we can make free test decryption here

Our blog of leaked companies:

wtyafjyxxxxxxxxxxxxxxxxxxxxxxxxljoyuklaad.onion

If you are unable to contact us through the site, then you can email us: mallox.resurrection@onionmail.org
Waiting for a response via mail can be several days. Do not use it if you have not tried contacting through the site.

通过分析,ibd文件情况尚可
ibd

对于这种情况,对于ibd文件进行分析结合客户提供的字典信息,完美恢复数据,业务直接使用

发表在 MySQL恢复, 勒索恢复 | 标签为 .hmallox勒索恢复, mallox.resurrection@onionmail.org, mariadb勒索恢复, mysql勒索恢复 | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

Oracle read only用户—23ai新特性:只读用户

迁移awr快照数据到自定义表空间

.hmallox加密mariadb/mysql数据库恢复

文章分类

最近发表

分类目录归档：数据库

Oracle read only用户—23ai新特性:只读用户

迁移awr快照数据到自定义表空间

.hmallox加密mariadb/mysql数据库恢复

标签云

文章分类

最近发表