Authorization Schemes Script & Video Demo

[admin]

Code: Select all

BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE ERPSTUFF_EMPS';
      EXECUTE IMMEDIATE 'DROP TABLE ERPSTUFF_DEPTS';      
  EXCEPTION
      WHEN OTHERS THEN NULL;
  END;
  /

CREATE TABLE  "ERPSTUFF_EMPS" 
   (	"EMPNO" NUMBER(4,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 90000 INCREMENT BY 10 START WITH 8000 NOCACHE NOT NULL ENABLE, 
	"ENAME" VARCHAR2(50) NOT NULL ENABLE, 
	"JOB" VARCHAR2(50), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE NOT NULL ENABLE, 
	"SAL" NUMBER(7,2) NOT NULL ENABLE, 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(4,0) NOT NULL ENABLE, 
	"CREATED_DATE" DATE NOT NULL ENABLE, 
	"CREATED_BY_USER" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	"LAST_UPDATED_DATE" DATE NOT NULL ENABLE, 
	"LAST_UPDATED_BY" VARCHAR2(255 CHAR) NOT NULL ENABLE, 
	 CONSTRAINT "ERPSTUFF_EMPS_PK" PRIMARY KEY ("EMPNO")
  USING INDEX  ENABLE
   )
/

CREATE TABLE  "ERPSTUFF_DEPTS" 
   (	"DEPTNO" NUMBER(4,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9000 INCREMENT BY 10 START WITH 50 NOCACHE NOT NULL ENABLE, 
	"DNAME" VARCHAR2(50), 
	"LOC" VARCHAR2(50), 
	 CONSTRAINT "ERPSTUFF_DEPTS_PK" PRIMARY KEY ("DEPTNO")
  USING INDEX  ENABLE
   )
/

CREATE OR REPLACE FORCE EDITIONABLE VIEW "ERPSTUFF_EMPS_V" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO", "CREATED_DATE", "CREATED_BY_USER", "LAST_UPDATED_DATE", "LAST_UPDATED_BY") AS 
  select EMPNO,
       ENAME,
       JOB,
       (select "ENAME" from "ERPSTUFF_EMPS" x where x."EMPNO" = a."MGR") "MGR",
       HIREDATE,
       SAL,
       COMM,
       (select "DNAME" from "ERPSTUFF_DEPTS" x where x."DEPTNO" = a."DEPTNO") "DEPTNO",
       CREATED_DATE,
       CREATED_BY_USER,
       LAST_UPDATED_DATE,
       LAST_UPDATED_BY
from "ERPSTUFF_EMPS" a
/

ALTER TABLE  "ERPSTUFF_EMPS" ADD CONSTRAINT "ERPSTUFF_EMPS_DEPT_FK" FOREIGN KEY ("DEPTNO")
	  REFERENCES  "ERPSTUFF_DEPTS" ("DEPTNO") ENABLE
/
ALTER TABLE  "ERPSTUFF_EMPS" ADD CONSTRAINT "ERPSTUFF_EMPS_MGR_FK" FOREIGN KEY ("MGR")
	  REFERENCES  "ERPSTUFF_EMPS" ("EMPNO") ENABLE
/
CREATE INDEX  "ERPSTUFF_EMPS_MGR" ON  "ERPSTUFF_EMPS" ("MGR")
/
CREATE INDEX  "ERPSTUFF_EMPS_DEPTNO" ON  "ERPSTUFF_EMPS" ("DEPTNO")
/

CREATE OR REPLACE EDITIONABLE TRIGGER  "ERPSTUFF_EMPS_BIU" 
    before insert or update   
    on ERPSTUFF_EMPS  
    for each row  
begin  
    if inserting then  
        :new.created_date := sysdate;  
        :new.created_by_user := coalesce(sys_context('APEX$SESSION','APP_USER'),user);  
    end if;  
    :new.last_updated_date := sysdate;  
    :new.last_updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);  
end; 
/

BEGIN
    APEX_UTIL.REMOVE_USER(p_user_name => 'MAHRUKH');
    APEX_UTIL.REMOVE_USER(p_user_name => 'SIKANDAR');

    APEX_UTIL.CREATE_USER(
        p_user_name                     => 'MAHRUKH',
        p_first_name                    => 'MAHRUKH',
        p_last_name                     => 'MALIK',
        p_description                   => 'ERPSTUFF YOUTUBE CHANNEL VIDEO',
        p_email_address                 => 'mahrukh.malik@erpstuff.com',
        p_web_password                  => 'password1',
        p_developer_privs               => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
        p_default_schema                => 'WKSP_ERPSTUFF',
        p_change_password_on_first_use  => 'N',
	p_attribute_01                  => 'MANAGER');

    APEX_UTIL.CREATE_USER(
        p_user_name                     => 'SIKANDAR',
        p_first_name                    => 'SIKANDAR',
        p_last_name                     => 'MALIK',
        p_description                   => 'ERPSTUFF YOUTUBE CHANNEL VIDEO',
        p_email_address                 => 'sikandar.malik@erpstuff.com',
        p_web_password                  => 'password1',
        p_developer_privs               => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
        p_default_schema                => 'WKSP_ERPSTUFF',
        p_change_password_on_first_use  => 'N',
	p_attribute_01                  => 'USER');
END;