Printable View
I am doing my Oracle course. I created a database with different users. One will be admin, another power user and the last one is the normal user. We must save each user's email, first name, last name in the database. I would like to know if it was possible to associate an Oracle user account to record in an ordinary table? I thought creating a user table that inherits from the user table in Oracle but I do not know if it's a good idea. Also where is this table, what is his name?
Usually we create a table where you will store the username (the same as the Oracle connection) and his email.
The rights are also managed in this table. When your application will connect to the database, it will read information about the user, and I imagine it will use the same login/password as that of Oracle (remote authentication in Oracle, it can be handy).
You can then use the keyword USER in queries, which replace the login at runtime. Example: Select from mytable where something = mytable.column_user USER ==> it will return the rows for this user ...
The system table you mention is "All_users" for anyoneor dba_users if you're DBA.
This is done in web apps, generally, is to have only one user defined on the basis, with access rights read/write on the diagram. You must then run a table of users, in which it operates not only rights but also passwords. The application will then handle all aspects of rights, based on the content of this table. It is possible to encrypt the contents of the column password if needed is more secure, but then galley to manage passwords.
As regards the rights on the tables, if you chose to create the login / password on Oracle for each user, it is better to go through the creation of roles, which give you rights (grant select, insert, update, delete on tablename to rolename), and make a great rolename to use then. It is enough to make a great rolename to use for adding a new user, or revoke rolename from user to take away rights.
To me it's after all a really bad idea. Why? Because if you pass the database for authentication etc, we need a different connection per user. I doubt that database can hold thousands of simultaneous connections. Its not really for that.
As the const said, in the web apps, there is a user with certain rights. But it is equally valid in other application (rich client, mobile etc.)
It is the application that manages itself its connection pool and the sessions they open. Open a connection and remove a session that takes time and resources ... keep as much open
In addition, make managing the rights and roles in the application that allows more flexibility ...
In fact we can be clever by playing with the views of rules
1) V1: create or replace view v1 as select ... where ...
2) grant select, insert ... on v1 to rolename
3) grant rolename to user1, user2 ...
4) create synonym user.thetable for owner.lavuev1
If the view was good idea to play on a column containing rules as by chance the name of the user, it gives bin where colname = USER. Hop, a view that can be put in public synonym. For restrictions on values, vision is what is best, because that is what we want