Skip to Main Content
  • Questions
  • (Theoretical) Confusion with roles and public synonyms

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Meenu.

Asked: October 12, 2019 - 7:55 pm UTC

Last updated: October 22, 2019 - 2:39 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi Tom,

Confusion1:-
Suppose I have three user accounts in my database: A, B and C.
'A' user has the privilege to create a role.

Suppose there is a table named 'employee' in schema 'A' and 'A' issues:-

1. create role GiveAccess;
2. grant select on employee to GiveAccess;

As they say "Role is a non-schema object and no user OWNS a role".
My question is:
If first 'A' issues:
grant GiveAccess to 'B' with admin option;
then only 'B' would be able to further grant this role to 'C'.

Doesn't this mean 'A' OWNS GiveAccess role?


------------------------------------------------------------------------------------------------

Confusion2:-
Suppose,
Schema X issues following statements:

1. create table emp (id number, name varchar2(20));
2. create table dept (dept_id number, dept_name varchar2(20));
3. create public synonym emp for dept; /* Please note the synonym name is emp and base table is dept.*/
4. grant select on emp to Y;
5. grant select on dept to Y;


Schema Y has:

6. create table emp (emp_id number, nm varchar2(100), salaray number);
7. select * from emp;

* Statement no. 7 would first search for object in own schema. Therefore would return data of Y.emp table.
* Suppose 'Y' want to access schema X's object and 'Y' issues

8. select * from X.emp;

* What would it display? The data of X.emp or X.dept table?

Please help me understand, how would I be able to access the public synonym?

Thank you in advance!
Regards











and Connor said...

Confusion1
==========
Doesn't this mean 'A' OWNS GiveAccess role?


No, it means A has the rights to *give* the role to others, but not the role itself. No-one "owns" the role, which is why you won't see it in DBA_OBJECTS (or any other dictionary view that has an "OWNER" column)

Confusion 2
=============
Let's give it a go and see what happens!

SQL> create user X identified by X;

User created.

SQL> create user Y identified by Y;

User created.

SQL>
SQL> grant resource, connect, create public synonym to X;

Grant succeeded.

SQL> grant resource, connect, create public synonym to Y;

Grant succeeded.

SQL> alter user X quota 100m on users;

User altered.

SQL> alter user Y quota 100m on users;

User altered.

SQL>
SQL> conn X/X@&db
Connected.

SQL> create table emp (id number, name varchar2(20));

Table created.

SQL> insert into emp values (1,'x emp');

1 row created.

SQL>
SQL> create table dept (dept_id number, dept_name varchar2(20));

Table created.

SQL> insert into dept values (2,'x dept');

1 row created.

SQL>
SQL> create public synonym emp for dept;

Synonym created.

SQL> grant select on emp to Y;

Grant succeeded.

SQL> grant select on dept to Y;

Grant succeeded.

SQL>
SQL> conn Y/Y@&db
Connected.

SQL> create table emp (emp_id number, nm varchar2(100), salaray number);

Table created.

SQL> insert into emp values (3,'y emp',0);

1 row created.

SQL>  select * from emp;

    EMP_ID NM                      SALARAY
---------- -------------------- ----------
         3 y emp                         0

1 row selected.

SQL> select * from X.emp;

        ID NAME
---------- --------------------
         1 x emp

1 row selected.


SQL>


So you can see what is happening. We resolve objects from the "inside" to "outside"

So if I am in schema "S" looking for object "O", I first look for something called "S"."O". That might be a real object or a synonym pointing somewhere else.

If there is no "S"."O", then I'll look for "PUBLIC"."O" (public synonyms are synonyms under a special account called "PUBLIC"). That synonyms "O" can point to (say) a different schema, and even point to another synonym etc etc etc...we'll just keep following the chain.

(Synonyms do not give you *access* to anything - it just lets you *reference* things via a different name)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database