Skip to content →

Tag: Coding

Raspberry Pi as a toy web server

1. Tell my router to route certain traffic to the Raspberry Pi. For example, I route HTTP and SSH traffic to one of my Pi’s. I disabled password login for  SSH, using public key authentication instead: in /etc/ssh/sshd_config, use the following setting and restart SSH service using `sudo service ssh restart’.

RSAAuthentication yes
PubkeyAuthentication yes

# To disable password authentication:
ChallengeResponseAuthentication no
PasswordAuthentication no
UsePAM no

# To disable root login:
PermitRootLogin no

2. Getting my IP address of the Raspberry Pi. I firstly created a PHP script on my domain to record the IP address in a text file.

<?php                                                                            
                                                                                 
$token = 'secret';                   
                                                                                 
if ($_GET['token'] == $token) {                                                  
    if (!empty($_SERVER['HTTP_CLIENT_IP'])) {                                    
        $ip = $_SERVER['HTTP_CLIENT_IP'];                                        
    } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {                        
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];                                  
    } else {                                                                     
        $ip = $_SERVER['REMOTE_ADDR'];                                           
    }                                                                            
    $fp = fopen('home_ip.txt', 'w');                                             
    fwrite($fp, $ip);                                                            
}                                                                                
                                                                                 
?>

Then tell the Raspberry Pi to report its IP address every 5 minutes, using crontab:

pi@alice ~ $ crontab -l
*/5 * * * * curl daoyuan.li/home_ip.php?token=secret

After a while the IP address is recorded in the text file and updated every 5 minutes.

3. Optionally create a DNS record for the Pi. I use Cloudflare to manage DNS settings by myself, so just add/update an entry in Cloudflare’s settings. I point pi.daoyuan.li to the IP address of one Pi. This can be done automatically in the future.

4. Install Flask on the Pi.

sudo apt-get update
sudo apt-get install python-pip
sudo pip install Flask

5. Install nginx and uwsgi on the Pi.

sudo apt-get install nginx
sudo apt-get install build-essential python python-dev
sudo apt-get install python-virtualenv
sudo pip install uwsgi

6. Set up nginx along with uwsgi and Flask.

mkdir flask && cd $_
virtualenv env
. env/bin/activate
pip install Flask

Edit nginx config:

~/flask $ cat flask_nginx.conf 
server {
    listen      5000;
    server_name localhost;
    charset     utf-8;
    client_max_body_size 75M;

    location / { try_files $uri @flask; }
    location @flask{
        include uwsgi_params;
        uwsgi_pass unix:/home/pi/flask/uwsgi.sock;
    }
}
sudo rm /etc/nginx/sites-enabled/default
sudo ln -s flask_nginx.conf /etc/nginx/sites-enabled/default 
sudo service nginx restart

Edit uwsgi config:

~/flask $ cat flask_uwsgi.ini 
[uwsgi]
#application's base folder
base = /home/pi/flask

#python module to import
app = hello
module = %(app)

home = %(base)/env
pythonpath = %(base)

#socket file's location
socket = /home/pi/flask/uwsgi.sock

#permissions for the socket file
chmod-socket    = 666

#the variable that holds a flask application inside the module imported at line #6
callable = app

#location of log files
logto = /home/pi/flask/uwsgi.log

Create a simple Flask app:

~/flask $ cat hello.py
from flask import Flask
app = Flask(__name__)

@app.route("/")
def hello():
    return "Hello World!"

if __name__ == "__main__":
    app.run()

Start up uwsgi:

uwsgi --ini flask_uwsgi.ini &

7. Done! http://pi.daoyuan.li:5000/

 Update on June 24, 2014:

Getting the external address in step 2 can be done by running this command in Raspberry Pi:

curl ifconfig.me

See: http://www.commandlinefu.com/commands/view/5427/get-your-external-ip-address

Leave a Comment

NumPy’s ndarray indexing

In NumPy a new kind of array is provided: n-dimensional array or ndarray. It’s usually fixed-sized and accepts items of the same type and size. For example, to define a 2×3 matrix:

import numpy as np
a = np.array([[1,2,3,], [4,5,6]], np.int32)

When indexing ndarray, it supports “array indexing” other than single element indexing.  (See http://docs.scipy.org/doc/numpy/user/basics.indexing.html)

It is possible to index arrays with other arrays for the purposes of selecting lists of values out of arrays into new arrays. There are two different ways of accomplishing this. One uses one or more arrays of index values. The other involves giving a boolean array of the proper shape to indicate the values to be selected. Index arrays are a very powerful tool that allow one to avoid looping over individual elements in arrays and thus greatly improve performance.

So you basically can do the following:

a = np.array([1, 2, 3], np.int32)
a[np.array([0, 2])) # Fetch the first the third elements, returns np.array([1, 3])
a[np.array([True, False, True])] # Same as the line above

Besides, when you do equals operation on ndarrays, another ndarray is returned by comparing each element:

a = np.array([1, 2, 3], np.int32)
a == 2 # Returns array([False,  True, False], dtype=bool)
a != 2 # Returns array([ True, False,  True], dtype=bool)
a[a != 2] # Returns a sub array that excludes elements with a value 2, in this case array([1, 3], dtype=int32)
Leave a Comment

mod_wsgi and mod_xsendfile on OS X 10.9 Mavericks

Updated on Nov 4, 2013: The following tricks may still work, however I have found a much easier solution. Simply install Xcode command line developer tools and you should be able to compile source code without issues:

$ xcode-select --install

After upgrading my Mac from 10.8 to 10.9 Mavericks my apache stopped working, so I have to reinstall mod_wsgi and mod_xsendfile. However, tricks are needed to compile and install these mods successfully.

Upgrade Xcode

Upgrade Xcode in App Store. As mentioned by Valerie:

I had to manually upgrade Xcode (after Mavericks upgrade) from the App Store & agree to its license because ./configure hung forever until I did that.

mod_wsgi

For mod_wsgi installation, create a soft link to OSX10.9.xctoolchain:

cd /Applications/Xcode.app/Contents/Developer/Toolchains/
sudo ln -s XcodeDefault.xctoolchain OSX10.9.xctoolchain

Then run configure under mod_wsgi source code directory:

mod_wsgi-3.4$ ./configure

It will generate a Makefile similar as follows:

#  Copyright 2007 GRAHAM DUMPLETON                                               
#                                                                                
#  Licensed under the Apache License, Version 2.0 (the "License");               
#  you may not use this file except in compliance with the License.              
#  You may obtain a copy of the License at                                       
#                                                                                
#      http://www.apache.org/licenses/LICENSE-2.0                                
#                                                                                
#  Unless required by applicable law or agreed to in writing, software           
#  distributed under the License is distributed on an "AS IS" BASIS,             
#  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.      
#  See the License for the specific language governing permissions and           
#  limitations under the License.                                                

APXS = /usr/sbin/apxs                                                            
PYTHON = /usr/local/bin/python                                                   

DESTDIR =                                                                        
LIBEXECDIR = /usr/libexec/apache2                                                

CPPFLAGS =  -I/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/include/python2.7 -DNDEBUG 
CFLAGS =   -Wc,'-arch x86_64'                                                    
LDFLAGS =  -Wl,-F/usr/local/Cellar/python/2.7.5/Frameworks -framework Python -u _PyMac_Error   -arch x86_64
LDLIBS =  -ldl  -framework CoreFoundation                                        

all : mod_wsgi.la                                                                

mod_wsgi.la : mod_wsgi.c                                                         
    $(APXS) -c $(CPPFLAGS) $(CFLAGS) mod_wsgi.c $(LDFLAGS) $(LDLIBS)             

$(DESTDIR)$(LIBEXECDIR) :                                                        
    mkdir -p $@                                                                  

install : all $(DESTDIR)$(LIBEXECDIR)                                            
    $(APXS) -i -S LIBEXECDIR=$(DESTDIR)$(LIBEXECDIR) -n 'mod_wsgi' mod_wsgi.la 

clean :                                                                          
    -rm -rf .libs                                                                
    -rm -f mod_wsgi.o mod_wsgi.la mod_wsgi.lo mod_wsgi.slo mod_wsgi.loT          
    -rm -f config.log config.status                                              
    -rm -rf autom4te.cache                                                       

distclean : clean                                                                
    -rm -f Makefile Makefile.in                                                  

realclean : distclean                                                            
    -rm -f configure

However, this Makefile is not correct and running ‘make’ the compiler will complain something like:

mod_wsgi.c:34:10: fatal error: 'httpd.h' file not found

Prepend the following line to CPPFLAGS value:

-I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include/apr-1 -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include/apache2

Save the Makefile and it will look something like:

#  Copyright 2007 GRAHAM DUMPLETON                                               
#                                                                                
#  Licensed under the Apache License, Version 2.0 (the "License");               
#  you may not use this file except in compliance with the License.              
#  You may obtain a copy of the License at                                       
#                                                                                
#      http://www.apache.org/licenses/LICENSE-2.0                                
#                                                                                
#  Unless required by applicable law or agreed to in writing, software           
#  distributed under the License is distributed on an "AS IS" BASIS,             
#  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.      
#  See the License for the specific language governing permissions and           
#  limitations under the License.                                                

APXS = /usr/sbin/apxs                                                            
PYTHON = /usr/local/bin/python                                                   

DESTDIR =                                                                        
LIBEXECDIR = /usr/libexec/apache2                                                

CPPFLAGS =  -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include/apr-1 -I/Applications/Xcode. app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include/apache2 -I/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/include/python2.7 -DNDEBUG
CFLAGS =   -Wc,'-arch x86_64'                                                             
LDFLAGS =  -Wl,-F/usr/local/Cellar/python/2.7.5/Frameworks -framework Python -u _PyMac_Error   -arch x86_64
LDLIBS =  -ldl  -framework CoreFoundation                                        

all : mod_wsgi.la                                                                

mod_wsgi.la : mod_wsgi.c                                                         
    $(APXS) -c $(CPPFLAGS) $(CFLAGS) mod_wsgi.c $(LDFLAGS) $(LDLIBS)             

$(DESTDIR)$(LIBEXECDIR) :                                                        
    mkdir -p $@                                                                  

install : all $(DESTDIR)$(LIBEXECDIR)                                            
    $(APXS) -i -S LIBEXECDIR=$(DESTDIR)$(LIBEXECDIR) -n 'mod_wsgi' mod_wsgi.la 

clean :                                                                          
    -rm -rf .libs                                                                
    -rm -f mod_wsgi.o mod_wsgi.la mod_wsgi.lo mod_wsgi.slo mod_wsgi.loT          
    -rm -f config.log config.status                                              
    -rm -rf autom4te.cache                                                       

distclean : clean                                                                
    -rm -f Makefile Makefile.in                                                  

realclean : distclean                                                            
    -rm -f configure

Then make && install:

mod_wsgi-3.4$ make
mod_wsgi-3.4$ sudo make install

Modify /etc/apache2/httpd.conf to enable mod_wsgi:

LoadModule wsgi_module libexec/apache2/mod_wsgi.so

mod_xsendfile

Use the following command to compile and install mod_xsendfile:

sudo apxs -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include/apr-1 -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.9.sdk/usr/include/apache2 -cia mod_xsendfile.c

Enabling HTTPS/SSL

After upgrading OS X the apache configuration was reset, but your original config is save to /etc/apache2/httpd.conf.pre-update. I need to enable SSL in httpd.conf again by uncommenting the following line:

# Include /private/etc/apache2/extra/httpd-ssl.conf

Restart Apache and everything should work fine

sudo apachectl restart
3 Comments

Upgrading Tastypie from v0.9.11 to v0.9.12 and above

Here are my notes on how to upgrade Tastypie from version 0.9.11 to 0.9.12, as there’re no release notes for v0.9.12. Release notes for 0.9.13 and above are available here: http://django-tastypie.readthedocs.org/en/latest/release_notes/index.html.

1. override_urls() becomes prepend_urls(), the new name makes more sense since what the function does is to insert customized urls instead of replacing the default urls with your customized urls.

2. obj_* methods accepts different parameters. Before:

def obj_get(self, request=None, **kwargs):
    pass

Now obj_get, obj_create, etc. accepts bundle as the parameter apart from keyword arguments. You can get request object from the bundle:

def obj_get(self, bundle, **kwargs):
    request = bundle.request
    pass

3. apply_authorization_limits is no longer in use and it’s replace with a finer grained authorization mechanism. For example:

from tastypie.authorization import Authorization
from tastypie.exceptions import Unauthorized

class ProgramAuthorization(Authorization):
    def read_list(self, object_list, bundle):
        user = bundle.request.user
        if user.is_authenticated() and user.is_staff:
            return object_list
        else:
            return object_list.filter(is_published=True)

    def read_detail(self, object_list, bundle):
        if bundle.request.user.is_staff or bundle.obj.is_published:
            return True
        raise Unauthorized()

    def create_list(self, object_list, bundle):
        raise Unauthorized()

    def create_detail(self, object_list, bundle):
        if bundle.request.user.is_staff:
            return True
        raise Unauthorized()

    def update_list(self, object_list, bundle):
        raise Unauthorized()

    def update_detail(self, object_list, bundle):
        if bundle.request.user.is_staff:
            return True
        raise Unauthorized()

    def delete_list(self, object_list, bundle):
        raise Unauthorized()

    def delete_detail(self, object_list, bundle):
        raise Unauthorized()

Please note that *_list methods should return a list or raise exceptions; whereas *_detail methods should return boolean values or raise exceptions. For more information, see: http://django-tastypie.readthedocs.org/en/v0.9.12/authorization.html

Hi, the company I’m working for (yabroad.com) is hiring Website Backend and Frontend Developers to our platform team. We are building an open platform for youngsters to travel beyond boarders and we offer youngsters internship, language study, travel and volunteer opportunities. Please contact me if you are interested.

Leave a Comment

Installing mod_xsendfile on OS X Lion

First download mod_xsendfile.c from https://tn123.org/mod_xsendfile/

Then compile and install it:

sudo apxs -cia mod_xsendfile.c

Add the following line to /etc/apache2/httpd.conf at the end of the first block of “Load” statements:

LoadModule xsendfile_module libexec/apache2/mod_xsendfile.so

At last, restart the apache server

sudo apachectl restart

Hi, the company I’m working for (yabroad.com) is hiring Website Backend and Frontend Developers to our platform team. We are building an open platform for youngsters to travel beyond boarders and we offer youngsters internship, language study, travel and volunteer opportunities. Please contact me if you are interested.

Leave a Comment

Giving name to a Finnish baby

Today was a good day, thanks to the following things:

  1. It was nice to talk with a colleague from Ericsson Beijing. I got to know what’s going on in China through someone else’s eyes. Also I know how it looks to work in Beijing and how a different job may feel like. She said working in Operations is quite stressful and may require a lot of night shifts, which is not good for anyone’s health. One of her colleagues in Japan had worked night shifts continuously for 90 days… I feel lucky to work in a job that is less likely to cause health problems, although sometimes I do want a bit more pressure.
  2. During lunch we were chatting and one topic came out when discussing giving names to babies. It turns out that Finns give names to their babies secretly for the first three month after the baby is born. That’s probably due to religious reasons, i.e., the devils can not claim the baby if it doesn’t bear a name. It’s also funny that Finns wait until the baby is born to give it a name, simply because they want to make sure the baby’s image matches the name. Hmm, how can you possibly do that? How do you know how “Jussi” should look like? Well, Finns simply do.
  3. I succeeded to revert my Galaxy Tab back to stock version, there wasn’t any noticeable damage to it. It’s nice to have a working tablet that a brick.
  4. I got a clearer view of the short-term goal of the project I’m currently working on.
  5. There are a lot of spam comments in my website, luckily the Akismet plugin successfully blocked all of them. Akismet is free of charge and it works like magic.
  6. Yesterday I managed to update my WordPress Responsive Theme to the latest version, and managed to fix the “number of comments title wrongly aligned” issue.
Leave a Comment

Migrating JComments to WordPress

I switched back to WordPress from Joomla! after I registered this new domain. After migrated all the posts to WordPress using FG Joomla to WordPress, I noticed the comments have not been transferred. After some digging I didn’t find satisfactory plugin to do that, so I wrote my own PHP script to fulfill this task. The code is also available from GitHub.


13 Comments

码农

最近看系统编程准备面试,顺便找点儿小算法练练手。在面试书上看到写冒泡排序的面试题,心血来潮也来练练手。闭着眼睛敲完代码,编译竟然没有错误,运行了一下结果也竟然是对的。

于是重新打开代码,怀着无比复杂的心情来审视自己的代码,郁闷的是看不懂自己5秒钟之前写的代码。我一边很囧的看着书上给出的代码,一边对着自己的代码检查。迷迷糊糊五分钟过去之后还是看不出来自己的代码是不是冒泡排序……

无奈之下拿出笔在草稿上画图一步一步推,终于验证了自己代码的正确性……30秒写完代码,300+秒来读自己写了什么,确实够囧的,
不知道是大一的时候代码背的太熟了的缘故……

估计以后当了码农会经常出现这种状况吧:噼里啪啦的一顿敲代码,敲完之后对着显示器一顿发愣,TMD刚才都敲了些啥?就像真正的农民种田一样,种久了之后做事情完全不过脑,偶尔思考一下的时候就会感慨:我靠,我这块地为啥要撒这么多种子,用这么多化肥啊。第二天睡觉前恍然大悟:就应该放这么多才对!之前折腾个什么劲儿阿!

Leave a Comment

Multiple Sessions

A linked list of all RUDP sockets is maintained. When rudp_socket() is called, an RUDP socket is created and added to the linked list. An RUDP socket keeps a record of the pees/sessions it talks with. When RUDP receives a packet from an unknown socket address, or when RUDP receives a send packet request to an unknown socket address, a new session is created. And for each session, a linked list of all buffered packets is kept.

Leave a Comment

Session Establishment and Tearing Down

When rudp_sendto() is called, the protocol first check if there exists a session between the sender and receiver. If not, the protocol will try to setup a session by sending RUDP_SYN messages. And the packet the application wants to send will be buffered in the created session. After an RUDP_ACK message is received, the server side socket start sending out packets. Go back N protocol is used to control the sending process. After the protocol receives a rudp_close() signal, it will first check whether there are still active sessions and packets in the sending buffer. If not, the protocol will send out RUDP_FIN messages and after receiving RUDP_ACKs, the session is torn down.

Leave a Comment

Transferring Voice Data

This is done using JMF (Java Media Framework). Voice data is read from a file using `MediaLocator’. It is used as the data source for a `Processor’. The `Processor’ specifies and converts the audio data to a certain format and then output the converted data into a `DataSink’. The `DataSink’ then transfers the stream to its destination address and port.

