locked
Database data labeling RRS feed

  • Question

  • Hi,

    I'm searching for a solution for the following 'problem':

    I want to design my database in a way, where it is not possible to run a query without a certain parameter in the where clause.

    For example:

    SELECT * FROM files => will not return any results or give an error.

    SELECT * FROM files WHERE company = 'A' => will return the files from company A.

    Is this possible?

    Grtz,

    Ruud
    Thursday, May 27, 2010 1:38 PM

Answers

  • In order to not allow a query to be executed, do not GRANT SELECT ON that TABLE to the user.

    Then allow it by forcing the parameter.

    If the parameter is chooseable by the user, CREATE a stored PROCEDURE that has parameters, does parameter checking, and returns the result of the query.

    If the parameter is set automatically by user (but they cannot change it), CREATE another TABLE that lists the username and their parameters: User_Parameter(User, Parameter, Value, PK(User, Parameter))

    Then, either use the SP method, or CREATE a VIEW that joins the parameter TABLE with and checks USER:

    CREATE VIEW User_File AS SELECT ... FROM User_Parameter, files
    WHERE User_Parameter.User = USER AND User_Parameter.Parameter = 'Company'
    AND files.company = User_Parameter.Value;

    Thursday, May 27, 2010 1:55 PM
    Answerer

All replies

  • Hi,

    AFAIK it is not possible, however you should consider stored procedures with parameters.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Thursday, May 27, 2010 1:46 PM
  • In order to not allow a query to be executed, do not GRANT SELECT ON that TABLE to the user.

    Then allow it by forcing the parameter.

    If the parameter is chooseable by the user, CREATE a stored PROCEDURE that has parameters, does parameter checking, and returns the result of the query.

    If the parameter is set automatically by user (but they cannot change it), CREATE another TABLE that lists the username and their parameters: User_Parameter(User, Parameter, Value, PK(User, Parameter))

    Then, either use the SP method, or CREATE a VIEW that joins the parameter TABLE with and checks USER:

    CREATE VIEW User_File AS SELECT ... FROM User_Parameter, files
    WHERE User_Parameter.User = USER AND User_Parameter.Parameter = 'Company'
    AND files.company = User_Parameter.Value;

    Thursday, May 27, 2010 1:55 PM
    Answerer