OracleNotes

Category: Security

  • Granting privileges on all or multiple objects to user/role

    Granting privileges on all or multiple objects to user/role

    Question:
    I need to grant SELECT, UPDATE privilege on all tables owned by schema XXVMX to users MSUBBU, SMARTIN. Is there a command in Oracle to grant a privilege on all objects in schema to user?

    Answer:
    No such privilege in Oracle. You will have to write a script to grant the privilege on individual objects to the users.

    If you have to repeat the same to more users, it may be better to create a role and grant the privileges to the role. Then assign the role to the users that need the privilege. Thus when new tables are created under XXVMX, the privilege need to be added to the role once only, do not have to do grant to all the individual users.

    Create Role:

    CREATE ROLE XXVMX_UPDATE;

    Grant Role to Users:

    GRANT XXVMX_UPDATE to MSUBBU, SMARTIN;

    SQL Script:

    set pages 0
    set lines 300 trims on feedback on echo off


    spool grants.sql


    SELECT ‘grant select, update on ‘ || owner ||’.’||table_name|| ‘ to XXVMX_UPDATE;’
    FROM dba_tables
    WHERE owner = ‘XXVMX’;


    spool off


    set pages 99 lines 80
    set feedback on echo on

    Execute the script file created to grant the privileges.

    @grants.sql

    You may replace the “dba_tables” in the query with “dba_objects” and change the WHERE clause appropriately to filter different sets of objects…

  • Passwords are Case Sensitive in Oracle11g

    The passwords in Oracle11g are case sensitive. All new users created in the 11g database have case sensitive password by default. For databases upgraded from earlier releases, the passwords are not case sensitive for existing accounts. They become case sensitive when you change password. A new column PASSWORD_VERSIONS is added to DBA_USERS view. A value ‘10G 11G’ in this column indicates that case sensitivity is enforced for the account.
    The PASSWORD column is still available in DBA_USERS view, it is not populated anymore. For external authenticated or global accounts, the PASSWORD column indicates such.
    select username, password_versions, password from dba_users;

USERNAME                       PASSWORD PASSWORD
------------------------------ -------- ---------------
MGMT_VIEW                      10G 11G
SYS                            10G 11G
SYSTEM                         10G 11G
SAMUEL                                  EXTERNAL
BTHOMAS                        10G 11G
ANONYMOUS
XDB                            10G 11G

     

    Disable Case Sensitive Passwords
    Oracle11g has a new parameter to disable the password case sensitivity – SEC_CASE_SENSITIVE_LOGON. This parameter is set to TRUE by default. Change to FALSE for pre-Oracle11g password behavior. This parameter can be changed using ALTER SYSTEM.
    SYSDBA/SYSOPER password
    The password file created using orapwd utility is also case sensitive by default. To make the password for SYS and SYSDBA/SYSOPER case insensitive, include flag ingnorecase=y in the command line.
    $ orapwd file=L11GR1.ora ignorecase=y
Enter password for SYS:

     

    DB Links
    When connecting to an Oracle11g database with default SEC_CASE_SENSITIVE_LOGON using a database link from pre-11g database, make sure the database password in Oracle11g database is set up as ALL UPPERCASE. Pre-Oracle11g databases send password in uppercase for db link connections. For 11g to 11g, the password case must be the same; for 11g to pre-11g database, the password case does not matter.
    To Pre-Oracle11g
    To Oracle11g
    From Pre-Oracle11g
    Case does not matter
    Uppercase
    From Oracle11g
    Case does not matter
    Same case
    Users with default passwords
    Oracle11g has a new very useful view to list the database accounts that have default password – DBA_USERS_WITH_DEFPWD. This view has only one column – USERNAME. By default the Oracle system accounts and example accounts are locked in Oracle11g.
    select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
HR
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
SI_INFORMTN_SCHEMA
WMSYS