I need to develop a simple HR Oracle/PHP application for test purposes. The details are as follow:
1) 1 user schema (for example HR/HR @ localhost:1521)
2) 1 user table (user_id PK, Name, LastName, Adres, Password)
The table have to be filled with users. The users need to connect with their credentials given in the table.
Roles must be as follow:
1) View own userdata from the user table
2) View the entire user table
3) View, Edit, Delete, Add the user table
My idea is as follow:
Orcl> Create a new user named HR (Connect/Resource)
Orcl> Create a new table names users (userid, name, password. role)
PHP> Database connecetion (OCI connect HR/HR@localhost/orcl:1521)
PHP> oci_bind_by_name userid, password (from table) for the login procedure
PHP> oci_bind_by_name check the role (from table)
PHP> if the role is set the user can execute commands
Are there other ways to make this possible? Use some stored procedures or triggers?
1 user schema - OK
1 user table - not that OK. Do not create "artificial" users and store their credentials (password included) into your table(s) - create as many [u]Oracle[/u users as you need and let Oracle take care about login procedure.
Privileges on "1 user schema" objects should be granted either directly or via roles.