SQL PAR LIST

 --List of Sysadmin

SELECT 'Name' = sp.NAME

    ,sp.is_disabled AS [Is_disabled]

FROM sys.server_role_members rm

    ,sys.server_principals sp

WHERE rm.role_principal_id = SUSER_ID('Sysadmin')

    AND rm.member_principal_id = sp.principal_id;

 --List of role members

 SELECT DP1.name AS DatabaseRoleName,   

    isnull (DP2.name, 'No members') AS DatabaseUserName   

FROM sys.database_role_members AS DRM  

RIGHT OUTER JOIN sys.database_principals AS DP1  

    ON DRM.role_principal_id = DP1.principal_id  

LEFT OUTER JOIN sys.database_principals AS DP2  

    ON DRM.member_principal_id = DP2.principal_id  

WHERE DP1.type = 'R'

--dumps

select * from sys.server_role_members;

select * from sys.server_principals;

select * from  sys.database_role_members;

select * from  sys.database_principals;

How to migrate oracle audit logs to a new tablespace

 There was this situation when our oracle audit log suddenly exploded when one of the users ran a procedure. This caused the system tablespace to grow from 1G to 32G in a span of 8 hours.

This was caused by the audit table is in the system tablespace.

Below are the following steps to move the default audit log to a different tablespace.

Migrate the AUDIT tables to a dedicated tablespace

STEP 1

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME           TABLESPACE_NAME

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

AUD$                 SYSTEM

FGA_LOG$             SYSTEM

STEP 2

CREATE TABLESPACE

CREATE TABLESPACE ORA_AUDIT 

DATAFILE '/logs/oradata/EPWEBP/ora_audit1.dbf' 

SIZE 3G;

   

CREATE TABLESPACE ORA_AUDIT_ARCHIVE 

DATAFILE '/logs/oradata/EPWEBP/audit_archive1.dbf' 

SIZE 3G;

STEP 3

--this moves table FGA_LOG$

Run as system

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'ORA_AUDIT');
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'ORA_AUDIT');
END;
/

STEP 4 ARCHIVE CURRENT AUDIT LOG

CREATE TABLE AUDIT_10272020 TABLESPACE ORA_AUDIT_ARCHIVE as select * from SYS.AUD$;

TRUNCATE TABLE sys.aud$; 

ALTER TABLESPACE

 ALTER DATABASE 

  TEMPFILE '/logs/oradata/EPCHRS/temp01.dbf'

  AUTOEXTEND ON

  NEXT 10M

  MAXSIZE UNLIMITED;

TNS-01189: The listener could not authenticate the user

 This happened to me when I cloned a VM.

[oracle@serpentcustom logs]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 21-OCT-2020 20:19:59

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serpentcustom.ecp.priv)(                                                                             PORT=1521)))

TNS-01189: The listener could not authenticate the user

SOLUTION:

[oracle@serpentcustom admin]$ cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.131.128.78 serpentcustomclone.ecp.priv serpentcustomclone

edit your listener.ora  to the correct host

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = serpentcustomclone.ecp.priv)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

How to check if you are able to connect to PART1i

 portalgun:/dbapps/webapps/reports/Loader>load_ep_delivery_documents.sh

-bash: load_ep_delivery_documents.sh: command not found

portalgun:/dbapps/webapps/reports/Loader>

Create Oracle READ ONLY account

CREATE USER READ_ONLY IDENTIFIED BY "password";

GRANT CREATE SESSION TO READ_ONLY;

SET HEADING OFF

SET PAGES 9999 LINES 400

SPOOL TABLE_GRANTS.sql

SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'

FROM DBA_TABLES

WHERE OWNER IN ('PRESS')

ORDER BY 1;

SELECT 'CREATE SYNONYM RO_CAPEX.'||TABLE_NAME|| ' FOR ' || OWNER || '.' || TABLE_NAME || ';'

FROM DBA_TABLES

WHERE OWNER IN ('PRESS')

ORDER BY 1;

SPOOL OFF

@TABLE_GRANTS.sql

!rm TABLE_GRANTS.sql

Oracle SOX Users

--Password History

select

  name,

   password_date

from

   sys.user$,

   sys.user_history$

where

   user$.user# = user_history$.user#;

   

--Password Expired

   

 select name,type#,ptime,exptime,ltime, lcount from sys.user$ where type#=1;

 

 select name,type#,ptime,exptime from sys.user$ where type#=1 order by exptime;

 

 

--Password history 

 select

  name,

   password_date

from

   sys.user$,

   sys.user_history$

where

name='DAV4635'

and

user$.user# = user_history$.user#;

--show logon failures

select dbusername,client_program_name,terminal,os_username,action_name, unified_audit_policies from unified_audit_trail 

where unified_audit_policies='ORA_LOGON_FAILURES' order by event_timestamp desc ;

 

How to connect mariadb to powerbi

 

Dennis asked me to help in connection POWER BI to Nagios

 

Follow this steps

 

1.       Download MariaDB connector https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.9/  or use the attached one.

2.       Install mariadb

3.       Choose typical >Install>finish

4.       Open ODBC 64 bit

5.       Add> Maria DB ODBC> Finish

6.       Name: Nagios_Test

7.       

8.      

9.       User:ro_chow/dropwater

10.   Click Next until finish

11.   Connect using Power BI

12.   

13.  

 

Have fun!!!

How to create user in mysql

 mysql -u root -p'nagiosxi'

  

  MariaDB [nagiosxi]> create user 'ro_chow'@'%' identified by 'dropwater';

Query OK, 0 rows affected (0.05 sec)

MariaDB [nagiosxi]> grant select on nagiosxi.* to 'ro_chow'@'%';

Query OK, 0 rows affected (0.00 sec)

MariaDB [nagiosxi]> flush privileges;

Type CREATE USER 'troy'@'%' IDENTIFIED BY 'troypassword'; and press Enter

o Type GRANT ALL PRIVILEGES ON *.* TO 'troy'@'%' WITH GRANT OPTION; and press Enter

o Type CREATE USER 'troy'@'localhost' IDENTIFIED BY 'troy'; and press Enter

o Type GRANT ALL PRIVILEGES ON *.* TO 'troy'@'localhost' WITH GRANT OPTION; and press Enter

o Type exit and press Enter

CREATE USER 'tony'@'%' IDENTIFIED BY 'Password'; and press Enter

o Type GRANT ALL PRIVILEGES ON *.* TO 'tony'@'%' WITH GRANT OPTION; and press Enter

o Type CREATE USER 'tony'@'localhost' IDENTIFIED BY Password'; and press Enter

o Type GRANT ALL PRIVILEGES ON *.* TO 'tony'@'localhost' WITH GRANT OPTION; and press Enter

o Type exit and press Enter