Re: [sqlite] Hidding records from the application

[Black, Michael (IS)]

I decided to test this so here's an almost-complete example minus any 
cross-checks on the tables which you should ultimately do.
This implements exclusion rules which is what you've been describing.
You can make them inclusion rules if you just remove the "not" in the select 
statement below, but then you need to do more for the admin user to see all.  
That could actually be a separate select anyways.

sqlite> create table objects (obj int);
sqlite> create table pids (pid int, rule int);
sqlite> create table rules (rule int, obj int);
sqlite>
// Our arbitrary objects that we ultimatately want to access
sqlite> insert into objects values(100);
sqlite> insert into objects values(200);
sqlite> insert into objects values(300);
sqlite> insert into objects values(400);
sqlite>
// No the rules that apply to each pid.  You can actually have multiple rules 
per pid if you need to.
sqlite> insert into pids values (1,0);
sqlite> insert into pids values (2,1);
sqlite> insert into pids values (3,2);
sqlite>
// Now our rules -- zero means no exclusion rules -- e.g. admin privledges
// One row for each object excluded by a rule
sqlite> insert into rules values(0,0);
sqlite> insert into rules values(1,100);
sqlite> insert into rules values(1,200);
sqlite> insert into rules values(2,300);
sqlite> insert into rules values(2,400);
sqlite> select pid,rule from pids where pid=1;
1|0
sqlite> select obj from rules where rule=1;
100
200
sqlite> select obj from rules where rule in (select rule from pids where pid=1);
0
sqlite> select obj from rules where rule in (select rule from pids where pid=2);
100
200
// pid 2 sees just the last two objects in our table
sqlite> select * from objects where obj not in(select obj from rules where rule 
in (select rule from pids where pid=2));
300
400
// Our admin user sees all
sqlite> select * from objects where obj not in(select obj from rules where rule 
in (select rule from pids where pid=1));
100
200
300
400
// User 3 sees the first two objects in our table
sqlite> select * from objects where obj not in(select obj from rules where rule 
in (select rule from pids where pid=3));
100
200

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/17/2011 06:29 AM, Simon Slavin wrote:
> Of course my hacked SQLite library 

You don't have to hack the SQLite library.  Simply create a virtual table
with the table name they expect and filter out rows as appropriate.  The
real table can be in the same database or a completely different one.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4i8CQACgkQmOOfHg372QSw4QCfc9bWDbnbczKrc3xMJPvlnxsS
tKYAoMSUq+fH6GPyJCNrw3DS5HuYMlhU
=ik/S
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Simon Slavin]

On 17 Jul 2011, at 2:00pm, Pavel Ivanov wrote:

>> I can only control the databases and the libsqlite.so.
>> Is everything clear?
> 
> How do you do that, I wonder? Why do you think that applications you
> don't control will always use your libsqlite.so? They may not use
> libsqlite.so at all (compile SQLite sources into application) or use
> whatever library they want even by simple providing their own value of
> LD_LIBRARY_PATH. So what makes you think that you control
> libsqlite.so?

Suppose I wanted to subvert an existing application that kept data in a SQLite 
database, I might not be able to modify the application itself, and might not 
have access to the database file.  It would be really useful to be able to 
replace the app's SQLite library with my own, where I could secretly 
observe/modify the SQL calls made by the application.

Of course my hacked SQLite library would have to be able to tell the difference 
between being called the app that adds interest to bank accounts (which should 
add interest to my special account), but the weekly reports app shouldn't see 
it.  Using the process name would probably help there.

I'm just saying.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Black, Michael (IS)]

If you don't know how to get your rules yet then you don't know how to design a 
solution.



Based on what you've said I see 2 more tables.

create table (pid int, rule int)

create table (rule int, record int)



You can have multiple rules per pid, reuse rules across pids, and records can 
overlap rules.



The your select joins the tables and one select will work for all pids.



Your rules can be exclusive or inclusive depending on what you want.  Or both 
if you add another field to say what the rule is.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of san long [kerneltrap...@gmail.com]
Sent: Sunday, July 17, 2011 1:00 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Hidding records from the application

actually I don't know how to get my rules yet, but let's assume the rules
exist and we can get it from a function.
get_forbidden_ids()

2011/7/17 Simon Slavin 

>
> On 17 Jul 2011, at 4:03am, san long wrote:
>
> > (process name)
>
> Implement this logic in your programming language, or do it by having your
> app consult a table to see what has access to what database.
>
> SQL is a database language.  You put data in and get the same data out
> again.  You don't get different data from the same database depending on
> what you are.  If you don't want all the records in a TABLE, use a 'WHERE'
> clause on your SELECT command.
>
> > "A" (process name) can see all records except rowid 1,2
> > "B"  .except rowid 1,3
> > "C" ..except rowid 4,5
>
>
> You still haven't explained how your application is meant to know what has
> access to which records.  How does it know process C isn't meant to be able
> to see record 4 ?  Is this information stored with record 4, or in a table
> about processes or what ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Pavel Ivanov]

> I can only control the databases and the libsqlite.so.
> Is everything clear?

How do you do that, I wonder? Why do you think that applications you
don't control will always use your libsqlite.so? They may not use
libsqlite.so at all (compile SQLite sources into application) or use
whatever library they want even by simple providing their own value of
LD_LIBRARY_PATH. So what makes you think that you control
libsqlite.so?


Pavel


On Sat, Jul 16, 2011 at 11:03 PM, san long  wrote:
> It seems that my words are still ambiguous, let me make it further clear.
> there are many processes in the system (ie, linux ), they use the sqlite
> databases, I add many rules to allow/forbidden their access to the
> databases, such as:
> "A" (process name) can see all records except rowid 1,2
> "B"  .except rowid 1,3
> "C" ..except rowid 4,5
> and so on. The rule could be updated at any time.
> I can't control the action of "A", "B", "C", they have their own normal SQL
> , so the WHERE clause can't be added except modifying the source code of
> sqlite3_exec() and change the zSql parameter.
> I can only control the databases and the libsqlite.so.
> Is everything clear?
>
>
>
>
> 2011/7/17 Jean-Christophe Deschamps 
>
>>
>> >I want to make things clear. there are some rules in my system, such
>> >as : process whose name is  proc_host can see all the records, and
>> >process whose name is proc_client1 can see all the records except
>> >rowid 1.
>>
>> First, keep away of relying on rowid values since they may change if
>> you don't create an INTEGER primary key yourself.
>>
>> That said, I have a feeling you don't expose all your requirements.
>>
>> Now from what you've told, what I would do is encode the sensitive
>> columns of your "protected" row(s) with any proven standard of your
>> choice.  Share the key with all privileged processes and leave the
>> other processes in the bush.  Eventually, have a boolean column called
>> "secret_row" to sort that out and you're home.
>>
>> Whatever OS you use, you can probably have access to an easy-to-use
>> system call to perform on the fly encryption.  Alternatively you can
>> also code a simple known one yourself (relying on a proven algorithm)
>> in your application.
>>
>> --
>> j...@antichoc.net
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Kevin Martin]

> actually I don't know how to get my rules yet, but let's assume the  
> rules
> exist and we can get it from a function.
> get_forbidden_ids()

I've never done it, so don't know whether it's possible, but you may  
be able to create a virtual table on top of the real table which calls  
get_forbidden_ids() and skips the appropriate rows. I think it could  
be  very difficult to handle the situation where the ids change mid  
query though.

Kevin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[san long]

actually I don't know how to get my rules yet, but let's assume the rules
exist and we can get it from a function.
get_forbidden_ids()

2011/7/17 Simon Slavin 

>
> On 17 Jul 2011, at 4:03am, san long wrote:
>
> > (process name)
>
> Implement this logic in your programming language, or do it by having your
> app consult a table to see what has access to what database.
>
> SQL is a database language.  You put data in and get the same data out
> again.  You don't get different data from the same database depending on
> what you are.  If you don't want all the records in a TABLE, use a 'WHERE'
> clause on your SELECT command.
>
> > "A" (process name) can see all records except rowid 1,2
> > "B"  .except rowid 1,3
> > "C" ..except rowid 4,5
>
>
> You still haven't explained how your application is meant to know what has
> access to which records.  How does it know process C isn't meant to be able
> to see record 4 ?  Is this information stored with record 4, or in a table
> about processes or what ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Simon Slavin]

On 17 Jul 2011, at 4:03am, san long wrote:

> (process name)

Implement this logic in your programming language, or do it by having your app 
consult a table to see what has access to what database.

SQL is a database language.  You put data in and get the same data out again.  
You don't get different data from the same database depending on what you are.  
If you don't want all the records in a TABLE, use a 'WHERE' clause on your 
SELECT command.

> "A" (process name) can see all records except rowid 1,2
> "B"  .except rowid 1,3
> "C" ..except rowid 4,5


You still haven't explained how your application is meant to know what has 
access to which records.  How does it know process C isn't meant to be able to 
see record 4 ?  Is this information stored with record 4, or in a table about 
processes or what ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[san long]

It seems that my words are still ambiguous, let me make it further clear.
there are many processes in the system (ie, linux ), they use the sqlite
databases, I add many rules to allow/forbidden their access to the
databases, such as:
"A" (process name) can see all records except rowid 1,2
"B"  .except rowid 1,3
"C" ..except rowid 4,5
and so on. The rule could be updated at any time.
I can't control the action of "A", "B", "C", they have their own normal SQL
, so the WHERE clause can't be added except modifying the source code of
sqlite3_exec() and change the zSql parameter.
I can only control the databases and the libsqlite.so.
Is everything clear?




2011/7/17 Jean-Christophe Deschamps 

>
> >I want to make things clear. there are some rules in my system, such
> >as : process whose name is  proc_host can see all the records, and
> >process whose name is proc_client1 can see all the records except
> >rowid 1.
>
> First, keep away of relying on rowid values since they may change if
> you don't create an INTEGER primary key yourself.
>
> That said, I have a feeling you don't expose all your requirements.
>
> Now from what you've told, what I would do is encode the sensitive
> columns of your "protected" row(s) with any proven standard of your
> choice.  Share the key with all privileged processes and leave the
> other processes in the bush.  Eventually, have a boolean column called
> "secret_row" to sort that out and you're home.
>
> Whatever OS you use, you can probably have access to an easy-to-use
> system call to perform on the fly encryption.  Alternatively you can
> also code a simple known one yourself (relying on a proven algorithm)
> in your application.
>
> --
> j...@antichoc.net
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Jean-Christophe Deschamps]

>I want to make things clear. there are some rules in my system, such
>as : process whose name is  proc_host can see all the records, and
>process whose name is proc_client1 can see all the records except
>rowid 1.

First, keep away of relying on rowid values since they may change if 
you don't create an INTEGER primary key yourself.

That said, I have a feeling you don't expose all your requirements.

Now from what you've told, what I would do is encode the sensitive 
columns of your "protected" row(s) with any proven standard of your 
choice.  Share the key with all privileged processes and leave the 
other processes in the bush.  Eventually, have a boolean column called 
"secret_row" to sort that out and you're home.

Whatever OS you use, you can probably have access to an easy-to-use 
system call to perform on the fly encryption.  Alternatively you can 
also code a simple known one yourself (relying on a proven algorithm) 
in your application.

--
j...@antichoc.net  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Simon Slavin]

On 16 Jul 2011, at 5:18pm, san long wrote:

> I want to make things clear. there are some rules in my system, such
> as : process whose name is  proc_host can see all the records, and
> process whose name is proc_client1 can see all the records except
> rowid 1.
> It looks like temporary view and temporary table is good solutions,
> which one is better and which one could be more efficient?

None of the above.  You seem to have ignored solutions that depend just on 
normal programming and how SQL works.

Put some logic in your code so that the code running in proc_client1 ignores 
row 1.

Or make two tables: one with the records that everyone can see, and the other 
with records that client1 can't see.

Or create a column in your table called 'shouldClient1IgnoreThis' and fill it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Stephan Wehner]

On Fri, Jul 15, 2011 at 6:19 PM, san long  wrote:
> Dear all,
> I have an idea related to the safety of the records in a table: if it is
> possible to hide some records in a table so the upper user application could
> not see them?
> For example:
> table food has content:
> 1, "food A"
> 2, "food B"
> I want to hide the record whose rowid is 2, so:
> sqlite> SELECT  * from food;
> --
> 1, "food A"
> ---
>
> Is there any good solution to satisfy my requirement?


Some queries may depend on the hidden items. For example, table food
has column price.

select sum(price) from food;

-> Is the hidden food's price included?

Other queries may depend on entries not being present.

delete from customers where "customer has not ordered hidden foods"

-> Is the customer record gone?

Can you look further up in your requirements? A different level of the
application
may find it easier (Check before "displaying" any column value whether
user has read-access)

Stephan

> Thanks in advance.
> long san
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[David Bicking]

Is an untrusted end user writing queries?

Are there clients other than proc_host and proc_client1?  If there is a 
proc_cleint2, would it also see all rows except rowid = 1?

If there is only proc_clent1, then create a permanent view of:
  Select * from mytable where mytable.rowid<>1
But I deeply suspect that is not the complete story.

David

On 07/16/2011 12:18 PM, san long wrote:
> Thanks for replies.
> I want to make things clear. there are some rules in my system, such
> as : process whose name is  proc_host can see all the records, and
> process whose name is proc_client1 can see all the records except
> rowid 1.
> It looks like temporary view and temporary table is good solutions,
> which one is better and which one could be more efficient?
>
>
>
> 2011/7/16, David Bicking:
>> I don't know I have much to offer here as I still really don't
>> understand what you are trying to accomplish.
>>
>> But I looked and it appears that sqlite supports TEMPORARY VIEW, which,
>> I believe, is only visible to the process that created it. And it will
>> automatically go away when that process ends.
>>
>> Sqlite doesn't allow you to insert, update or delete records in a view,
>> but you can create INSTEAD OF INSERT/UPDATE/DELETE triggers on the view,
>> which you then have update the underlying real table. If you go with
>> temporary views, you'll have to recreate the triggers each time you
>> recreate the view.
>>
>> In some of your emails you say that hidden records are to be hidden from
>> all processes, yet you seem to not like the idea of deleting them. I am
>> assuming that our statement is really, "some records are to be hidden
>> from all processes AT THIS TIME." In the future, a process may be
>> allowed to see the visible record, and thus you can't just delete it. Am
>> I right in this?
>>
>> Who is creating the queries that you want to hide records from? If you,
>> then just don't include those records in the WHERE clause. If the end
>> user is typing in adhoc queries, than even if you go with using views,
>> you are going to having to make sure they don't figure out the
>> underlying real table name. I've never used it, but I gather that is
>> what the authorizer callback is for.
>>
>> How do you determine which records should be visible to which process?
>> Are you looking at the user information captured in the process? The
>> actual pid, as I understand it, changes each time a program is run, so I
>> don't see you doing a lookup on that number.
>>
>> At any rate, I suspect you will want to create a temporary table in
>> which you will either populate the real table rows that you want to
>> allow or disallow for the given process. You could then create the
>> temporary view as a join between the real table and the temp table. You
>> also need to create the associated triggers for the user in that process
>> to use.
>>
>> But in the end, I am just guessing what you are really trying to do, so
>> I may be off by a mile.
>>
>> David
>>
>>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[san long]

Thanks for replies.
I want to make things clear. there are some rules in my system, such
as : process whose name is  proc_host can see all the records, and
process whose name is proc_client1 can see all the records except
rowid 1.
It looks like temporary view and temporary table is good solutions,
which one is better and which one could be more efficient?



2011/7/16, David Bicking :
> I don't know I have much to offer here as I still really don't
> understand what you are trying to accomplish.
>
> But I looked and it appears that sqlite supports TEMPORARY VIEW, which,
> I believe, is only visible to the process that created it. And it will
> automatically go away when that process ends.
>
> Sqlite doesn't allow you to insert, update or delete records in a view,
> but you can create INSTEAD OF INSERT/UPDATE/DELETE triggers on the view,
> which you then have update the underlying real table. If you go with
> temporary views, you'll have to recreate the triggers each time you
> recreate the view.
>
> In some of your emails you say that hidden records are to be hidden from
> all processes, yet you seem to not like the idea of deleting them. I am
> assuming that our statement is really, "some records are to be hidden
> from all processes AT THIS TIME." In the future, a process may be
> allowed to see the visible record, and thus you can't just delete it. Am
> I right in this?
>
> Who is creating the queries that you want to hide records from? If you,
> then just don't include those records in the WHERE clause. If the end
> user is typing in adhoc queries, than even if you go with using views,
> you are going to having to make sure they don't figure out the
> underlying real table name. I've never used it, but I gather that is
> what the authorizer callback is for.
>
> How do you determine which records should be visible to which process?
> Are you looking at the user information captured in the process? The
> actual pid, as I understand it, changes each time a program is run, so I
> don't see you doing a lookup on that number.
>
> At any rate, I suspect you will want to create a temporary table in
> which you will either populate the real table rows that you want to
> allow or disallow for the given process. You could then create the
> temporary view as a join between the real table and the temp table. You
> also need to create the associated triggers for the user in that process
> to use.
>
> But in the end, I am just guessing what you are really trying to do, so
> I may be off by a mile.
>
> David
>
>
> On 07/16/2011 12:01 AM, san long wrote:
>> sqlite3 support a trigger on SELECT ? View is a good solution, but I
>> want to let different process see different records, like:
>> pid A sees rowid 1,2
>> pid B sees rowid 1,3
>>
>>
>> 2011/7/16, san long:
>>> haha, if I CREATE VIEW in process A and DROP VIEW when A dies. Process
>>> B could see this VIEW or not?
>>> A and B run at the same thime.
>>>
>>> 2011/7/16, Simon Slavin:

 On 16 Jul 2011, at 4:23am, san long wrote:

> Thanks for advice, delete or update the record in a view could affect
> the
> true table in sqlite now?

 No.  Either use the VIEW for SELECT and the TABLE for changes, or
 implement
 triggers so attempts to change your VIEW actually change the underlying
 TABLE.

> And, if I create a view dynamically using sqlite3_exec, is it visible
> to
> other process who opens the same database?

 Yes.  A VIEW is a stored SELECT statement (or it has a similar effect).


 On 16 Jul 2011, at 4:25am, san long wrote:

> right, but now I just want to hide these records to all processes.

 Then DELETE them !

 Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[David Bicking]

I don't know I have much to offer here as I still really don't 
understand what you are trying to accomplish.

But I looked and it appears that sqlite supports TEMPORARY VIEW, which, 
I believe, is only visible to the process that created it. And it will 
automatically go away when that process ends.

Sqlite doesn't allow you to insert, update or delete records in a view, 
but you can create INSTEAD OF INSERT/UPDATE/DELETE triggers on the view, 
which you then have update the underlying real table. If you go with 
temporary views, you'll have to recreate the triggers each time you 
recreate the view.

In some of your emails you say that hidden records are to be hidden from 
all processes, yet you seem to not like the idea of deleting them. I am 
assuming that our statement is really, "some records are to be hidden 
from all processes AT THIS TIME." In the future, a process may be 
allowed to see the visible record, and thus you can't just delete it. Am 
I right in this?

Who is creating the queries that you want to hide records from? If you, 
then just don't include those records in the WHERE clause. If the end 
user is typing in adhoc queries, than even if you go with using views, 
you are going to having to make sure they don't figure out the 
underlying real table name. I've never used it, but I gather that is 
what the authorizer callback is for.

How do you determine which records should be visible to which process? 
Are you looking at the user information captured in the process? The 
actual pid, as I understand it, changes each time a program is run, so I 
don't see you doing a lookup on that number.

At any rate, I suspect you will want to create a temporary table in 
which you will either populate the real table rows that you want to 
allow or disallow for the given process. You could then create the 
temporary view as a join between the real table and the temp table. You 
also need to create the associated triggers for the user in that process 
to use.

But in the end, I am just guessing what you are really trying to do, so 
I may be off by a mile.

David


On 07/16/2011 12:01 AM, san long wrote:
> sqlite3 support a trigger on SELECT ? View is a good solution, but I
> want to let different process see different records, like:
> pid A sees rowid 1,2
> pid B sees rowid 1,3
>
>
> 2011/7/16, san long:
>> haha, if I CREATE VIEW in process A and DROP VIEW when A dies. Process
>> B could see this VIEW or not?
>> A and B run at the same thime.
>>
>> 2011/7/16, Simon Slavin:
>>>
>>> On 16 Jul 2011, at 4:23am, san long wrote:
>>>
 Thanks for advice, delete or update the record in a view could affect
 the
 true table in sqlite now?
>>>
>>> No.  Either use the VIEW for SELECT and the TABLE for changes, or
>>> implement
>>> triggers so attempts to change your VIEW actually change the underlying
>>> TABLE.
>>>
 And, if I create a view dynamically using sqlite3_exec, is it visible to
 other process who opens the same database?
>>>
>>> Yes.  A VIEW is a stored SELECT statement (or it has a similar effect).
>>>
>>>
>>> On 16 Jul 2011, at 4:25am, san long wrote:
>>>
 right, but now I just want to hide these records to all processes.
>>>
>>> Then DELETE them !
>>>
>>> Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Igor Tandetnik]

san long  wrote:
> right, but now I just want to hide these records to all processes.

Just delete them. That would have the same observable effect.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Igor Tandetnik]

san long  wrote:
> It doesn't matter no application can see the underlying data as long as they
> exist physically. I just want to hide them.

If no application whatsoever can see them, what does it mean for them to exist? 
How can you observe the difference between records that exist but are hidden 
from you, and records that don't exist at all?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Black, Michael (IS)]

If each pid sees a completely separate set of rows then add the pid to each row 
and select by pid.



If 2 pid's can see any of the same records this won't work (i.e. all pid's must 
be mutually exclusive)



No views necessary but you could create a view for each pid if you wanted to 
for some reason (if it made coding simpler but I doubt it).



Adding pid also allows you to clean up the table by deleting all records where 
you don't have an active pid.







Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of san long [kerneltrap...@gmail.com]
Sent: Friday, July 15, 2011 11:01 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Hidding records from the application

sqlite3 support a trigger on SELECT ? View is a good solution, but I
want to let different process see different records, like:
pid A sees rowid 1,2
pid B sees rowid 1,3


2011/7/16, san long :
> haha, if I CREATE VIEW in process A and DROP VIEW when A dies. Process
> B could see this VIEW or not?
> A and B run at the same thime.
>
> 2011/7/16, Simon Slavin :
>>
>> On 16 Jul 2011, at 4:23am, san long wrote:
>>
>>> Thanks for advice, delete or update the record in a view could affect
>>> the
>>> true table in sqlite now?
>>
>> No.  Either use the VIEW for SELECT and the TABLE for changes, or
>> implement
>> triggers so attempts to change your VIEW actually change the underlying
>> TABLE.
>>
>>> And, if I create a view dynamically using sqlite3_exec, is it visible to
>>> other process who opens the same database?
>>
>> Yes.  A VIEW is a stored SELECT statement (or it has a similar effect).
>>
>>
>> On 16 Jul 2011, at 4:25am, san long wrote:
>>
>>> right, but now I just want to hide these records to all processes.
>>
>> Then DELETE them !
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Simon Slavin]

On 16 Jul 2011, at 4:56am, san long wrote:

> haha, if I CREATE VIEW in process A and DROP VIEW when A dies. Process
> B could see this VIEW or not?
> A and B run at the same thime.

Process B can see the VIEW until process A DROPs the VIEW.


On 16 Jul 2011, at 5:01am, san long wrote:

> sqlite3 support a trigger on SELECT ? View is a good solution, but I
> want to let different process see different records, like:
> pid A sees rowid 1,2
> pid B sees rowid 1,3

Is there another process that can see all three rows ?  How are you deciding 
which rows each process can see ?  Is it magic ?  Are there keys stored 
somewhere ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/15/2011 09:01 PM, san long wrote:
> sqlite3 support a trigger on SELECT ? View is a good solution, but I
> want to let different process see different records, like:
> pid A sees rowid 1,2
> pid B sees rowid 1,3

Add a column to the table for the process roles.  Then they do something like:

  select * from table where role='B' ...

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4hE38ACgkQmOOfHg372QS5uACgo3kDXm4pRmEmxe3Vr25Oo8yG
xt4AnjPXz7Fq5iBbCqUIHkGhaoS3R0al
=3xUP
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[san long]

sqlite3 support a trigger on SELECT ? View is a good solution, but I
want to let different process see different records, like:
pid A sees rowid 1,2
pid B sees rowid 1,3


2011/7/16, san long :
> haha, if I CREATE VIEW in process A and DROP VIEW when A dies. Process
> B could see this VIEW or not?
> A and B run at the same thime.
>
> 2011/7/16, Simon Slavin :
>>
>> On 16 Jul 2011, at 4:23am, san long wrote:
>>
>>> Thanks for advice, delete or update the record in a view could affect
>>> the
>>> true table in sqlite now?
>>
>> No.  Either use the VIEW for SELECT and the TABLE for changes, or
>> implement
>> triggers so attempts to change your VIEW actually change the underlying
>> TABLE.
>>
>>> And, if I create a view dynamically using sqlite3_exec, is it visible to
>>> other process who opens the same database?
>>
>> Yes.  A VIEW is a stored SELECT statement (or it has a similar effect).
>>
>>
>> On 16 Jul 2011, at 4:25am, san long wrote:
>>
>>> right, but now I just want to hide these records to all processes.
>>
>> Then DELETE them !
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[san long]

haha, if I CREATE VIEW in process A and DROP VIEW when A dies. Process
B could see this VIEW or not?
A and B run at the same thime.

2011/7/16, Simon Slavin :
>
> On 16 Jul 2011, at 4:23am, san long wrote:
>
>> Thanks for advice, delete or update the record in a view could affect the
>> true table in sqlite now?
>
> No.  Either use the VIEW for SELECT and the TABLE for changes, or implement
> triggers so attempts to change your VIEW actually change the underlying
> TABLE.
>
>> And, if I create a view dynamically using sqlite3_exec, is it visible to
>> other process who opens the same database?
>
> Yes.  A VIEW is a stored SELECT statement (or it has a similar effect).
>
>
> On 16 Jul 2011, at 4:25am, san long wrote:
>
>> right, but now I just want to hide these records to all processes.
>
> Then DELETE them !
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/15/2011 06:19 PM, san long wrote:
> Is there any good solution to satisfy my requirement?

Since you want absolutely nothing to see them, delete them.

Or make triggers to copy data between the real table that contains all the
data and your surrogate table that is missing this data.

Or alter the queries in the applications.

Or make a view.

Or make a virtual table.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4hC4AACgkQmOOfHg372QS5NwCgy/r8zkTpjeuvNtqv9Vy0hmqL
o8sAnje+hWHuWx+/uARNLATY5Gpc3yK2
=+veo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Simon Slavin]

On 16 Jul 2011, at 4:23am, san long wrote:

> Thanks for advice, delete or update the record in a view could affect the
> true table in sqlite now?

No.  Either use the VIEW for SELECT and the TABLE for changes, or implement 
triggers so attempts to change your VIEW actually change the underlying TABLE.

> And, if I create a view dynamically using sqlite3_exec, is it visible to
> other process who opens the same database?

Yes.  A VIEW is a stored SELECT statement (or it has a similar effect).


On 16 Jul 2011, at 4:25am, san long wrote:

> right, but now I just want to hide these records to all processes.

Then DELETE them !

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[san long]

right, but now I just want to hide these records to all processes.

2011/7/16 Igor Tandetnik 

