Results 1 to 5 of 5

Thread: Roles

  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: Roles

    Hi,

    I have the following two questions which I would like to get your opinion on:

    1) I have a user A with some tables and created a user B. User B should have read-only access for A's tables. There is a single command in Mysql that performs this task:

    GRANT SELECT ON A.* TO B;

    How can I do the same in Oracle?

    I'd like to avoid granting SELECT ANY TABLE privilege to the user B.

    2) Also, I created a role, A_RO (which is supposed to be a read-only role) and granted it SELECT ANY TABLE privilege. When I select everything from the role_sys_privs table I don't see any corresponding privilege for the A_RO role. Which dictionary view can I use in order to view the granted to this role privilege(s)?

    Thanks in advance.
    Sincerely,
    Alla

  2. #2
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    roles

    dba_roles;
    dba_tab_privs
    user_roles;
    user_tab_privs;

    grant select on table_name to ro_role_name;

    role_sys_privs show system privilegs for roles -- you probably didnt set any system privs for your read-only role

  3. #3
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    BTW

    In oracle -- if your ever unsure of what data dict views exist to get info and you know the object your interested in -- just try

    select table_name from dict where table_name like '%ROLE%';
    select table_name from dict where table_name like '%INDEX%';

    etc

    its useful -- but many objects are abbreviated like indexes in user_ind_columns , etc

  4. #4
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40
    also -- to do dynamically for all atbles in a schema

    set heading off
    set pageize 0
    spool grant_ro_role.sql
    select 'grant select on '||table_name ||' to ro_role;' from user_tables;
    spool off
    @grant_ro_role.sql

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    • In other words, there's no statement like the one you use in MySQL. In Oracle, you'll have to grant a privilege table by table (i.e. 100 tables, 100 GRANT SELECT statements)
    • View you are looking for is 'DBA_SYS_PRIVS'
      Code:
      SQL> CREATE ROLE a_ro;
      
      Role created.
      
      SQL> GRANT select any table, drop any table TO a_ro;
      
      Grant succeeded.
      
      SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'A_RO';
      
      GRANTEE                        PRIVILEGE                                ADM
      ------------------------------ ---------------------------------------- ---
      A_RO                           DROP ANY TABLE                           NO
      A_RO                           SELECT ANY TABLE                         NO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •