Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unhappy Please help, database design....

    I have already written a front end program
    There have many function such as

    (a) maintain customer
    (b) maintain supplier
    (c) maintain staff
    (d) maintain invoice and creadit note

    Now, I am going to writing a security control. Say, I have writing a function that allow administrator to create user to login the system and using the function, however, there have some restriction, for example: user A can using (a) and (b) only, user B can using (b), (c) and (d), user c can using (a), but only allow insert and update, (b) for only export, and (d) for only create, print but cannot void.

    Any idea?

    Using a table to store all function allowed? Or any other comment? Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Please help, database design....

    Have a look at what your DBMS can provide first. For example in Oracle you can do this:

    create role customer_manager;
    grant select, insert, update, delete on customer to customer_manager;
    grant select on supplier to customer_manager;
    ...
    grant customer_manager to utada;
    grant customer_manager to andrewst;

    This protects the data against unauthorised changes, which is the most important thing. Of course, you also want to apply this security in the application, so that users don't waste time trying to use functions that they can't. You would interrogate the data dictionary to see if the user has a role that enables him/her to use the function e.g.:
    PHP Code:
    if user_has_role('customer_manager') or user_has_role('salesman')
    then
      do_something
    ;
    else
      
    message('Insufficient privileges');
    end if; 
    If your DBMS doesn't have this kind of security then you could define your own tables along the same lines. (Note the use of "roles" to easily grant the same access rights to more than one person).

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    Re: Please help, database design....

    I am using SQL 2000 Server. The concept used in Oracle security issueI have though before. um...but I really dont know how to apply in table design.
    Would you please give me more hint?

    Thanks~


    Originally posted by andrewst
    Have a look at what your DBMS can provide first. For example in Oracle you can do this:

    create role customer_manager;
    grant select, insert, update, delete on customer to customer_manager;
    grant select on supplier to customer_manager;
    ...
    grant customer_manager to utada;
    grant customer_manager to andrewst;

    This protects the data against unauthorised changes, which is the most important thing. Of course, you also want to apply this security in the application, so that users don't waste time trying to use functions that they can't. You would interrogate the data dictionary to see if the user has a role that enables him/her to use the function e.g.:
    PHP Code:
    if user_has_role('customer_manager') or user_has_role('salesman')
    then
      do_something
    ;
    else
      
    message('Insufficient privileges');
    end if; 
    If your DBMS doesn't have this kind of security then you could define your own tables along the same lines. (Note the use of "roles" to easily grant the same access rights to more than one person).

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Please help, database design....

    It would be something like this:

    create table role ( role_name varchar2(30) primary key );
    create table user ( user_name varchar2(30) primary key );
    create table user_role( user_name references user, role_name references role, primary key(user_name, role_name) );

    create table function ( function_name varchar2(30) primary key );
    create table role_function ( role_name references role, function_name references function,
    access_type varchar2(10), -- something like 'SELECT', 'INSERT', ...
    primary key (role_name, function_name) );

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Please help, database design....

    That last table needs a correction to allow more than one kind of access per role:

    create table role_function ( role_name references role, function_name references function,
    access_type varchar2(10), -- something like 'SELECT', 'INSERT', ...
    primary key (role_name, function_name, access_type) );

  6. #6
    Join Date
    Mar 2004
    Posts
    3

    Re: Please help, database design....

    Wor~~ andrewst
    That's great ~~ I am very thanks for your help~ man~

    Originally posted by andrewst
    It would be something like this:

    create table role ( role_name varchar2(30) primary key );
    create table user ( user_name varchar2(30) primary key );
    create table user_role( user_name references user, role_name references role, primary key(user_name, role_name) );

    create table function ( function_name varchar2(30) primary key );
    create table role_function ( role_name references role, function_name references function,
    access_type varchar2(10), -- something like 'SELECT', 'INSERT', ...
    primary key (role_name, function_name) );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •