GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator

docs: Add Oracle documentation for when DBMS_RANDOM access has been removed from PUBLIC

Open
#1,207 0 comments 0 reactions 1 assignee Claimed by @luispavaogoogle View on GitHub
good first issue priority: p3 type: docs
Dominant language
Python
Stars
524
Forks
171
Avg merge
5d 15h
Merged PRs (30d)
4

Description

I think we could change `docs/samples/oracle/README.md` to an index page and move the content to a BLOB specific page, linked to from the index.

Then we can add a new page (`dbms_random_not_public.md`?) with some of the content below, this content was taken from a reply to a user question):

Regarding the exception:
```
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: "DBMS_RANDOM"."VALUE": invalid identifier
```

By default in Oracle we get the following grant to PUBLIC:
```
SQL> select grantee,owner,table_name,privilege from dba_tab_privs where table_name = 'DBMS_RANDOM';

GRANTEE OWNER TABLE_NAME PRIVILEGE
------------ ------------ --------------- ------------
PUBLIC SYS DBMS_RANDOM EXECUTE
```

And the following public synonym:
```
SQL> select owner,synonym_name,table_owner,table_name from dba_synonyms where table_name = 'DBMS_RANDOM';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------ --------------- ------------ ---------------
PUBLIC DBMS_RANDOM SYS DBMS_RANDOM
```

We can encounter exception `ORA-00904: "DBMS_RANDOM"."VALUE": invalid identifier` when either the public synonym has been removed or the grant to public has been removed (or both). This is not uncommon to see in systems satisfying security audits.

If the execute privilege has been revoked from PUBLIC then a DBA can execute the following command (where DVT_USER is the user for your connection):
```
grant execute on sys.dbms_random to DVT_USER;
```

If the pulic synonym has been removed then a DBA could create a synonym in the DVT_USER schema (where DVT_USER is the user configured in the DVT connection):
```
create synonym DVT_USER.dbms_random for sys.dbms_random;
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.