Real Problem !!

[karephul]

I never thoght of it before, but lets see ..

when a person login on any login page what happens ?

1. database is already working and there is a table called users(user, password (encrypted)), we compare username and password and give user the access.

--> Problem: what If I have to define roles on the user, rolese can be defined on the database user rather than a user in a users table, RIGHT !!

that means, the user and password used to login should be database user password, created by CREATE USER by admin if I want to define roles on these users.

--> Problem: If user does not exist, he cannot login into the database, and even if he want to register, how could he register to the database, any clue ??

--> solution: if a person is already a user he will be able to login in, otherwise there will be a database exception so, a person has to register, to register .. our login page should remember a dummy user and password who has access to one table to database in which request could be saved .. and thats all. Next time admin will login and see the request and he will create a user looking at the request .. and hence the person who made a request can LOGIN now,

--> this solves the problem of ROLES, Admin can assign roles to the user he creates !!

---------------------
** IS this the way its done in REAL world ??? **
---------------------

Regards,

 

[SQLSister]

One approach we took is to have an application role for the website or user interface. This is the role which has the rights to the database defined. This role has no direct access to tables, all rights are set on sps only and no dynamic SQL allowed. If you didn't come in through the website, no rights.

The new user creates a login and it is stored. There is then a corresponding table which takes the type of user created and decides which pages that person can access (our menus were dynamic depending on the person's user group). If they aren't in the user group to see the data then they can't even see that the page exists.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[karephul]

Okay, so this is at interface level. What if I want to do the same at the database level ? I Just have one login page and register page, and thats all. Everthing which a user can access have to be at database level.

example: there are 5 tables(say) in a database, and three roles defined in the database,"admin role", "special role","public role".

Step1:
when a user want to register, that user will be connected to database by a "dummy user" which already exists in the database and which will have access to only one table "register user table". so, new user's userName and pwd will be kept in that and will loggoff.

step 2: Administer will login into the database and will CREATE a user according to the requested userName and PWD by the user and put him in some ROLE that he already defined. Now depending on his preveleges in the roles, he/she can view/edit/insert into the tables.

** so, its like .. I do not want a website, but just want to demonstrate/use the roles based security in the database **
This is the initial step I have to take to move further. Everything has to be at database level..

Regards,

 

[SQLSister]

On the other hand you are forcing the new user to wait until the admin gets around to creting him. Most people want login privledges from the second they sign up to be a user.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[karephul]

That is other thing, but thats what I want ..

but, Is this the right way to do this.

If there is a new user, a dummy user should login to the database by default and shold enter the new user's information in the temporary table ?? Is it the way it works ??