Describing security

[oracleSQLdba]

Can I check the dba_users or other views in Oracle 7.3.4.x to show me security. I cannot remember...

Thanks.

 

[SantaMufasa]

Can I check the dba_users or other views in Oracle 7.3.4.x to show me security.

What, exactly, would you see if Oracle showed you security?


Also, I don't know if you have access on your database to the "DBA_..." views.

Here is a "DESCRIBE" of DBA_USERS (for an Oracle 9.2.0.4, containing more data than DBA_USERS in an Oracle 5,6,or 7 db.):

desc dba_users

Name                          Null?    Type
----------------------------- -------- --------------
USERNAME                      NOT NULL VARCHAR2(30)
USER_ID                       NOT NULL NUMBER
PASSWORD                               VARCHAR2(30)
ACCOUNT_STATUS                NOT NULL VARCHAR2(32)
LOCK_DATE                              DATE
EXPIRY_DATE                            DATE
DEFAULT_TABLESPACE            NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE          NOT NULL VARCHAR2(30)
CREATED                       NOT NULL DATE
PROFILE                       NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP            VARCHAR2(30)
EXTERNAL_NAME                          VARCHAR2(4000)

The "PASSWORD" column is an encrypted value for a user's password. Do the above columns appear to give you the results for security that you expected?



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oracleSQLdba]

Hence the term, I cannot remember. I need to check an Oracle 7.3 for roles, etc. but cannot remember (same phrase) how to do so. I'm not interested in 9i. I've got OEM and books out the wazoo for that.

 

[SantaMufasa]

The reason I mentioned that my sample results, above, were for Oracle 9 is because my results show more columns than you would expect to see in "DBA_USERS" from Oracle7.

The following DBA views relating to ROLES exist for Oracle7, as well:

SQL> desc dba_roles
 Name                          Null?    Type
 ----------------------------- -------- ------------
 ROLE                          NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                      VARCHAR2(8)

SQL> desc dba_role_privs
 Name                          Null?    Type
 ----------------------------- -------- ------------
 GRANTEE                                VARCHAR2(30)
 GRANTED_ROLE                  NOT NULL VARCHAR2(30)
 ADMIN_OPTION                           VARCHAR2(3)
 DEFAULT_ROLE                           VARCHAR2(3)

SQL> desc role_role_privs
 Name                          Null?    Type
 ----------------------------- -------- ------------
 ROLE                          NOT NULL VARCHAR2(30)
 GRANTED_ROLE                  NOT NULL VARCHAR2(30)
 ADMIN_OPTION                           VARCHAR2(3)

SQL> desc role_sys_privs
 Name                          Null?    Type
 ----------------------------- -------- ------------
 ROLE                          NOT NULL VARCHAR2(30)
 PRIVILEGE                     NOT NULL VARCHAR2(40)
 ADMIN_OPTION                           VARCHAR2(3)

SQL> desc role_tab_privs
 Name                          Null?    Type
 ----------------------------- -------- ------------
 ROLE                          NOT NULL VARCHAR2(30)
 OWNER                         NOT NULL VARCHAR2(30)
 TABLE_NAME                    NOT NULL VARCHAR2(30)
 COLUMN_NAME                            VARCHAR2(30)
 PRIVILEGE                     NOT NULL VARCHAR2(40)
 GRANTABLE                              VARCHAR2(3)

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oracleSQLdba]

Many thanks for your completeness.