need suggestions and insights

[adshocker]

hi all,

here's what i'm trying to do... on my database, i created a user/schema named AEN.. inside this AEN are tables, views etc... i created a user_tbl in which i want to populate user details.

now on my application, i created a form (Admin Form) that allows AEN to create users in oracle database and when it creates the user, i want the user_tbl to be populated with some of the details like user_id, user_name, account_status, role_name, created_date, last_login_date, last_password_change_date, expiry_date etc.

now i understand the most of these data can come from the dba_users view but i dont want everyone to have access to that view thats why i created my own table which i will allow my users to view and which will contain info that are not sensitive/confidential.

what i did is that i created a stored procedure

create or replace procedure create_user(p_username varchar2, p_password varchar2)
as
begin
execute immediate 'create user ' || p_username || ' identified by ' || p_password || ' default tablespace users temporary tablespace temp';
execute immediate 'grant role to ' || p_username;
end;

on my application are the codes where they save other datas that comes from the form. some of the data i dont know how to get like the expiry_date, last_password_change_date, account_status, role_name and some others.

can anyone suggest another method on how i can go about this?

thanks.

 

[cjard]

youre gonna dig yourself into a very nasty hole, trying to set up oracle users for every user of your app. It's far better to create one oracle user for your app, and manage the user permissions yourself. If you want the headache, then carry on, but it's something i've tenuously avoided in 3+ years of oracle app development now

 

[adshocker]

so its like all users will have the same username and password?

or is it like all users will be using the same connection string then on that schema i will create a users table of my own, where it lists the usernames, etc... of all the users of my application? (if this is the case, i wont be able to use the audting feature of Oracle10g?)

 

[cjard]

Nods.. yup.. You lose some ability to audit some stuff. As I saw, it wasnt that useful vs the complexity of setting up the user roles.. Yep, I usually have a table that holds username/password and flags for what they can and cannot do in my app.. Usually the db level controls are too powerful, too fine grained for the basic control I want to implement