[Oracle] Network ACL

Oracle 11g可利用 ACL(Access Control List)來做外部訪問權限設定.

Principal will have what privileges on what object. --Principal: WHO, Object: WHAT

1. Create the ACL

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
      description => 'WWW ACL',
      principal   => 'SCOTT', --表示權限賦予給哪個用戶 or PUBLIC
      is_grant    => true,
      privilege   => 'connect');

2.Grant privilege to user or Public

  -- Grant connect permission 
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'www.xml',
principal => 'PUBLIC',  --if you want user like 'SCOTT' 
is_grant => true,
privilege => 'connect');
COMMIT;
END;
/

-- grant resolve permission
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'www.xml',
principal => 'PUBLIC',  --if you want user like 'SCOTT' 
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/

3.Assign ACL to network. 分配受該ACL制約的主機以及其他訊息

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'www.xml',
host => '192.168.63.63', -- specify the host IP address 
lower_port => 34,       -- specify the port range lower value (* for all)
upper_port => 63);      -- specify the port range higher value
COMMIT;
END;
/

4.Verified the ACL is created.

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

5.Drop the ACL

BEGIN
   DBMS_NETWORK_ACL_ADMIN.drop_acl ( acl => 'www.xml');
END;

[Oracel ebs]How to create Request

 Reference Source: 

https://subscription.packtpub.com/book/application-development/9781849684842/1/ch01lvl1sec11/creating-request-sets

Test Run Request:

Reports, Request : Run Request

Parameters: 若有參數要輸入, 例如:有p_user_id, p_order_number, Parameters輸入G1234:55667700  (:分隔)

[Oracle EBS]TABLE後墜代表意思

https://bpcblogsoracle.wordpress.com/2012/05/09/oracle-inventory-inventory-organizations-2/

https://erpschools.com/erps/articles/table-suffixes-oracle-apps_all_tl_f_v_vl_s_a

https://www.oracleappsdna.com/2012/02/oracle-tables-with-suffix-_all-_tl-_b-_f-_v-_vl-_s-_a-_avn-and-_acn/

_ALL: 以_ALL 結尾的表表示它存儲與多個組織相關的數據。這樣的表肯定會有一個名為 org_id 的列，它指定記錄所屬的組織。

_TL: 以_TL 結尾的表是一個翻譯表。這樣的表有一個叫做language的列

_B: Tables ending _B are the base tables

_VL: 以 _VL 結尾的表是基於多語言或翻譯表構建的視圖。換句話說，這種視圖將基表數據與轉換錶數據結合起來。如果您希望查看與一種特定語言相關的數據，您應該使用 USERENV(‘LANG’) 設置語言。

_V: Tables ending with _V are views

_S: Tables ending with _S are Sequences

_A, _Avn, _ACn: Tables ending with _A, _Avn, _ACn are audit tables.

* audit tables:審計表是包含行的完整歷史記錄的表。

_F: 使用兩個日期列（Effective_start_Date、Effective_end_date）跟踪數據的表。這些表主要用於 HRMS 和工資模塊。