> On 7/15/2011 9:19 PM, san long wrote:
> > Dear all,
> > I have an idea related to the safety of the records in a table: if it is
> > possible to hide some records in a table so the upper user application
> could
> > not see them?
> > For example:
> > table food has content:
> > 1, "food A"
> > 2, "food B"
> > I want to hide the record whose rowid is 2, so:
> > sqlite>  SELECT  * from food;
> > --
> > 1, "food A"
> > ---
>
> How is SQLite supposed to know which application is allowed to see these
> rows and which one isn't? Presumably, *someone* must be able to see
> them, or else you can just delete them and be done with it. What exactly
> makes an application "upper user application" (as opposed to  "lower
> system application", I guess)?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[san long]

Thanks for advice, delete or update the record in a view could affect the
true table in sqlite now?
And, if I create a view dynamically using sqlite3_exec, is it visible to
other process who opens the same database?

2011/7/16 Simon Slavin 

>
> On 16 Jul 2011, at 2:19am, san long wrote:
>
> > I have an idea related to the safety of the records in a table: if it is
> > possible to hide some records in a table so the upper user application
> could
> > not see them?
>
> You could CREATE a VIEW which selected just some rows of a TABLE.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[san long]

Thanks for replies.
It doesn't matter no application can see the underlying data as long as they
exist physically. I just want to hide them.


2011/7/16 Simon Slavin 

>
> On 16 Jul 2011, at 2:32am, Danny wrote:
>
> > In my mainframe days, using IDMS/SQL, I limited user access to table
> data, down to the column level, based upon logged on userid.  This was
> accomplished via database procedures. I'm new to SQLite, so don't know if it
> has any similar capabilities.
>
> SQLite is very 'lite'.  It doesn't even know what users are.  You can find
> the documentation at
>
> http://www.sqlite.org/
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Simon Slavin]

On 16 Jul 2011, at 2:32am, Danny wrote:

> In my mainframe days, using IDMS/SQL, I limited user access to table data, 
> down to the column level, based upon logged on userid.  This was accomplished 
> via database procedures. I'm new to SQLite, so don't know if it has any 
> similar capabilities.

SQLite is very 'lite'.  It doesn't even know what users are.  You can find the 
documentation at

http://www.sqlite.org/

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Danny]

In my mainframe days, using IDMS/SQL, I limited user access to table data, down 
to the column level, based upon logged on userid.  This was accomplished via 
database procedures.  I'm new to SQLite, so don't know if it has any similar 
capabilities.

>
>From: Igor Tandetnik 
>To: sqlite-users@sqlite.org
>Sent: Friday, July 15, 2011 9:24 PM
>Subject: Re: [sqlite] Hidding records from the application
>
>On 7/15/2011 9:19 PM, san long wrote:
>> Dear all,
>> I have an idea related to the safety of the records in a table: if it is
>> possible to hide some records in a table so the upper user application could
>> not see them?
>> For example:
>> table food has content:
>> 1, "food A"
>> 2, "food B"
>> I want to hide the record whose rowid is 2, so:
>> sqlite>  SELECT  * from food;
>> --
>> 1, "food A"
>> ---
>
>How is SQLite supposed to know which application is allowed to see these 
>rows and which one isn't? Presumably, *someone* must be able to see 
>them, or else you can just delete them and be done with it. What exactly 
>makes an application "upper user application" (as opposed to  "lower 
>system application", I guess)?
>-- 
>Igor Tandetnik
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Simon Slavin]

On 16 Jul 2011, at 2:19am, san long wrote:

> I have an idea related to the safety of the records in a table: if it is
> possible to hide some records in a table so the upper user application could
> not see them?

You could CREATE a VIEW which selected just some rows of a TABLE.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[Igor Tandetnik]

On 7/15/2011 9:19 PM, san long wrote:
> Dear all,
> I have an idea related to the safety of the records in a table: if it is
> possible to hide some records in a table so the upper user application could
> not see them?
> For example:
> table food has content:
> 1, "food A"
> 2, "food B"
> I want to hide the record whose rowid is 2, so:
> sqlite>  SELECT  * from food;
> --
> 1, "food A"
> ---

How is SQLite supposed to know which application is allowed to see these 
rows and which one isn't? Presumably, *someone* must be able to see 
them, or else you can just delete them and be done with it. What exactly 
makes an application "upper user application" (as opposed to  "lower 
system application", I guess)?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hidding records from the application

[san long]

Dear all,
I have an idea related to the safety of the records in a table: if it is
possible to hide some records in a table so the upper user application could
not see them?
For example:
table food has content:
1, "food A"
2, "food B"
I want to hide the record whose rowid is 2, so:
sqlite> SELECT  * from food;
--
1, "food A"
---

Is there any good solution to satisfy my requirement?
Thanks in advance.
long san
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users