English

How To: Create an Oracle view on a layer or table that is not owned

Summary

A data owner may want to grant access on their data to another user. The non-data owner may also have a need to create a spatial or non-spatial view of the owner’s data and allow other non-data owners to access the view. Instructions provided describe how to create an Oracle view from a registered ArcSDE layer or non-spatial table. This requires the data owner to grant the SELECT privilege to the non-data owner using the 'with grant option'. Without this additional grant in Oracle, the non-data owner is not able to create the view successfully.

Procedure

To create an Oracle view from ArcSDE registered layers and tables, the data owner needs to grant privileges to the non-owner with the ‘with grant option’ by executing one of the following commands under step 1:

  1. Execute the sdelayer –o grant command to grant the non-data owner access to the ArcSDE layers.

    SYNTAX:
    sdelayer -o grant | -l <table,column> -U <user> -A SELECT
    [-i <service>] [-s <server_name>] [-D <database>]
    -u <Username of data owner> [-p <User_Password of data owner>] [-I] [-q]

    Execute the sdetable –o grant command to grant the non-data owner access to the non-spatial ArcSDE tables.

    SYNTAX:
    sdetable -o grant | -l <table,column> -U <user> -A SELECT
    [-i <service>] [-s <server_name>] [-D <database>]
    -u <Username of data owner> [-p <User_password of data owner>] [-I] [-q]

    With either of these commands, the data owner can grant the non-data owner the SELECT privilege. But the data owner must also specify the '-I' option to give the non-data owner the ability to inherit the privilege to grant privileges to other users. The grant option is included with the granted privilege. For example, if user A grants user B select privileges on a table, the -I option indicates that user A also wants to grant user B the ability to grant other users select privileges on a particular table.

    Once the data owner has granted the non-data owner access to the layers or tables along with the granted option, the non-data owner can create the view by doing the following under step 2:
  2. Create a spatial or non-spatial view using the sdetable -o create_view command line:

    sdetable -o create_view -T <view_name> -t <table1,table2...tablen>
    -c <table_col1,table_col2...table_coln>
    [-a <view_col1,view_col2...view_coln>] [-w <"where_clause">]
    [-i <service>] [-s <server_name>] [-D <database>]
    -u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

Related Information