PUBLIC User

Database
Enthusiast

PUBLIC User

All,

I need some information on GRANTS/REVOKE on system user PUBLIC.

I believe if we grant any privilege to PUBLIC, all system wide users get that privileges implicitly.
I also know that this is not the best way of granting access to the RDBMS.

I have a situation, where in I need to revoke permissions from Users which will prohibit from issuing statements like.

GRANT SELECT ON SANDBOX_DB TO PUBLIC; (Assume User ABC executed this)

Can we limit users ability to grant/revoke on PUBLIC.
I wanted to limit this ability of the user for us to have a tighter control on the grants and there by keeping DBC.AllRights table from growing.

When I run

REVOKE ALL ON PUBLIC FROM ABC;

I get error 3707.

Any ideas?

Vinay
3 REPLIES
Enthusiast

Re: PUBLIC User

Vinay,
It should be
REVOKE ALL ON ABC FROM PUBLIC;
Enthusiast

Re: PUBLIC User

I wanted to limit the user to anything on PUBLIC database.
I don't want user ABC to give GRANTS such as

GRANT SELECT ON SANDBOX_DB.TABLE TO PUBLIC;

User may have done this to provide implicit access to SELECT SANDBOX_DB.TABLE data to all system wide users.

I think below revoke statement does not limit user from giving grants.

REVOKE ALL ON ABC FROM PUBLIC;

What do you think?

Vinay
Teradata Employee

Re: PUBLIC User

If the user holds a right WITH GRANT OPTION, he is free to grant that right to ANY database, user, or role. And the creator of a table automatically is given rights WITH GRANT OPTION.