Granting privileges to the BIS workspace components in Oracle

The geodatabase administrator needs to grant specific privileges to the tables for all users who will be accessing the Bathymetry Information System (BIS) workspace. This can be accomplished by creating database roles and assigning the roles to the individual users.

Nota:

La operación de copiar y pegar los ejemplos puede dar lugar a errores de sintaxis.

Granting permissions

In Oracle SQL Plus or Oracle SQL Developer, grant permissions to the BIS workspace tables through the schema. The sample script shows how to drop the roles, then re-create them and assign permissions.

SET SERVEROUTPUT ON;

DROP ROLE "RLBISEDITOR";
CREATE ROLE "RLBISEDITOR" NOT IDENTIFIED;

DROP ROLE "RLBISVIEWER";
CREATE ROLE "RLBISVIEWER" NOT IDENTIFIED;

spool Roles_bis.sql;

select 'grant select on ' ||owner|| '.' || table_name || ' to RLBISVIEWER;'
from sys.dba_tables where lower(owner) = 'bis' order by table_name;
select 'grant select,insert,update,delete on ' ||owner|| '.' || table_name || ' to RLBISEDITOR;'
from sys.dba_tables where lower(owner) = 'bis' order by table_name;

spool off;
SET SERVEROUTPUT ON;
/
@Roles_bis.sql;
/

Creating an editor user

Users should have their own login names. The example below shows how to create an editor geodatabase user and grant the RLBISEDITOR role to the geodatabase editor user.

CREATE USER BIS_EDITOR PROFILE "DEFAULT" 
	IDENTIFIED BY editor 
	DEFAULT TABLESPACE "USERS" 
	TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "BIS_EDITOR";
GRANT CREATE TABLE TO "BIS_EDITOR";
GRANT "RLBISEDITOR" TO "BIS_EDITOR";  
/*-- RLBISEDITOR role has SELECT, INSERT, UPDATE and DELETE permission on the BIS data (feature classes, tables, etc.) */
ALTER USER BIS_EDITOR QUOTA UNLIMITED ON "GDBLOGFILE";
ALTER USER BIS_EDITOR QUOTA UNLIMITED ON "GDBLOGFILEIDX";

Creating a viewer user

Users should have their own login names. The example below shows how to create a viewer geodatabase user and grant the RLBISVIEWER role to the geodatabase viewer user.

CREATE USER BIS_VIEWER PROFILE "DEFAULT" 
IDENTIFIED BY viewer
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "BIS_VIEWER";
GRANT CREATE TABLE TO "BIS_VIEWER";
GRANT "RLBISVIEWER" TO "BIS_VIEWER";  
/*-- RLBISVIEWER role has SELECT permission on the BIS data (feature classes, tables, etc.) */
ALTER USER BIS_VIEWER QUOTA UNLIMITED ON "GDBLOGFILE";
ALTER USER BIS_VIEWER QUOTA UNLIMITED ON "GDBLOGFILEIDX";