All posts by m.ogun

Active Directory kullanıcılarını bir tablodan SELECT etmek:

Tech, , ,

Oracle veri tabanından domain sunucunuza erişip kullanıcıları görme konusunu detaylı örneklerle anlatan sayısız doküman var.

Bu örnekler genel anlamda DBMS_LDAP paketini kullanarak domain sunucunuza nasıl erişeceğinizi, eriştikten sonra nasıl browse edeceğinizi gösteriyor.

Test etmek için domain sunucunuzun adres ve port bilgilerini edinmelisiniz. Tabii bir de username/password.

Ben bu örneklerde tarif edilen yöntemi kendi tablomu doldurmasını sağlayacak şekilde biraz değiştirdim. Bunu yaparken de neredeyse tamamen Billy~Verreynne ‘in https://community.oracle.com/thread/4064849 adresindeki önerilerini kullandım.

Eklediğim bir kaç küçük şey oldu:

  • ldap_host, ldap_user, ldap_passw gibi değişkenleri plain-text göndermek yerine encrypt etmek istedim.
  • bu örnek attribute_name ve attribute_value isimli iki kolonda alt alta sıralıyordu oysa ben isim, email, departman, title, telefon gibi bilgileri de içeren bir tablom olsun, bu tablo ben her sorguladığımda canlı olarak LDAP üzerinden beslensin istiyordum.

Yaptıklarımsa şunlar;

  • önce objelerimi hazırladım.
    • TLDAPATTR için kullandığım kolonları özelleştirebilirsiniz. Ama tabii bu durumda DBMS_LDAP kullandığımız fonksiyonda da uygun değişiklikler yapmamız gerekecek.
CREATE OR REPLACE TYPE tstrings IS TABLE OF VARCHAR2(4000);
 
CREATE OR REPLACE TYPE tldapattr IS object 
(
attribute_name VARCHAR2(50), 
attribute_value VARCHAR2(4000), 
dispname VARCHAR2(255), 
email VARCHAR2(255), 
department VARCHAR2(255), 
memberof VARCHAR2(255), 
manager VARCHAR2(255), 
extension VARCHAR2(255
);
 
CREATE OR REPLACE TYPE tldapattrlist IS TABLE OF tldapattr;
  • sonra type objemde görmek istediğim kolonları besleyecek şekilde değiştirerek fonksiyonumu create ettim.
CREATE OR REPLACE FUNCTION ldapattr (filter VARCHAR2, attributes tstrings DEFAULT NULL) 
RETURN tldapattrlist pipelined AUTHID definer IS
 
ldap_host    VARCHAR2(256);
ldap_port    VARCHAR2(256) := '389';
ldap_user    VARCHAR2(256); 
ldap_passw   VARCHAR2(256);
ldap_base    VARCHAR2(256) := 'OU=buraya_Organization_Unit_bilgisi_yaz,dc=sizin_domain_controlleriniz,dc=muhtemelen_com'; 
LDAP_ATTR    CONSTANT tstrings := NEW tstrings('displayname' ,'mail', 'department', 'memberof', 'manager','telephonenumber');
 
retVal          INTEGER;  
ldapSession     DBMS_LDAP.SESSION;  
attrList        DBMS_LDAP.string_collection;  
valList         DBMS_LDAP.string_collection;  
ldapMessage     DBMS_LDAP.message;  
berElement      DBMS_LDAP.ber_element;  
ldapTimeout     DBMS_LDAP.timeval;  
 
attrName        VARCHAR2(256);  
attrDisplay     VARCHAR2(4000);  
name            VARCHAR2(256);  
attrNum         INTEGER;  
i               INTEGER; 
 
v_dispname     VARCHAR2(255);
v_email        VARCHAR2(255);  
v_department   VARCHAR2(255);
v_memberof     VARCHAR2(255);
v_manager      VARCHAR2(255);
v_extension    VARCHAR2(255);
BEGIN
SELECT 'foo' INTO ldap_host FROM dual;
SELECT 'goo' INTO ldap_user FROM dual;
SELECT 'hoo' INTO ldap_passw FROM dual;
 
pipe ROW(TLDAPATTR('PARAM.<filter>', filter, v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));  
         DBMS_LDAP.USE_EXCEPTION := TRUE;
 
-- create LDAP session  
         ldapSession := DBMS_LDAP.init(hostname => LDAP_HOST, portnum  => LDAP_PORT);  
         retval := DBMS_LDAP.simple_bind_s(ld=> ldapSession,dn=> LDAP_USER,passwd=> LDAP_PASSW);  
 
-- build a distinct ordered list of attributes  
         i := 0;  
         FOR c IN (SELECT DISTINCT column_value AS ATTR FROM TABLE(NVL(attributes,LDAP_ATTR)) ORDER BY 1) LOOP  
                 i := i + 1;  
                 attrList(i) := c.attr;  
                 attrDisplay := attrDisplay || c.attr || ' ';  
         END LOOP;  
pipe ROW(TLDAPATTR('PARAM:<attribute list>', TRIM(attrDisplay), v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));  
 
-- use supplied filter to search  
        ldapTimeout.seconds := 5;  
        ldapTimeout.useconds := 0;
 
        retval := DBMS_LDAP.search_st(
		ld => ldapSession,
		base => ldap_base,
		scope => DBMS_LDAP.SCOPE_SUBTREE,
		filter => filter,
		attrs => attrList,
		attronly => 0,
		tv => ldapTimeout,
		res => ldapMessage);
 
-- is the search successful?  
        retval := DBMS_LDAP.count_entries(ld => ldapSession, msg => ldapMessage);  
        IF retval = 0 THEN  
pipe ROW(TLDAPATTR('Error', 'No matches found for filter ['||filter||'].',v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));  
retVal := DBMS_LDAP.unbind_s(ld => ldapSession);  
RETURN;  
        END IF;  
-- Get all the entries returned by our search.  
        ldapMessage := DBMS_LDAP.first_entry(ld  => ldapSession, msg => ldapMessage);  
 
        WHILE ldapMessage IS NOT NULL  
        LOOP  
-- Get all the attributes for this entry.  
        attrName := DBMS_LDAP.first_attribute(ld => ldapSession, ldapentry => ldapMessage, ber_elem  => berElement);  
--degerleri her defasinda resetle
v_dispname   := NULL;
v_email      := NULL;
v_department := NULL;
v_memberof   := NULL;
v_manager 	 := NULL;
v_extension  := NULL;
-- output a null row for formatting an empty line between records  
                pipe ROW( NULL );  
-- output DN as unique record identifier  
                pipe ROW(TLDAPATTR('distinguishedName', DBMS_LDAP.get_dn(ldapSession, ldapMessage), v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));   
-- now output attribute name-values found for the DN  
attrNum := 1;
		LOOP  
		EXIT WHEN attrName IS NULL;
		EXIT WHEN attrNum > attrList.COUNT;   
-- Get all the values for this attribute.  
		valList := DBMS_LDAP.get_values (ld => ldapSession, ldapentry => ldapMessage, attr => attrName);
			FOR i IN valList.FIRST .. valList.LAST
			LOOP
				IF attrName    = 'displayName' THEN v_dispname := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'mail' THEN v_email := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'department' THEN v_department := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'memberOf' THEN v_memberof := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'manager' THEN v_manager := SUBSTR(valList(i),1,200);
				ELSIF attrName = 'telephoneNumber' THEN v_extension := SUBSTR(valList(i),1,200);
				END IF ;
			END LOOP values_loop;
name := valList.FIRST;  
WHILE name IS NOT NULL LOOP  
pipe ROW(TLDAPATTR(attrName,valList(name),v_dispname, v_email, v_department, v_memberof, v_manager, v_extension));  
name := valList.Next(name);  
END LOOP;  
 
attrName := DBMS_LDAP.next_attribute(ld => ldapSession, ldapentry => ldapMessage, ber_elem  => berElement);
attrNum := attrNum + 1;  
END LOOP;  
 
ldapMessage := DBMS_LDAP.next_entry(ld  => ldapSession, msg => ldapMessage);  
END LOOP;  
 
-- Disconnect from the LDAP server.  
retVal := DBMS_LDAP.unbind_s(ld => ldapSession);  
RETURN;  
END;

bunları yaptıktan sonra şöyle test edebiliyor olmalısınız:

SELECT * 
FROM TABLE(ldapattr(filter => '(&(objectclass=user) (!(useraccountcontrol=514)))'))

514 pasif kullanıcıları dışarıda bırakmak için. Search stringi kendi ihtiyaçlarınıza göre değiştirebilirsiniz. Son olarak datayı derli toplu görmek için bir VIEW oluşturun.

Böylece a/d kullanıcılarınız, bu kullanıcıların bağlı olduğu yöneticiler, departmanları, masa telefonları gibi bilgilere dilediğiniz zaman gerçek zamanlı olarak erişebilirsiniz.

APEX import. Bad Request?

Tech, , , ,

The request could not be understood by server due to malformed syntax.

I needed to export one of my APEX application page to deploy it on another APEX environment.

But it raised this error: BAD REQUEST, malformed syntax.

It’s not self explanatory. Hard to understand what’s wrong at first glance. I checked .sql file to see if there is an ascii character, a non-unicode string and so on.

Better check your application logs

If you see an error like this and have used WIZARD template, perhaps the easiest work-around is: remove wizard region, export again, then import.

Finally!

APEX Page Validation: a global check for special characters

Tech, , , , , , ,

I’ve been struggling with a long form which has tens of text areas on my APEX env.

At some point I needed to apply some restrictions on input fields. Basically to refrain any unwanted non-unicode characters to be inserted into table.

APEX has great Built-in restrictions and they work like a charm. However, it has only a few options. It didn’t help my case when I had to set a restriction to reject all characters but a-Z letters, all the numbers, spaces AND punctuations.

Perhaps there are easier ways to do that. Actually I’m sure there are easier ways to do that. But here is what I ‘ve done and feel free to use & feedback.

DECLARE
l_query VARCHAR2(100);
l_res clob;
v_out clob;
v_special NUMBER;
CURSOR cur_main IS
SELECT item
FROM (
SELECT item_name item 
FROM APEX_APPLICATION_PAGE_ITEMS 
WHERE application_id = :app_id AND page_id = :app_page_id 
AND LOWER(display_as) LIKE '%text%'
);
BEGIN
v_out:= '';
   FOR rec_main IN cur_main LOOP
   l_query := 'select v('''||NVL(rec_main.item, 'null')||''') from dual';
   EXECUTE IMMEDIATE l_query INTO l_res;
   v_out := v_out||l_res;
   END LOOP;
SELECT COUNT(1) 
INTO v_special 
FROM dual 
WHERE regexp_like(REPLACE(v_out, CHR(13)||CHR(10), '_carriegeReturn_'), 
                 '[^a-zA-Z0-9 .,:;?%&+-_]');
   IF v_special >0 THEN
   RETURN FALSE;
   ELSE
   RETURN TRUE;
  END IF;
END;

let’s unwrap it a bit:

  1. cur_main is the cursor where I get all my text-based input fields on the page.
  2. l_query holds my dynamic sql string.
  3. v_out is concatenated string which consists of each item’s value on the screen for current page. note that it won’t work based on the data from session state.
  4. I simply hate being have to use regular expressions. So I copied a simple syntax and modified for my scenario.

Rest are just true/false conditions.

In my case it’s a Page Validation Process. To pass this validation no rows should be returned. Otherwise it will raise the error message.

Cheers!

Querying Active Directory Through Oracle

Science

davegugg's avatarThe Fisherman DBA

It’s usually easy to tell when a muskie fisherman in another boat sees a good sized fish.  He’ll usually use the trolling motor to slowly move around a small area, picking it apart from different angles with his casts.  He may stay in an area of 100 square yards for a couple hours, trying to find the right presentation and angle to get the follower to bite.  Seeing a big fish follow your bait to the boat is exciting, but if you want to catch more of these followers, you need to be ready to change go at them with something different.  If bucktail gets them to follow lazily, try a jerkbait.  If casting from shallow water brings them in too late, try casting from the deep.

In my previous post, I showed how to query Active Directory from SQL Server.  This was nice and easy to do; since they’re…

View original post 1,288 more words

Getting and setting Apex page item values using $v(), $s(), $v2()

Science

Christoph Ruepprich's avatarChristoph's 2 Oracle Cents

The Apex JavaScript API has some very convenient functions to access the values of page items.

For example, if you wante to access the contents of a text field with JavaScript, would would need to reference it something like this:

$x("P2_TEXT_FIELD").value;

If the item you want to reference is a display only item, then the syntax changes:

x = $("#P2_DISPLAY_ONLY").text();

If you need to set the values of these items, the you need to use the varying syntax as well:

$x("P2_TEXT_FIELD").value = "Hello World!";

or

$("#P2_DISPLAY_ONLY").text("Hello World!");

Dealing with these various syntax constructs can be confusing. Fortunately the Apex JavaScript API makes this process much easier. To get page item values simply use $v(“<item_name>”):

x = $v("P2_TEXT_FIELD");
y = $v("P2_DISPLAY_ONLY");

To set the item values use:

$s("P2_TEXT_FIELD","Hello World!");
$s("P2_DISPLAY_ONLY","Hello World!");

See an example on my demo page.

The $v2() is handy if you need to access multiple items in…

View original post 65 more words

Viewing APEX Mail Log and Queue

Science

RussD's avatarBrainStorage

Run these from APEX SQL Workshop:

SELECT * from APEX_MAIL_LOG ORDER BY LAST_UPDATED_ON DESC;

The next one will normally be empty, unless just prior to a queue purge (every 15 min?) or an error happened, then it will be retried 10X before finally being purged:

SELECT * from APEX_MAIL_QUEUE ORDER BY LAST_UPDATED_ON DESC;

View original post

APEX 5.1 – Interactive Grids – Controlling Allowed Operations

Science

John Scott's avatarJohns Blog

In the last post I showed how easily you can enable an Interactive Grid for editing, however what if you want to only allow the user to edit certain rows?

Lets imagine a scenario – given the standard EMP table, I should only be able to change the salary of Employees who report to me (i.e. I can’t change the salary of an employee if they report to someone else).

So, let’s create an Interactive Grid with the following query –

Now, for the purposes of this example, given the hierarchy of the EMP table –

I’m going to “pretend” to be BLAKE – since BLAKE is the manager of 5 people.

If you look at the properties of the Interactive Grid you’ll see the “Allowed Row Operations Column” setting, this allows us to define a column in the query which will be used to define whether I can Update…

View original post 125 more words