Leave a Comment

CrazyBus Launch Script

#!/bin/bash

PREFIX=
if [ -L "$0" ]; then
    PREFIX=`readlink -f $0`
    if [ $? -eq 0 ]; then
        PREFIX=`dirname $PREFIX`        
    else 
            PREFIX=`file $0`
            PREFIX=${PREFIX##*symbolic link to }
            PREFIX=`dirname $PREFIX`
    fi
else

PREFIX=`dirname $0`
fi

case $PREFIX in
        /*)
        ;;
        *)
        cd $PREFIX
        PREFIX=`pwd`
        ;;
esac

cp=
for i in $PREFIX/lib/*.jar; do
        cp=$i:$cp
done


$PREFIX/java/bin/java \
-Djava.library.path=$PREFIX/swt -classpath $cp \
org.crazybus.login.Login  &
Leave a Comment

Collage

Collage is a little game that let users to rearrange pictures according to the given order. The main logic is as follows:

void canvasFrame::OnLButtonDown(UINT nFlags, CPoint point) 
{
    CClientDC dc(this);
    xx = yy = -1;

    //判断鼠标点击的位置
    if(point.x > 0 && point.x < 100)
    {

if(point.y > 0 && point.y < 100)
        {
            xx = 0;
            yy = 0;
        }
        if(point.y > 100 && point.y < 200)
        {
            xx = 1;
            yy = 0;
        }
        if(point.y > 200 && point.y < 300)
        {
            xx = 2;
            yy = 0;
        }
    }
    if(point.x > 100 && point.x < 200)
    {
        if(point.y >
0 && point.y < 100)
        {
            xx = 0;
            yy = 1;
        }
        if(point.y > 100 && point.y < 200)
        {
            xx = 1;
            yy = 1;
        }
        if(point.y > 200 && point.y < 300)
        {
            xx = 2;
            yy = 1;
        }
    }
    if(point.x > 200 && point.x < 300)
    {
        if(point.y > 0 && point.y <
100)
        {
            xx = 0;
            yy = 2;
        }
        if(point.y > 100 && point.y < 200)
        {
            xx = 1;
            yy = 2;
        }
        if(point.y > 200 && point.y < 300)
        {
            xx = 2;
            yy = 2;
        }
    }

    int xxx, yyy;
    if(xx != -1 && yy != -1)
    {
        for(int i = 0; i < 3; ++i)
        {

for(int j = 0; j < 3; ++j)
            {
                if(cur[i][j] == 9)
                {
                    xxx = i;
                    yyy = j;
                }
            }
        }
    }

    if(abs(xxx - xx) + abs(yyy - yy) == 1)
    {
        //dc.TextOut(400, 400 ,"hello!");
        //int t = cur[xxx][yyy];
        cur[xxx][yyy] = cur[xx][yy];
        cur[xx][yy] = 9;

    //交换图片位置
        mdc->SelectObject(b);
        dc.BitBlt(a[xx * 3 + yy].x, a[xx * 3 + yy].y, 100, 100, mdc, 0, 0, SRCCOPY);

        switch(cur[xxx][yyy])
        {
            case 1: mdc->SelectObject(p1);break;
            case 2: mdc->SelectObject(p2);break;
            case 3: mdc->SelectObject(p3);break;
            case 4: mdc->SelectObject(p4);break;
            case 5: mdc->SelectObject(p5);break;

    case 6: mdc->SelectObject(p6);break;
            case 7: mdc->SelectObject(p7);break;
            case 8: mdc->SelectObject(p8);break;
        }
        dc.BitBlt(a[xxx * 3 + yyy].x, a[xxx * 3 + yyy].y, 100, 100, mdc, 0, 0, SRCCOPY);

        int f = 0;
        for(int i = 0; i < 3; ++i)
        {
            int flag = 0;
            for(int j = 0; j < 3; ++j)
            {
                if(cur[i][j] != 3 * i + j + 1)

    {
                    flag = 1;
                    break;
                }
            }
            if(flag == 1)
            {
                f = 1;
                break;
            }
        }
        if(f == 0)
        {
            strcpy(result,"恭喜你,你赢了!");
            dc.TextOut(100 , 350 ,result);
        }
    }
    CFrameWnd::OnLButtonDown(nFlags,
    point);
}

 

Leave a Comment

Oracle VPD策略实现行级安全性

VPD 即Virtual Private Databases,提供了对数据库的精密访问控制 (graind access control (FGAC) ),使用VPD,可以在数据记录集定义用户的访问权限。

通过VPD 策略,相当于用户操作数据库中的数据时隐式添加一些条件。比如我们要实现特定角色只能操作数据库表中的特定行的数据,这就可以用 VPD 策略实现。现在以车队长只能操作员工表中的属于他的车队的员工的数据信息为例具体说明:

1. 构造策略函数:

CREATE OR REPLACE FUNCTION "VPD"."FUN_MOTORCADE" (
 p_schema in varchar2, p_table in varchar2
)
return varchar2
as
 l_retstr varchar2(100);
 eid number(5, 0);
 did number(5, 0);
begin
 if lower(user)<> 'admin'
 then
 l_retstr := null;
 for user_rec in
 (
 select eid, did
 from dpmanager
 where loginname = lower(user)
 ) loop
 eid := user_rec.eid;
 did := user_rec.did;
 end loop;
 l_retstr := 'did = ' || did ;--|| 'eid = ' || eid;
 return l_retstr;
 else
 l_retstr := null;
 return l_retstr;
 end if;
end;

2. 授予执行策略函数的权限:

grant execute on FUN_MOTORCADE to public;

3. 向数据库表添加策略:

Begin
Dbms_Rls.Add_Policy(
Object_Schema =>'admin', --数据表(或视图)所在的Schema名称
Object_Name =>'employee', --数据表(或视图)的名称
Policy_Name =>'motorcade_ply', --POLICY的名称,主要用于将来对Policy的管理
Function_Schema =>'vpd', --返回Where子句的函数所在Schema名称
Policy_Function =>'fun_motorcade', --返回Where子句的函数名称
Statement_Types =>'Select,Insert,Update,Delete', --要使用该Policy的DML类型,如'Select,Insert,Update,Delete'
Update_Check =>True, --仅适用于Statement_Type为'Insert,Update',值为'True'或'False'
Enable =>True --是否启用,值为'True'或'False'
);
end;

最开始不明白策略函数的作用,花了好几天上网查资料才知道原来函数的返回值便是你想往表上加的隐式约束。
比如,你向EMP表添加了下面的策略函数:


CREATE OR REPLACE FUNCTION "VPD"."TEST" (
 p_schema in varchar2, p_table in varchar2
)
return varchar2
as
 l_retstr varchar2(100);
begin
  if user = ‘admin’
 then
 l_retstr := ‘1 = 1’;
 else
 l_retstr := ‘1 = 2’;
 end if;
 return l_retstr;
end;

现在,如果你以admin身份登录执行 ‘SELECT * FROM EMP’ , 则实际执行的是 ‘SELECT * FROM EMP WHERE 1 = 1’ , 其中 ‘1 = 1’ 这个条件是策略函数的返回值;如果你以其他身份登录执行 ‘SELECT * FROM EMP’ , 则实际执行的是 ‘SELECT * FROM EMP WHERE 1 = 2’ 。

Leave a Comment