_AVN and _ACN are Audit Shadow Views (when data was changed, and with what values

(何時更改數據，以及更改了哪些值)

_KFV is the view which contains all the information about Key Flex Fields(KFFs).

_DFV is the view which contains the information about Descriptive Flex Fields(DFFs).

GL: General Ledger 總帳，使存儲會計資訊的中心，接收來自財務和製造模組的會計資訊
GL包括(共6個模組)：
      採購 Purchasing ，
      應付 Account Payable ，
      資產 Fixed Assets，
      應收 Accounts Receivable，
      庫存 Inventory，
      訂單 Order Management

[Oracel ebs]How to create Concurrent Program

Reference source:

https://www.youtube.com/watch?v=Qjjyraip4DM

http://www.bizinsightconsultingblog.com/2009/06/how-to-create-concurrent-program-on.html

 1. what is concurrent?

2.Concurrent Program Type

PL/SQL Store Procedure | PLSQL

Oracle Reports | SQL/PLSQLL

Host | Shell Script

SQL*Loader | SQLLoader Control File

SQL*Plus | SQL/PLSQL

Java Concurrent Program | Java

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

Step:

1. Executable

2. Concurrent Program

3. Attach Concurrent Program with Request Group

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

Create Executable

    Concurrent : Program : Executable

Executable

Enter all the details for Executable

Short Name

Best Practice is Short Name of Executable and concurrent program Schedule be same

Subroutine Name

For example: define executable for store procedure

Create Executable  

    Concurrent : Program : Define

  

Program

Enter the name for CP. Check the enable check box.

Short Name

Enter Name for CP Short Name should be same as short name of executable.(It's Best Practice)

Executable-Name

Select executable name from LOV.

Output-Format

Select the output format for CP.

Parameters

    1.Select the Value Set .

    2.Default Type.

    3.Default value for Set

    4.Default Value.

    

Attach Concurrent Program with Request Group  

Security : Responsibility : Request

After Saving the Concurrent Program, Attach it with required Request Set.

Choose the group where you want to attach CP

Add Concurrent program

[GIT]Azue DevOps SSH公用金鑰:Windows上產生SSH keygen

1. 先安裝Git(https://git-scm.com/downloads)

2. 開啟提示命令字元 或 Windows PoweShell

3. 輸入指令:

>ssh-keygen -t rsa

  3.1 詢問儲存的檔案位置/檔案名稱(預設:C:\User\[username]/.ssh/id_rsa)

  3.2 輸入passphrase(可空白)

4.到產生的位置 開啟id_rsa.pub

5.複製內容至Azue DevOps SSH公用金鑰

MSSQL 還原

--MSSQL 還原
restore DATABASE SBODemoCN  -- 指定 欲還原的資料庫名稱db 
FROM disk='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SAPCN20200221.bak'  -- 設定 資料庫備份檔 .bak 路徑
WITH
move 'SBODemoCN' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SAPCN20200221.mdf',      -- 設定成 MSSQL資料庫存放路徑 (mdf)
move 'SBODemoCN_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SAPCN20200221_log.ldf'  -- 設定Log 存放路徑
,NoRecovery
,REPLACE
GO

--若執行錯誤顯示:無法獲得獨佔存取權，因為資料庫正在使用中。
--先查詢出有哪些連線
USE master;
GO
exec sp_who

--看準哪些spid的dbname欄位在使用我們要還原的DB
--就可以解開這段，直接下 kill [spid欄位]指令殺掉所有使用還原DB的連線就完成了
kill 63;

--若DB顯示正在還原中  恢復狀態
-- 切換資料庫的狀態為：ONLINE。 RESTORE DATABASE [資料庫名稱] WITH RECOVERY GO

[MSSQL]批次刪除table資料

資料來源:https://blog.miniasp.com/post/2007/11/16/SQL-Tips-Delete-All-Data-in-whole-Database

select
    distinct 表格名稱
    into #LISTTABLES
from
    (
    SELECT
        Top 100 PERCENT
        a.TABLE_NAME as 表格名稱
    FROM
        INFORMATION_SCHEMA.TABLES  a
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON ( a.TABLE_NAME=b.TABLE_NAME )
    WHERE
        a.TABLE_TYPE='BASE TABLE'
        and a.TABLE_NAME not in ('sysdiagrams','spt_fallback_db','MSreplication_options','spt_fallback_dev','spt_fallback_usg','spt_monitor')
    ORDER BY
        a.TABLE_NAME, ordinal_position) c;

declare
    @tablename nvarchar(max)

declare icur cursor static for select * from #LISTTABLES
 
OPEN icur

fetch next from icur into @tablename
while(@@FETCH_STATUS=0)
BEGIN


    -- 使用 TRUNCATE 最快，且會將表格中的自動編號欄位歸零
    -- 但若表格有被參考 Foreign Key 的話，會無法使用 TRUNCATE 指令
    exec ('TRUNCATE TABLE ' + @tablename); 

    -- 如果 TRUNCATE 不成功，還可以用 DELETE 刪除所有資料
    exec ('delete from ' + @tablename + ' where 1=1');

    -- 使用 DELETE 不會將自動編號的欄位歸零，使用 DBCC CHECKIDENT 指令歸零
    exec ('DBCC CHECKIDENT(''' + @tablename + ''', RESEED, 0)');

    FETCH NEXT FROM icur INTO @tablename
END

close icur
deallocate icur

drop table #LISTTABLES