Oracle Roles Implementation Details

Steps to be taken to set up Oracle Roles for accessing GLAST databases

Request Oracle DBA to create roles in each of the GLAST Oracle instances

• Determine which database will need to use roles (i.e. pipeline, ISOC, glastgen etc.)
• Determine how many roles are needed and how they will be used
• Some Possible Roles:

Grant privileges to the roles on each GLAST instance for access to the individual databases.

• Any database owner can grant access to it's tables to any role
• Need to determine which database and what access each role should have.
• Determine which role(s) will need to access data across database and then coordinate role grants with database owners. For example, web pages that link trending and pipeline data would need to access both ISOC trending https://confluence.slac.stanford.edu/pages/editpage.action?pageId=4784197#
  Wiki Markupand GLASTP processing tables.
• Scripts should be used to written to grant and maintained role privileges.

• SQL example:
  • grant select, insert, update,delete on dataset,datasetgroup to pipelineDevelopers

Request DBA to create oracle accounts that will use the roles to access data

• need an undetermined number of generic accounts to access web database data
• need to determine the number of developers who will need personal oracle accounts

Grant roles to oracle generic and user accounts

• Need to determine which accounts need to use which roles
  • Any database owner can grant role access any oracle generic or user account
  • Each database owner (GLASTP, ISOC etc.) needs to grant roles privileges to appropriate oracle accounts
  • database owners need to coordinated grants to roles with each other .
• Scripts should be used to written to grant and maintained role privileges.
• SQL example:
  • GRANT pipelineReadRole to karen;

Modify existing code to explicitly state owner account of the table(s) being queried

• Currently imbedded SQL statements in applications do not state the table owner of the data. In order for other Oracle accounts to access the data, the SQL statements must include the data table owner
  • for example, SELECT * FROM DATASET needs to be changed to SELECT * FROM GLASTP.DATASET

General Information on Oracle Roles:

Administration of large numbers of objects can be difficult. Roles allow you to localize the administration of objects. Roles are most helpful when large numbers of users will need the same system and object privileges (which we discussed earlier in this chapter).

Think of a role like a database user that nobody uses. You create the role using the create role command. You then grant the role all of the common privileges that users will require to do their work, like the ability to select, insert, update and delete data from various tables.

Once the role is all setup, you only need grant the role to users and all the privileges will be transferred along with that grant. Later, you can add additional privileges to the role if required.

You create a role with the create role command and then grant the role to the user with the grant command as seen in this command:

SQL> Create role select_data_role;

SQL> Grant select on emp, dept, bonus to select_data_role;

You can then grant that role to other users as in this case where we grant the select_data_role to the ROBERT user role. Once this is done, ROBERT will be able to query the EMP, DEPT and BONUS tables in the SCOTT schema:

SQL> GRANT select_data_role TO Robert;

Roles have some limitations. In particular object privilges are granted through roles can not be used when writing PL/SQL code. When writing PL/SQL code, you must have direct grants to the objects in the database that your code is accessing.

If you wish to revoke a role from a user, simply use the revoke command as demonstrated earlier in this chapter:

SQL> REVOKE select_data_role FROM Robert;

excerpt from: Oracle Tips by Burleson Consulting Managing Oracle Roles