All security settings concerning authorization are made at the role level, where end users always receive their authorization through roles.
A model for a security and authorization approach is made by using rules that protect the database from being accessed by end-users, using external database tools (such as SQL* Plus) with their ordinary password given to the client application. The reason is to keep the database consistent, and avoid any violation against the software integrity constraints made in the business logic layer of the application.
The security model is based on server authorization concepts and there is no difference between ordinary users and ”external” users from a security and authorization point of view. The server-side authorization methods will decide whether a method is available to a specific user or not.
By solving all security and authorization mechanisms in the server, it is possible and easy to add new clients with strong security control on the business processes for all kind of clients.
Database reads are made by using views, which are granted (SELECT) to a role. These views should be considered as application views, but may also be used from external tools such as MS-Query.
Database writes are made using database packages (PL/SQL). The end-user will in this case not need the write privileges to the database tables. However it is necessary to grant execute rights (GRANT EXECUTE) to the packaged PL/SQL that make the table manipulation through SQL.
It is also important to understand that the Oracle authorization level for procedures in packages is on the package level and not on method level (see below). If a user gets EXECUTE-privileges on a package, no other grants such as table-grants or grants to packages accessed by the first package are needed.